Cursors in PL/SQL

Cursors

Some Review on Procedure:

CREATE PROCEDURE fire_employee (emp_id NUMBER) AS

BEGIN

DELETE FROM emp WHERE eid = emp_id;

END;

execute fire_employee(123)

create procedure getStudent(sid number) as

sss number;

begin

select age into sss from student where snum=sid;

end;

What is Cursor?

A cursor is a variable that runs through the tuples of some relation. This relation can be a stored table, or it can be the answer to some query.

Why do we need the Cursors?

• SELECT statement should return only one row at a time in previous PL/SQL programs. This is too restrictive in many applications. For Example:

DECLARE

Student_record student%ROWTYPE;

BEGIN

SELECT * INTO student_record

FROM student;

SELECT department INTO v_department

FROM classes

WHERE room_id = 999;

END;

The above example works only if one row is selected.
• We use the idea of Cursor to handle the above problem. Cursor is the mechanism that allow the program to step through the rows one at a time.
• By fetching into the cursor each tuple of the relation, we can write a program to read and process the value of each such tuple. If the relation is stored, we can also update or delete the tuple at the current cursor position.

Different types of Cursors
• Explicit Cursor and implicit Cursor (e.g., SQL statement in PL/SQL)
• Static cursor and Dynamic cursor
• Client side cursor and Server side cursor

Explicit cursor (the cursor name is explicitly assigned to a SELECT statement) involves four steps
(a) Declare the cursor (static part)          =>           CURSOR cursor_name IS select_statement;
(b) Open the cursor for a query               =>           OPEN cursor_name;
(c) Fetch the results into PL/SQL variables    =>     FETCH cursor_name INTO variable_list;
(d) Close the cursor                                    =>        CLOSE cursor_name;

• The cursor declaration is the only step that goes in the PL/SQL declarative section.

Implicit cursor is used for all other SQL statements (INSERT, UPDATE, DELETE, and single-row SELECT…INTO)

• Actually PL/SQL engine takes care of the above four steps automatically

Drawbacks of Implicit Cursors
• It is less efficient than an explicit cursor -- it performs minimum two fetches to see if it has
more than one rows.
• It is more vulnerable to data errors.
• It gives you less programmatic control.

Even if your query returns only a single row, you might still decide to use an explicit cursor.
However, there is no explicit cursor for UPDATE, DELETE, and INSERT statements.

Declaring a Cursor in the DECLARE section

employee_rec employee%ROWTYPE;

CURSOR employee_cur IS -- employee_cur is not declared
SELECT * FROM employee; -- this is a static cursor

• SELECT statement contains no INTO clause.
• A cursor declaration can reference PL/SQL variables in the WHERE clause.

The above cursor declaration associates the entire employee table with the cursor named employee_cur.

The SQL is static (or static SQL), which means that the content of the SQL statement is fixed (static).

The name of an explicit cursor is not a PL/SQL variable. Instead, it is an undeclared identifier used to refer to the query. You can't assign values to a cursor, nor can you use it in an expression.

PL/SQL variables in a Cursor

In native SQL, the SELECT list may contain both columns and expressions. In PL/SQL, the SELECT list may contain PL/SQL variables, expressions, and even functions as well as host language bind variables (> PL/SQL 2.1).

DECLARE
project_bonus NUMBER := 1000;

                     CURSOR employee_cur IS
                              SELECT employee_id, salary + project_bonus New_salary
                              FROM employee
                              WHERE hiredate < ADD_MONTHS (SYSDATE, -36);
BEGIN
…
END;

Note that it requires always a name for each column entry in the SELECT statement for a cursor. For example in a cursor declaration, the following SQL statement is not allowed:

SELECT state, SUM(salary)
FROM employee
GROUP BY state;

In this SQL, the SUM(salary) needs a name like

SELECT state, SUM(salary) AS sum_salary
FROM employee
GROUP BY state;

Opening a Cursor

e.g., in the BEGIN section
OPEN employee_cur;

When a cursor is opened, the following things happen:

• The values of the bind variables are examined.
• Based on the values of the bind variables, the active set (the query result) is determined.
• The active set pointer is set to the first row.

Fetching from a Cursor

Syntax: FETCH cursor_name INTO record_or_variable_list;
e.g., FETCH employee_cur INTO employee_rec;
e.g.,. FETCH employee_cur INTO name, age, salary

• When you fetch into a list of variables or record, the number of variables and types in the record must match the number of expressions in the SELECT list of the cursor.
• After each FETCH, the active set pointer is increased to the next row.
• You can FETCH from it until there are no more records left in the active set. At this point the %NOTFOUND cursor attribute for the cursor is set to TRUE. So, %NOTFOUND attribute is used to determine when the entire active set has been retrieved.
• The last fetch will assign NULL to the output variables.

Closing a Cursor

e.g.,   CLOSE employee_cur;
e.g.,   IF employee_cur%ISOPEN THEN
                 CLOSE employee_cur;
          END IF

The resources associated with it (memory space) can be freed.

When execution of the block terminates, PL/SQL will automatically close any local cursors. But don't depend on the runtime engine to do your cleaning up for you.

Cursor attributes                     --   four attributes appended to a cursor name
%FOUND                                -- error (not open or already closed), NULL (no fetch), TRUE, FALSE)
%NOTFOUND                        -- TRUE if record was not fetched successfully, FALSE otherwise.
%ISOPEN                                -- TRUE if cursor is open, FALSE otherwise.
%ROWCOUNT                       --   number of records fetched

