Using ref cursor vs sys_refcursor inside a Stored Procedure Parameters

CREATE  OR  REPLACE  PROCEDURE GetEmployeesInDept( c OUT  SYS_REFCURSOR)

I am having a query related to the above Stored Procedures, That is while defining the cursor we mentioned it as sys_refcursor, and in some, web site I have seen it as REF CURSOR as shown

create or replace procedure GetEmployeesInDept( c out ref cursor)

开发者_StackOverflow社区Please tell me what is the difference between the ref cursor and sys_refcursor.

I'm not sure what you mean by

create or replace procedure GetEmployeesInDept( c out ref cursor)

I've not seen this before and I can't get a procedure declared like this to compile. Could you please provide sample code or links to where you've seen this before?

What you might have seen is something like the following:

CREATE OR REPLACE PACKAGE demo AS

  TYPE ref_cursor IS REF CURSOR;

  PROCEDURE GetEmployeesInDept(c OUT ref_cursor);

END demo;
/

CREATE OR REPLACE PACKAGE BODY demo AS

  PROCEDURE GetEmployeesInDept(c OUT ref_cursor)
  IS
  BEGIN
    RAISE NO_DATA_FOUND;
  END GetEmployeesInDept;

END demo;
/

In this case, we declare a type to be a REF CURSOR, and use it as an OUT parameter in a stored procedure.

There is no difference between using a type declared as REF CURSOR and using SYS_REFCURSOR, because SYS_REFCURSOR is defined in the STANDARD package as a REF CURSOR in the same way that we declared the type ref_cursor. In fact, if you're using Oracle 9i or later, look within your Oracle database installation, in %ORACLE_HOME%\rdbms\admin\stdspec.sql, and you should find the following line somewhere in there:

type sys_refcursor is ref cursor;

SYS_REFCURSOR was introduced in Oracle 9i. You may find various types declared as REF CURSOR in PL/SQL code that was written before Oracle 9i was released.

There are two forms of ref Cursor the strong REF CURSOR and the weak REF CURSOR. PL/SQL is a statically typed language, and the weak REF CURSOR is one of the few dynamically typed constructs supported.

(statically typed language: that was mean that type checking is performed at compile time not at runtime)

When you defined a SYS_REFCURSOR is a predefined weak REF CURSOR type.

A strong ref cursor it's something like this:

TYPE book_data_t IS REF CURSOR RETURN book%ROWTYPE;

cause associate the cursor variable with a specific record structure.

There can be one difference between the two, I can think off is ref_cursor can be STRONG or WEAK type whereas SYS_REFCURSOR is always weak type as it is defined that way.

Check the following Difference :

1)Using ref cursor

                        SQL> CREATE OR REPLACE package p19
                          2  as
                          3         PROCEDURE p;
                          4  END;
                          5  /

Package CREATEd.

                      1  CREATE OR REPLACE package BODY p19
                      2  as
                      3     type s is ref cursor;
                      4     PROCEDURE p
                      5     as
                      6     BEGIN
                      7     NULL;
                      8     END;
                      9* END;
                      SQL> /

                     Package BODY CREATEd.

2)Using Sys_Refcursor:

        SQL> CREATE OR REPLACE package p19
             2  as
             3      PROCEDURE p;
             4  END;
             5  /

Package CREATED.

SQL> ed Wrote file afiedt.buf

                1  CREATE OR REPLACE package BODY p19
                2  as
                3   s sys_refcursor;
                4   PROCEDURE p
                5   as
                6   BEGIN
                7   NULL;
                8   END;
                9* END;
               SQL> /

Warning: Package BODY CREATEd with compilation errors.

SQL> show err Errors for PACKAGE BODY P19:

LINE/COL ERROR

3/4 PL/SQL: Item ignored 3/4 PLS-00994: Cursor Variables cannot be declared as part of a package