Cursors (Cont.)

Below are explanations for the previous program:
Line 01: DECLARE
Introduce the declaration section.

Line 02: /* Output variables to hold the result of the query: */
Everything between the two markers “/*” and “*/” is a comment.

Line 03: a T1.e%type;
It declares a variable a to have a type equal to the type of attribute e of the relation T1: 
     SQL> CREATE TABLE  T1 (
       2    e  INTEGER,
       3    f  INTEGER );
Although we know these types are INTEGER, we wisely make sure that whatever types they may have are copied to the PL/SQL variables.

Line 04: b T1.f%type;
It declares variable b to have a type equal to the type of attribute f of the relation T1.

Line 06: CURSOR T1Cursor IS SELECT e, f FROM T1 WHERE e < f FOR UPDATE;
Define the cursor T1Cursor. It ranges over a relation defined by the SELECT-FROM-WHERE query. That query selects those tuples of T1 whose first component is less than the second component. It declares the cursor FOR UPDATE since we will modify T1 using this cursor later on Line 15.

Line 07: BEGIN
Begin the executable section of the program.

Line 08: OPEN T1Cursor;
Open the cursor, an essential step.
view source
print?
01DECLARE
02  /* Output variables to hold the result of the query: */
03  a  T1.e%type;
04  b  T1.f%type;
05  /* Cursor declaration: */
06  CURSOR  T1Cursor  IS SELECT  e, f  FROM  T1  WHERE  e < f  FOR UPDATE;
07BEGIN
08  OPEN  T1Cursor;
09  LOOP
10    /* Retrieve each row of the result of the above query into PL/SQL variables: */
11    FETCH  T1Cursor  INTO  a, b;
12    /* If there are no more rows to fetch, exit the loop: */
13    EXIT WHEN  T1Cursor%NOTFOUND;
14    /* Delete the current tuple: */
15    DELETE FROM  T1  WHERE CURRENT OF  T1Cursor;
16    /* Insert the reverse tuple: */
17    INSERT INTO  T1  VALUES( b, a );
18  END LOOP;
19  /* Free cursor used by the query. */
20  CLOSE  T1Cursor;
21END;




      “The Cryptogram” is full of wit,    
      though it’s not exactly a barrel of laughs.