Cursor overview

What are Cursors?

A cursor is a temporary work area created in the system memory when a SQL statement is executed. A cursor contains information on a select statement and the rows of data accessed by it.

This temporary work area is used to store the data retrieved from the database, and manipulate this data. A cursor can hold more than one row, but can process only one row at a time. The set of rows the cursor holds is called the active set

There are two types of cursors in PL/SQL:

Implicit cursors

These are created by default when DML statements like, INSERT, UPDATE, and DELETE statements are executed. They are also created when a SELECT statement that returns just one row is executed.

Explicit cursors

They must be created when you are executing a SELECT statement that returns more than one row. Even though the cursor stores multiple records, only one record can be processed at a time, which is called as current row. When you fetch a row the current row position moves to next row.
Both implicit and explicit cursors have the same functionality, but they differ in the way they are accessed.

DECLARE

    l_total       INTEGER := 10000;

      CURSOR employee_id_cur

      IS

           SELECT employee_id

             FROM plch_employees

         ORDER BY salary ASC;

     l_employee_id   employee_id_cur%ROWTYPE;

  BEGIN

     OPEN employee_id_cur;

     LOOP

        FETCH employee_id_cur INTO l_employee_id;

        EXIT WHEN employee_id_cur%NOTFOUND;

        assign_bonus (l_employee_id, l_total);

        EXIT WHEN l_total <= 0;

     END LOOP;

     CLOSE employees_cur;

  END; 

Cursor Attributes:
Oracle provides few attributes called as implicit cursor attributes to check the status of DML operations. The cursor attributes available are %FOUND, %NOTFOUND, %ROWCOUNT, and %ISOPEN.

DECLARE  var_rows number(5);

BEGIN

  UPDATE employee

  SET salary = salary + 1000;

  IF SQL%NOTFOUND THEN

    dbms_output.put_line('None of the salaries where updated');

  ELSIF SQL%FOUND THEN

    var_rows := SQL%ROWCOUNT;

    dbms_output.put_line('Salaries for ' || var_rows || 'employees are updated');

  END IF;

END;

Dynamic Queries with EXECUTE IMMEDIATE (Execute Immediate)

Dynamic SQL means that at the time you write (and then compile) your code, you do not have all the information you need for parsing a SQL statement. Instead, you must wait for runtime to complete the SQL statement and then parse and execute it.
Oracle Database makes it easy to execute SQL statements (and PL/SQL blocks) dynamically with the EXECUTE IMMEDIATE statement. And querying data is the easiest dynamic SQL operation of all!
You can fetch a single row or multiple rows. Here is a generic function that fetches the value of a numeric column in any table, for the specified WHERE clause:

CREATE OR REPLACE FUNCTION 

single_number_value (

   table_in    IN VARCHAR2,

   column_in   IN VARCHAR2,

   where_in    IN VARCHAR2)

   RETURN NUMBER  IS

   l_return   NUMBER;

BEGIN

   EXECUTE IMMEDIATE

         'SELECT '

      || column_in

      || ' FROM '

      || table_in

      || ' WHERE '

      || where_in

      INTO l_return;

   RETURN l_return;   

     END; 

Instead of SELECT-INTO use  EXECUTE IMMEDIATE-INTO.
As with SELECT-INTO, EXECUTE IMMEDIATE-INTO will raise NO_DATA_FOUND if no rows are found and TOO_MANY_ROWS if more than one row is found.
Execute Immediate with BULK COLLECT:
You can also use EXECUTE IMMEDIATE to fetch multiple rows of data, which means that you will populate a collection, so you must use BULK COLLECT. The following is a procedure that will display the values of any numeric column for all rows specified in the WHERE clause:

CREATE OR REPLACE PROCEDURE 

show_number_values (

   table_in    IN VARCHAR2,

   column_in   IN VARCHAR2,

   where_in    IN VARCHAR2)

IS

   TYPE values_t IS TABLE OF NUMBER;

   l_values   values_t;

BEGIN

   EXECUTE IMMEDIATE

         'SELECT '

      || column_in

      || ' FROM '

      || table_in

      || ' WHERE '

      || where_in

      BULK COLLECT INTO l_values;

   FOR indx IN 1 .. l_values.COUNT

   LOOP

      DBMS_OUTPUT.put_line 

      (l_values (indx));

   END LOOP;

END; 

And when I call the procedure for the standard employees table

BEGIN

   show_number_values (

      'employees',

      'salary',

      'department_id = 10 

       order by salary desc');

END; 

A general note of caution regarding dynamic SQL and the preceding examples in particular: whenever you concatenate text to execute a dynamically executed statement, you run the risk of SQL injection. This occurs when a malicious user “injects,” or inserts into the statement, code that changes the behavior of that SQL statement.
REF Cursor:
A REF Cursor is a datatype that holds a cursor value in the same way that a VARCHAR2 variable will hold a string value. (Just Reference).
A REF Cursor can be opened on the server and passed to the client as a unit rather than fetching one row at a time. One can use a Ref Cursor as target of an assignment, and it can be passed as parameter to other program units. Ref Cursors are opened with an OPEN FOR statement. In most other ways they behave similar to normal cursors.
 
Difference between Cursor & REF Cursor:

Technically, at the most "basic level", they are the same.

A "normal" plsql cursor is static in definition.

Ref cursors may be dynamically opened or opened based on logic.

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;

/

1=>

CREATE OR REPLACE FUNCTION f RETURN SYS_REFCURSOR

AS

  l_cursor SYS_REFCURSOR;

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;

  RETURN l_cursor;

END;

/

Call the above function and fetch all rows from the cursor it returns:

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;

/

Given that block of code -- you see perhaps the most "salient" difference -- no matter 

how many times you run that block -- cursor C will always be select * from dual.  The ref cursor can be anything.

Another difference is a ref cursor can be returned to a client.  

a plsql "cursor" cannot be returned to a client.

Another difference is a cursor can be global -- a ref cursor cannot (you cannot define them OUTSIDE of a procedure / function)

Another difference is a ref cursor can be passed from subroutine to subroutine. (Across the procedure/functions)

But a cursor cannot be.

Another difference is that static sql (not using a ref cursor) is much more efficient then using ref cursors and that use of ref cursors should be limited to

- returning result sets to clients

- when there is NO other efficient/effective means of achieving the goal

Cursor with Parameter  (Parameterized Cursor).

Parameterized cursors are static cursors that can accept passed-in parameter values when they are opened.

The following example includes a parameterized cursor.

 The cursor displays the name and salary of each employee in the EMP table whose salary is less than that specified by a passed-in parameter value.

DECLARE
    my_record       emp%ROWTYPE;
    CURSOR c1 (max_wage NUMBER) IS
        SELECT * FROM emp WHERE sal < max_wage;
BEGIN
    OPEN c1(2000);
    LOOP
        FETCH c1 INTO my_record;
        EXIT WHEN c1%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Name = ' || my_record.ename || ', salary = '
            || my_record.sal);
    END LOOP;
    CLOSE c1;
END;