Explain reference cursor?

Question Posted / guest

1 Answers
8747 Views
I also Faced
E-Mail Answers

Explain reference cursor?..

Answer / nagaraju bhatraju

Basically, Reference cursor is datatype.Reference cursor
works
as a cursor variable.the advantage of using reference cursor
is it pass the result sets to the subprograms
(ie,procedures,packages and functions etc).
example for reference cursor usage:
type r_cursor is ref cursor;
c_emp r_cursor;
ename1 emp.ename%type;
begin
open c_emp is select ename from emp;
loop
fetch c_emp into ename1;
exit when c_emp% notfound;
dbms_output.put_line(ename1);
end loop;
close c_emp;
end;
--------------

A REF CURSOR have a return type and it as 2 type Strongly
Typed Cursor and Weakly Typed Cursor

but Cursor doesn't have return type

Ex:

TYPE ref_type_name IS REF CURSOR RETURN return_type;

return_type represents a record in the database

DECLARE TYPE EmpCurType IS REF CURSOR RETURN emp%ROWTYPE;

Cursor doesn't have a return type but
A Reference Cursor have a return type and it as 2 type one
is
Strongly Typed Cursor and Weakly Typed Cursor.

Another difference is REF curson can be assigned
dynamically while Normal cursor once defined you cann't
change it

ref cursor can be associated with many no. of sql
statements where cursor can be associated only with one sql
statement.

ref cursor is dynamic,cursor is static.

ref cursor points to a location.

Reference cursors have 2 types.

1 is strong cursors and 2 one is week-cursor

in stron cursor we given return type. in week cursor no
return type

A REF CURSOR have a return type and it as 2 type Strongly
Typed Cursor and Weakly Typed Cursor

but Cursor doesn't have return type

Ex:

TYPE ref_type_name IS REF CURSOR RETURN return_type;

return_type represents a record in the database

DECLARE TYPE EmpCurType IS REF CURSOR RETURN emp%ROWTYPE;
***********************************

Cursor doesn't have a return type but
A Reference Cursor have a return type and it as 2 type one
is
Strongly Typed Cursor and Weakly Typed Cursor.

**************************************************

ref cursor can be associated with many no. of sql
statements where cursor can be associated only with one sql
statement.

ref cursor is dynamic,cursor is static.

ref cursor points to a location.

CURSOR

In cursor there are 2 types explicit and implicit cursor

Explicit cursor

Explicit cursors are SELECT statements that are DECLAREd
explicitly in the declaration section of the current block
or in a package specification. Use OPEN, FETCH, and CLOSE
in the execution or exception sections of your programs.

IMPLICIT CURSOR

Whenever a SQL statement is directly in the execution or
exception section of a PL/SQL block, you are working with
implicit cursors. These statements include INSERT, UPDATE,
DELETE, and SELECT INTO statements. Unlike explicit
cursors, implicit cursors do not need to be declared,
OPENed, FETCHed, or CLOSEd.

REFERENCE CURSOR

A cursor variable is a data structure that points to a
cursor object, which in turn points to the cursor's result
set. You can use cursor variables to more easily retrieve
rows in a result set from client and server programs. You
can also use cursor variables to hide minor variations in
queries.

The syntax for a REF_CURSOR type is:

TYPE ref_cursor_name IS REF CURSOR [RETURN
record_type];If you do not include a RETURN clause, then
you are declaring a weak REF CURSOR. Cursor variables
declared from weak REF CURSORs can be associated with any
query at runtime. A REF CURSOR declaration with a RETURN
clause defines a "strong" REF CURSOR. A cursor variable
based on a strong REF CURSOR can be associated with queries
whose result sets match the number and datatype of the
record structure after the RETURN at runtime.

To use cursor variables, you must first create a REF_CURSOR
type, then declare a cursor variable based on that type.

The following example shows the use of both weak and strong
REF CURSORs:

DECLARE -- Create a cursor type based on the
companies table. TYPE company_curtype IS REF
CURSOR RETURN companies%ROWTYPE; -- Create the
variable based on the REF CURSOR. company_cur
company_curtype; -- And now the weak, general approach.
TYPE any_curtype IS REF CURSOR; generic_curvar
any_curtype;The syntax to OPEN a cursor variable is:

OPEN cursor_name FOR select_statement;

Hi

Cursor (explicit cursor) are static cursors which can be
associated with onlyone SQl statement at the same timeand
this statement is known when block is compiled. A Cursor
Variable, on the other hand, can be associated with
different queries at runtime.

Static Cursor are analogus to PL/SQL constants because they
can only be associated with one runtime query, whereas
reference cursor are analogus to PL/SQL variables, which
can hold different values at runtime.

Reference Cursor can have return type.

Beacause of reference type, no storage is allocated for it
when it is declared. Before it can be used, it needs to
point to a valid area of memory, which can be created
either by allocating it to the client-side program or on
the server by PL/SQL engine.

Ref cursor can contain multiple query in single variable.
where cursor can be associated only with one sql query

Ref cursor is dynamic,cursor is static.

CURSOR IS A STATIC TYPE. BUT REF CURSOR IS DYNAMIC.

Is This Answer Correct ?

0 Yes

2 No

Post New Answer