Difference between cursor and a ref cursor

zhouwf0726發表於2019-06-06
technically, under the covers, at the most "basic level", they are the same.

A "normal" plsql cursor is static in defintion.

Ref cursors may be dynamically opened or opened based on logic.

Declare
type rc is ref cursor;

cursor c is select * from dual;

l_cursor rc;
begin
if ( to_char(sysdate,'dd') = 30 ) then
open l_cursor for 'select * from emp';
elsif ( to_char(sysdate,'dd') = 29 ) then
open l_cursor for select * from dept;
else
open l_cursor for select * from dual;
end if;
open c;
end;
/


Given that block of code -- you see perhaps the most "salient" difference -- no
matter how many times you run that block -- cursor C will always be select *
from dual. The ref cursor can be anything.

Another difference is a ref cursor can be returned to a client. a plsql "cursor
cursor" cannot be returned to a client.

Another difference is a cursor can be global -- a ref cursor cannot (you cannot
define them OUTSIDE of a procedure / function)

Another difference is a ref cursor can be passed from subroutine to subroutine
-- a cursor cannot be.

Another difference is that static sql (not using a ref cursor) is much more
efficient then using ref cursors and that use of ref cursors should be limited
to
- returning result sets to clients
- when there is NO other efficient/effective means of achieving the goal

that is, you want to use static SQL (with implicit cursors really) first and use
a ref cursor only when you absolutely have to

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/756652/viewspace-242289/，如需轉載，請註明出處，否則將追究法律責任。

相關文章