When you're using SQL*Plus, you can submit a query without being concerned with the number of rows that are returned. It doesn't matter whether the query retrieves zero, one, or a thousand rows. However, in PL/SQL subprograms, you cannot use an ordinary SELECT statement to retrieve more than one row. If a SELECT statement in a PL/SQL subprogram--;be it an anonymous block, a stored procedure, or a trigger--;retrieves more than one row, Oracle returns an error message. Obviously, the capability to retrieve more than one row is essential, and the resource that Oracle provides to accomplish this job is the cursor. Oracle utilities such as SQL*Plus automatically create and use cursors.
Here's an example of how Oracle deals with a SELECT statement that returns more than one row:
SQL> declare 2 2 Employee_ID Employee.Employee_ID%type; 3 Last_Name Employee.Last_Name%type; 4 First_Name Employee.First_Name%type; 5 5 begin 6 6 select Employee_ID, Last_Name, First_Name 7 into Employee_ID, Last_Name, First_Name 8 from Employee 9 order by Employee_ID; 10 10 end; 11 / declare * ERROR at line 1: ORA-01422: exact fetch returns more than requested number of rows ORA-06512: at line 6
You can think of a cursor as a window into the result set of a query. (See Figure 22.1.) You will generally perform four steps when you use a cursor:
Open the cursor: The Oracle RDBMS executes the query associated with the cursor and determines the qualified rows (active set).
Figure 22.1. Illustration of a cursor.
NoteIn PL/SQL, a SELECT statement that returns more than one row raises the predefined exception TOO_MANY_ROWS.
This chapter focuses on the practical use of cursors in PL/SQL.
Here is a stored procedure that returns information about repair items that probably shouldn't be repaired because the market value of the item is less than the estimated repair cost. This procedure--;Get_Uneconomical_Repairs--;makes use of a cursor.
SQL> create or replace procedure Get_Uneconomical_Repairs is 2 2 Rep_ID Repair_Item.Repair_ID%type; 3 Item_No Repair_Item.Item_Number%type; 4 Est_Cost Repair_Item.Estimated_Cost%type; 5 Used_Val Product.Current_Used_Value%type; 6 6 cursor get_est_cost_used_val is 7 select Repair_ID, Item_Number, Estimated_Cost, Current_Used_Value 8 from Repair_Item I, Product P 9 where 10 I.Product_ID = P.Product_ID and 11 I.Manufacturer_ID = P.Manufacturer_ID and 12 Estimated_Cost > Current_Used_Value 13 order by Repair_ID, Item_Number; 14 14 begin 15 15 dbms_output.enable; 16 16 open get_est_cost_used_val; 17 17 loop 18 18 fetch get_est_cost_used_val 19 into Rep_ID, Item_No, Est_Cost, Used_Val; 20 exit when get_est_cost_used_val%notfound; 21 21 dbms_output.put_line(Rep_ID || ' ' || to_char(Item_No) || ' ' 22 || ' ' || to_char(Est_Cost) || ' ' 23 || to_char(Used_Val)); 24 24 24 end loop; 25 25 end; 26 / Procedure created. SQL> set serveroutput on SQL> execute Get_Uneconomical_Repairs; 501 1 231 85 501 1 231 85 PL/SQL procedure successfully completed.
Here's a look at each step of this cursor in detail. First, the cursor--;Get_Est_Cost_Used_Val--;is declared as a join between two tables--;Repair_Item and Product. Second, the cursor is opened within the executable section of the stored procedure. Third, a loop statement fetches rows from the cursor until no more rows are retrieved.
Every cursor must be declared before it can be used. Declaring a cursor means giving it a name and specifying the SELECT statement with which the cursor is associated. The basic syntax used in PL/SQL to declare a cursor is
CURSOR cursor-name [(parameter1 parameter1-datatype [:= default1], ... parameterN parameterN-datatype [:= defaultN])] IS select-stmt;
where cursor-name is the name of the cursor and subject to Oracle object-naming requirements.
parameter1 is the name of the first parameter to be supplied to the cursor.
parameter1-datatype is the datatype for parameter1.
default1 is an optional default value for parameter1.
parameterN is the name of the last parameter to be supplied to the cursor.
parameterN-datatype is the datatype for parameterN.
defaultN is an optional default value for parameterN.
select-stmt is a valid SELECT statement that will be associated with the declared cursor.
For instance, here is a simple cursor declaration that doesn't specify any parameters:
cursor get_employees is
select Employee_ID, Last_Name, First_Name
from Employee
order by Employee_ID;
The following example shows a cursor declaration with three parameters:
2 cursor patients_with_hypertension 3 (patient_age number, 4 normal_dyastolic, 5 normal_systolic) is 6 select patient_id, age, dyastolic, systolic 7 from patient 8 where 9 dyastolic > normal_dyastolic * (age+200)/200 and 10 systolic > normal_systolic * (age+200)/200;
Here is the same cursor with default values specified for each of the cursor parameters:
2 cursor patients_with_hypertension 3 (patient_age number default 55, 4 normal_dyastolic number default 70, 5 normal_systolic number default 130) is 6 select patient_id, age, dyastolic, systolic 7 from patient 8 where 9 dyastolic > normal_dyastolic * (age+200)/200 and 10 systolic > normal_systolic * (age+200)/200;
NoteIf you use any of the Oracle precompilers--;for example, Pro*C--;you'll need to use a cursor to retrieve more than one row via a SELECT statement.
Before you can fetch rows from a cursor, you must open the cursor. When the cursor is opened, its SELECT statement is executed and Oracle constructs a list of the qualified rows. These rows are referred to as the active set. If the cursor was declared without any parameters, the syntax is very simple.
open my_cursor;
If the cursor was declared with parameters, you must supply a PL/SQL variable or a literal value for each parameter when you open the cursor.
SQL> declare 2 2 cursor patients_with_hypertension 3 (patient_age number, 4 normal_dyastolic number) is 5 select patient_id 6 from patient 7 where 8 dyastolic > normal_dyastolic * (age+200)/200 and 9 systolic > 180; 10 10 Patient_ID Patient.Patient_ID%type; 11 11 begin 12 12 open patients_with_hypertension (45, 80); 13 13 end; 14 / PL/SQL procedure successfully completed.
If the cursor was declared with parameters--;and default values were specified for those parameters--;you do not have to furnish a PL/SQL variable or a literal value for each parameter.
SQL> declare 2 2 cursor patients_with_hypertension 3 (patient_age number default 55, 4 normal_dyastolic number default 70, 5 normal_systolic number default 130) is 6 select patient_id 7 from patient 8 where 9 dyastolic > normal_dyastolic * (age+200)/200 and 10 systolic > normal_systolic * (age+200)/200; 11 11 Patient_ID Patient.Patient_ID%type; 12 12 begin 13 13 dbms_output.enable; 14 14 open patients_with_hypertension; 15 15 loop 16 16 fetch patients_with_hypertension 17 into Patient_ID; 18 exit when patients_with_hypertension%notfound; 19 19 dbms_output.put_line(patient_record.patient_id); 20 20 end loop; 21 21 end; 22 / N3393 PL/SQL procedure successfully completed.
If the cursor was declared with parameters--;but no default values were specified for those parameters--;you must supply a PL/SQL variable or a literal value for each parameter. Otherwise, Oracle will reject the open cursor statement.
SQL> declare
2
2 cursor patients_with_hypertension
3 (patient_age number,
4 normal_dyastolic number,
5 normal_systolic number) is
6 select patient_id
7 from patient
8 where
9 dyastolic > normal_dyastolic * (age+200)/200 and
10 systolic > 180;
11
11 Patient_ID Patient.Patient_ID%type;
12
12 begin
13
13 open patients_with_hypertension;
14
14 end;
15 /
declare
*
ERROR at line 1:
ORA-06550: line 13, column 1:
PLS-00306: wrong number or types of arguments
in call to 'PATIENTS_WITH_HYPERTENSION'
ORA-06550: line 13, column 1:
PL/SQL: SQL Statement ignored
Once the cursor has been opened, the query has been executed and the qualified rows have been identified. To retrieve the rows, you must execute the FETCH statement, which retrieves the value of each column specified in the cursor's SELECT statement and places it in a PL/SQL variable. In general, you'll want to fetch rows within a loop. To illustrate, here is an anonymous PL/SQL block that fetches rows from the Employee table:
SQL> declare 2 2 Employee_ID Employee.Employee_ID%type; 3 Last_Name Employee.Last_Name%type; 4 First_Name Employee.First_Name%type; 5 5 cursor get_employees is 6 select Employee_ID, Last_Name, First_Name 7 from Employee 8 order by Employee_ID; 9 9 begin 10 10 dbms_output.enable; 11 11 open get_employees; 12 12 loop 13 13 fetch get_employees 14 into 15 Employee_ID, Last_Name, First_Name; 16 16 exit when get_employees%notfound; 17 17 dbms_output.put_line(to_char(Employee_ID)); 18 18 end loop; 19 19 end; 20 / 1001 1002 1003 1004 1005 1006 1007 1008 1009 PL/SQL procedure successfully completed.
Never make any assumptions about how many rows will be fetched from a cursor. Instead, you should use the EXIT statement to exit the loop when all rows have been fetched from the cursor. The syntax to use is
EXIT [label] [WHEN condition]
where label is an optional label name that specifies which loop should be exited and condition is a PL/SQL condition that returns a Boolean value.
Four specific attributes are associated with declared cursors: %ROWCOUNT, %FOUND, %NOTFOUND, and %ISOPEN. These attributes are referenced by placing them after a cursor's name. To terminate a loop with the EXIT statement, reference a cursor's %NOTFOUND attribute in this way:
exit when get_employees%notfound;
You must close a cursor for two reasons:
If a PL/SQL program doesn't close a cursor, Oracle closes the cursor when the subprogram disconnects from the Oracle database, either by terminating or by performing a DISCONNECT. Closing a cursor is straightforward:
close get_employees;
The following example shows you how you to supply a different set of parameter values to a cursor by closing the cursor, changing the parameter values, and reopening the cursor. First, the patients_with_hypertension cursor is opened with age set to 50 and normal_dyastolic set to 80. The rows are fetched in a loop, and the cursor is closed. Next, the cursor is reopened with age equal to 40 and normal_dyastolic set at 70. The rows are fetched in a loop with different results.
SQL> declare 2
2 Patient_ID Patient.Patient_ID%type;
3 Age Patient.Age%type;
4 Dyastolic Patient.Dyastolic%type;
5
5 cursor patients_with_hypertension
6 (patient_age number,
7 normal_dyastolic number) is
8 select patient_id, age, dyastolic
9 from patient
10 where
11 dyastolic > normal_dyastolic * (age+200)/200;
12
12 begin
13
13 dbms_output.enable;
14
14 open patients_with_hypertension (50, 80);
15
15 loop
16
16 fetch patients_with_hypertension
17 into Patient_ID, Age, Dyastolic;
18 exit when patients_with_hypertension%notfound;
19
19 dbms_output.put_line('With age=50, dyas=80: ' || Patient_ID);
20
20 end loop;
21
21 close patients_with_hypertension;
22
22 open patients_with_hypertension (40, 70);
23
23 loop
24
24 fetch patients_with_hypertension
25 into Patient_ID, Age, Dyastolic;
26 exit when patients_with_hypertension%notfound;
27
27 dbms_output.put_line('With age=40, dyas=70: ' || Patient_ID);
28
28 end loop;
29
29 close patients_with_hypertension;
30
30 end;
31 /
With age=50, dyas=80: N3393
With age=40, dyas=70: A2002
With age=40, dyas=70: N3393
With age=40, dyas=70: E3893
PL/SQL procedure successfully completed.
As an alternative to opening, fetching, and closing a cursor, Oracle furnishes another approach--;the cursor FOR loop. With the cursor FOR loop, Oracle implicitly declares a variable--;the loop index--;that is of the same record type as the cursor's record.
SQL> declare
2
2 cursor Get_Unecon_Repairs is
3 select Repair_ID, Item_Number, Estimated_Cost, Current_Used_Value
4 from Repair_Item I, Product P
5 where
6 I.Product_ID = P.Product_ID and
7 I.Manufacturer_ID = P.Manufacturer_ID and
8 Estimated_Cost > Current_Used_Value
9 order by Repair_ID, Item_Number;
10
10 begin
11
11 dbms_output.enable;
12
12 for Get_Unecon_Repairs_Rec in Get_Unecon_Repairs loop
13
13 dbms_output.put_line ('Repair ID: ' ||
14 Get_Unecon_Repairs_Rec.Repair_ID);
15
15 end loop;
16
16 end;
17 /
Repair ID: 501
PL/SQL procedure successfully completed.
The name that follows FOR is the loop index that is implicitly declared. However, you can't reference the loop index outside of the loop statement.
SQL> declare
2
2 cursor Get_Unecon_Repairs is
3 select Repair_ID, Item_Number, Estimated_Cost, Current_Used_Value
4 from Repair_Item I, Product P
5 where
6 I.Product_ID = P.Product_ID and
7 I.Manufacturer_ID = P.Manufacturer_ID and
8 Estimated_Cost > Current_Used_Value
9 order by Repair_ID, Item_Number;
10
10 begin
11
11 dbms_output.enable;
12
12 for Get_Unecon_Repairs_Rec in Get_Unecon_Repairs loop
13
13 dbms_output.put_line ('Repair ID: ' ||
14 Get_Unecon_Repairs_Rec.Repair_ID);
15
15 end loop;
16
16 Get_Unecon_Repairs_Rec.Manufacturer_ID := 'XYZ';
17
17 end;
18 /
declare
*
ERROR at line 1:
ORA-06550: line 16, column 24:
PLS-00201: identifier 'GET_UNECON_REPAIRS_REC.MANUFACTURER_ID' must be declared
ORA-06550: line 16, column 1:
PL/SQL: Statement ignored
The previous examples used the %NOTFOUND attribute to determine whether a FETCH statement retrieved a row. When all of the rows in the active set have been fetched and the last FETCH statement fails to retrieve a row, %NOTFOUND evaluates to TRUE.
NoteBefore the FETCH statement is invoked, %NOTFOUND returns a NULL. If your PL/SQL program has a loop in which the FETCH statement might not be called, you should consider testing for the condition of %NOTFOUND evaluating to NULL.
You don't need a counter to keep track of the number of rows that are fetched from a cursor. Instead, reference the cursor's %ROWCOUNT attribute. %ROWCOUNT returns the running count of the rows that have been fetched. Here's an example:
SQL> declare
2
2 Employee_ID Employee.Employee_ID%type;
3 Last_Name Employee.Last_Name%type;
4 First_Name Employee.First_Name%type;
5
5 cursor get_employees is
6 select Employee_ID, Last_Name, First_Name
7 from Employee
8 order by Employee_ID;
9
9 begin
10
10 dbms_output.enable;
11
11 open get_employees;
12
12 loop
13
13 fetch get_employees
14 into
15 Employee_ID, Last_Name, First_Name;
16
16 exit when get_employees%notfound;
17
17 dbms_output.put_line ('Rowcount: ' || get_employees%rowcount);
18
18 end loop;
19
19 end;
20 /
Rowcount: 1
Rowcount: 2
Rowcount: 3
Rowcount: 4
Rowcount: 5
Rowcount: 6
Rowcount: 7
Rowcount: 8
Rowcount: 9
PL/SQL procedure successfully completed.
Instead of exiting a loop when there are no more rows in the cursor, you can specify an exit condition when a specified %ROWCOUNT is achieved.
SQL> declare
2
2 Employee_ID Employee.Employee_ID%type;
3 Last_Name Employee.Last_Name%type;
4 First_Name Employee.First_Name%type;
5
5 cursor get_employees is
6 select Employee_ID, Last_Name, First_Name
7 from Employee
8 order by Employee_ID;
9
9 begin
10
10 dbms_output.enable;
11
11 open get_employees;
12
12 loop
13
13 fetch get_employees
14 into
15 Employee_ID, Last_Name, First_Name;
16
16 exit when get_employees%rowcount >= 5;
17
17 dbms_output.put_line ('Rowcount: ' || get_employees%rowcount);
18
18 end loop;
19
19 end;
20 /
Rowcount: 1
Rowcount: 2
Rowcount: 3
Rowcount: 4
PL/SQL procedure successfully completed.
A PL/SQL subprogram can have multiple cursors open simultaneously. The program logic of a PL/SQL subprogram might be quite complex; during the subprogram's execution, you might need to determine whether a particular cursor is open. The %ISOPEN attribute can be associated with any cursor; it returns TRUE if the cursor is open and FALSE if the cursor is not open.
SQL> declare
2
2 Employee_ID Employee.Employee_ID%type;
3 Last_Name Employee.Last_Name%type;
4 First_Name Employee.First_Name%type;
5
5 cursor get_employees is
6 select Employee_ID, Last_Name, First_Name
7 from Employee
8 order by Employee_ID;
9
9 begin
10
10 dbms_output.enable;
11
11 open get_employees;
12
12 loop
13
13 fetch get_employees
14 into
15 Employee_ID, Last_Name, First_Name;
16
16 exit when get_employees%rowcount >= 5;
17
17 if get_employees%isopen then
18 dbms_output.put_line ('The get_employees cursor is open.');
19 else
20 dbms_output.put_line ('The get_employees cursor is closed.');
21 end if;
22
22 end loop;
23
23 close get_employees;
24
24 if get_employees%isopen then
25 dbms_output.put_line ('The get_employees cursor is open.');
26 else
27 dbms_output.put_line ('The get_employees cursor is closed.');
28 end if;
29
29 end;
30 /
The get_employees cursor is open.
The get_employees cursor is open.
The get_employees cursor is open.
The get_employees cursor is open.
The get_employees cursor is closed.
PL/SQL procedure successfully completed.
You can declare cursors based on very complex SELECT statements--;for example, multiple-table joins or UNIONs. However, some calculations involve multiple steps that simply can't be performed in a single SELECT. In such cases, you can implement the processing logic by nesting one cursor inside another cursor.
I'll illustrate this concept with an example from the repair store application. Using the Repair_Header and Repair_Item tables, suppose that you wanted to retrieve each repair header, analyze the related customer information, and--;depending on the results of the customer analysis--;retrieve information on each repair item for that repair. You can achieve this result by defining two cursors: get_repair_header_info and get_repair_item_info. The second cursor is defined with the argument Repair_ID. Here's what the block looks like: ***Production: Please make sure the instances of double hyphens in the code below do not become em dashes. Thanks.***
SQL> declare
2
2 Rep_ID Repair_Header.Repair_ID%type;
3 Customer_ID Repair_Header.Customer_ID%type;
4 Item_Number Repair_Item.Item_Number%type;
5 Product_ID Repair_Item.Product_ID%type;
6 Manufacturer_ID Repair_Item.Manufacturer_ID%type;
7
7 cursor get_repair_header_info is
8 select Repair_ID, Customer_ID
9 from Repair_Header
10 order by Repair_ID;
11
11 cursor get_repair_item_info (This_Repair_ID number) is
12 select Item_Number, Product_ID, Manufacturer_ID
13 from Repair_Item
14 where
15 Repair_ID = This_Repair_ID
16 order by Item_Number;
17
17 begin
18
18 dbms_output.enable;
19
19 open get_repair_header_info;
20
20 loop
21
21 fetch get_repair_header_info
22 into Rep_ID, Customer_ID;
23 exit when get_repair_header_info%notfound;
24
24 dbms_output.put_line('Repair_ID: ' || Rep_ID);
25
25 open get_repair_item_info (Rep_ID);
26
26 -- Additional statements for customer analysis
27 -- appear here.
28
28 loop
29
29 fetch get_repair_item_info
30 into Item_Number, Product_ID, Manufacturer_ID;
31 exit when get_repair_item_info%notfound;
32
32 dbms_output.put_line(' Item_Number: ' || to_char(Item_Number));
33 dbms_output.put_line(' Product_ID: ' || Product_ID);
34 dbms_output.put_line(' Manufacturer_ID:' || Manufacturer_ID);
35
35 end loop;
36
36 close get_repair_item_info;
37
37 end loop;
38
38 close get_repair_header_info;
39
39 end;
40 /
Repair_ID: 501
Item_Number: 1
Product_ID: C2002
Manufacturer_ID: MIT501
Repair_ID: 502
Item_Number: 1
Product_ID: D301
Manufacturer_ID: SEN101
Repair_ID: 503
Item_Number: 1
Product_ID: B311
Manufacturer_ID: TES801
Repair_ID: 504
Item_Number: 1
Product_ID: B801
Manufacturer_ID: SEN101
Repair_ID: 505
Item_Number: 1
Product_ID: A903
Manufacturer_ID: TES801
Repair_ID: 506
Item_Number: 1
Product_ID: TR901
Manufacturer_ID: TES801
PL/SQL procedure successfully completed.
This chapter covers the following concepts: