, , ,

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.

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.

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;

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply