What is Cursor?
When Oracle process an SQL statement, it needs to allocate memory called context area (which is part of the program global area (PGA) allocated on the server).
• Cursor is a handle (or pointer), to the context area. PL/SQL program can control the context area using Cursor.
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.
• We use the idea of Cursor to handle the above problem.
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 experssion.
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.
Cursor Examples Using LOOP … END LOOP
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;
-- Free resources used by the
cursor
CLOSE c_HistoryStudents;
COMMIT;
-- Commit our work
END;
Cursor Examples Using FOR LOOP … END LOOP
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;
/