Lab 9.1 Cursor Manipulation
Lab Objectives
After this Lab, you will be able to:
In order for Oracle to process an SQL statement, it needs to create an area of memory known as the context area; this will have the information needed to process the statement. This information includes the number of rows processed by the statement, a pointer to the parsed representation of the statement (parsing an SQL statement is the process whereby information is transferred to the server, at which point the SQL statement is evaluated as being valid). In a query, the active set refers to the rows that will be returned.
A cursor is a handle, or pointer, to the context area. Through the cursor, a PL/SQL program can control the context area and what happens to it as the statement is processed. Two important features about the cursor are as follows:
Cursors allow you to fetch and process rows returned by a SELECT statement, one row at a time. A cursor is named so that it can be referenced.
Types of Cursors
There are two types of cursors:
An implicit cursor is automatically declared by Oracle every time an SQL statement is executed. The user will not be aware of this happening and will not be able to control or process the information in an implicit cursor. An explicit cursor is defined by the program for any query that returns more than one row of data. That means the programmer has declared the cursor within the PL/SQL code block. This declaration allows for the application to sequentially process each row of data as it is returned by the cursor.
Implicit Cursor
In order to better understand the capabilities of an explicit cursor, you first need to run through the process of an implicit cursor. The process is as follows:
Any given PL/SQL block issues an implicit cursor whenever an SQL statement is executed, as long as an explicit cursors does not exist for that SQL statement.
A cursor is automatically associated with every DML (Data Manipulation) statement (UPDATE, DELETE, INSERT).
All UPDATE and DELETE statements have cursors that identify the set of rows that will be affected by the operation.
An INSERT statement needs a place to receive the data that is to be inserted in the database; the implicit cursor fulfills this need.
The most recently opened cursor is called the 'SQL%' cursor.
The Processing of an Implicit Cursor.
The implicit cursor is used to process IN SERT, UPDATE, DELETE, and SELECT INTO statements. During the processing of an implicit cursor, Oracle automatically performs the OPEN, FETCH, and CLOSE operations.
|
An implicit cursor cannot tell you how many rows were affected by an update. SQL%ROWCOUNT returns numbers of rows updated. It can be used as follows:
|
SET SERVEROUTPUT ON
BEGIN
UPDATE student
SET first_name = 'B'
WHERE first_name LIKE 'B%';
DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT);
END;
Consider the following example of an implicit cursor.
FOR EXAMPLE
SET SERVEROUTPUT ON;
DECLARE
v_first_name VARCHAR2(35);
v_last_name VARCHAR2(35);
BEGIN
SELECT first_name, last_name
INTO v_first_name, v_last_name
FROM student
WHERE student_id = 123;
DBMS_OUTPUT.PUT_LINE ('Student name: '||
v_first_name||' '||v_last_name);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE
('There is no student with student ID 123');
END;
It is important to note that Oracle automatically associates an implicit cursor with the SELECT INTO statement and fetches the values for the variables, v_first_name and v_last_name. Once the SELECT INTO statement completes, Oracle closes the implicit cursor.
Unlike implicit cursor, explicit cursor is defined by the program for any query that returns more than one row of data. So you need to process an explicit cursor as follows. First you declare a cursor. Next, you open earlier declared cursor. Next, you fetch earlier declared and opened cursor. Finally, you close the cursor.
Explicit Cursor
The only means of generating an explicit cursor is for the cursor to be named in the DECLARE section of the PL/SQL block.
The advantages of declaring an explicit cursor over the indirect implicit cursor are that the explicit cursor gives more programmatic control to the programmer. Implicit cursors are less efficient than explicit cursors, and thus it is harder to trap data errors.
The process of working with an explicit cursor consists of the following steps:
Declaring the cursor.
This initializes the cursor into memory.
Opening the cursor.
The previously declared cursor can now be opened; memory is allotted.
Fetching the cursor.
Previously declared and opened cursor can now retrieve data; this is the process of fetching the cursor.
Closing the cursor.
Previously declared, opened, and fetched cursor must now be closed to release memory allocation.
Declaring a Cursor
Declaring a cursor defines the name of the cursor and associates it with a SELECT statement. The first step is to Declare the Cursor with the following syntax:
CURSOR c_cursor_name IS select statement
|
The naming conventions that are used in the Oracle Interactive Series advise you always to name a cursor as c_cursorname. By using a c_ in the beginning of the name, it will always be clear to you that the name is referencing a cursor.
|
It is not possible to make use of a cursor unless the complete cycle of (1) declaring, (2) opening, (3) fetching, and finally (4) closing has been performed. In order to explain these four steps, the following examples will have code fragments for each step and finally will show you the complete process.
FOR EXAMPLE
This is a PL/SQL fragment that demonstrates the first step of declaring a cursor. A cursor named C_MyCursor is declared as a select statement of all the rows in the zipcode table that have the item state equal to 'NY'.
DECLARE
CURSOR C_MyCursor IS
SELECT *
FROM zipcode
WHERE state = 'NY';
...
<code would continue here with Opening, Fetching
and closing of the cursor>
|
Cursor names follow the same rules of scope and visibility that apply to the PL/SQL identifiers. Because the name of the cursor is a PL/SQL identifier, it must be declared before it is referenced. Any valid select statement can be used to define a cursor, including joins and statements with the UNION or MINUS clause.
|
Record Types
A record is a composite data structure, which means that it is composed of more than one element. Records are very much like a row of a database table, but each element of the record does not stand on its own. PL/SQL supports three kinds of records: (1) table-based, (2) cursor-based, (3) programmer-defined.
A table-based record is one whose structure is drawn from the list of columns in the table. A cursor-based record is one whose structure matches the elements of a predefined cursor. To create a table-based or cursor-based record, use the %ROWTYPE attribute.
<record_name> <table_name or cursor_name>%ROWTYPE
FOR EXAMPLE
-- ch09_1a.sql
SET SERVEROUTPUT ON
DECLARE
vr_student student%ROWTYPE;
BEGIN
SELECT *
INTO vr_student
FROM student
WHERE student_id = 156;
DBMS_OUTPUT.PUT_LINE (vr_student.first_name||' '
||vr_student.last_name||' has an ID of 156');
EXCEPTION
WHEN no_data_found
THEN
RAISE_APPLICATION_ERROR(-2001,'The Student '||
'is not in the database');
END;
The variable vr_student is a record type of the existing database table student. That is, it has the same components as a row in the student table. A cursor-based record is much the same, except that it is drawn from the select list of an explicitly declared cursors. When referencing elements of the record, you use the same syntax that you use with tables.
record_name.item_name
In order to define a variable that is based on a cursor record, the cursor must first be declared. In the following lab, you will start by declaring a cursor and then proceed with the process of opening the cursor, fetching from the cursor, and finally closing the cursor.
A table-based record is drawn from a particular table structure. Consider the following code fragment.
FOR EXAMPLE
DECLARE
vr_zip ZIPCODE%ROWTYPE;
vr_instructor INSTRUCTOR%ROWTYPE;
Record vr_zip has structure similar to a row of the ZIPCODE table. Its elements are CITY, STATE, and ZIP. It is important to note that if CITY column of the ZIPCODE table has been defined as VARCHAR2(15), the attribute CITY of the vr_zip record will have the same datatype structure. Similarly, record vr_ instructor is based on the row of the INSTRUCTOR table.
Lab 9.1 Exercises
9.1.1 Make Use of Record Types
Here is an example of a record type in an anonymous PL/SQL block.
FOR EXAMPLE
SET SERVEROUTPUT ON;
DECLARE
vr_zip ZIPCODE%ROWTYPE;
BEGIN
SELECT *
INTO vr_zip
FROM zipcode
WHERE rownum < 2;
DBMS_OUTPUT.PUT_LINE('City: '||vr_zip.city);
DBMS_OUTPUT.PUT_LINE('State: '||vr_zip.state);
DBMS_OUTPUT.PUT_LINE('Zip: '||vr_zip.zip);
END;
| a) |
What will happen when the preceding example is run in a SQL*Plus session? |
A cursor-based record is based on the list of elements of a predefined cursor.
| b) |
b) Explain how the record type vr_student_name is being used in the following example.
FOR EXAMPLE
DECLARE
CURSOR c_student_name IS
SELECT first_name, last_name
FROM student;
vr_student_name c_student_name%ROWTYPE;
|
In the next Lab you will learn how to process an explicit cursor. Afterward you will address record types within that process.
9.1.2 Process an Explicit Cursor
| a) |
Write the declarative section of a PL/SQL block that defines a cursor named c_student, based on the student table with the last_name and the first_name concatenated into one item called name and leaving out the created_by and modified_by columns. Then declare a record based on this cursor. |
Opening a Cursor
The next step in controlling an explicit cursor is to open it. When the Open cursor statement is processed, the following four actions will take place automatically:
The variables (including bind variables) in the WHERE clause are examined. Based on the values of the variables, the active set is determined and the PL/SQL engine executes the query for that cursor. Variables are examined at cursor open time only. The PL/SQL engine identifies the active set of datathe rows from all involved tables that meet the WHERE clause criteria. The active set pointer is set to the first row.
The syntax for opening a cursor is
OPEN cursor_name;
|
A pointer into the active set is also established at the cursor open time. The pointer determines which row is the next to be fetched by the cursor. More than one cursor can be open at a time.
|
| b) |
Add the necessary lines to the PL/SQL block that you just wrote to open the cursor. |
Fetching Rows in a Cursor
After the cursor has been declared and opened, you can then retrieve data from the cursor. The process of getting the data from the cursor is referred to as fetching the cursor. There are two methods of fetching a cursor, done with the following command:
FETCH cursor_name INTO PL/SQL variables;
or
FETCH cursor_name INTO PL/SQL record;
When the cursor is fetched, the following occurs:
The fetch command is used to retrieve one row at a time from the active set. This is generally done inside a loop. The values of each row in the active set can then be stored into the corresponding variables or PL/SQL record one at a time, performing operations on each one successively. After each FETCH, the active set pointer is moved forward to the next row. Thus, each fetch will return successive rows of the active set, until the entire set is returned. The last FETCH will not assign values to the output variables; they will still contain their prior values.
FOR EXAMPLE
-- ch09_2a.sql
SET SERVEROUTPUT ON
DECLARE
CURSOR c_zip IS
SELECT *
FROM zipcode;
vr_zip c_zip%ROWTYPE;
BEGIN
OPEN c_zip;
LOOP
FETCH c_zip INTO vr_zip;
EXIT WHEN c_zip%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(vr_zip.zip||
' '||vr_zip.city||' '||vr_zip.state);
END LOOP;
END;
The lines in italics have not yet been covered but are essential for the code to run correctly. They will be explained later in this chapter.
| c) |
In Chapter 3 you learned how to construct a loop. For the PL/SQL block that you have been writing, add a loop. Inside the loop FETCH the cursor into the record. Include a DBMS_OUTPUT line inside the loop so that each time the loop iterates, all the information in the record is displayed in a SQL*Plus session. |
Closing a Cursor
Once all of the rows in the cursor have been processed (retrieved), the cursor should be closed. This tells the PL/SQL engine that the program is finished with the cursor, and the resources associated with it can be freed. The syntax for closing the cursor is
CLOSE cursor_name;
|
Once a cursor is closed, it is no longer valid to fetch from it. Likewise, it is not possible to close an already closed cursor (either one will result in an Oracle error).
|
| d) |
Continue with the code you have developed by adding a close statement to the cursor. Is your code complete now? |
Next, consider another example.
FOR EXAMPLE
SET SERVEROUTPUT ON;
DECLARE
CURSOR c_student_name IS
SELECT first_name, last_name
FROM student
WHERE rownum <= 5;
vr_student_name c_student_name%ROWTYPE;
BEGIN
OPEN c_student_name;
LOOP
FETCH c_student_name INTO vr_student_name;
EXIT WHEN c_student_name%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Student name: '||
vr_student_name.first_name||'
'||vr_student_name.last_name);
END LOOP;
CLOSE c_student_name;
END;
| e) |
Explain what is occurring in this PL/SQL block. What will be the output from the preceding example? | | f) |
Next, consider the same example with single modification. Notice that the DBMS_OUTPUT.PUT_LINE statement has been moved outside the loop (shown in bold letters). Execute this example, and try to explain why this version of the script produces different output.
FOR EXAMPLE
SET SERVEROUTPUT ON;
DECLARE
CURSOR c_student_name IS
SELECT first_name, last_name
FROM student
WHERE rownum <= 5;
vr_student_name c_student_name%ROWTYPE;
BEGIN
OPEN c_student_name;
LOOP
FETCH c_student_name INTO vr_student_name;
EXIT WHEN c_student_name%NOTFOUND;
END LOOP;
CLOSE c_student_name;
DBMS_OUTPUT.PUT_LINE('Student name: '||
vr_student_name.first_name||'
'||vr_student_name.last_name);
END;
|
A programmer-defined record is based on the record type defined by a programmer. First you declare a record type, and next, you declare a record based on the record type defined in the previous step as follows:
type type_name IS RECORD
(field_name 1 DATATYPE 1,
field_name 2 DATATYPE 2,
…
field_name N DATATYPE N);
record_name TYPE_NAME%ROWTYPE;
Consider the following code fragment.
FOR EXAMPLE
SET SERVEROUTPUT ON;
DECLARE
-- declare user-defined type
TYPE instructor_info IS RECORD
(instructor_id instructor.instructor_id%TYPE,
first_name instructor.first_name%TYPE,
last_name instructor.last_name%TYPE,
sections NUMBER(1));
-- declare a record based on the type defined above
rv_instructor instructor_info;
In this code fragment, you define your own type, instructor_info. This type contains four attributes: instructor's ID, first and last names, and number of sections taught by this instructor. Next, you declare a record based on the type just described. As a result, this record has structure similar to the type, instructor_ info. Consider the following example.
FOR EXAMPLE
SET SERVEROUTPUT ON;
DECLARE
TYPE instructor_info IS RECORD
(first_name instructor.first_name%TYPE,
last_name instructor.last_name%TYPE,
sections NUMBER);
rv_instructor instructor_info;
BEGIN
SELECT RTRIM(i.first_name),
RTRIM(i.last_name), COUNT(*)
INTO rv_instructor
FROM instructor i, section s
WHERE i.instructor_id = s.instructor_id
AND i.instructor_id = 102
GROUP BY i.first_name, i.last_name;
DBMS_OUTPUT.PUT_LINE('Instructor, '||
rv_instructor.first_name||'
'||rv_instructor.last_name||
', teaches '||rv_instructor.sections||'
section(s)');
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE
('There is no such instructor');
END;
| g) |
Explain what is declared in the previous example. Describe what is happening to the record and explain how this results in the output. |
9.1.3 Make Use of Cursor Attributes
Table 9.1 lists the attributes of a cursor, which are used to determine the result of a cursor operation when fetched or opened.
| a) |
Now that you know cursor attributes, you can use one of these to exit the loop within the code you developed in the previous example. Are you able to make a fully executable block now? If not, explain why. |
Cursor attributes can be used with implicit cursors by using the prefix SQL, for example, SQL%ROWCOUNT.
If you use a SELECT INTO syntax in your PL/SQL block, you will be creating an implicit cursor. You can then use these attributes on the implicit cursor.
FOR EXAMPLE
-- ch09_3a.sql
SET SERVEROUTPUT ON
DECLARE
v_city zipcode.city%type;
BEGIN
SELECT city
INTO v_city
FROM zipcode
WHERE zip = 07002;
IF SQL%ROWCOUNT = 1
THEN
DBMS_OUTPUT.PUT_LINE(v_city ||' has a '||
'zipcode of 07002');
ELSIF SQL%ROWCOUNT = 0
THEN
DBMS_OUTPUT.PUT_LINE('The zipcode 07002 is '||
' not in the database');
ELSE
DBMS_OUTPUT.PUT_LINE('Stop harassing me');
END IF;
END;
Table 9.1. Explicit Cursor Attributes|
%NOTFOUND |
cursor_name%NOTFOUND |
A Boolean attribute that returns TRUE if the previous FETCH did not return a row, and FALSE if it did. |
%FOUND |
cursor_name%FOUND |
A Boolean attribute that returns TRUE if the previous FETCH returned a row, and FALSE if it did not. |
%ROWCOUNT |
cursor_name%ROWCOUNT |
# of records fetched from a cursor at that point in time. |
%ISOPEN |
Cursor_name%ISOPEN |
A Boolean attribute that returns TRUE if cursor is open, FALSE if it is not. |
| b) |
What will happen if this code is run? Describe what is happening in each phase of the example. | | c) |
Rerun this block, changing 07002 to 99999. What do you think will happen? Explain. | | d) |
Now, try running this file. Did it run as you expected? Why or why not? What could be done to improve the way it handles a possible error condition? |
9.1.4 Put It All Together
Here is an example of the complete cycle of declaring, opening, fetching, and closing a cursor, including use of cursor attributes.
-- ch09_4a.sql
1> DECLARE
2> v_sid student.student_id%TYPE;
3> CURSOR c_student IS
4> SELECT student_id
5> FROM student
6> WHERE student_id < 110;
7> BEGIN
8> OPEN c_student;
9> LOOP
10> FETCH c_student INTO v_sid;
11> EXIT WHEN c_student%NOTFOUND;
12> DBMS_OUTPUT.PUT_LINE('STUDENT ID : '||v_sid);
13> END LOOP;
14> CLOSE c_student;
15> EXCEPTION
16> WHEN OTHERS
17> THEN
18> IF c_student%ISOPEN
19> THEN
20> CLOSE c_student;
21> END IF;
22> END;
| a) |
Describe what is happening in each phase of example ch09_4a.sql. Use the line numbers to reference the example. | | b) |
Modify the example to make use of the cursor attributes %FOUND and %ROWCOUNT. | | c) |
Fetch a cursor that has a data from the student table into a %ROWTYPE. Only select students with a student_id under 110. The columns are the STUDENT_ID, LAST_NAME, FIRST_NAME, and a count of the number of classes they are enrolled in (using the enrollment table). Fetch the cursor with a loop and then output all the columns.You will have to use an alias for the enrollment count. |
Lab 9.1 Exercise Answers
9.1.1 Answers| a) | What will happen when the preceding example is run in a SQL*Plus session? | A1:
| Answer: In this example, you select a single row for the ZIPCODE table into vr_zip record. Next, you display each element of the record on the screen. Notice that in order to reference each attribute of the record, dot notation is used. When run, the example produces the following output:
City: Santurce
State: PR
Zip: 00914
PL/SQL procedure successfully completed.
|
| b) | b) Explain how the record type vr_student_name is being used in the following example.
FOR EXAMPLE
DECLARE
CURSOR c_student_name IS
SELECT first_name, last_name
FROM student;
vr_student_name c_student_name%ROWTYPE;
| A1:
| Answer: Record vr_student_name has structure similar to a row returned by the SELECT statement defined in the cursor. It contains two attributes, student's first and last names.
|
It is important to note that a cursor-based record can be declared only after its corresponding cursor has been declared; otherwise, a compilation error will occur.
|
|
9.1.2 Answers| a) | Write the declarative section of a PL/SQL block that defines a cursor named c_student, based on the student table with the last_name and the first_name concatenated into one item called name and leaving out the created_by and modified_by columns. Then declare a record based on this cursor. | A1:
|
DECLARE
CURSOR c_student is
SELECT first_name||' '||Last_name name
FROM student;
vr_student c_student%ROWTYPE;
|
| 