Cursor, Ref Cursor Simple understanding

Cursor Types:

1.      Static

a.       Implicit Cursor

b.      Explicit Cursor

2.      Dynamic

a.       Reference Cursor

                                                              i.      Strong

                                                            ii.      Week

Implicit Cursor:

            Fire during any queries execution in oracle.

Explicit Cursor:

            User explicitly defining the cursor.

Cursor Attributes:

1.      %is open

2.      %found

3.      %not found

    %rowcount

Cursor Commands:

·         Open

·         Fetch

·          Close

Five steps needs to be follow, when we specifying Cursor:

1.                  Declare the variables to store the columns values from the SELECT statements.
2.  Declare the cursor to specify the SELECT statement.
3.  Open the cursor
4.  Fetch the values from the cursor
5.  Close the cursor

For Cursor:

·         No need to declare the variables

·         No need to open the cursor

·         No need to fetch the data.

·                     No need to close the cursor.

Reference Cursor:

Introduced in Oracle 7.3 Version

TYPE STRONG_REF_CURSOR IS REF CURSOR RETURN EMP%ROWTYPE;

 TYPE WEAK8i_REF_CURSOR IS REF CURSOR

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;

SYS_REFCURSOR

            Introduced in Oracle 9i. Its a predefined REF CURSOR defined in standard package of Oracle located at following location in windows: %ORACLE_HOME%/rdbms/admin/stdspec.sql
where %ORACLE_HOME% = C:\oraclexe_32bit\app\oracle\product\11.2.0\server\

CREATE OR REPLACE FUNCTION f RETURN SYS_REFCURSOR

AS

  c SYS_REFCURSOR;

BEGIN

  OPEN c FOR select * from dual;

  RETURN c;

END;

set serveroutput on

DECLARE

 c SYS_REFCURSOR;

 v VARCHAR2(1);

BEGIN

 c := f();   -- Get ref cursor from function

 LOOP

   FETCH c into v;

   EXIT WHEN c%NOTFOUND;

   dbms_output.put_line('Value from cursor: '||v);

 END LOOP;

END;

Difference between Ref_cursor and Sys_refcursor

1. Nothing

SYS_REFCURSOR is just a synonym for the REF CURSOR type.