ORACLE EXECUTE IMMEDIATE Ó÷¨
EXECUTE IMMEDIATE ´úÌæÁËÒÔÇ°Oracle8iÖĞDBMS_SQL package°ü.
Ëü½âÎö²¢ÂíÉÏÖ´Ğж¯Ì¬µÄSQLÓï¾ä»ò·ÇÔËĞĞʱ´´½¨µÄPL/SQL¿é.¶¯Ì¬´´½¨ºÍÖ´ĞĞSQLÓï¾äĞÔÄܳ¬Ç°£¬EXECUTE IMMEDIATEµÄÄ¿±êÔÚÓÚ¼õĞ¡ÆóÒµ·ÑÓò¢»ñµÃ½Ï¸ßµÄĞÔÄÜ£¬½ÏÖ®ÒÔÇ°ËüÏ൱ÈİÒ×±àÂë.¾¡¹ÜDBMS_SQLÈÔÈ»¿ÉÓ㬵«ÊÇÍƼöʹÓÃEXECUTE IMMEDIATE,ÒòΪËü»ñµÄÊÕÒæÔÚ°üÖ®ÉÏ¡£
-- ʹÓü¼ÇÉ
1. EXECUTE IMMEDIATE½«²»»áÌá½»Ò»¸öDMLÊÂÎñÖ´ĞĞ£¬Ó¦¸ÃÏÔʽÌá½»
Èç¹ûͨ¹ıEXECUTE IMMEDIATE´¦ÀíDMLÃüÁ
ÄÇôÔÚÍê³ÉÒÔÇ°ĞèÒªÏÔʽÌá½»»òÕß×÷ΪEXECUTE IMMEDIATE×Ô¼ºµÄÒ»²¿·Ö.
Èç¹ûͨ¹ıEXECUTE IMMEDIATE´¦ÀíDDLÃüÁî,ËüÌá½»ËùÓĞÒÔÇ°¸Ä±äµÄÊı¾İ
2. ²»Ö§³Ö·µ»Ø¶àĞеIJéѯ,ÕâÖÖ½»»¥½«ÓÃÁÙʱ±íÀ´´æ´¢¼Ç¼(²ÎÕÕÀı×ÓÈçÏÂ)»òÕßÓÃREF cursors.
3. µ±Ö´ĞĞSQLÓï¾äʱ£¬²»ÒªÓ÷ֺţ¬µ±Ö´ĞĞPL/SQL¿éʱ£¬ÔÚÆäβ²¿Ó÷ֺÅ.
4. ÔÚOracleÊÖ²áÖĞ£¬Î´Ïêϸ¸²¸ÇÕâĞ©¹¦ÄÜ¡£
ÏÂÃæµÄÀı×ÓչʾÁËËùÓĞÓõ½Execute immediateµÄ¿ÉÄÜ·½Ãæ.Ï£ÍûÄܸøÄã´øÀ´·½±ã.
5. ¶ÔÓÚForms¿ª·¢Õß,µ±ÔÚPL/SQL 8.0.6.3.°æ±¾ÖĞ£¬Forms 6i²»ÄÜʹÓô˹¦ÄÜ.
EXECUTE IMMEDIATE -- Ó÷¨Àı×Ó
1. ÔÚPL/SQLÔËĞĞDDLÓï¾ä
Sql´úÂë
begin
execute immediate 'set role all';
end;
2. ¸ø¶¯Ì¬Óï¾ä´«Öµ(USING ×Ó¾ä)
Sql´úÂë
declare
l_depnam varchar2(20) := 'testing';
l_loc varchar2(10) := 'D?i';
begin
execute immediate 'insert into dept vals (:1, :2, :3)'
using 50, l_depnam, l_loc;
commit;
end;
3. ´Ó¶¯Ì¬Óï¾ä¼ìË÷Öµ(INTO×Ó¾ä)
Sql´úÂë
declare
l_cnt varchar2(20);
begin
execute immediate 'select count(1) from emp'
into l_cnt;
dbms_output.put_line(l_cnt);
end;
4. ¶¯Ì¬µ÷ÓÃÀı³Ì.Àı³ÌÖĞÓõ½µÄ°ó¶¨±äÁ¿²ÎÊı±ØĞëÖ¸¶¨²ÎÊıÀàĞÍ.
ü]ÈÏΪINÀàĞÍ,ÆäËüÀàĞͱØĞëÏÔʽָ¶¨
Sql´úÂë
declare
l_routin varchar2(100) := 'gen2161.get_rowcnt';
l_tblnam varchar2(20) := 'emp';
l_cnt number;
l_status varchar2(200);
begin
execute immediate 'begin ' || l_routin || '(:2, :3, :4); end;'
using in l_tblnam, out l_cnt, in out l_status;
