13. END LOOP;
14. ELSE
15. DBMS_OUTPUT.PUT_LINE('Óû§ÐÅÏ¢£ºÓαêûÓдò¿ª£¡'); 16. END IF;
17. CLOSE emp_cursor; 18. END;
Ö´Ðнá¹ûΪ£º Sql´úÂë
1. 1-SMITH 2. 2-ALLEN 3. 3-WARD
4. PL/SQL ¹ý³ÌÒѳɹ¦Íê³É¡£
˵Ã÷£º±¾ÀýʹÓÃemp_cursor%ISOPENÅжÏÓαêÊÇ·ñ´ò¿ª£»Ê¹ÓÃemp_cursor%ROWCOUNT»ñµÃµ½Ä¿Ç°ÎªÖ¹FETCHÓï¾ä·µ»ØµÄÊý¾ÝÐÐÊý²¢Êä³ö£»Ê¹ÓÃÑ»·À´»ñÈ¡Êý¾Ý£¬ÔÚÑ»·ÌåÖÐʹÓÃFETCHÓï¾ä£»Ê¹ÓÃemp_cursor%NOTFOUNDÅжÏFETCHÓï¾äÊÇ·ñ³É¹¦Ö´ÐУ¬µ±FETCHÓï¾äʧ°Üʱ˵Ã÷Êý¾ÝÒѾȡÍ꣬Í˳öÑ»·¡£
¡¾Á·Ï°1¡¿È¥µôOPEN emp_cursor;Óï¾ä£¬ÖØÐÂÖ´ÐÐÒÔÉϳÌÐò¡£
Óαê²ÎÊýµÄ´«µÝ
¡¾ÑµÁ·1¡¿ ´ø²ÎÊýµÄÓαꡣ Sql´úÂë
1. SET SERVEROUTPUT ON 2. DECLARE
3. V_empno NUMBER(5); 4. V_ename VARCHAR2(10);
5. CURSOR emp_cursor(p_deptno NUMBER, p_job VARCHAR2) IS 6. SELECT empno, ename FROM emp
7. WHERE deptno = p_deptno AND job = p_job; 8. BEGIN
9. OPEN emp_cursor(10, 'CLERK');
10. LOOP
11. FETCH emp_cursor INTO v_empno,v_ename; 12. EXIT WHEN emp_cursor%NOTFOUND;
13. DBMS_OUTPUT.PUT_LINE(v_empno||','||v_ename); 14. END LOOP; 15. END;
Ö´Ðнá¹ûΪ£º
Sql´úÂë
1. 7934,MILLER
2. PL/SQL ¹ý³ÌÒѳɹ¦Íê³É¡£
˵Ã÷£ºÓαêemp_cursor¶¨ÒåÁËÁ½¸ö²ÎÊý£ºp_deptno´ú±í²¿ÃűàºÅ£¬p_job´ú±íÖ°Îñ¡£Óï¾äOPEN emp_cursor(10, 'CLERK')´«µÝÁËÁ½¸ö²ÎÊýÖµ¸øÓα꣬¼´²¿ÃÅΪ10¡¢Ö°ÎñΪCLERK£¬ËùÒÔÓαê²éѯµÄÄÚÈÝÊDz¿ÃÅ10µÄÖ°ÎñΪCLERKµÄ¹ÍÔ±¡£Ñ»·²¿·ÖÓÃÓÚÏÔʾ²éѯµÄÄÚÈÝ¡£ ¡¾Á·Ï°1¡¿ÐÞ¸ÄOpenÓï¾äµÄ²ÎÊý£º²¿ÃźÅΪ20¡¢Ö°ÎñΪANALYST£¬²¢ÖØÐÂÖ´ÐС£
Ò²¿ÉÒÔͨ¹ý±äÁ¿ÏòÓα괫µÝ²ÎÊý£¬µ«±äÁ¿ÐèÒªÏÈÓÚÓα궨Ò壬²¢ÔÚÓαê´ò¿ªÖ®Ç°¸³Öµ¡£¶ÔÒÔÉÏÀý×ÓÖØÐ¸Ķ¯ÈçÏ£º
¡¾ÑµÁ·2¡¿ ͨ¹ý±äÁ¿´«µÝ²ÎÊý¸øÓαꡣ Sql´úÂë
1. SET SERVEROUTPUT ON 2. DECLARE
3. v_empno NUMBER(5); 4. v_ename VARCHAR2(10); 5. v_deptno NUMBER(5); 6. v_job VARCHAR2(10);
7. CURSOR emp_cursor IS
8. SELECT empno, ename FROM emp
9. WHERE deptno = v_deptno AND job = v_job; 10. BEGIN
11. v_deptno:=10; 12. v_job:='CLERK'; 13. OPEN emp_cursor; 14. LOOP
15. FETCH emp_cursor INTO v_empno,v_ename; 16. EXIT WHEN emp_cursor%NOTFOUND; 17. DBMS_OUTPUT.PUT_LINE(v_empno||','||v_ename); 18. END LOOP; 19. END;
Ö´Ðнá¹ûΪ£º Sql´úÂ룺
1. 7934,MILLER
2. PL/SQL ¹ý³ÌÒѳɹ¦Íê³É¡£
˵Ã÷£º¸Ã³ÌÐòÓëǰһ³ÌÐòʵÏÖÏàͬµÄ¹¦ÄÜ¡£
¶¯Ì¬SELECTÓï¾äºÍ¶¯Ì¬ÓαêµÄÓ÷¨£º
OracleÖ§³Ö¶¯Ì¬SELECTÓï¾äºÍ¶¯Ì¬Óα꣬¶¯Ì¬µÄ·½·¨´ó´óÀ©Õ¹Á˳ÌÐòÉè¼ÆµÄÄÜÁ¦¡£ ¶ÔÓÚ²éѯ½á¹ûΪһÐеÄSELECTÓï¾ä£¬¿ÉÒÔÓö¯Ì¬Éú³É²éѯÓï¾ä×Ö·û´®µÄ·½·¨£¬ÔÚ³ÌÐòÖ´Ðн׶ÎÁÙʱµØÉú³É²¢Ö´ÐУ¬Óï·¨ÊÇ£º
execute immediate ²éѯÓï¾ä×Ö·û´® into ±äÁ¿1[,±äÁ¿2...]; ÒÔÏÂÊÇÒ»¸ö¶¯Ì¬Éú³ÉSELECTÓï¾äµÄÀý×Ó¡£ ¡¾ÑµÁ·1¡¿ ¶¯Ì¬SELECT²éѯ¡£ Sql´úÂ룺
1. SET SERVEROUTPUT ON 2. DECLARE
3. str varchar2(100); 4. v_ename varchar2(10); 5. begin
6. str:='select ename from scott.emp where empno=7788'; 7. execute immediate str into v_ename; 8. dbms_output.put_line(v_ename); 9. END;
Ö´Ðнá¹ûΪ£º Sql´úÂ룺
1. SCOTT
2. PL/SQL ¹ý³ÌÒѳɹ¦Íê³É¡£
˵Ã÷£ºSELECT...INTO...Óï¾ä´æ·ÅÔÚSTR×Ö·û´®ÖУ¬Í¨¹ýEXECUTEÓï¾äÖ´ÐС£ ÔÚ±äÁ¿ÉùÃ÷²¿·Ö¶¨ÒåµÄÓαêÊǾ²Ì¬µÄ£¬²»ÄÜÔÚ³ÌÐòÔËÐйý³ÌÖÐÐ޸ġ£ËäÈ»¿ÉÒÔͨ¹ý²ÎÊý´«µÝÀ´È¡µÃ²»Í¬µÄÊý¾Ý£¬µ«»¹ÊÇÓкܴóµÄ¾ÖÏÞÐÔ¡£Í¨¹ý²ÉÓö¯Ì¬Óα꣬¿ÉÒÔÔÚ³ÌÐòÔËÐн׶ÎËæÊ±Éú³ÉÒ»¸ö²éѯÓï¾ä×÷ΪÓαꡣҪʹÓö¯Ì¬ÓαêÐèÒªÏȶ¨ÒåÒ»¸öÓαêÀàÐÍ£¬È»ºóÉùÃ÷Ò»¸öÓαê±äÁ¿£¬Óαê¶ÔÓ¦µÄ²éѯÓï¾ä¿ÉÒÔÔÚ³ÌÐòµÄÖ´Ðйý³ÌÖж¯Ì¬µØËµÃ÷¡£ ¶¨ÒåÓαêÀàÐ͵ÄÓï¾äÈçÏ£º TYPE ÓαêÀàÐÍÃû REF CURSOR; ÉùÃ÷Óαê±äÁ¿µÄÓï¾äÈçÏ£º Óαê±äÁ¿Ãû ÓαêÀàÐÍÃû;
ÔÚ¿ÉÖ´Ðв¿·Ö¿ÉÒÔÈçÏÂÐÎʽ´ò¿ªÒ»¸ö¶¯Ì¬Óα꣺ OPEN Óαê±äÁ¿Ãû FOR ²éѯÓï¾ä×Ö·û´®;
¡¾ÑµÁ·2¡¿ °´Ãû×ÖÖаüº¬µÄ×Öĸ˳Ðò·Ö×éÏÔʾ¹ÍÔ±ÐÅÏ¢¡£ ÊäÈë²¢ÔËÐÐÒÔϳÌÐò£º Sql´úÂ룺
1. declare
2. type cur_type is ref cursor; 3. cur cur_type;
4. rec scott.emp%rowtype; 5. 6. 7. 8.
str varchar2(50); letter char:= 'A'; begin
loop
9. str:= 'select ename from emp where ename like ''%'||letter||'%'
''; 10. open cur for str;
11. dbms_output.put_line('°üº¬×Öĸ'||letter||'µÄÃû×Ö£º'); 12. loop
13. fetch cur into rec.ename; 14. exit when cur%notfound;
15. dbms_output.put_line(rec.ename); 16. end loop;
17. exit when letter='Z';
18. letter:=chr(ascii(letter)+1); 19. end loop; 20. end;
ÔËÐнá¹ûΪ£º Sql´úÂ룺
1. °üº¬×ÖĸAµÄÃû×Ö£º 2. ALLEN 3. 4. 5. 6.
WARD MARTIN BLAKE CLARK
7. ADAMS
8. JAMES
9. °üº¬×ÖĸBµÄÃû×Ö£º 10. BLAKE
11. °üº¬×ÖĸCµÄÃû×Ö£º 12. CLARK 13. SCOTT
˵Ã÷£ºÊ¹ÓÃÁ˶þÖØÑ»·£¬ÔÚÍâÑ»·ÌåÖУ¬¶¯Ì¬Éú³ÉÓαêµÄSELECTÓï¾ä£¬È»ºó´ò¿ª¡£Í¨¹ýÓï¾äletter:=chr(ascii(letter)+1)¿É»ñµÃ×Öĸ±íÖеÄÏÂÒ»¸ö×Öĸ¡£
Òì³£´¦Àí ´íÎó´¦Àí
´íÎó´¦Àí²¿·ÖλÓÚ³ÌÐòµÄ¿ÉÖ´Ðв¿·ÖÖ®ºó£¬ÊÇÓÉWHENÓï¾äÒýµ¼µÄ¶à¸ö·ÖÖ§¹¹³ÉµÄ¡£´íÎó´¦ÀíµÄÓï·¨ÈçÏ£º