¶¯Ì¬SQLÓ뾲̬SQL
Ëùν¾²Ì¬SQLÖ¸ÔÚPL/SQL¿éÖÐʹÓõÄSQLÓï¾äÔÚ±àÒëʱÊÇÃ÷È·µÄ£¬Ö´ÐеÄÊÇÈ·¶¨¶ÔÏó¡£¶ø¶¯Ì¬SQLÊÇÖ¸ÔÚPL/SQL¿é±àÒëʱSQLÓï¾äÊDz»È·¶¨µÄ£¬Èç¸ù¾ÝÓû§ÊäÈëµÄ²ÎÊýµÄ²»Í¬¶øÖ´Ðв»Í¬µÄ²Ù×÷¡£±àÒë³ÌÐò¶Ô¶¯Ì¬Óï¾ä²¿·Ö²»½øÐд¦Àí£¬Ö»ÊÇÔÚ³ÌÐòÔËÐÐʱ¶¯Ì¬µØ´´½¨Óï¾ä¡¢¶ÔÓï¾ä½øÐÐÓï·¨·ÖÎö²¢Ö´ÐиÃÓï¾ä¡£
OracleÖж¯Ì¬SQL¿ÉÒÔͨ¹ý±¾µØ¶¯Ì¬SQLÀ´Ö´ÐУ¬Ò²¿ÉÒÔͨ¹ýDBMS_SQL°üÀ´Ö´ÐС£ÏÂÃæ¾ÍÕâÁ½ÖÖÇé¿ö·Ö±ð½øÐÐ˵Ã÷£º
¡¡¡¡Ò»¡¢±¾µØ¶¯Ì¬SQL
¡¡¡¡±¾µØ¶¯Ì¬SQLÊÇʹÓÃEXECUTE IMMEDIATEÓï¾äÀ´ÊµÏֵġ£
¡¡¡¡1¡¢±¾µØ¶¯Ì¬SQLÖ´ÐÐDDLÓï¾ä£º
¡¡¡¡ÐèÇ󣺸ù¾ÝÓû§ÊäÈëµÄ±íÃû¼°×Ö¶ÎÃûµÈ²ÎÊý¶¯Ì¬½¨±í¡£
create or replace procedure proc_test
(
table_name in varchar2, --±íÃû
field1 in varchar2, --×Ö¶ÎÃû
datatype1 in varchar2, --×Ö¶ÎÀàÐÍ
field2 in varchar2, --×Ö¶ÎÃû
datatype2 in varchar2 --×Ö¶ÎÀàÐÍ
) as
str_sql varchar2(500);
begin
str_sql:=’create table ’||table_name||’(’||field1||’ ’||datatype1||’,’||field2||’ ’||datatype2||’)’;
execute immediate str_sql; --¶¯Ì¬Ö´ÐÐDDLÓï¾ä
exception
when others then
null;
end ;
¡¡¡¡ÒÔÉÏÊDZàÒëͨ¹ýµÄ´æ´¢¹ý³Ì´úÂë¡£ÏÂÃæÖ´Ðд洢¹ý³Ì¶¯Ì¬½¨±í¡£
SQL> execute proc_test(’dinya_test’,’id’,’number(8) not null’,’name’,’varchar2(100)’); http://www.mscto.com
PL/SQL procedure successfully completed
SQL> desc dinya_test;
Name Type Nullable Default Comments
---- ------------- -------- ------- --------
ID NUMBER(8)
NAME VARCHAR2(100) Y
SQL>
¡¡¡¡µ½ÕâÀ¾ÍʵÏÖÁËÎÒÃǵÄÐèÇó£¬Ê¹Óñ¾µØ¶¯Ì¬SQL¸ù¾ÝÓû§ÊäÈëµÄ±íÃû¼°×Ö¶ÎÃû¡¢×Ö¶ÎÀàÐ͵ȲÎÊýÀ´ÊµÏÖ¶¯Ì¬Ö´ÐÐDDLÓï¾ä¡£
¡¡¡¡2¡¢±¾µØ¶¯Ì¬SQLÖ´ÐÐDMLÓï¾ä¡£
¡¡¡¡ÐèÇ󣺽«Óû§ÊäÈëµÄÖµ²åÈëµ½ÉÏÀýÖн¨ºÃµÄdinya_test±íÖС£
create or replace procedure proc_insert
(
id in number, --ÊäÈëÐòºÅ
name in varchar2 --ÊäÈëÐÕÃû
) as
str_sql varchar2(500);
begin
str_sql:=’insert into dinya_test values(:1,:2)’;
execute immediate str_sql using id,name; --¶¯Ì¬Ö´ÐвåÈë²Ù×÷
exception
when others then
null;
end ;
¡¡¡¡Ö´Ðдæ
Ïà¹ØÎĵµ£º
DECLARE @sql varchar(8000)
--ÏÂÃæÊÇɾ³ý15Ììǰ±¸·ÝÎļþµÄ´¦Àí
set @sql='Del E:\Databasebackup\'
@sql=@sql +rtrim(convert(varchar,getdate()-15,112))+'.bak'
Exec master..xp_cmdshell @sql
DECLARE @DelFile Varchar(256)
set ......
ÒýÓÃ×Þ½¨µÄ£º
Cod
SELECT
±íÃû=case when a.colorder=1 then d.name else '' end,
×Ö¶ÎÐòºÅ=a.colorder,
......
select f.AGENTID as 'ID',
f.AGENTNAME as 'ÐÕÃû',
f.COMNAME as '¹«Ë¾¼ò³Æ',
c.REGISTDATE as '×¢²áʱ¼ä',
......
×î½ü×öÏîÄ¿µÄʱºò£¬Óöµ½ÁËÒ»¸öÎÊÌâ¡£ÎÒÖ÷ÒªÊÇ×öÒ»¸öWeb Services¸ø±ðÈËÓõġ£±ðÈË´«Ò»¸öÓû§IDºÅ¹ýÀ´£¬È»ºóÎÒ½«Õâ¸öÓû§µÄËùÓкÃÓѵÄÏÂÔØ¼Ç¼°ü×°³ÉÒ»¸öDataSet·µ»ØÈ¥¡£ ¶ø¸ù¾ÝÓû§IDºÅ»ñÈ¡¸ÃÓû§µÄËùÓкÃÓÑÐÅÏ¢£¬ÔòÊÇͨ¹ýÁíÒ»¸öWeb ServicesµÃµ½µÄ£¬ÕâÀïΪFriendDS¡£
......
ÉêÃ÷¡£ÎÄÕ½ö´ú±í¸öÈ˹۵㣬ÓëËùÔÚ¹«Ë¾ÎÞÈκÎÁªÏµ¡£
1. ¸ÅÊö
ÍøÒ³¹ÒÂíÕâ¸ö»°ÌâÏëÀ´´ó¼Ò²¢²»Ä°Éú¡£ÎªÊ²Ã´ÓÐÕâô¶àµÄÍøÒ³ÉÏ´æÔÚ×ÅľÂíÈ¥¹¥»÷ÆÕͨÓû§£¿²»¿É·ñÈÏ£¬Ï൱һ²¿·ÖÍøÒ³Ô±¾¾ÍÊǶñÒâµÄ£ºÍøÒ³µÄ×÷Õß¹ÊÒâÔÚÉÏÃæ·ÅÉÏľÂí£¬È»ºóͨ¹ý¸÷ÖÖÊÖ¶ÎÒýÓÕÓû§È¥ä¯ÀÀ¡£µ«ÊǾø´ó¶àÊý±»¹ÒÂíµÄÍøÒ³Ô±¾ÊÇÕý³ ......