REF Cursor

Oracle 系列:REF Cursor

在上文  Oracle 系列:Cursor  (参见:http://blog.youkuaiyun.com/qfs_v/archive/2008/05/06/2404794.aspx)中
 提到个思考:怎样让游标作为参数传递? 解决这个问题就需要用到 REF Cursor 。

1,什么是 REF游标 ?
 动态关联结果集的临时对象。即在运行的时候动态决定执行查询。
 
2,REF 游标 有什么作用?
 实现在程序间传递结果集的功能,利用REF CURSOR也可以实现BULK SQL,从而提高SQL性能。

3,静态游标和REF 游标的区别是什么?
 ①静态游标是静态定义,REF 游标是动态关联;
 ②使用REF 游标需REF 游标变量。
 ③REF 游标能做为参数进行传递,而静态游标是不可能的。
 
4,什么是REF 游标变量?
 REF游标变量是一种 引用 REF游标类型  的变量,指向动态关联的结果集。

5,怎么使用  REF游标 ?
 ①声明REF 游标类型,确定REF 游标类型;
  ⑴强类型REF游标:指定retrun type,REF 游标变量的类型必须和return type一致。
   语法:Type   REF游标名   IS   Ref Cursor Return  结果集返回记录类型;
  ⑵弱类型REF游标:不指定return type,能和任何类型的CURSOR变量匹配,用于获取任何结果集。
   语法:Type   REF游标名   IS   Ref Cursor;

 ②声明Ref 游标类型变量;
  语法:变量名  已声明Ref 游标类型;
  
 ③打开REF游标,关联结果集 ;
  语法:Open   Ref 游标类型变量   For   查询语句返回结果集;
  
 ④获取记录,操作记录;
  语法:Fatch    REF游标名 InTo   临时记录类型变量或属性类型变量列表;
  
 ⑤关闭游标,完全释放资源;
  语法:Close   REF游标名;
 
 例子:强类型REF游标
 /*conn scott/tiger*/
 Declare
  Type MyRefCurA IS  REF CURSOR RETURN emp%RowType;
  Type MyRefCurB IS  REF CURSOR RETURN emp.ename%Type;
  vRefCurA  MyRefCurA;
  vRefCurB  MyRefCurB;
  vTempA  vRefCurA%RowType;
  vTempB  vRefCurB.ename%Type;
  
 Begin
  Open  vRefCurA  For Select  *  from   emp   Where  SAL > 2000;
  Loop
   Fatch  vRefCurA InTo  vTempA;
   Exit  When  vRefCurA%NotFound;
   DBMS_OUTPUT.PUT_LINE(vRefCurA%RowCount||'  '|| vTempA.eno||'  '||vTempA.ename ||'  '||vTempA.sal)
  End Loop;
  Close vRefCurA;
  
  DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------------------------------');
  
  Open  vRefCurB  For Select  ename  from   emp   Where  SAL > 2000;
  Loop
   Fatch  vRefCurB InTo  vTempB;
   Exit  When  vRefCurB%NotFound;
   DBMS_OUTPUT.PUT_LINE(vRefCurB%RowCount||'  '||vTempB)
  End Loop;
  Close vRefCurB; 
  
  DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------------------------------');   
  
  Open  vRefCurA  For Select  *  from   emp   Where  JOB = 'CLERK';
  Loop
   Fatch  vRefCurA InTo  vTempA;
   Exit  When  vRefCurA%NotFound;
   DBMS_OUTPUT.PUT_LINE(vRefCurA%RowCount||'  '|| vTempA.eno||'  '||vTempA.ename ||'  '||vTempA.sal)
  End Loop;
  Close vRefCurA;
 End;
 
 例子:弱类型REF游标
 /*conn scott/tiger*/
 Declare
  Type MyRefCur  IS  Ref  Cursor;
  vRefCur MyRefCur;
  vtemp  vRefCur%RowType;
 Begin
  Case(&n)
   When  1 Then Open vRefCur  For Select   *   from emp;
   When  2 Then Open vRefCur  For Select   *   from dept;
   Else
    Open vRefCur  For Select   eno,  ename  from emp Where JOB = 'CLERK';
  End Case;
  Close  vRefCur;
 End;

6,怎样让REF游标作为参数传递?

### Refcursor vs Cursor in PostgreSQL Differences and Usage In PostgreSQL, both `REFCURSOR` and cursors serve similar purposes but differ significantly in their implementation and use cases. #### Definition and Basic Characteristics A **cursor** allows retrieval of multiple rows from a query one at a time or in blocks. This feature is particularly useful when dealing with large datasets that cannot fit into memory all at once[^1]. Cursors can be declared within PL/pgSQL functions or procedures directly using the `DECLARE CURSOR` syntax. On the other hand, a **REFCURSOR** represents an abstract handle to a cursor rather than being a cursor itself. It acts more like a pointer or reference to a cursor object created elsewhere. Unlike regular cursors, `REFCURSOR`s do not require explicit declaration inside procedural code; instead, they get associated dynamically through function calls returning such references. #### Syntax Examples Here’s how each type gets defined: For standard cursors: ```sql BEGIN; DECLARE my_cursor SCROLL CURSOR FOR SELECT * FROM employees WHERE department_id = 50; FETCH ALL IN my_cursor; CLOSE my_cursor; COMMIT; ``` Using REFCURSORS involves creating stored procedures/functions that open cursors internally before passing back handles via OUT parameters. ```plpgsql CREATE OR REPLACE FUNCTION fetch_employee_data(dept integer, refcur refcursor) RETURNS refcursor AS $$ BEGIN OPEN refcur FOR SELECT * FROM employees WHERE department_id = dept; RETURN refcur; END; $$ LANGUAGE plpgsql; -- Calling the above-defined procedure would look something like this: BEGIN; SELECT fetch_employee_data(50, 'my_ref_cursor'); FETCH ALL IN "my_ref_cursor"; CLOSE "my_ref_cursor"; COMMIT; ``` #### Key Distinctions Between REF_CURSOR and Regular Cursors - **Scope**: Standard cursors exist only during transaction boundaries while `REFCURSOR`s persist beyond transactions since these act merely as pointers passed around between different parts of applications. - **Usage Contexts**: While direct manipulation (open/fetch/close) works fine for simple scripts or interactive sessions involving plain SQL commands, complex logic requiring dynamic queries benefits greatly from employing `REFCURSOR`. - **Portability Across Sessions/Connections**: Since `REFCURSOR` essentially serves as opaque identifiers pointing towards actual server-side resources allocated upon invocation, sharing across separate client connections becomes feasible without exposing underlying details about internal structures involved. #### Practical Implications on Application Design Choices Choosing whether to implement traditional cursors versus leveraging `REFCURSOR` depends largely on specific requirements surrounding data access patterns alongside architectural considerations regarding session management strategies employed throughout software stacks interfacing with relational databases managed under PostgreSQL environments. --related questions-- 1. How does Spring Batch address performance issues related to handling massive volumes of records? 2. What changes were made to alter column definitions specifically concerning storage characteristics in Oracle tables? 3. Can DelimitedLineTokenizer process CSV files containing quoted strings properly?
评论 3
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值