Lab $5$: Cursors in PL$/$SQL

LAB $4$ Content

PL$/$SQL $-$ Cursors

In this chapter, we will discuss the cursors in PL$/$SQL$.$ Oracle creates a memory area, known as the context area, for processing an SQL statement, which contains all the information needed for processing the statement; for example, the number of rows processed, etc.

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

You can name a cursor so that it could be referred to in a program to fetch and process the rows returned by the SQL statement, one at a time. There are two types of cursors $$

$$ Implicit cursors

$$ Explicit cursors

Implicit Cursors

Implicit cursors are automatically created by Oracle whenever an SQL statement is executed, when there is no explicit cursor for the statement. Programmers cannot control the implicit cursors and the information in it$.$

Whenever a DML statement $($INSERT$,$ UPDATE and DELETE$)$ is issued, an implicit cursor is associated with this statement. For INSERT operations, the cursor holds the data that needs to be inserted. For UPDATE and DELETE operations, the cursor identifies the rows that would be affected.

In PL$/$SQL$,$ you can refer to the most recent implicit cursor as the$$SQL cursor, which always has attributes such as$\%$FOUND, $\%$ISOPEN, $\%$NOTFOUND, and$\%$ROWCOUNT. The SQL cursor has additional attributes,$\%$BULK$\_$ROWCOUNT$$and$\%$BULK$\_$EXCEPTIONS, designed for use with the$$FORALL$$statement$.$ The following table provides the description of the most used attributes $$

S$.$No Attribute & Description

$1\%$FOUND

Returns TRUE if an INSERT, UPDATE, or DELETE statement affected one or more rows or a SELECT INTO statement returned one or more rows. Otherwise, it returns FALSE.

$2\%$NOTFOUND

The logical opposite of $\%$FOUND. It returns TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows. Otherwise, it returns FALSE.

$3\%$ISOPEN

Always returns FALSE for implicit cursors, because Oracle closes the SQL cursor automatically after executing its associated SQL statement.

$4\%$ROWCOUNT

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

Any SQL cursor attribute will be accessed as$$sql$\%$attribute$\_$name$$as shown below in the example.

Example

We will be using the CUSTOMERS table we had created and used in the previous chapters.

Select $*$ from customers;

$+----+----------+-----+-----------+----------+$

$|$ ID $|$ NAME $|$ AGE $|$ ADDRESS $|$ SALARY $|$

$+----+----------+-----+-----------+----------+$

$|1|$ Ramesh $|32|$ Ahmedabad $|2000\mathrm{.}00|$

$|2|$ Khilan $|25|$ Delhi $|1500\mathrm{.}00|$

$|3|$ kaushik $|23|$ Kota $|2000\mathrm{.}00|$

$|4|$ Chaitali $|25|$ Mumbai $|6500\mathrm{.}00|$

$|5|$ Hardik $|27|$ Bhopal $|8500\mathrm{.}00|$

$|6|$ Komal $|22|$ MP $|4500\mathrm{.}00|$

$+----+----------+-----+-----------+----------+$

The following program will update the table and increase the salary of each customer by $500$ and use the$$SQL$\%$ROWCOUNT$$attribute to determine the number of rows affected $$

DECLARE

total$\_$rows number$(2)$;

BEGIN

UPDATE customers

SET salary $=$ salary $+500$;

IF sql$\%$notfound THEN

dbms$\_$output.put$\_$line$(\text{'}$no customers selected'$)$;

ELSIF sql$\%$found THEN

total$\_$rows :$=$ sql$\%$rowcount;

dbms$\_$output.put$\_$line$($ total$\_$rows $||\text{'}$ customers selected $\text{'})$;

END IF;

END;

$/$

When the above code is executed at the SQL prompt, it produces the following result $$

$6$ customers selected

PL$/$SQL procedure successfully completed.

If you check the records in customers table, you will find that the rows have been updated $$

Select $*$ from customers;

$+----+----------+-----+-----------+----------+$

$|$ ID $|$ NAME $|$ AGE $|$ ADDRESS $|$ SALARY $|$

$+----+----------+-----+-----------+----------+$

$|1|$ Ramesh $|32|$ Ahmedabad $|2500\mathrm{.}00|$

$|2|$ Khilan $|25|$ Delhi $|2000\mathrm{.}00|$

$|3|$ kaushik $|23|$ Kota $|2500\mathrm{.}00|$

$|4|$ Chaitali $|25|$ Mumbai $|7000\mathrm{.}00|$

$|5|$ Hardik $|27|$ Bhopal $|9000\mathrm{.}00|$

$|6|$ Komal $|22|$ MP $|5000\mathrm{.}00|$

$+----+----------+-----+-----------+----------+$

Explicit Cursors

Explicit cursors are programmer$-$defined cursors for gaining more control over the$$context area. An explicit cursor should be defined in the declaration section of the PL$/$SQL Block. It is created on a SELECT Statement which returns more than one row.

The syntax for creating an explicit cursor is $$

CURSOR cursor$\_$name IS select$\_$statement;

Working with an explicit cursor includes the following steps $$

$$ Declaring the cursor for initializing the memory

$$ Opening the cursor for allocating the memory

$$ Fetching the cursor for retrieving the data

$$ Closing the cursor to release the allocated memory

Declaring