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

Regards

Ramesh G

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.

Regards

Pawan Ahuja