PL/SQL - Cursors

Cursors:

Oracle creates a memory area, called as context area for processing an SQL statement, which contains all information needed for processing the statement.

A cursor is a pointer to this context area. PL/SQL controls the context area through cursor. A cursor holds the rows( one or many) returned by a SQL statement. The set of rows the cursor holds is referred to as the active set.

We can name a cursor so that we can use it in a program to fetch and process the rows returned by the SQL statements, one at a time,

There are two types of cursors:

• Implicit cursor
• Explicit cursor

Implicit cursor:

• Implicit cursors are automatically created by Oracle whenever an SQL statement(DML statements like INSERT, UPDATE AND DELETE) is executed. 
• For INSERT operations, the cutsor holds the data that needs to be inserted. For UPDATE AND DELETE operations, the cursors identifies the tows that would be affected.

In PL/SQL, we can refer to the most recent implicit cursor as the SQL CURSOR, which always has the following attributes:

%FOUND : Returns true of Select into, insert, update and delete statement affected one or more rows, else it returns FALSE.

%ISOPEN : always returns false, cause Oracle closes the sql cursor automatically after executing its associated sql statement.

%NOTFOUND : The logical opposite of %FOUND.

%ROWCOUNT: Returns the number of rows affected by an INSERT, UPDATE OR DELETE or returned by a SELECT INTO statement.

Example:

IF sql%found THEN

total_rows := sql%rowcount;

dbms_output.put_line( total_rows || 'customers selected ');

END IF;

Explicit cursor:

A cursor which we can create ourselves, not a default oracle one, is called explicit cursor. 

**A cursor is the name for a structure in memory, called a private SQL area, which the server allocates at runtime for each statement.

Steps to create an Explicit cursor:

• Declare
• Open
• Fetch one or more times
• Close

Example1: retrieving 1 row using explicit cursor:

CREATE OR REPLACE PROCEDURE GET_CUSTOMER

(

c_id IN NUMBER

)

AS

c_name customer.first_name%type;

c_country customer.country%type;

--declaration of cursor

cursor c is SELECT first_name, country FROM customer WHERE customer_id = c_id;

BEGIN

open c; -- open cursor

-- fetching of data

fetch c INTO c_name, c_country;

dbms_output.put_line('Name: '|| c_name);

dbms_output.put_line('Country: '|| c_country);

Close c; --closing cursor.

EXCEPTION

WHEN no_data_found THEN

dbms_output.put_line('No such customer!');

WHEN too_many_rows THEN

dbms_output.put_line('You got more than 1 row!');

WHEN others THEN

dbms_output.put_line('Error!');

END;

/

Example2: retrieving more than 1 row using explicit cursor:

CREATE OR REPLACE PROCEDURE GET_CUSTOMER

(

c_id IN NUMBER

)

AS

c_name customer.first_name%type;

c_country customer.country%type;

--declaration of cursor

cursor c is SELECT first_name, country FROM customer WHERE customer_id = c_id;

BEGIN

open c; -- open cursor

LOOP

-- fetching of data

fetch c INTO c_name, c_country;

EXIT WHEN C%NOTFOUND;

dbms_output.put_line('Name: '|| c_name);

dbms_output.put_line('Country: '|| c_country);

END LOOP;

Close c; --closing cursor.

END;

Using Records in cursor:

CREATE OR REPLACE PROCEDURE GET_CUSTOMER

(

c_id IN NUMBER

)

AS

--declaration of cursor

cursor c is SELECT first_name, country FROM customer WHERE customer_id = c_id;

c_rec customer%rowtype;

BEGIN

open c; -- open cursor

LOOP

-- fetching of data

fetch c INTO c_rec;

EXIT WHEN C%NOTFOUND;

dbms_output.put_line('Name: '|| c_rec.first_name);

dbms_output.put_line('Country: '|| c_rec.country);

END LOOP;

Close c; --closing cursor.

END;

Cursor For Loop:

With cursor FOR loop, the body of the loop is executed for each row returned by the sql query.

Syntax:

FOR customer_rec IN

(Select * from customer where customer_id = 10)

LOOP

Dbms_output.put_line(customer_rec.last_name);

END LOOP;

Example:

CREATE OR REPLACE PROCEDURE GET_CUSTOMER

(

c_id IN NUMBER

)

AS

BEGIN

FOR c_rec IN (SELECT first_name, country FROM customer WHERE customer_id = c_id)

LOOP

dbms_output.put_line('Name: '|| c_rec.first_name);

dbms_output.put_line('Country: '|| c_rec.country);

END LOOP;

END;

Cursor variable and reference cursor:

A cursor variable is a variable that points to a cursor or a result set. We can pass a cursor variable as a arugment to a procedure or function.

Pass a cursor variable back to the host environment that called the program unit - the result set can be consumed for display or other processing.

Syntax:

C_variable SYS_REFCURSOR;

Example:

CREATE OR REPLACE FUNCTION get_names

(

     cust_id IN NUMBER

)

     RETURN SYS_REFCURSOR

IS

     l_return SYS_REFCURSOR;

BEGIN

     OPEN l_return FOR

          SELECT first_name,last_name FROM        customer WHERE customer_id = cust_id;

     RETURN l_return;

END get_names;

CREATE OR REPLACE PROCEDURE DISPLAY_NAMES

IS

C_REC SYS_REFCURSOR;

FNAME VARCHAR2(50);

LNAME VARCHAR2(50);

BEGIN

C_REC := GET_NAMES(10);

LOOP

FETCH C_REC INTO FNAME,LNAME;

EXIT WHEN C_REC%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(FNAME);

DBMS_OUTPUT.PUT_LINE(LNAME);

END LOOP;

CLOSE C_REC;

END;

EXECUTE DISPLAY_NAMES();

Exceptions for cursors:

CURSOR_ALREADY_OPEN: we get this exception when we try to open a cursor thats already open.

INVALID_CURSOR: If we try to reference a cursor that does not exist. This may have happened cause we have executed a FETCH cursor or CLOSE cursor before opening the cursor.