Page 215
to declare the cursor in the application. The definition occurred in the package. To use the cursor, you must just use the full cursor name "employee_objects.employee_list." The first part of the expression uses the package name "employee_objects." The second part, following the dot, is the name of the cursor.
Listing 9.15 L_09_15.sqlCalling a Package Cursor from an Application
SQL> set serveroutput on; SQL> declare 2 e_rec employee%rowtype; 3 begin 4 open employee_objects.employee_list; 5 fetch employee_objects.employee_list into e_rec; 6 while (employee_objects.employee_list%found) loop 7 dbms_output.put_line(e_rec.last_name); 8 fetch employee_objects.employee_list into e_rec; 9 end loop; 10 close employee_objects.employee_list; 11 end; 12 / ROOSEVELT ANTHONY JOHNSON ROOSEVELT TAFT WILSON COOLIDGE HOOVER ROOSEVELT TRUMAN EISENHOWER KENNEDY JOHNSON NIXON FORD CARTER REAGAN BUSH CLINTON PL/SQL procedure successfully completed.
Listing 9.16 illustrates the use of the package procedure object "dpt_employees." The procedure is called using dot notation. The package name followed by the procedure name calls the object. The calling of this object is virtually identical to the way this procedure would be called if it were a stored procedure. You might check Listing 9.11 to see the similarities.
Page 216
Listing 9.16 L_09_16.sqlCalling a Package Procedure
SQL> set serveroutput on; SQL> begin 2 employee_objects.dpt_employees (`WEL'); 3 end; 4 / ANTHONY CARTER HOOVER REAGAN ROOSEVELT TAFT PL/SQL procedure successfully completed.
Calling a function package object is the same as calling the function when it is a stored function. Listing 9.17 illustrates the use of a package function. This program is identical to the function called in Listing 9.12 except for the name of the function in line 15. The name of the package precedes the function in this block.
Listing 9.17 L_09_17.sqlCalling a Package Function
SQL> set serveroutput on; SQL> declare 2 age number; 3 age_dif number; 4 lname varchar2(15); 5 fname varchar2(15); 6 dept varchar2(4); 7 cursor a is 8 select last_name, first_name, fk_department, 9 (employment_date - birth_date)/365 10 from employee; 11 begin 12 open a; 13 fetch a into lname, fname, dept, age; 14 while (a%found) loop 15 age_dif := age - employee_objects.avg_age(dept); 16 dbms_output.put_line (lname||' `||fname||' `||'Age Diff `||age_dif); 17 fetch a into lname, fname, dept, age; 18 end loop; 19 end; 20 / COOLIDGE CALVIN Age Diff -9.87442922374429223744292237442922374426 JOHNSON LYNDON Age Diff 1.81135029354207436399217221135029354198 REAGAN RONALD Age Diff 7.03652968036529680365296803652968036534 BUSH GEORGE Age Diff 17.95844748858447488584474885844748858451 JOHNSON ANDREW Age Diff 2.86066536203522504892367906066536203513 CLINTON WILLIAM Age Diff -1.68454011741682974559686888454011741692 CARTER JIMMY Age Diff 14.61187214611872146118721461187214611877 FORD GERALD Age Diff 1.39406392694063926940639269406392694067
Page 217
NIXON RICHARD Age Diff 6.87710371819960861056751467710371819952 KENNEDY JOHN Age Diff -9.84070450097847358121330724070450097856 EISENHOWER DWIGHT Age Diff 3.465296803652968036529680365296803653 TRUMAN HAROLD Age Diff 1.96940639269406392694063926940639269409 ROOSEVELT FRANKLIN Age Diff -2.28180039138943248532289628180039138952 HOOVER HERBERT Age Diff 5.26940639269406392694063926940639269411 WILSON WOODROW Age Diff 2.25792563600782778864970645792563600774 TAFT WILLIAM Age Diff 2.32146118721461187214611872146118721466 ROOSEVELT THEODORE Age Diff -14.91278538812785388127853881278538812782 ANTHONY SUSANNE Age Diff -28.28675799086757990867579908675799086753 ROOSEVELT ELEANOR Age Diff -.95251141552511415525114155251141552507 PL/SQL procedure successfully completed.
These preceding three examples show that it is easy to call objects from a procedure. You call them in the same manner as any other object, except you preface them with the name of the package.
You can use several cursor attributes to control cursors. The %found attribute is true when the last fetch returned a value. The %notfound attribute is true when the last fetch did not return a value. The %isopen attribute is true when the specified cursor is currently open. Finally, the %rowcount cursor attribute returns the number of records fetched by the cursor.
The While loop is another type of loop available. This loop evaluates the exit condition at the beginning of the loop. This means the condition must be true before the application can even enter the loop. While loops can be nested, enabling you to use nested cursors within your applications.
There are two types of For loops. The first type is the Numeric For loop. It has a counting variable evaluated with each iteration of the loop. When the counting variable is within the high and low parameters, the loop is performed. When the counting variable falls outside these parameters, the loop terminates. The Cursor For loop is an especially powerful procedure that performs many of the cursor functions for you. This cursor operates as long as the %found cursor attribute is true. The loop performs all the Cursor commands for you. It also enables you to define the select statement as part of the For loop.
The For Update cursor option locks the records retrieved into the cursor. This prohibits other users from updating the records while the cursor is open. The where current of clause is used with update and delete statements. It allows these DML statements to identify the record to perform their operation on by using the cursor position rather than a where clause. This avoids having to perform index or table scans.
Procedures are named blocks of code defined and stored in the database. The name of the procedure is contained in the Header section of the PL/SQL block. Because the blocks are named, other applications can call them. They enable the developer to create code that is
Page 218
usable in multiple applications. Procedures can have parameters that allow values to be passed to and from it. The mode of transfer is specified in the parameter declaration by using the in, out, and in out keywords. You can use the name of the procedure as the statement that calls the procedure.
Functions are also named PL/SQL blocks similar to procedures. The difference is that functions return a value and are part of a PL/SQL expression. They cannot exist by themselves in a block of code like a procedure can. Both functions and procedures are devices used to define reusable code.
Packages are PL/SQL devices that enable the developer to group cursors, procedures, and functions into logical groupings. A package has two parts. The first is the package specification that contains a declaration of the public objects. These objects are the ones that other applications can use. The second part of the package is the package body. This part contains the actual PL/SQL statements for the package objects. It also contains private objects that only objects in the package can use. Objects in a package are referenced by other applications using the name of the object preceded by the package name.
This chapter concludes the discussion of PL/SQL. The next chapter covers Oracle's SQL *Loader product. You use SQL *Loader to load Oracle tables with data from flat files. This is a very common procedure to perform when moving data from one system to another. At the end of the next chapter, you will be presented with installment III of the Employee project. This installment requests you to load the tables created at the end of part II with data. You must use SQL *Loader to accomplish this.