关闭

oracle 游标变量ref cursor详解

标签: oracleref cursor
22122人阅读 评论(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、使用游标变量作为参数传递:

DECLARE
TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;
emp empcurtyp;
-- after result set is built, process all the rows inside a single procedure
-- rather than calling a procedure for each row
PROCEDURE process_emp_cv (emp_cv IN empcurtyp) IS
person employees%ROWTYPE;
BEGIN
DBMS_OUTPUT.PUT_LINE('-----');
DBMS_OUTPUT.PUT_LINE('Here are the names from the result set:');
LOOP
FETCH emp_cv INTO person;
EXIT WHEN emp_cv%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Name = ' || person.first_name ||
' ' || person.last_name);
END LOOP;
END;
BEGIN
-- First find 10 arbitrary employees.
OPEN emp FOR SELECT * FROM employees WHERE ROWNUM < 11;
process_emp_cv(emp);
CLOSE emp;
-- find employees matching a condition.
OPEN emp FOR SELECT * FROM employees WHERE last_name LIKE 'R%';
process_emp_cv(emp);
CLOSE emp;
END;
/

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

DECLARE
TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;
emp_cv empcurtyp;
BEGIN
IF NOT emp_cv%ISOPEN THEN -- open cursor variable
OPEN emp_cv FOR SELECT * FROM employees;
END IF;
CLOSE emp_cv;
END;
/

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

CREATE PACKAGE emp_data AS
TYPE empcurtyp IS REF CURSOR RETURN employees%ROWTYPE;
PROCEDURE open_emp_cv (emp_cv IN OUT empcurtyp);
END emp_data;
/
CREATE PACKAGE BODY emp_data AS
PROCEDURE open_emp_cv (emp_cv IN OUT EmpCurTyp) IS
BEGIN
OPEN emp_cv FOR SELECT * FROM employees;
END open_emp_cv;
END emp_data;
/

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

DECLARE
TYPE empcurtyp IS REF CURSOR;
TYPE namelist IS TABLE OF employees.last_name%TYPE;
TYPE sallist IS TABLE OF employees.salary%TYPE;
emp_cv empcurtyp;
names namelist;
sals sallist;
BEGIN
OPEN emp_cv FOR SELECT last_name, salary FROM employees
WHERE job_id = 'SA_REP';
FETCH emp_cv BULK COLLECT INTO names, sals;
CLOSE emp_cv;
-- loop through the names and sals collections
FOR i IN names.FIRST .. names.LAST
LOOP
DBMS_OUTPUT.PUT_LINE('Name = ' || names(i) || ', salary = ' || sals(i));
END LOOP;
END;
/

三 游标变量的使用限制:

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

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

 3、不能存储于表列中;

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

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

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

附:

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

1、强类型游标:

CREATE OR REPLACE PACKAGE strongly_typed IS

TYPE return_cur IS REF CURSOR RETURN all_tables%ROWTYPE;
PROCEDURE child(p_return_rec OUT return_cur);
PROCEDURE parent(p_NumRecs PLS_INTEGER);

END strongly_typed;
/
CREATE OR REPLACE PACKAGE BODY strongly_typed IS
PROCEDURE child(p_return_rec OUT return_cur) IS

BEGIN
  OPEN p_return_rec FOR 
  SELECT * FROM all_tables; 
END child;
--==================================================
PROCEDURE parent (p_NumRecs PLS_INTEGER) IS 
 p_retcur return_cur;
 at_rec   all_tables%ROWTYPE;
BEGIN
  child(p_retcur);

  FOR i IN 1 .. p_NumRecs
  LOOP
    FETCH p_retcur
    INTO at_rec;

    dbms_output.put_line(at_rec.table_name || 
    ' - ' || at_rec.tablespace_name || 
    ' - ' || TO_CHAR(at_rec.initial_extent) || 
    ' - ' || TO_CHAR(at_rec.next_extent));
  END LOOP;
END parent;
END strongly_typed;
/

set serveroutput on

exec strongly_typed.parent(1);
exec strongly_typed.parent(8);

2、弱类型游标:

CREATE OR REPLACE PROCEDURE child (
 p_NumRecs IN PLS_INTEGER,
 p_return_cur OUT SYS_REFCURSOR)
IS

BEGIN
  OPEN p_return_cur FOR
  'SELECT * FROM all_tables WHERE rownum <= ' || p_NumRecs ;
END child;
/

CREATE OR REPLACE PROCEDURE parent (pNumRecs VARCHAR2) IS
 p_retcur  SYS_REFCURSOR;
 at_rec    all_tables%ROWTYPE;
BEGIN
  child(pNumRecs, p_retcur);

  FOR i IN 1 .. pNumRecs
  LOOP
    FETCH p_retcur
    INTO at_rec;

    dbms_output.put_line(at_rec.table_name ||
    ' - ' || at_rec.tablespace_name ||
    ' - ' || TO_CHAR(at_rec.initial_extent) ||
    ' - ' || TO_CHAR(at_rec.next_extent));
  END LOOP;
