PLSQL Cursor:
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.
There are two types of cursors:
·
Implicit cursors
·
Explicit cursors
Implicit Cursor: 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.
Attribute of Implicit Cursor:
Attribute
|
Description
|
%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.
|
%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.
|
%ISOPEN
|
Always returns FALSE for implicit cursors, because Oracle closes
the SQL cursor automatically after executing its associated SQL statement.
|
%ROWCOUNT
|
Returns the number of rows affected by an INSERT, UPDATE, or
DELETE statement, or returned by a SELECT INTO statement.
|
Example: The
following program would update the table and increase 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('no customers
selected');
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || '
customers selected ');
END IF;
END;
/
Result:
6 customers selected
PL/SQL procedure successfully completed.
Explicit Cursor: 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.
Explicit cursor involves four steps:
·
Declaring the cursor for initializing in the
memory
·
Opening the cursor for allocating memory
·
Fetching the cursor for retrieving data
·
Closing the cursor to release allocated memory
Example:
DECLARE
c_id customers.id%type;
c_name customers.name%type;
c_addr customers.address%type;
CURSOR c_customers is
SELECT id, name, address FROM customers;
BEGIN
OPEN c_customers;
LOOP
FETCH c_customers into c_id, c_name, c_addr;
EXIT WHEN c_customers%notfound;
dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
END LOOP;
CLOSE c_customers;
END;
/
Result:
1 Ramesh Ahmedabad
2 Khilan Delhi
3 kaushik Kota
4 Chaitali Mumbai
5 Hardik Bhopal
6 Komal MP
PL/SQL procedure successfully completed.
