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;
/