END parent;
/

set serveroutput on

exec parent(1);
exec parent(17);

3、预定义游标变量:

CREATE TABLE employees (
empid   NUMBER(5),
empname VARCHAR2(30));

INSERT INTO employees (empid, empname) VALUES (1, 'Dan Morgan');
INSERT INTO employees (empid, empname) VALUES (2, 'Hans Forbrich');
INSERT INTO employees (empid, empname) VALUES (3, 'Caleb Small');
COMMIT;
CREATE OR REPLACE PROCEDURE pass_ref_cur(p_cursor SYS_REFCURSOR) IS

TYPE array_t IS TABLE OF VARCHAR2(4000)
INDEX BY BINARY_INTEGER;

rec_array array_t;

BEGIN
  FETCH p_cursor BULK COLLECT INTO rec_array;

  FOR i IN rec_array.FIRST .. rec_array.LAST
  LOOP
    dbms_output.put_line(rec_array(i));
  END LOOP;
END pass_ref_cur;
/
set serveroutput on

DECLARE
 rec_array SYS_REFCURSOR;
BEGIN
  OPEN rec_array FOR
  'SELECT empname FROM employees';

  pass_ref_cur(rec_array);
  CLOSE rec_array;
END;
/


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

Dylan    presents.




0
0
查看评论
发表评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场

oracle 游标变量ref cursor详解

1、强类型游标: CREATE OR REPLACE PACKAGE strongly_typed IS TYPE return_cur IS REF CURSOR RETURN all_table...
  • IndexMan
  • IndexMan
  • 2013-12-04 15:15
  • 22122

使用sys_refcursor和临时表返回oracle结果集

sys_refcursor是oracle9i以后系统定义的一个refcursor,主要用在过程中返回结果集。 如果仅仅为了返回值,无需自己在包头中定义游标类型,只需直接使用sys_refcursor即...
  • neweastsun
  • neweastsun
  • 2016-09-22 09:28
  • 3068

oracle存储过程(游标作为OUT参数输出)

包中带过程      要自己定义一个type [cur_name] is ref cursor游标,返回的时候就直接 procedure AAA(变量名 out [cur_name])如此申明O...
  • ytlizhen1
  • ytlizhen1
  • 2013-02-26 21:08
  • 2798

游标变量REF COUSOR(动态游标)使用实例

写存储过程时,会经常使用游标来处理数据。 一般在游标数据是变化时*也就是获取游标数据的表是变化的),使用动态游标。本例中,游标数据和传入参数有关,是变化的蛮久可以用动态游标来处理。 这里分享一个动态游...
  • SPE_Andy
  • SPE_Andy
  • 2016-01-04 17:13
  • 312

Oracle PL/SQL开发基础(第二十六弹:游标变量)

前面介绍游标的时候,可以看到,美定义一个游标,就为其绑定一个查询语句,这种游标称为静态游标。游标变量是另一种类型的游标,在定义时并不绑定到具体的查询,而是可以打开任何类型兼容的查询,灵活性相当大。 ...
  • lianjiww
  • lianjiww
  • 2017-08-02 22:02
  • 460

Oracle pl/sql编程 15---游标变量

与游标类似,游标变量也可以处理多行查询的结果集。但是,游标与游标变量是不同的,就像常量和变量的关系一样。游标包括显式游标和隐式游标,它们都是静态定义的。当用户使用它们时,就需要再声明时定义查询。而游标...
  • gzhan1603
  • gzhan1603
  • 2015-10-20 20:40
  • 559

Oracle12C--游标变量(三十八)

啥是游标变量? 在定义游标时,不绑定具体的查询,而是动态地打开指定类型的查询; 使用游标变量,首先应像集合那样定义一种新的游标变量类型,语法: CURSOR 游标变量类型名称 IS REF C...
  • qq_33301113
  • qq_33301113
  • 2017-06-05 09:26
  • 385

oracle存储过程中使用Ref Cursor强类型游标返回结果集

定义类型 定义游标 chuangj
  • wangdonghao137
  • wangdonghao137
  • 2014-10-31 10:37
  • 754

PL/SQL 游标变量

游标变量与游标相似,有其共性,也有其不同点。就其共性来说两者都是指向多行查询的结果集中的当前行。都要经历声明,打开,检索与 关闭的过程。所不同的是游标与游标变量类似于常量与变量。游标是静态的,而游标...
  • zheng123123123123
  • zheng123123123123
  • 2013-04-19 16:05
  • 500

手把手教你mysql(十五)游标变量流程控制

手把手教你mysql(十五)游标变量流程控制   前言: 这是我准备的测试用数据 Mysql>DROP TABLE IF EXISTS `t_user`; Mysql>CREATE TABL...
  • a672489861
  • a672489861
  • 2014-01-11 11:19
  • 2513
    个人资料
    • 访问:1417420次
    • 积分:10289
    • 等级:
    • 排名:第1925名
    • 原创:161篇
    • 转载:48篇
    • 译文:22篇
    • 评论:113条
    博客专栏
    最新评论