oracle 游标变量ref cursor详解

oracle 游标变量ref cursor详解

分类： PL/SQL开发 2013-12-04 15:15 685人阅读 评论(0) 收藏 举报
oracleref cursor

一 介绍

     像游标cursor一样，游标变量ref cursor指向指定查询结果集当前行。游标变量显得更加灵活因为其声明并不绑定指定查询。

其主要运用于PLSQL函数或存储过程以及其他编程语言java等程序之间作为参数传递。

    不像游标的一点，游标变量没有参数。

    游标变量具有以下属性：

    (%FOUND, %NOTFOUND, %ISOPEN, and %ROWCOUNT)


二 用法介绍：

1、声明格式：

    DECLARE  

           TYPE DeptCurTyp IS REF CURSOR RETURN departments%ROWTYPE;

2、游标变量又分为强类型strong(with a return type)和弱类型(with no return type)：

    DECLARE
   TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE; -- 强类型
   TYPE genericcurtyp IS REF CURSOR; -- 弱类型
   cursor1 empcurtyp;
   cursor2 genericcurtyp;
   my_cursor SYS_REFCURSOR; -- 使用预定义游标变量sys_refcursor
   TYPE deptcurtyp IS REF CURSOR RETURN departments%ROWTYPE;
   dept_cv deptcurtyp; -- 声明游标变量

   或是返回record类型：

   DECLARE
   TYPE EmpRecTyp IS RECORD (
   employee_id NUMBER,
   last_name VARCHAR2(25),
   salary NUMBER(8,2));
   TYPE EmpCurTyp IS REF CURSOR RETURN EmpRecTyp;
   emp_cv EmpCurTyp; -- declare cursor variable 


3、使用游标变量作为参数传递：

[sql] view plaincopy在CODE上查看代码片派生到我的代码片
  1. DECLARE  
  2. TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;  
  3. emp empcurtyp;  
  4. -- after result set is built, process all the rows inside a single procedure  
  5. -- rather than calling a procedure for each row  
  6. PROCEDURE process_emp_cv (emp_cv IN empcurtyp) IS  
  7. person employees%ROWTYPE;  
  8. BEGIN  
  9. DBMS_OUTPUT.PUT_LINE('-----');  
  10. DBMS_OUTPUT.PUT_LINE('Here are the names from the result set:');  
  11. LOOP  
  12. FETCH emp_cv INTO person;  
  13. EXIT WHEN emp_cv%NOTFOUND;  
  14. DBMS_OUTPUT.PUT_LINE('Name = ' || person.first_name ||  
  15. ' ' || person.last_name);  
  16. END LOOP;  
  17. END;  
  18. BEGIN  
  19. -- First find 10 arbitrary employees.  
  20. OPEN emp FOR SELECT * FROM employees WHERE ROWNUM < 11;  
  21. process_emp_cv(emp);  
  22. CLOSE emp;  
  23. -- find employees matching a condition.  
  24. OPEN emp FOR SELECT * FROM employees WHERE last_name LIKE 'R%';  
  25. process_emp_cv(emp);  
  26. CLOSE emp;  
  27. END;  
  28. /  


4、使用游标熟悉检查游标变量是否打开

[sql] view plaincopy在CODE上查看代码片派生到我的代码片
  1. DECLARE  
  2. TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;  
  3. emp_cv empcurtyp;  
  4. BEGIN  
  5. IF NOT emp_cv%ISOPEN THEN -- open cursor variable  
  6. OPEN emp_cv FOR SELECT * FROM employees;  
  7. END IF;  
  8. CLOSE emp_cv;  
  9. END;  
  10. /  


5、在包package中声明游标变量：

[sql] view plaincopy在CODE上查看代码片派生到我的代码片
  1. CREATE PACKAGE emp_data AS  
  2. TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;  
  3. PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp);  
  4. END emp_data;  
  5. /  
  6. CREATE PACKAGE BODY emp_data AS  
  7. PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp) IS  
  8. BEGIN  
  9. OPEN emp_cv FOR SELECT * FROM employees;  
  10. END open_emp_cv;  
  11. END emp_data;  
  12. /  


6、提取游标变量到集合类型collection：

[sql] view plaincopy在CODE上查看代码片派生到我的代码片
  1. DECLARE  
  2. TYPE empcurtyp IS REF CURSOR;  
  3. TYPE namelist IS TABLE OF employees.last_name%TYPE;  
  4. TYPE sallist IS TABLE OF employees.salary%TYPE;  
  5. emp_cv empcurtyp;  
  6. names namelist;  
  7. sals sallist;  
  8. BEGIN  
  9. OPEN emp_cv FOR SELECT last_name, salary FROM employees  
  10. WHERE job_id = 'SA_REP';  
  11. FETCH emp_cv BULK COLLECT INTO names, sals;  
  12. CLOSE emp_cv;  
  13. -- loop through the names and sals collections  
  14. FOR i IN names.FIRST .. names.LAST  
  15. LOOP  
  16. DBMS_OUTPUT.PUT_LINE('Name = ' || names(i) || ', salary = ' || sals(i));  
  17. END LOOP;  
  18. END;  
  19. /  


