CURSORS : Explict Cursors

EXPLICIT CURSORS:

Syntax:
CURSOR cursor_name [ ( [ parameter [, parameter ...] ) ]
       [ RETURN return_specification ]
       IS SELECT_statement
           [FOR UPDATE [OF [column list]];

USAGE FLOW:
Declare the cursor in the declaration block.
Open the cursor in execution block.
fetch the data from the opened cursor.
Close the cursor.


1. OPEN :

OPEN cursor_name [ ( arguments,... ) ];

arguments are the values to be passed, if the cursor was declared with a parameter list.

When we open the cursor,PL/SQL executes the query for that cursor.
Regardless of the when we perform the fetch, it reflects the data as it existed when cursor was opened.
If Select statement in the cursor uses a FOR UPDATE,all rows will be locked untill cursor is closed.

We can't open an already opened cursor untill closed.

2. FETCH :

FETCH cursor_name INTO record_or_variable_list;

We should always fetch into a record that was defined with %ROWTYPE against the cursor.

Once a cursor is open, we can fetch from it untill no more rows left but we can continue to fetch past the last record.we don't get any exception & fetch doesn't do anything( it wont' even make the variables to null).

Example:

Declare
-- returns zero records
cursor my_test_cursor is select sysdate from dual where 1=2;
my_date date:=sysdate+10;
begin
open my_test_cursor;
fetch my_test_cursor into my_date;
-- The variable is not set to NULL and it has the default value.
dbms_output.put_line(my_date);
end;



Note: All the calculated or virtual columns need to be aliased in the Explicit cursors.

3. CLOSE :

CLOSE cursor_name;

If we leave too many cursors open, we may exceed the value set by the database initialization parameter, OPEN_CURSORS.
If this happens, we will encounter error message:
    ORA-01000: maximum open cursors exceeded
If we get this message, check the usage of package-based cursors to make sure they are closed
when no longer needed.
BEGIN
       OPEN my_package.my_cursor;
       ... Do stuff with the cursor
       CLOSE my_package.my_cursor;
    EXCEPTION
       WHEN OTHERS
       THEN
          CLOSE my_package.my_cursor;
    END;

Explicit Cursor Attributes :

1. cursor_NAME%FOUND    : TRUE if a record was fetched good.
2. cursor_NAME%NOTFOUND : FALSE if a record was fetched good.
3. cursor_NAME%ROWCOUNT : Number of records fetched at that point in time.
4. cursor_NAME%ISOPEN   : TRUE if cursor is open.

EXCEPTIONS:

INVALID_CURSOR
If we try to use %FOUND, %NOTFOUND, or %ROWCOUNT before the cursor is opened
or after it is closed, Oracle will raise an INVALID_CURSOR error.

Cursor Parameters:
We can parameterize the Cursors and make it generic.
we can specify as many cursor parameters as we need.
When we OPEN the cursor, we need to include an argument in the parameter list for each parameter, except for trailing parameters that have default values.


The scope of the cursor parameter is confined to that cursor.

Example:

CURSOR emp_cur (emp_id_in NUMBER := 0)
    IS
       SELECT employee_id, emp_name
         FROM employee
        WHERE employee_id = emp_id_in;