e.g., EXIT WHEN employee_cur%NOTFOUND;

Processing Implicit Cursors

The same cursor attributes can be applied to the SQL cursor: e.g., SQL%NOTFOUND

SQL%NOTFOUND is not normally used with SELECT…INTO statements but used with Exception handler (NO_DATA_FOUND) -- we will talk about this in the later section.

EXAMPLE 1: Cursor Using LOOP … END LOOP (Explicit Cursor)

DECLARE
--  Declare variables to hold information about the students majoring in 'History'.
v_StudentID students.id%TYPE;
v_FirstName students.first_name%TYPE;
v_LastName students.last_name%TYPE;
-- Cursor to retrieve the information about History students
CURSOR c_HistoryStudents IS
                   SELECT id, first_name, last_name
                   FROM students
                   WHERE major = 'History';
BEGIN
        OPEN c_HistoryStudents;                   -- Open the cursor and initialize the active set
        LOOP
                 -- Retrieve information for the next student
                 FETCH c_HistoryStudents INTO v_StudentID, v_FirstName, v_LastName;
                 EXIT WHEN c_HistoryStudents%NOTFOUND;       -- Exit loop when there are no more rows to fetch

-- Process the fetched rows, in this case sign up each student for History 301 by inserting them
-- into the registered_students table. Record the first and last names in temp_table as well.

                INSERT INTO registered_students (student_id, department, course)
                            VALUES (v_StudentID, 'HIS', 301);
                INSERT INTO temp_table (num_col, char_col)
                            VALUES (v_StudentID, v_FirstName || ' ' || v_LastName);
       END LOOP;

        CLOSE c_HistoryStudents;    -- Free resources used by the cursor
       COMMIT;                            -- Commit our work
END;
/

EXAMPLE 2: Cursor using FOR LOOP … END LOOP (implicit Cursor)

DECLARE
    -- Cursor to retrieve the information about History students
    CURSOR c_HistoryStudents IS
           SELECT id, first_name, last_name
           FROM students
           WHERE major = 'History';
BEGIN
    FOR v_StudentData IN c_HistoryStudents          -- An implicit OPEN of c_HistoryStudents is done here.
          LOOP
              -- v_StudentData is implicitly Defined, and the scope of the variable is within the loop
              -- An implicit FETCH is done here. Now process the fetched rows as in the followings.
              INSERT INTO registered_students (student_id, department, course)
                      VALUES (v_StudentData.ID, 'HIS', 101);
             INSERT INTO temp_table (num_col, char_col)
                     VALUES (v_StudentData.ID, v_StudentData.first_name || ' ' || v_StudentData.last_name);
          -- Before the loop will continue, an implicit Check of c_HistoryStudents is done here.
          END LOOP;
      -- Now that the loop is finished, an implicit CLOSE of c_HistoryStudents is done.
      COMMIT;
END;
/

EXAMPLE 3: Cursor using Loop

The example below illustrates a cursor loop. It uses our example relation T1(e,f) whose tuples are pairs of integers. The program will delete every tuple whose first component is less than the second, and insert the reverse tuple into T1.

1) DECLARE /* Output variables to hold the result of the query: */ 2) a T1.e%TYPE; 3) b T1.f%TYPE; /* Cursor declaration: */ 4) CURSOR T1Cursor IS 5) SELECT e, f 6) FROM T1 7) WHERE e < f 8) FOR UPDATE; 9) BEGIN 10) OPEN T1Cursor; 11) LOOP /* Retrieve each row of the result of the above query into PL/SQL variables: */ 12) FETCH T1Cursor INTO a, b; /* If there are no more rows to fetch, exit the loop: */ 13) EXIT WHEN T1Cursor%NOTFOUND; /* Delete the current tuple: */ 14) DELETE FROM T1 WHERE CURRENT OF T1Cursor; /* Insert the reverse tuple: */ 15) INSERT INTO T1 VALUES(b, a); 16) END LOOP; /* Free cursor used by the query. */ 17) CLOSE T1Cursor; 18) END; 19) / 20) run;
Here are explanations for the various lines of this program:

Line (1) introduces the declaration section.

Lines (2) and (3) declare variables a and b to have types equal to the types of attributes e and f of the relation T1. Although we know these types are INTEGER, we wisely make sure that whatever types they may have are copied to the PL/SQL variables (compare with the previous example, where we were less careful and declared the corresponding variables to be of type NUMBER).

Lines (4) through (8) 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. Line (8) declares the cursor FOR UPDATE since we will modify T1 using this cursor later on Line (14). In general, FOR UPDATE is unnecessary if the cursor will not be used for modification.

Line (9) begins the executable section of the program.

Line (10) opens the cursor, an essential step.

Lines (11) through (16) are a PL/SQL loop. Notice that such a loop is bracketed by LOOP and END LOOP. Within the loop we find:

On Line (12), a fetch through the cursor into the local variables. In general, the FETCH statement must provide variables for each component of the tuple retrieved. Since the query of Lines (5) through (7) produces pairs, we have correctly provided two variables, and we know they are of the correct type.

On Line (13), 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.

On Line (14), a SQL DELETE statement that deletes the current tuple using the special WHERE condition CURRENT OF T1Cursor.

On Line (15), a SQL INSERT statement that inserts the reverse tuple into T1.

Line (17) closes the cursor.

Line (18) ends the PL/SQL program.

Lines (19) and (20) cause the program to execute.