oracle游标变量refcursor详解
分类： 2013-12-04 15:15 685⼈阅读 (0)
⼀介绍
像游标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]
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]
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]
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]
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]
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]
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(_extent));
24. END LOOP;
25. END parent;
26. END strongly_typed;
oracle游标的使用27. /
[sql]
1. set serveroutput on
2.
3. exec strongly_typed.parent(1);
4. exec strongly_typed.parent(8);
2、弱类型游标：
[sql]
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(_extent));
27. END LOOP;
28. END parent;
29. /
30.
31. set serveroutput on
32.
33. exec parent(1);
34. exec parent(17);
3、预定义游标变量：
[sql]
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. /
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论