PL/SQL Cursors

A cursor is a pointer to a private SQL area that stores information about processing a specific SELECT or DML statement. 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

A cursor that is constructed and managed by PL/SQL is an implicit cursor.
A cursor that you construct and manage is an explicit cursor.

Implicit Cursors

Implicit cursors are automatically created by Oracle whenever an SQL statement is executed. We cannot control an implicit cursor, but we can get information from its attributes.

The syntax of an implicit cursor attribute value is SQL%attribute (therefore, an implicit cursor is also called a SQL cursor), it always refers to the most recently run SELECT or DML statement. If no such statement has run, the value of SQL attribute is NULL.

An implicit cursor closes once its associated statement runs and its attribute values remain available until another SELECT or DML statement runs.

To save an attribute value for later use, assign it to a local variable immediately.

The implicit cursor attributes are:

SQL%ISOPEN : Is the Cursor Open ?
SQL%FOUND : Were Any Rows Affected ?
SQL%NOTFOUND : Were No Rows Affected ?
SQL%ROWCOUNT : How Many Rows Were Affected ?
SQL%BULK_ROWCOUNT : Getting Number of Rows Affected by FORALL Statement
SQL%BULK_EXCEPTIONS : Handling FORALL Exceptions After FORALL Statement Completes

Example :

DECLARE
 total_rows number(4);
BEGIN
 UPDATE emp
 SET sal = sal + 500;
 IF sql%notfound THEN
 dbms_output.put_line('no employees sal updated');
 ELSIF sql%found THEN 
 total_rows := sql%rowcount;
 dbms_output.put_line( total_rows || 'employees sal updated ');
 END IF;
END;
/

You can get the number of rows that each DML statement affected from the implicit cursor attribute SQL%BULK_ROWCOUNT.

Showing Number of Rows Affected by Each DELETE in FORALL

DECLARE
 TYPE NumList IS TABLE OF NUMBER;
 depts NumList := NumList(30, 40, 50);
BEGIN
 FORALL j IN depts.FIRST..depts.LAST
 DELETE FROM emp_temp WHERE department_id = depts(j);

FOR i IN depts.FIRST..depts.LAST LOOP
 DBMS_OUTPUT.PUT_LINE (
 'Statement #' || i || ' deleted ' ||
 SQL%BULK_ROWCOUNT(i) || ' rows.'
 );
 END LOOP;

DBMS_OUTPUT.PUT_LINE('Total rows deleted: ' || SQL%ROWCOUNT);
END;
/

Statement #1 deleted 6 rows.
Statement #2 deleted 45 rows.
Statement #3 deleted 5 rows.
Total rows deleted: 56

Explicit Cursors

An explicit cursor is a session cursor that you construct and manage. 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. Then you can process the query result set in either of these ways:

  • Open the explicit cursor with the OPEN statement, fetch rows from the result set with the FETCH statement, and close the explicit cursor with the CLOSE statement.
  • Use the explicit cursor in a cursor FOR LOOP statement

Declaring the Cursor

Declaring the cursor defines the cursor with a name and the associated SELECT statement.

CURSOR cursor_name [ parameter_list ] [ RETURN return_type ]
 IS select_statement;

Cursor with parameters

CURSOR cursor_name (parameter_list)
IS
 SELECT_statement;

Cursor with return clause

CURSOR cursor_name
RETURN field%ROWTYPE
IS
 SELECT_statement;

Opening the Cursor

After declaring and defining an explicit cursor, you can open it with the OPEN statement.

OPEN cursor_name;

Fetching Data with Explicit Cursors

After opening an explicit cursor, you can fetch the rows of the query result set with the FETCH statement.

FETCH cursor_name INTO into_clause;

Closing the Cursor

Closing the cursor means releasing the allocated memory.

CLOSE cursor_name;

Example :

DECLARE 
 c_id customers.id%type; 
 c_name customerS.No.ame%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; 
/

Fetching Same Explicit Cursor into Different Variables

DECLARE
 CURSOR c IS
 SELECT e.job_id, j.job_title
 FROM employees e, jobs j
 WHERE e.job_id = j.job_id AND e.manager_id = 100
 ORDER BY last_name;
 
 -- Record variables for rows of cursor result set:
 
 job1 c%ROWTYPE;
 job2 c%ROWTYPE;
 job3 c%ROWTYPE;
 job4 c%ROWTYPE;
 job5 c%ROWTYPE;
 
BEGIN
 OPEN c;
 FETCH c INTO job1; -- fetches first row
 FETCH c INTO job2; -- fetches second row
 FETCH c INTO job3; -- fetches third row
 FETCH c INTO job4; -- fetches fourth row
 FETCH c INTO job5; -- fetches fifth row
 CLOSE c;
 
 DBMS_OUTPUT.PUT_LINE(job1.job_title || ' (' || job1.job_id || ')');
 DBMS_OUTPUT.PUT_LINE(job2.job_title || ' (' || job2.job_id || ')');
 DBMS_OUTPUT.PUT_LINE(job3.job_title || ' (' || job3.job_id || ')');
 DBMS_OUTPUT.PUT_LINE(job4.job_title || ' (' || job4.job_id || ')');
 DBMS_OUTPUT.PUT_LINE(job5.job_title || ' (' || job5.job_id || ')');
END;
/

Explicit Cursor FOR LOOP Statement

DECLARE
 CURSOR c1 IS
 SELECT last_name, job_id FROM employees
 WHERE job_id LIKE '%CLERK%' AND manager_id > 120
 ORDER BY last_name;
BEGIN
 FOR item IN c1
 LOOP
 DBMS_OUTPUT.PUT_LINE
 ('Name = ' || item.last_name || ', Job = ' || item.job_id);
 END LOOP;
END;
/

Passing Parameters to Explicit Cursor FOR LOOP Statement

DECLARE
 CURSOR c1 (job VARCHAR2, max_wage NUMBER) IS
 SELECT * FROM employees
 WHERE job_id = job
 AND salary > max_wage;
BEGIN
 FOR person IN c1('ST_CLERK', 3000)
 LOOP
 -- process data record
 DBMS_OUTPUT.PUT_LINE (
 'Name = ' || person.last_name || ', salary = ' ||
 person.salary || ', Job Id = ' || person.job_id
 );
 END LOOP;
END;
/

Subquery in FROM Clause of Parent Query

DECLARE
 CURSOR c1 IS
 SELECT t1.department_id, department_name, staff
 FROM departments t1,
 ( SELECT department_id, COUNT(*) AS staff
 FROM employees
 GROUP BY department_id
 ) t2
 WHERE (t1.department_id = t2.department_id) AND staff >= 5
 ORDER BY staff;

BEGIN
 FOR dept IN c1
 LOOP
 DBMS_OUTPUT.PUT_LINE ('Department = '
 || dept.department_name || ', staff = ' || dept.staff);
 END LOOP;
END;
/

 

Advertisements

Post your Comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s