Cursors (Cont.)

Below are explanations for the previous program (cont.):
Lines 09 - 18:
They are a PL/SQL loop. Notice that such a loop is bracketed by LOOP and END LOOP. Within the loop we find:

Line 09: LOOP
Start the loop.

Line 11: FETCH T1Cursor INTO a, b;
A fetch through the cursor into the local variables. In general, the FETCH statement must provide variables for each component of the tuple retrieved.

Line 13: EXIT WHEN T1Cursor%NOTFOUND;
A test for the loop-breaking condition. Its meaning should be clear: %NOTFOUND after the name of a cursor is true exactly when a fetch through that cursor has failed to find any more tuples.

Line 15: DELETE FROM T1 WHERE CURRENT OF T1Cursor;
An SQL DELETE statement that deletes the current tuple using the special WHERE condition CURRENT OF T1Cursor.

Line 17: INSERT INTO T1 VALUES( b, a );
An SQL INSERT statement that inserts the reverse tuple into T1.

Line 18: END LOOP;
End the loop.

Line 20: CLOSE T1Cursor;
Close the cursor.

Line 21: END;
End the PL/SQL program.
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 only way to get through life is to laugh your way through it.    
      You either have to laugh or cry.    
      I prefer to laugh.    
      Crying gives me a headache.”    
      ― Marjorie Pay Hinckley