Cursor is a temporary memory area (context
area) where Oracle executes SQL statements. Oracle associates every
SELECT statement with a cursor to hold the
query information in this context area.
There are two types of cursors - implicit
and explicit cursor.
Implicit cursor: Oracle automatically (implicit) controls or processes the
information of SQL statement executed. In this process, the user is unaware of
implicit cursor. Oracle automatically performs the
OPEN, FETCH, and CLOSEoperations.
Explicit cursor: Explicit cursor is used for the query that returns more than
one row of data. These cursors are explicitly declared in the
DECLARE section of the
PL/SQL block. This declaration allows to sequentially process each row of data
as the cursor returns it. In explicit cursor DECLARE,OPEN,FETCH,and CLOSE operations are done by the programmer.
The process of
working with an explicit cursor:
·
Declare: The
cursor is initialised into temporary memory area.
·
Open: The
cursor is opened which is declared, and the temporary memory area is allotted.
·
Fetch: Cursor
which is declared and opened can now retrieve rows from data.
·
Close:
The CLOSE statement disables the cursor, and releases the
temporary memory area.
Cursor Attributes
These attributes
return useful information about the execution of a SQL statement. Cursor
attributes can be used in procedural statements but not in SQL statements.
|
CURSOR ATTRIBUTE
|
SYNTAX
|
DESCRIPTION
|
|
%NOTFOUND
|
cursor_name%NOTFOUND
|
%NOTFOUND returns TRUE if last fetch did not return a row, Else FALSEif last fetch returns row.
|
|
%FOUND
|
cursor_name%FOUND
|
%FOUND returns TRUE if the cursor is open, fetches the row till the
last fetch. FALSE if last fetch did not return any row.
|
|
%ROWCOUNT
|
cursor_name%ROWCOUNT
|
%ROWCOUNT keeps track of fetched rows from cursor until it is closed.
|
|
%ISOPEN
|
cursor_name%ISOPEN
|
%ISOPEN returns TRUE if its cursor or cursor variable is open,
otherwise,%ISOPEN returns FALSE.
|
The general
syntax for creating a cursor is as follows :
CURSOR cursor_name IS select_statement;
·
cursor_name : name for the cursor
·
select_statement : select query which
returns multiple rows
As we have gone through an
introductory part , let's start exploring cursor with examples to understand
more in depth.
NOTE : In programming language, standardisation is
the most important part. When you declare a cursor in declaration part, always
start with initial c_cursor_name, it will always be clear that name refers to cursor.
Before we
start with Explicit cursor, let us understand...
Record Types
Record
is composite data structure, it is group of data items similar to the row of
database table, each item with its own name and datatype. Assume the record is
a variable which holds a table row or some columns of a table. If you want to
learn more about
RECORD,
you can refer to PL/SQL User's Guide and Reference chapter 5.
PL/SQL
supports only three types of records - table based, cursor based and
programmer defined.
·
A table based record is one whose entire structure is
similar to columns of a table.
·
A cursor based record is one whose structure is
similar to items (elements) of a pre-defined cursor.
·
A cursor and record datatype created in declaration
part of PL/SQL Block having the same variables with same datatype in the
same order is called programmer defined record.
NOTE: To create a
table based and cursor based record, always prefer to use
%ROWTYPE attribute. The %ROWTYPEattribute provides a record type that represents a row in a
database table. The record can store an entire row of data selected from the
table or fetched from a cursor or cursor variable. To learn more about %ROWTYPE, refer to PL/SQL User's Guide and Reference chapter 2.
Example 1. Table based record
DECLARE
vr_emp employees%ROWTYPE;
/* variable vr_emp is a record type existing database table employees.
record vr_emp has a similar structure to row of the employees table.*/
BEGIN
SELECT *
INTO vr_emp -- INTO clause always notifies only single row can be fetch
FROM employees
WHERE employee_id = 100;
/* to display each element of record,reference each attribute of record with dot notation*/
DBMS_OUTPUT.PUT_LINE('Employee Details : '||vr_emp.employee_id
||' '||vr_emp.first_name||' '||vr_emp.last_name||' '||vr_emp.salary);
END;
Employee Details : 100 Steven King 24000
Example 2. Cursor based record
DECLARE
CURSOR c_emp_detail IS
SELECT employee_id,first_name,last_name,salary
FROM employees;
rec_emp_detail c_emp_detail%ROWTYPE;
/* A cursor based record is based on elements of pre-Defined cursor.
A cursor based record can be only declared after its corresponding
cursor, else an error occurs.*/
BEGIN
OPEN c_emp_detail;
LOOP
FETCH c_emp_detail INTO rec_emp_detail;
EXIT WHEN c_emp_detail%NOTFOUND; -- cursor attribute to exit when no rows found to fetch.
DBMS_OUTPUT.PUT_LINE('Employees Details : '||' '||rec_emp_detail.employee_id
||' '||rec_emp_detail.first_name||' '||rec_emp_detail.last_name);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total number of rows : '||c_emp_detail%ROWCOUNT);
-- cursor attribute to find the total number of rows executed.
CLOSE c_emp_detail;
END;
Employees Details : 198 Donald OConnell
Employees Details : 199 Douglas Grant
Employees Details : 200 Jennifer Whalen
-----------------------------
Employees Details : 196 Alana Walsh
Employees Details : 197 Kevin Feeney
Total number of rows : 107
Example 3. Programmer defined record
DECLARE
CURSOR c_emp_detail IS
SELECT employee_id,first_name,last_name,salary
FROM employees;
/*declaring a record datatype, with same datatype of tables of database using %TYPE attribute,
with same order of corresponding cursor */
TYPE type_rectype IS RECORD
(emp_id employees.employee_id%TYPE,
f_name employees.first_name%TYPE,
l_name employees.last_name%TYPE,
s_salary employees.salary%TYPE
);
rec_type type_rectype; --variable of record datatype.
BEGIN
OPEN c_emp_detail;
LOOP
FETCH c_emp_detail INTO rec_type; -- Fetches the cursor into record variable.
EXIT WHEN c_emp_detail%NOTFOUND;
-- variable is part of each record datatype,so to reference it use dot notation in DBMS_OUTPUT.
DBMS_OUTPUT.PUT_LINE('Employees Details : '||' '||rec_type.emp_id
||' '||rec_type.f_name||' '||rec_type.l_name||' '||rec_type.s_salary);
END LOOP;
DBMS_OUTPUT.PUT_LINE('Total number of Employees : '||c_emp_detail%ROWCOUNT);
CLOSE c_emp_detail;
END;
Employees Details : 198 Donald OConnell 2600
Employees Details : 199 Douglas Grant 2600
Employees Details : 200 Jennifer Whalen 4400
-----------------------------
Employees Details : 196 Alana Walsh 3100
Employees Details : 197 Kevin Feeney 3000
Total number of Employees : 107
Cursors in Nested Loops
Example 4. Cursors in Nested Loops
DECLARE
CURSOR c_dept IS
SELECT *
FROM departments
WHERE manager_id IS NOT NULL
ORDER BY department_name;
r_dept c_dept%ROWTYPE;
-- Declaration of department cursor and record variable.
CURSOR c_emp (c_dept_no departments.department_id%TYPE) IS
SELECT *
FROM employees
WHERE department_id = c_dept_no;
r_emp c_emp%ROWTYPE;
-- Declaration of employees cursor and record variable.
BEGIN
OPEN c_dept;
LOOP
FETCH c_dept INTO r_dept;
EXIT WHEN c_dept%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('----------------------------------');
DBMS_OUTPUT.PUT_LINE('Department Name : '||r_dept.department_name);
DBMS_OUTPUT.PUT_LINE('----------------------------------');
OPEN c_emp(r_dept.department_id);
LOOP
FETCH c_emp INTO r_emp;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employees Details : '||r_emp.employee_id
||' '||r_emp.first_name||' '||r_emp.last_name||' '||r_emp.salary);
END LOOP;
CLOSE c_emp;
END LOOP;
CLOSE c_dept;
END;
----------------------------------
Department Name : Accounting
----------------------------------
Employees Details : 205 Shelley Higgins 12000
Employees Details : 206 William Gietz 8300
-----------------------------
----------------------------------
Department Name : Shipping
----------------------------------
Employees Details : 198 Donald OConnell 2600
Employees Details : 199 Douglas Grant 2600
-----------------------------
The
c_emp cursor specifies the parameter in c_dept_no. Each time cursor c_emp is called, it returns only the list
of employees of which the department_id specified by the parameter (c_dept_no). In Example
4, if we segregate the individual cursor c_emp, it becomes a parameterised cursor.
Cursor For Loops
In
Cursor for loops, the process of opening, fetching and closing is handled
implicitly. This makes the programmer code and maintain the blocks easily.
In
cursor for loop, before each iteration PL/SQL fetches into implicitly declared
record.
The
sequence of statements inside the loop is executed once for each row that
satisfies the query.
When
loop is left, the cursor is automatically closed.
The
cursor is closed even if you use an
EXIT or GOTO statement
to leave the loop before all rows are fetched.
Example 5. Implicit cursor for loop
BEGIN
FOR item IN(SELECT department_name,d.department_id,last_name,job_id,salary
FROM departments d JOIN employees e
ON e.department_id = d.department_id
WHERE JOB_ID = 'IT_PROG'
AND salary > 4800)
LOOP
DBMS_OUTPUT.PUT_LINE(item.last_name||' '||item.department_name
||' '||item.department_id||' '||item.job_id||' '||item.salary);
END LOOP;
END;
Hunold IT 60 IT_PROG 9000
Ernst IT 60 IT_PROG 6000
If you need the same query to reference from
different parts of the same procedure, you can declare a cursor with that
specific query,and get the results using cursor for loop. For this, I am going
to use the same
select query from Example 5.
Example 6. Explicit cursor for loop
DECLARE
CURSOR c_detail IS
SELECT department_name,d.department_id,last_name,job_id,salary
FROM departments d JOIN employees e
ON e.department_id = d.department_id
WHERE JOB_ID = 'IT_PROG'
AND salary > 4800;
BEGIN
FOR item IN c_detail
LOOP
DBMS_OUTPUT.PUT_LINE(item.last_name||' '||item.department_name
||' '||item.department_id||' '||item.job_id||' '||item.salary);
END LOOP;
END;
Hunold IT 60 IT_PROG 9000
Ernst IT 60 IT_PROG 6000
If
you notice, example 4 and example 7 outputs are the same, the difference is
example 7. We are using cursor for loop where record variable declares
r_dept and r_emp ,
opening fetching and closing is done automatically by each loop iteration until
all rows are fetched according to the specific query in cursor.
The
variable
v_dept_id is
initialized to be the department_id of
the current record of the c_dept cursor.
Thec_dept cursor
ties in the c_emp cursor
by means of this variable.
Thus,
when the cursor
c_emp is
processed, it is retrieving employees who have the department_id of
the current record for the c_dept cursor.
Each
iteration of the
c_dept cursor
will execute the DBMS_OUTPUT only
once. The DBMS_OUTPUT will
be executed once for each iteration of the c_emp cursor
loop, producing a line of output for each employee.DECLARE
v_dept_id departments.department_id%TYPE;
CURSOR c_dept IS
SELECT *
FROM departments
WHERE manager_id IS NOT NULL
ORDER BY department_name;
CURSOR c_emp IS
SELECT *
FROM employees
WHERE department_id = v_dept_id;
BEGIN
FOR r_dept IN c_dept
LOOP
v_dept_id := r_dept.department_id;
DBMS_OUTPUT.PUT_LINE('----------------------------------');
DBMS_OUTPUT.PUT_LINE('Department Name : '||r_dept.department_name);
DBMS_OUTPUT.PUT_LINE('----------------------------------');
FOR r_emp IN c_emp
LOOP
DBMS_OUTPUT.PUT_LINE('Employee Name : '||r_emp.last_name);
END LOOP;
END LOOP;
END;
----------------------------------
Department Name : Accounting
----------------------------------
Employee Name : Higgins
Employee Name : Gietz
----------------------------------
--------------------
----------------------------------
Department Name : Shipping
----------------------------------
Employee Name : OConnell
Employee Name : Grant
--------------------
Example 8. Using Parameters with Nested Cursors For Loops
CREATE OR REPLACE PROCEDURE print_emp_dept(v_lo_id IN locations.location_id%TYPE)
IS
v_flag departments.department_id%TYPE;
CURSOR c_locations IS
SELECT *
FROM locations
WHERE location_id = v_lo_id;
CURSOR c_departments(v_loc_id locations.location_id%TYPE) IS
SELECT l.location_id,department_name,department_id
FROM locations l JOIN departments d
ON l.location_id = d.location_id
WHERE l.location_id = v_loc_id
AND d.manager_id IS NOT NULL;
CURSOR c_employees (v_dept_id departments.department_id%TYPE,
v_loc_id locations.location_id%TYPE) IS
SELECT d.department_id,employee_id,first_name,last_name,salary,job_id,city
FROM locations l JOIN departments d
ON l.location_id = d.location_id
JOIN employees e
ON d.department_id = e.department_id
WHERE d.department_id = v_dept_id
AND l.location_id = v_loc_id;
BEGIN
FOR r_location IN c_locations
LOOP
DBMS_OUTPUT.PUT_LINE
('Location ID : '||r_location.location_id||
' Belong''s to '||r_location.city||' city');
DBMS_OUTPUT.PUT_LINE('In city '||r_location.city||' '||'Departments are ');
FOR r_department IN c_departments(r_location.location_id)
LOOP
DBMS_OUTPUT.PUT_LINE('-----------------------------------------');
DBMS_OUTPUT.PUT_LINE('Department ID :
'||r_department.department_id||' '||'Location ID : '||
r_department.location_id||' '||
'Department Name : '||r_department.department_name);
DBMS_OUTPUT.PUT_LINE('-----------------------------------------');
v_flag := r_department.department_id;
EXIT WHEN v_flag IS NULL;
FOR r_employee IN c_employees
(r_department.department_id,r_location.location_id)
LOOP
DBMS_OUTPUT.PUT_LINE(r_employee.employee_id||'
'||r_employee.first_name||' '||
r_employee.last_name||' work''s in city '||r_employee.city);
END LOOP;
END LOOP;
END LOOP;
IF v_flag IS NULL THEN
DBMS_OUTPUT.PUT_LINE('Not Available');
END IF;
END print_emp_dept;
Call the procedure:
CALL print_emp_dept(3000);
BEGIN
print_emp_dept(1700);
END;
Location ID : 1400 Belong's to Southlake city
In city Southlake Departments are
-----------------------------------------
Department ID :60 Location ID : 1400 Department Name : IT
-----------------------------------------
103 Alexander Hunold work's in city Southlake
104 Bruce Ernst work's in city Southlake
105 David Austin work's in city Southlake
106 Valli Pataballa work's in city Southlake
107 Diana Lorentz work's in city Southlake
In this complete process, the child cursor loop
ends first, then parent cursor loop ends, then grandparent cursor loop ends.
Example 9. For UPDATE and WHERE CURRENT CLAUSE
The
FOR UPDATE clause is only used in cursor, when update or delete statements are
used for database tables. Normally, when programmer executes SELECT statement,
there is no locking of rows. The main aim to use FOR UPDATE clause is to
lock rows when performing update or delete statements inside the cursor, and
restrict other users to perform any updation in particular database tables.
Once the updation is done inside the cursor, then COMMITor ROLLBACK placed inside the execution block
releases the lock. Now FOR UPDATE clause with particular Column name
i.e.. FOR UPDATE salary will only lock salary column
according to select statement even if there is joincondition, else FOR UPDATE clause will lock entire row of that
particular table.NOWAIT is
an optional keyword, if the rows are already locked by another programmer, then
control is immediately returned to programmer so that meanwhile he can do other
work before trying again. If you omit the keyword, then the wait may be a long
period of time.
The
WHERE CURRENT OF clause
can be used only if FOR UPDATE clause
is used in cursor.
The
WHERE current
of clause only references the cursor which fetches the latest row.
The
WHERE CURRENT OF CLAUSE is
useful to eliminate the where condition in update clause.
First,
I have created a demo table, because I don't want to change data in the
actual table of database.
create table emp1 as select * from employees;
create table dept1 as select * from departments;
DECLARE
CURSOR c_sal_update IS
SELECT employee_id,first_name,last_name,job_id,department_name,e.department_id,salary
FROM dept1 d , emp1 e
WHERE e.department_id = 80
FOR UPDATE OF salary NOWAIT;
rec_sal c_sal_update%ROWTYPE;
BEGIN
OPEN c_sal_update; -- rows are locked.
LOOP
FETCH c_sal_update INTO rec_sal;
EXIT WHEN c_sal_update%NOTFOUND;
IF rec_sal.job_id = 'SA_MAN' THEN
UPDATE emp1
SET salary = rec_sal.salary + 1000
WHERE CURRENT OF c_sal_update;
END IF;
END LOOP;
COMMIT; -- rows are unlocked.
CLOSE c_sal_update;
END;
OUTPUT
SELECT employee_id,first_name,last_name,job_id,department_id,salary
FROM emp1
WHERE job_id = 'SA_MAN'
AND department_id = 80;
EMPLOYEE_ID FIRST_NAME LAST_NAME JOB_ID DEPARTMENT_ID SALARY
----------- --------------- ----------------- ---------- ------------- --------
145 John Russell SA_MAN 80 15000
146 Karen Partners SA_MAN 80 14500
147 Alberto Errazuriz SA_MAN 80 13000
148 Gerald Cambrault SA_MAN 80 12000
149 Eleni Zlotkey SA_MAN 80 11500
Example 10. Cursor attributes with explicit cursor
The cursor declared in the below example
executes highest salary paid employees.
DECLARE
CURSOR c_high_sal IS
SELECT *
FROM (SELECT employee_id,first_name,last_name,salary
FROM employees ORDER BY salary DESC)
WHERE ROWNUM < 11;
high_sal c_high_sal%ROWTYPE;
BEGIN
IF NOT c_high_sal%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('Cursor is Closed');
END IF;
OPEN c_high_sal;
IF c_high_sal%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('Cursor is open');
END IF;
LOOP
FETCH c_high_sal INTO high_sal;
IF c_high_sal%FOUND THEN
DBMS_OUTPUT.PUT_LINE(high_sal.employee_id||' '||high_sal.first_name
||' '||high_sal.last_name||' '||high_sal.salary);
ELSE
EXIT; -- the same as exit when c_high_sal%NOTFOUND;
END IF;
END LOOP;
DBMS_OUTPUT.PUT_LINE(' Number of rows fetched : '||c_high_sal%ROWCOUNT);
CLOSE c_high_sal;
IF NOT c_high_sal%ISOPEN THEN
DBMS_OUTPUT.PUT_LINE('Cursor is closed ');
END IF;
END;
Cursor is Closed
Cursor is open
100 Steven King 24000
101 Neena Kochhar 17000
102 Lex De Haan 17000
145 John Russell 14000
146 Karen Partners 13500
201 Michael Hartstein 13000
205 Shelley Higgins 12000
108 Nancy Greenberg 12000
147 Alberto Errazuriz 12000
168 Lisa Ozer 11500
Number of rows fetched : 10
Cursor is closed
Example 11. Cursor attributes with Implicit cursor
An implicit cursor can tell us how many rows
were affected by an
update, insert and delete with the help
of %FOUND and %ROWCOUNT attribute.DECLARE
v_dept departments.department_id%TYPE := 270;
v_dept_name departments.department_name%TYPE;
BEGIN
DELETE FROM dept1 WHERE department_id = v_dept;
IF SQL%FOUND THEN
INSERT INTO dept1 VALUES(270,'Personnel',200,1700);
END IF;
DBMS_OUTPUT.PUT_LINE('Number of rows inserted : '||SQL%ROWCOUNT);
SELECT department_name INTO v_dept_name
FROM dept1
WHERE department_id = 270;
DBMS_OUTPUT.PUT_LINE('Department Name : '||v_dept_name);
END;
Number of rows inserted : 1
Department Name : Personnel
NOTE: Between implicit and explicit cursor, the fastest is implicit
cursor, because opening and closing of cursor and looping is automatically
handled by Oracle. Cursor for loop is also an implicit cursor where opening and
closing of cursor in done implicitly. But cursor should be used according to
requirement because each type of cursor has its own advantages and
disadvantages. To know more about this, you can refer to Steven Feuerstein's
book PL-SQL Programming 5th edition.
The concepts like Refs cursor, cursor with bulk
operations, cursor with packages and cursor with exception .
I hope you all have enjoyed reading this article. Comments are welcome....
No comments:
Post a Comment