| 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| Cursor Attribute | Syntax | Explanation |
|---|
| %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; |
| b) | Add the necessary lines to the PL/SQL block that you just wrote to open the cursor. | | A1: | Answer: The following lines should be added to the lines in a). BEGIN OPEN c_student; |
| 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. | | A1: | Answer: The following lines should be added: LOOP FETCH c_student INTO vr_student; DBMS_OUTPUT.PUT_LINE(vr_student.name); |
| d) | Continue with the code you have developed by adding a close statement to the cursor. Is your code complete now? | | A1: | Answer: The following lines should be added: CLOSE c_student; The code is not complete since there is not a proper way to exit the loop. |
| e) | Explain what is occurring in this PL/SQL block. What will be the output from the preceding example? | | A1: | Answer: In this example, you declare a cursor that returns five student names. Next, you declare a cursor-based record. In the body of the program you process explicit cursors via the cursor loop. In the body of the loop, you assign each record returned by the cursor to the cursor-based record, vr_student_name. Next, you display its contents on the screen.When run, the example produces the following output: Student name: George Eakheit Student name: Leonard Millstein Student name: Austin V. Cadet Student name: Tamara Zapulla Student name: Jenny Goldsmith PL/SQL procedure successfully completed. | | 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; | | A2: | Answer: The DBMS_OUTPUT.PUT_LINE has been moved outside the loop. First the loop will process the five student records. The values for each record will be placed in the record vr_student_name, but each time the loop iterates it will replace the value in the record with a new value. When the five iterations of the loop are finished, it will exit because of the EXIT WHEN condition, leaving the vr_student_name record with the last value that was in the cursor. This is the only value that will be displayed via the DBMS_OUTPUT.PUT_LINE, which comes after the loop is closed. |
| g) | Explain what is declared in the previous example. Describe what is happening to the record and explain how this results in the output. | | A1: | Answer: In this example, you declare a record called vr_instructor. This record is based on the type you defined previously. In the body of the PL/SQL block, you initialize this record with the help of the SELECT INTO statement, and display its value on the screen. It is important to note that the columns of the SELECT INTO statement are listed in the same order the attributes are defined in instructor_info type. So there is no need to use dot notation for this record initialization.When run, this example produces the following output: Instructor, Tom Wojick, teaches 9 section(s) PL/SQL procedure successfully completed. |
9.1.3 Answers | 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. | | A1: | Answer: You can make use of attribute %NOTFOUND to close the loop. It would also be a wise idea to add an exception clause to the end of the block to close the cursor if it is still open. If you add the following statements to the end of your block, it will be complete. EXIT WHEN c_student%NOTFOUND; END LOOP; CLOSE c_student; EXCEPTION WHEN OTHERS THEN IF c_student%ISOPEN THEN CLOSE c_student; END IF; END; |
| b) | What will happen if this code is run? Describe what is happening in each phase of the example. | | A1: | Answer: The PL/SQL block ch09_3a would display the following output: Bayonne has a zipcode of 07002 PL/SQL procedure successfully completed. The declaration section declares a variable, v_city, anchored to the datatype of the city item in the zipcode table. The SELECT statement causes an implicit cursor to be opened, fetched, and then closed. The IF clause makes use of the attribute %ROWCOUNT to determine if the implicit cursor has a rowcount of 1 or not. If it does have a row count of 1, then the first DBMS_OUTPUT line will be displayed. You should notice that this example does not handle a situation where the rowcount is greater than 1. Since the zipcode table's primary key is the zipcode, this could happen. | | c) | Rerun this block, changing 07002 to 99999. What do you think will happen? Explain. | | A2: | Answer: The PL/SQL block would display the following: DECLARE ERROR at line 1: ORA-01403: no data found ORA-06512: at line 4 A select statement in a PL/SQL block that does not return any rows will raise a no data found exception. Since there was no exception handler, the preceding error would be displayed. | | 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? | | A3: | Answer: You may have expected the second and third condition of the IF statement to capture the instance of a %ROWCOUNT equal to 0. Now that you understand that a SELECT statement that returns no rows will raise a NO_DATA_FOUND exception, it would be a good idea to handle this by adding a <%WHEN NO_DATA_FOUND> exception to the existing block. You can add a %ROWCOUNT in the exception, either to display the rowcount in a DBMS_OUTPUT or to put an IF statement to display various possibilities. |
9.1.4 Answers| a) | Describe what is happening in each phase of example ch09_4a.sql. Use the line numbers to reference the example. | | A1: | Answer: The example illustrates a cursor fetch loop, in which multiple rows of data are returned from the query. The cursor is declared in the declaration section of the block (16) just like other identifiers. In the executable section of the block (715), a cursor is opened using the OPEN (8) statement. Because the cursor returns multiple rows, a loop is used to assign returned data to the variables with a FETCH statement (10). Because the loop statement has no other means of termination, there must be an exit condition specified. In this case, one of the attributes for the cursor is %NOTFOUND (12). The cursor is then closed to free the memory allocation (14). Additionally, if the exception handler is called, there is a check to see if the cursor is open (18) and if it is closed (20). | | b) | Modify the example to make use of the cursor attributes %FOUND and %ROWCOUNT. | | A2: | Answer: Your modification should look like this: -- ch09_5a.sql SET SERVEROUTPUT ON DECLARE v_sid student.student_id%TYPE; CURSOR c_student IS SELECT student_id FROM student WHERE student_id < 110; BEGIN OPEN c_student; LOOP FETCH c_student INTO v_sid; IF c_student%FOUND THEN DBMS_OUTPUT.PUT_LINE ('Just FETCHED row ' ||TO_CHAR(c_student%ROWCOUNT)|| ' Student ID: '||v_sid); ELSE EXIT; END IF; END LOOP; CLOSE c_student; EXCEPTION WHEN OTHERS THEN IF c_student%ISOPEN THEN CLOSE c_student; END IF; END; There has been a modification to the loop structure. Instead of having an exit condition, an IF statement is being used. The IF statement is making use of the cursor attribute %FOUND. This attribute returns true when a row has been "found" in the cursor and false when it has not. The next attribute %ROWCOUNT returns a number, which is the current row number of the cursor. | | 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. | | A3: | Answer: One method of doing this would be as follows: -- ch09_6a.sql SET SERVEROUTPUT ON DECLARE CURSOR c_student_enroll IS SELECT s.student_id, first_name, last_name, COUNT(*) enroll, (CASE WHEN count(*) = 1 Then ' class.' WHEN count(*) is null then ' no classes.' ELSE ' classes.' END) class FROM student s, enrollment e WHERE s.student_id = e.student_id AND s.student_id <110 GROUP BY s.student_id, first_name, last_name; r_student_enroll c_student_enroll%ROWTYPE; BEGIN OPEN c_student_enroll; LOOP FETCH c_student_enroll INTO r_student_enroll; EXIT WHEN c_student_enroll%NOTFOUND; DBMS_OUTPUT.PUT_LINE('Student INFO: ID '|| r_student_enroll.student_id||' is '|| r_student_enroll.first_name|| ' ' || r_student_enroll.last_name|| ' is enrolled in '||r_student_enroll.enroll|| r_student_enroll.class); END LOOP; CLOSE c_student_enroll; EXCEPTION WHEN OTHERS THEN IF c_student_enroll %ISOPEN THEN CLOSE c_student_enroll; END IF; END; | | Remember that the CASE syntax was introduced in Oracle 9i. This means that the previous statement will not run in Oracle 8 or 8i. You can change the CASE statement to a DECODE statement as follows: |
DECODE( count(*), 1, ' class. ', null, ' no classes.', 'classes') class In the declarative section, a cursor c_student_enroll is defined as well as a record, which is the type of a row of the cursor. The cursor loop structure makes use of an exit condition with the %NOTFOUND cursor attribute. When there are no more rows, the %NOTFOUND will be false and will cause the loop to exit. While the cursor is open and loop is processing, it will fetch a row of the cursor in a record one at a time. The DBMS output will cause each row to be displayed to the screen. Finally, the cursor is closed, and an exception clause will also close the cursor if any error is raised. Assorted Tips on Cursors Cursor SELECT LIST Match the Select list with PL/SQL variables or PL/SQL record components. The number of variables must be equal to the number of columns or expressions in the Select list. The number of the components of a record must match the columns or expressions in the Select list. Cursor Scope The scope of a cursor declared in the main block (or an enclosing block) extends to the sub-blocks. Expressions in a Cursor SELECT List PL/SQL variables, expressions, and even functions can be included in the Cursor Select list. Column Aliases in Cursors An alternative name you provide to a column or expression in the Select list. In an Explicit cursor column, aliases are required for calculated columns when |
Lab 9.1 Self-Review Questions In order to test your progress, you should be able to answer the following questions. Answers appear in Appendix A, Section 9.1. | 1) | Implicit cursors are the only way to fetch and manage data from the database. _____ True _____ False
| | 2) | What are cursor attributes used for? _____ Controlling cursors _____ Populating cursors _____ Ordering pizza _____ Closing cursors
| | 3) | Number the following steps in processing a cursor. _____ Fetch _____ Declare _____ Close _____ Open _____ Dance
| | 4) | What is the difference between an implicit and an explicit cursor? _____ An implicit cursor is easier to manage. _____ Cursor attributes can only be used on explicit cursors. _____ It is easier to trap errors with implicit cursors. _____ Explicit cursors give the programmer greater control.
| | 5) | What must be done to place a cursor in memory? _____ It must be fetched. _____ It must be pinned. _____ It must be memorized verbatim. _____ It must be declared.
|
|
