Examples Using Cursors

TimesTen supports cursors. Use a cursor to handle the result set of a SELECT statement.

Examples in this section cover the following:

See Explicit Cursor Attributes in Oracle Database PL/SQL Language Reference for information about the cursor attributes used in these examples.

Fetching Values

This section provides examples of how to fetch values from a cursor, including how to fetch the values into a record.

The following example uses a cursor to select employee_id and last_name from the employees table where department_id is 30 Two variables are declared to hold the fetched values from the cursor, and the FETCH statement retrieves rows one at a time in a loop to retrieve all rows. Execution stops when there are no remaining rows in the cursor, illustrating use of the %NOTFOUND cursor attribute.

%NOTFOUND yields TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement returned no rows.

Command>  DECLARE
            CURSOR c_emp_cursor IS
              SELECT employee_id, last_name FROM employees
              WHERE department_id = 30;
            v_empno  employees.employee_id%TYPE;
            v_lname  employees.last_name%TYPE;
          BEGIN
            OPEN c_emp_cursor;
            LOOP
             FETCH c_emp_cursor INTO v_empno, v_lname;
            EXIT WHEN c_emp_cursor%NOTFOUND;
            DBMS_OUTPUT.PUT_LINE (v_empno || ' ' || v_lname);
            END LOOP;
            CLOSE c_emp_cursor;
          END;
          /

114 Raphaely
115 Khoo
116 Baida
117 Tobias
118 Himuro
119 Colmenares

This next example is similar to the preceding one, with the same results, but fetches the values into a PL/SQL record instead of PL/SQL variables.

Command> DECLARE
           CURSOR c_emp_cursor IS
             SELECT employee_id, last_name FROM employees
             WHERE department_id = 30;
           v_emp_record  c_emp_cursor%ROWTYPE;
         BEGIN
           OPEN c_emp_cursor;
           LOOP
             FETCH c_emp_cursor INTO v_emp_record;
           EXIT WHEN c_emp_cursor%NOTFOUND;
           DBMS_OUTPUT.PUT_LINE (v_emp_record.employee_id || ' ' |
             v_emp_record.last_name);
           END LOOP;
           CLOSE c_emp_cursor;
         END;
         /

114 Raphaely
115 Khoo
116 Baida
117 Tobias
118 Himuro
119 Colmenares
 
PL/SQL procedure successfully completed.

Using the %ROWCOUNT and %NOTFOUND Attributes

This example shows how to use the %ROWCOUNT cursor attribute as well as the %NOTFOUND cursor attribute shown in the examples in the preceding section, Fetching Values. %ROWCOUNT yields the number of rows affected by an INSERT, UPDATE, or DELETE statement or returned by a SELECT...INTO or FETCH...INTO statement.

Command> DECLARE
           CURSOR c_emp_cursor IS
             SELECT employee_id, last_name FROM employees
             WHERE department_id = 30;
           v_emp_record  c_emp_cursor%ROWTYPE;
         BEGIN
         OPEN c_emp_cursor;
         LOOP
           FETCH c_emp_cursor INTO v_emp_record;
           EXIT WHEN c_emp_cursor%ROWCOUNT > 10 OR c_emp_cursor%NOTFOUND;
           DBMS_OUTPUT.PUT_LINE (v_emp_record.employee_id || ' ' ||
             v_emp_record.last_name);
           END LOOP;
           CLOSE c_emp_cursor;
          END;
          /
114 Raphaely
115 Khoo
116 Baida
117 Tobias
118 Himuro
119 Colmenares
 
PL/SQL procedure successfully completed.

Using Cursor FOR Loops

PL/SQL in TimesTen supports cursor FOR loops.

In the first example, PL/SQL implicitly declares emp_record. No OPEN and CLOSE statements are necessary. The results are the same as in the example in the preceding section, Using the %ROWCOUNT and %NOTFOUND Attributes.

Command> DECLARE
           CURSOR c_emp_cursor IS
            SELECT employee_id, last_name FROM employees
            WHERE department_id = 30;
         BEGIN
           FOR emp_record IN c_emp_cursor
            LOOP
              DBMS_OUTPUT.PUT_LINE (emp_record.employee_id || ' ' ||
                 emp_record.last_name);
            END LOOP;
         END;
         /
114 Raphaely
115 Khoo
116 Baida
117 Tobias
118 Himuro
119 Colmenares
 
PL/SQL procedure successfully completed.

This second example illustrates a FOR loop using subqueries. The results are the same as in the preceding example and the example in the previous section, Using the %ROWCOUNT and %NOTFOUND Attributes.

Command> BEGIN
          FOR emp_record IN (SELECT employee_id, last_name FROM
           employees WHERE department_id = 30)
          LOOP
            DBMS_OUTPUT.PUT_LINE (emp_record.employee_id || ' ' ||
              emp_record.last_name);
           END LOOP;
         END;
         /
114 Raphaely
115 Khoo
116 Baida
117 Tobias
118 Himuro
119 Colmenares
 
PL/SQL procedure successfully completed.