Santo

• Oct 9th, 2006

 

Cursor is static one and ref cursor is dynamic with return type.

rampratap409

 Profile Answers by rampratap409 Questions by rampratap409

• Oct 9th, 2006

 

Example for cursor:

declare

cursor c1 is select * from emp;

begin

for r1 in c1 loop

dbms_output.put_line(r1.empno||'  '||r1.ename);

end loop;

end;

/

Example for ref cursor

CREATE OR REPLACE package emp_data is

-- Author : RPSINGH

-- Created : 8/17/2006 12:54:03 AM

-- Purpose : displaying the data from different tables

-- Public type declarations

type my_cur is ref cursor;

-- Public constant declarations

-- Public variable declarations

-- Public function and procedure declarations

procedure tabledata(tname in varchar2, v_cur out my_cur);

end emp_data;

/

CREATE OR REPLACE PACKAGE BODY EMP_DATA IS

procedure tabledata(tname in varchar2,v_cur out my_cur) is

begin

OPEN V_CUR FOR

'SELECT * FROM '||TNAME;

end tabledata;

end emp_data;

/

Now to use this code:

declare

v_cur employees%rowtype;

C_CUR EMP_DATA.MY_CUR;

begin

emp_data.tabledata('EMPLOYEES', C_CUR);

LOOP

FETCH C_CUR INTO V_CUR;

EXIT WHEN C_CUR%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(V_CUR.FIRST_NAME||'  '||V_CUR.LAST_NAME);

END LOOP;

END;

/

Rajeshwaran

• Oct 12th, 2006

 

Cursor is a structure which points to a memory locations

while Ref-cursor is a datastructure which point to a object which inturn points to Memory locations..the advantage of having Ref-cursor is that we can pass dynamically the Ref-cursor as a parameter to a procedures..

harinath

• Jul 15th, 2011

 

cursor is a static,
ref cursor is dynamic,
we can't use ref cursor directly, so we declare cursor variable
cursor variables can't be stored in the database