¶¯Ì¬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 ;
¡¡¡¡Ö´Ðдæ
Ïà¹ØÎĵµ£º
½ñÌì½Óµ½¿Í»§µç»°£¬Ëµ²Ù×÷Êý¾ÝÎÞ·¨±£´æ¡£¾¹ý·ÖÎö£¬·¢ÏÖËûµÄÊý¾Ý¿âÒѾÓÐ5G¶àµÄ´óС£¬¶ø×îÖÕ·¢ÏÖÓÐÕűíµÄË÷Òý³ö´íÁË£¬ÓÃDBCC CHECKÒ²ÎÞÁ¦»ØÌì¡£
ÿ´ÎÓÃselect * from ln003082 Óï¾ä²éѯ£¬¶¼±¨ÈçÏ´íÎó£º
·þÎñÆ÷: ÏûÏ¢ 605£¬¼¶±ð 21£¬×´Ì¬ 1£¬ÐÐ 1
ÊÔͼ´ÓÊý¾Ý¿â 'ln_fl0125' ÖÐÌáÈ¡µÄÂß¼ ......
select ÐÕÃû,סַ,ÆÚ³õÓà¶î=isnull(ÆÚ³õÔö¼Ó,0)-isnull(ÆÚ³õ¼õÉÙ,0),±¾ÆÚÔö¼Ó,±¾ÆÚ¼õÉÙ,
±¾ÆÚ½áÓà=(isnull(ÆÚ³õÔö¼Ó,0)-isnull(ÆÚ³õ¼õÉÙ,0)+isnull(±¾ÆÚÔö¼Ó,0)-isnull(±¾ÆÚ¼õÉÙ,0)) from (
select ÐÕÃû,סַ,
ÆÚ³õÔö¼Ó=(select ÆÚ³õÔö¼Ó=sum(Ôö¼Ó»ý·Ö) from b where ·¢ÉúÈÕÆÚ<'2006-5-1' and ¿¨ºÅ=a.¿¨ºÅ),
ÆÚ³õ¼õÉ ......
ÉêÃ÷¡£ÎÄÕ½ö´ú±í¸öÈ˹۵㣬ÓëËùÔÚ¹«Ë¾ÎÞÈκÎÁªÏµ¡£
1. ¸ÅÊö
ÍøÒ³¹ÒÂíÕâ¸ö»°ÌâÏëÀ´´ó¼Ò²¢²»Ä°Éú¡£ÎªÊ²Ã´ÓÐÕâô¶àµÄÍøÒ³ÉÏ´æÔÚ×ÅľÂíÈ¥¹¥»÷ÆÕͨÓû§£¿²»¿É·ñÈÏ£¬Ï൱һ²¿·ÖÍøÒ³Ô±¾¾ÍÊǶñÒâµÄ£ºÍøÒ³µÄ×÷Õß¹ÊÒâÔÚÉÏÃæ·ÅÉÏľÂí£¬È»ºóͨ¹ý¸÷ÖÖÊÖ¶ÎÒýÓÕÓû§È¥ä¯ÀÀ¡£µ«ÊǾø´ó¶àÊý±»¹ÒÂíµÄÍøÒ³Ô±¾ÊÇÕý³ ......
¡¡¡¡¡¡
һֱûÓÐÍæ¹ýͨ¹ýÃüÁîÔÚDOSÏÂÖ´ÐÐoracleÊý¾Ý¿â,ËäÈ»¸ã¿ª·¢2ÄêÁË£¬»òÐíÕâÆªÎÄÕÂдµÃ±È½Ï·ôdz£®µ«»òÐí¶ÔÄÄЩ¸Õ¸ÕѧORACLEµÄÅóÓÑ»¹ÊÇÓÐÒ»¶¨°ïÖúµÄ£®
¡¡¡¡¡¡Í¨¹ýÃüÁîÄܹ»ºÜºÃµÄÖ´ÐдóÅúÁ¿Êý¾Ý½Å±¾£§½Å±¾£®sql£§£®±ÜÃâͨ¹ýPLSQL DeveloperÖ´ÐÐÅúÁ¿Êý¾ÝÔì³ÉµÄËÀ»úÇé¿ö£®
¶þ ......
ת×Ô http://database.ctocio.com.cn/222/9068222.shtml
1¡¢Ã»ÓÐË÷Òý»òÕßûÓÐÓõ½Ë÷Òý(ÕâÊDzéѯÂý×î³£¼ûµÄÎÊÌ⣬ÊdzÌÐòÉè¼ÆµÄȱÏÝ)
¡¡¡¡2¡¢I/OÍÌÍÂÁ¿Ð¡£¬ÐγÉÁËÆ¿¾±Ð§Ó¦¡£
¡¡¡¡3¡¢Ã»Óд´½¨¼ÆËãÁе¼Ö²éѯ²»ÓÅ»¯¡£
¡¡¡¡4¡¢ÄÚ´æ²»×ã
¡¡¡¡5¡¢ÍøÂçËÙ¶ÈÂý
¡¡¡¡6¡¢²éѯ³öµÄÊý¾ÝÁ¿¹ý´ó(¿ÉÒÔ²É ......