¡¡¡¡ÓÃREF CURSORʵÏÖBULK¹¦ÄÜ
¡¡¡¡1. ¿ÉÒÔ¼ÓËÙINSERT, UPDATE, DELETEÓï¾äµÄÖ´ÐУ¬Ò²¾ÍÊÇÓÃFORALLÓï¾äÀ´Ìæ´úÑ»·Óï¾ä¡£
¡¡¡¡2. ¼ÓËÙSELECT£¬ÓÃBULK COLLECT INTO À´Ìæ´úINTO¡£
SQL> create table tab2 as select empno ID, ename NAME, sal SALARY from emp where 1=2; create or replace procedure REF_BULK is /* ¶¨Ò帴ÔÓÀàÐÍ */ type empcurtyp is ref cursor; type idlist is table of emp.empno%type; type namelist is table of emp.ename%type; type sallist is table of emp.sal%type; /* ¶¨Òå±äÁ¿ */ emp_cv empcurtyp; ids idlist; names namelist; sals sallist; row_cnt number; begin open emp_cv for select empno, ename, sal from emp; fetch emp_cv BULK COLLECT INTO ids, names, sals; --½«×ֶγÉÅú·ÅÈë±äÁ¿ÖУ¬´Ëʱ±äÁ¿ÊÇÒ»¸ö¼¯ºÏ close emp_cv; for i in ids.first .. ids.last loop dbms_output.put_line(' || ids(i) || ' || names(i) ||' salary=' || sals(i)); end loop; FORALL i IN ids.first .. ids.last insert into tab2 values (ids(i), names(i), sals(i)); commit; select count(*) into row_cnt from tab2; dbms_output.put_line('-----------------------------------'); dbms_output.put_line('The row number of tab2 is ' || row_cnt); end REF_BULK; |
¡¡¡¡3¡¢cursor ºÍ ref cursorµÄÇø±ð
¡¡¡¡´Ó¼¼Êõµ×²ã¿´£¬Á½ÕßÊÇÏàͬµÄ¡£ÆÕͨplsql cursorÔÚ¶¨ÒåʱÊÇ¡°¾²Ì¬¡±µÄ¡£¶øRef cursors¿ÉÒÔ¶¯Ì¬´ò¿ª¡£
¡¡¡¡ÀýÈçÏÂÃæÀý×Ó£º
Declare type rc is ref cursor; cursor c is select * from dual; l_cursor rc; begin if ( to_char(sysdate,'dd') = 30 ) then open l_cursor for 'select * from emp'; elsif ( to_char(sysdate,'dd') = 29 ) then open l_cursor for select * from dept; else open l_cursor for select * from dual; end if; open c; end; / |
¡¡¡¡rc¸ù¾ÝÂß¼¶¯Ì¬´ò¿ª£»¶øÓαêc¶¨ÒåºÃÁËÖ»ÓоÍÎÞ·¨ÐÞ¸ÄÁË¡£
¡¡¡¡ref cursor¿ÉÒÔ·µ»Ø¸ø¿Í»§¶Ë£¬cursorÔò²»ÐС£
¡¡¡¡cursor¿ÉÒÔÊÇÈ«¾ÖµÄglobal £¬ref cursorÔò±ØÐ붨ÒåÔÚ¹ý³Ì»òº¯ÊýÖС£
¡¡¡¡ref cursor¿ÉÒÔÔÚ×Ó³ÌÐò¼ä´«µÝ£¬cursorÔò²»ÐС£
¡¡¡¡cursorÖж¨ÒåµÄ¾²Ì¬sql±Èref cursorЧÂʸߣ¬ËùÒÔref cursorͨ³£ÓÃÔÚ£ºÏò¿Í»§¶Ë·µ»Ø½á¹û¼¯¡£