Cursor

What is a cursor:

When a query is executed in oracle, a result set is produced and stored in the memory(RAM). Oracle allows the programmer to access this result set in the memory through cursor.

Also we can say it is a mechanism by which you can assign a name to a "select statement" and manipulate the information within that SQL statement.

It is defined within the declaration section of your PLSQL code.

Why use a cursor?

Many times, when a query returns more than one row as a result, we might want to go through each row and process the data in different way for them. Cursor is handy here.

Types of cursors:

Oracle PL/SQL declares a cursor implicitly for all queries and DML statements (including queries that return only one row). But in most of the cases we don’t use these cursors for queries that return one row. Based on this, there are 2 types of cursors

1. Explicit cursor
2. Implicit cursor

Explicit cursor:

The programmer declares a cursor explicitly to process the result set of query (Mostly the query that returns multiple rows as result). The following are the steps to use an explicit cursor.

a. Declare the cursor -- This means give the cursor a name and associate the query that is going to return multiple rows.
b. Open the cursor -- execute the query
c. Fetch the cursor -- Get the result set and loop through to process them
d. Close the cursor -- Close cursor processing.

Implicit Cursor:

Oracle implicitly opens a cursor to process each sql statement that is not associated with an explicit cursor. An implicit cursor is opened for all queries (even if it returns only one row as the result set). Unlike explicit cursors, there is no necessity to use the OPEN, FETCH and CLOSE statements with these cursors. Instead cursor attributes could be used.

Cursor attributes:

Cursor attributes are variables that take some value about the status of the cursor. These values are automatically set by Oracle and the programmer can read them not write values for them. There are four cursor attributes. They are

1. %FOUND
2. %ISOPEN
3. %NOTFOUND
4. %ROWCOUNT

%FOUND Attribute: Has a Row Been Fetched?

After a cursor or cursor variable is opened but before the first fetch, %FOUND returns NULL. After any fetches, it returns TRUE if the last fetch returned a row, or FALSE if the last fetch did not return a row.

DECLARE
CURSOR c1 IS SELECT last_name, salary FROM employees WHERE ROWNUM < 11;
my_ename employees.last_name%TYPE;
my_salary employees.salary%TYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO my_ename, my_salary;
IF c1%FOUND THEN -- fetch succeeded
DBMS_OUTPUT.PUT_LINE('Name = ' || my_ename || ', salary = ' || my_salary);
ELSE -- fetch failed, so exit loop
EXIT;
END IF;
END LOOP;
END;
/

If a cursor or cursor variable is not open, referencing it with %FOUND raises the predefined exception INVALID_CURSOR.

%ISOPEN Attribute: Is the Cursor Open?

%ISOPEN returns TRUE if its cursor or cursor variable is open; otherwise, %ISOPEN returns FALSE

DECLARE
CURSOR c1 IS SELECT last_name, salary FROM employees WHERE ROWNUM < 11;
the_name employees.last_name%TYPE;
the_salary employees.salary%TYPE;
BEGIN
IF c1%ISOPEN = FALSE THEN -- cursor was not already open
OPEN c1;
END IF;
FETCH c1 INTO the_name, the_salary;
CLOSE c1;
END;
/

%NOTFOUND Attribute: Has a Fetch Failed?

%NOTFOUND is the logical opposite of %FOUND. %NOTFOUND yields FALSE if the last fetch returned a row, or TRUE if the last fetch failed to return a row.

DECLARE
CURSOR c1 IS SELECT last_name, salary FROM employees WHERE ROWNUM < 11;
my_ename employees.last_name%TYPE;
my_salary employees.salary%TYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO my_ename, my_salary;
IF c1%NOTFOUND THEN -- fetch failed, so exit loop
-- Another form of this test is "EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;"
EXIT;
ELSE -- fetch succeeded
DBMS_OUTPUT.PUT_LINE('Name = ' || my_ename || ', salary = ' || my_salary);
END IF;
END LOOP;
END;
/

%ROWCOUNT Attribute: How Many Rows Fetched So Far?

When its cursor or cursor variable is opened, %ROWCOUNT is zeroed. Before the first fetch, %ROWCOUNT yields 0. Thereafter, it yields the number of rows fetched so far. The number is incremented if the last fetch returned a row.

DECLARE
CURSOR c1 IS SELECT last_name FROM employees WHERE ROWNUM < 11;
name employees.last_name%TYPE;
BEGIN
OPEN c1;
LOOP
FETCH c1 INTO name;
EXIT WHEN c1%NOTFOUND OR c1%NOTFOUND IS NULL;
DBMS_OUTPUT.PUT_LINE(c1%ROWCOUNT || '. ' || name);
IF c1%ROWCOUNT = 5 THEN
DBMS_OUTPUT.PUT_LINE('--- Fetched 5th record ---');
END IF;
END LOOP;
CLOSE c1;
END;
/

Referencing %FOUND, %NOTFOUND, or %ROWCOUNT before a cursor is opened or after it is closed raises INVALID_CURSOR.

After the first FETCH, if the result set was empty, %FOUND yields FALSE, %NOTFOUND yields TRUE, and %ROWCOUNT yields 0.

Another example of cursor:

CURSOR c1
IS
SELECT course_number
from courses_tbl
where course_name = name_in;

The result set of this cursor is all course_numbers whose course_name matches the variable called name_in.

Below is a function that uses this cursor.

CREATE OR REPLACE Function FindCourse
( name_in IN varchar2 )
RETURN number
IS
cnumber number;

CURSOR c1
IS
SELECT course_number
from courses_tbl
where course_name = name_in;

BEGIN

open c1;
fetch c1 into cnumber;

if c1%notfound then
cnumber := 9999;
end if;

close c1;

RETURN cnumber;

END;

Cursor with parameters:

CURSOR c2 (subject_id_in IN varchar2)
IS
SELECT course_number
from courses_tbl
where subject_id = subject_id_in;