三 游标变量的使用限制：

 1、不能再包说明中声明游标变量；

 2、不能用“=”运算符比较游标变量相等性、不等性及是否为空；

 3、不能存储于表列中；

 4、不能将游标变量存在于关联数组、嵌套表或数组；

 5、游标和游标变量之前是不可互操作的！

--------------------------------------------------------------------------------------

附：

------------------------

1、强类型游标：

[sql] view plaincopy在CODE上查看代码片派生到我的代码片
  1. CREATE OR REPLACE PACKAGE strongly_typed IS  
  2.   
  3. TYPE return_cur IS REF CURSOR RETURN all_tables%ROWTYPE;  
  4. PROCEDURE child(p_return_rec OUT return_cur);  
  5. PROCEDURE parent(p_NumRecs PLS_INTEGER);  
  6.   
  7. END strongly_typed;  
  8. /  
[sql] view plaincopy在CODE上查看代码片派生到我的代码片
  1. CREATE OR REPLACE PACKAGE BODY strongly_typed IS  
  2. PROCEDURE child(p_return_rec OUT return_cur) IS  
  3.   
  4. BEGIN  
  5.   OPEN p_return_rec FOR   
  6.   SELECT * FROM all_tables;   
  7. END child;  
  8. --==================================================  
  9. PROCEDURE parent (p_NumRecs PLS_INTEGER) IS   
  10.  p_retcur return_cur;  
  11.  at_rec   all_tables%ROWTYPE;  
  12. BEGIN  
  13.   child(p_retcur);  
  14.   
  15.   FOR i IN 1 .. p_NumRecs  
  16.   LOOP  
  17.     FETCH p_retcur  
  18.     INTO at_rec;  
  19.   
  20.     dbms_output.put_line(at_rec.table_name ||   
  21.     ' - ' || at_rec.tablespace_name ||   
  22.     ' - ' || TO_CHAR(at_rec.initial_extent) ||   
  23.     ' - ' || TO_CHAR(at_rec.next_extent));  
  24.   END LOOP;  
  25. END parent;  
  26. END strongly_typed;  
  27. /  
[sql] view plaincopy在CODE上查看代码片派生到我的代码片
  1. set serveroutput on  
  2.   
  3. exec strongly_typed.parent(1);  
  4. exec strongly_typed.parent(8);  

2、弱类型游标：

[sql] view plaincopy在CODE上查看代码片派生到我的代码片
  1. CREATE OR REPLACE PROCEDURE child (  
  2.  p_NumRecs IN PLS_INTEGER,  
  3.  p_return_cur OUT SYS_REFCURSOR)  
  4. IS  
  5.   
  6. BEGIN  
  7.   OPEN p_return_cur FOR  
  8.   'SELECT * FROM all_tables WHERE rownum <= ' || p_NumRecs ;  
  9. END child;  
  10. /  
  11.   
  12. CREATE OR REPLACE PROCEDURE parent (pNumRecs VARCHAR2) IS  
  13.  p_retcur  SYS_REFCURSOR;  
  14.  at_rec    all_tables%ROWTYPE;  
  15. BEGIN  
  16.   child(pNumRecs, p_retcur);  
  17.   
  18.   FOR i IN 1 .. pNumRecs  
  19.   LOOP  
  20.     FETCH p_retcur  
  21.     INTO at_rec;  
  22.   
  23.     dbms_output.put_line(at_rec.table_name ||  
  24.     ' - ' || at_rec.tablespace_name ||  
  25.     ' - ' || TO_CHAR(at_rec.initial_extent) ||  
  26.     ' - ' || TO_CHAR(at_rec.next_extent));  
  27.   END LOOP;  
  28. END parent;  
  29. /  
  30.   
  31. set serveroutput on  
  32.   
  33. exec parent(1);  
  34. exec parent(17);  

3、预定义游标变量：

[sql] view plaincopy在CODE上查看代码片派生到我的代码片
  1. CREATE TABLE employees (  
  2. empid   NUMBER(5),  
  3. empname VARCHAR2(30));  
  4.   
  5. INSERT INTO employees (empid, empname) VALUES (1, 'Dan Morgan');  
  6. INSERT INTO employees (empid, empname) VALUES (2, 'Hans Forbrich');  
  7. INSERT INTO employees (empid, empname) VALUES (3, 'Caleb Small');  
  8. COMMIT;  
  9. CREATE OR REPLACE PROCEDURE pass_ref_cur(p_cursor SYS_REFCURSOR) IS  
  10.   
  11. TYPE array_t IS TABLE OF VARCHAR2(4000)  
  12. INDEX BY BINARY_INTEGER;  
  13.   
  14. rec_array array_t;  
  15.   
  16. BEGIN  
  17.   FETCH p_cursor BULK COLLECT INTO rec_array;  
  18.   
  19.   FOR i IN rec_array.FIRST .. rec_array.LAST  
  20.   LOOP  
  21.     dbms_output.put_line(rec_array(i));  
  22.   END LOOP;  
  23. END pass_ref_cur;  
  24. /  
  25. set serveroutput on  
  26.   
  27. DECLARE  
  28.  rec_array SYS_REFCURSOR;  
  29. BEGIN  
  30.   OPEN rec_array FOR  
  31.   'SELECT empname FROM employees';  
  32.   
  33.   pass_ref_cur(rec_array);  
  34.   CLOSE rec_array;  
  35. END;  
  36. /  


【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/wahaccp/p/4186225.html