PL SQL Cursors
A cursor is the Private Memory area which is created by an Oracle server for manipulating the data.
Two Types of CURSORS
1. EXPLICIT : Multiple row SELECT STATEMENTS
2. IMPLICIT
All INSERT statements
All UPDATE statements
All DELETE statements
Single row SELECT….INTO Statements
Using Explicit Cursors
The set of rows returned by a query can consist of zero, one, or multiple rows, depending on how many rows meet your search criteria. When a query returns multiple rows, you can explicitly declare a cursor to process the rows. Moreover,
you can declare a cursor in the declarative part of any PL/SQL block, subprogram, or package.
You use three commands to control a cursor: OPEN, FETCH, and CLOSE. First, you initialize the cursor with the OPEN statement, which identifies the result set. Then, you use the FETCH statement to retrieve the first row. You can execute FETCH repeatedly until all rows have been retrieved. When the last row has been processed, you release the cursor with the CLOSE statement. You can process several queries in parallel by declaring and opening multiple cursors.
FOR UPDATE Clause
Two Types of CURSORS
1. EXPLICIT : Multiple row SELECT STATEMENTS
2. IMPLICIT
All INSERT statements
All UPDATE statements
All DELETE statements
Single row SELECT….INTO Statements
Using Explicit Cursors
The set of rows returned by a query can consist of zero, one, or multiple rows, depending on how many rows meet your search criteria. When a query returns multiple rows, you can explicitly declare a cursor to process the rows. Moreover,
you can declare a cursor in the declarative part of any PL/SQL block, subprogram, or package.
You use three commands to control a cursor: OPEN, FETCH, and CLOSE. First, you initialize the cursor with the OPEN statement, which identifies the result set. Then, you use the FETCH statement to retrieve the first row. You can execute FETCH repeatedly until all rows have been retrieved. When the last row has been processed, you release the cursor with the CLOSE statement. You can process several queries in parallel by declaring and opening multiple cursors.
Using Cursor FOR Loops
In most situations that require an explicit cursor, you can simplify coding by using a cursor FOR loop instead of the OPEN, FETCH, and CLOSE statements. A cursor FOR loop implicitly declares its loop index as a %ROWTYPE record, opens a cursor,
repeatedly fetches rows of values from the result set into fields in the record, and closes the cursor when all rows have been processed.
Consider the PL/SQL block below, which computes results from an experiment, then stores the results in a temporary table. The FOR loop index c1_rec is implicitly declared as a record. Its fields store all the column values fetched from the cursor c1. Dot notation is used to reference individual fields.
DECLARE
result temp.col1%TYPE;
CURSOR c1 IS
SELECT n1, n2, n3 FROM data_table WHERE exper_num = 1;
BEGIN
FOR c1_rec IN c1 LOOP
/* calculate and store the results */
result := c1_rec.n2 / (c1_rec.n1 + c1_rec.n3);
INSERT INTO temp VALUES (result, NULL, NULL);
END LOOP;
COMMIT;
END; Passing Parameters
You can pass parameters to the cursor in a cursor FOR loop. In the following example, you pass a department number. Then, you compute the total wages paid to employees in that department. Also, you determine how many employees have
salaries higher than $2000 and/or commissions larger than their salaries.
— available online in file ’examp8’
DECLARE
CURSOR emp_cursor(dnum NUMBER) IS
SELECT sal, comm FROM emp WHERE deptno = dnum;
total_wages NUMBER(11,2) := 0;
high_paid NUMBER(4) := 0;
higher_comm NUMBER(4) := 0;
BEGIN
/* The number of iterations will equal the number of rows
returned by emp_cursor. */
FOR emp_record IN emp_cursor(20) LOOP
emp_record.comm := NVL(emp_record.comm, 0);
total_wages := total_wages + emp_record.sal +
emp_record.comm;
IF emp_record.sal > 2000.00 THEN
high_paid := high_paid + 1;
END IF;
IF emp_record.comm > emp_record.sal THEN
higher_comm := higher_comm + 1;
END IF;
END LOOP;
INSERT INTO temp VALUES (high_paid, higher_comm,
’Total Wages: ’ || TO_CHAR(total_wages));
COMMIT;
END;
Implicit Cursors – FOR Loops
An Implicit Cursor is automatically associated with any SQL DML statement that does not have an explicit cursor associated with it.
This includes :
1. ALL INSERT statements
2. ALL UPDATE statements
3. ALL DELETE statements
4. ALL SELECT…INTO statements
QuickNotes – Implicit Cursors
1. Implicit cursor is called the “SQL” cursor –it stores information concerning the processing of the last SQL statement not associated with an explicit cursor.
2.OPEN, FETCH, AND CLOSE don’t apply.
3. All cursor attributes apply.
In most situations that require an explicit cursor, you can simplify coding by using a cursor FOR loop instead of the OPEN, FETCH, and CLOSE statements. A cursor FOR loop implicitly declares its loop index as a %ROWTYPE record, opens a cursor,
repeatedly fetches rows of values from the result set into fields in the record, and closes the cursor when all rows have been processed.
Consider the PL/SQL block below, which computes results from an experiment, then stores the results in a temporary table. The FOR loop index c1_rec is implicitly declared as a record. Its fields store all the column values fetched from the cursor c1. Dot notation is used to reference individual fields.
DECLARE
result temp.col1%TYPE;
CURSOR c1 IS
SELECT n1, n2, n3 FROM data_table WHERE exper_num = 1;
BEGIN
FOR c1_rec IN c1 LOOP
/* calculate and store the results */
result := c1_rec.n2 / (c1_rec.n1 + c1_rec.n3);
INSERT INTO temp VALUES (result, NULL, NULL);
END LOOP;
COMMIT;
END; Passing Parameters
You can pass parameters to the cursor in a cursor FOR loop. In the following example, you pass a department number. Then, you compute the total wages paid to employees in that department. Also, you determine how many employees have
salaries higher than $2000 and/or commissions larger than their salaries.
— available online in file ’examp8’
DECLARE
CURSOR emp_cursor(dnum NUMBER) IS
SELECT sal, comm FROM emp WHERE deptno = dnum;
total_wages NUMBER(11,2) := 0;
high_paid NUMBER(4) := 0;
higher_comm NUMBER(4) := 0;
BEGIN
/* The number of iterations will equal the number of rows
returned by emp_cursor. */
FOR emp_record IN emp_cursor(20) LOOP
emp_record.comm := NVL(emp_record.comm, 0);
total_wages := total_wages + emp_record.sal +
emp_record.comm;
IF emp_record.sal > 2000.00 THEN
high_paid := high_paid + 1;
END IF;
IF emp_record.comm > emp_record.sal THEN
higher_comm := higher_comm + 1;
END IF;
END LOOP;
INSERT INTO temp VALUES (high_paid, higher_comm,
’Total Wages: ’ || TO_CHAR(total_wages));
COMMIT;
END;
Implicit Cursors – FOR Loops
An Implicit Cursor is automatically associated with any SQL DML statement that does not have an explicit cursor associated with it.
This includes :
1. ALL INSERT statements
2. ALL UPDATE statements
3. ALL DELETE statements
4. ALL SELECT…INTO statements
QuickNotes – Implicit Cursors
1. Implicit cursor is called the “SQL” cursor –it stores information concerning the processing of the last SQL statement not associated with an explicit cursor.
2.OPEN, FETCH, AND CLOSE don’t apply.
3. All cursor attributes apply.
FOR UPDATE Clause
- Use explicit locking to deny access for the duration of a transaction
- Locks the rows before update or delete .
Syntax: Select …..
from
FOR UPDATE [ OF column reference ] [NOWAIT];e.g.
Declare
Cursor EmpCursor is
select emp_id, last_name, dept_name
from employees , department
where employees.dept_id=department.dept_id
and employees.dept_id=80
FOR UPDATE OF salary NOWAIT;
Leave a Reply
Want to join the discussion?Feel free to contribute!