A stored procedure or function is a PL/SQL program stored in an Oracle database and invoked by a user, either directly or indirectly. The benefits of using stored procedures and functions are
In this chapter, you learn the essentials of using stored procedures.
Using a text processor, such as Notepad or Write, to construct a stored procedure is a good idea. You can copy the stored procedure from the text processor and paste it into SQL*Plus for development and testing. The syntax for creating a stored procedure is
CREATE [OR REPLACE] PROCEDURE procedure-name [(argument1 ... [, argumentN) ] IS [local-variable-declarations] BEGIN executable-section [exception-section] END [procedure-name];
where procedure-name is the procedure name subject to Oracle database object-naming restrictions.
argument1 through argumentN are optional argument declarations that consist of
argument-name [IN | OUT] datatype [ {:= | DEFAULT} value]
local-variable-declarations are optional declarations of variables, constants, and other procedures and functions local to procedure-name.
executable-section is the PL/SQL statements that compose the procedure.
exception-section is the optional exception handling section of the procedure.
For example, the following stored procedure has a single argument that the DELETE statement uses to determine which products to remove from the Product table.
SQL> create or replace procedure Delete_Specified_Product 2 (Description_Phrase varchar2) is 3 3 begin 4 4 delete from Product 5 where 6 upper(Description) like Description_Phrase; 7 7 end; 8 / Procedure created.
The syntax for creating a stored function is very similar to the syntax for creating a stored procedure. Of course, a stored function must also return a value.
CREATE [OR REPLACE] FUNCTION function-name [(argument1 ... [, argumentN) ] RETURN function-datatype IS [local-variable-declarations] BEGIN executable-section [exception-section] RETURN function-value END [function-name];
The variables are defined in this way: function-name is the function name subject to Oracle database object-naming restrictions.
argument1 through argumentN are optional argument declarations that consist of
argument-name [IN | OUT] datatype [ {:= | DEFAULT} value]
function-datatype is the datatype of the value returned by the function.
local-variable-declarations are optional declarations of variables, constants, and other procedures and functions local to function-name.
executable-section is the PL/SQL statements that compose the function.
exception-section is the optional exception-handling section of the function
function-value is the value that the function returns to the caller.
NoteThe difference between a stored procedure and a stored function is that a stored procedure does not return a value whereas a stored function does return a value. As a result, a stored function can be called in a SQL statement in the same manner as a built-in function is called; a stored procedure cannot. However, stored procedures and functions can both return a modified argument value if the argument is declared as OUT or IN OUT.
Here is an example of a stored function that obtains a new Customer ID and stores the information about a new customer in the Customer table:
SQL> create or replace function Get_New_Customer_ID 2 (Salutation varchar2, 3 Last_Name varchar2, 4 First_Name varchar2, 5 Street_Address varchar2, 6 City varchar2, 7 State varchar2, 8 Zipcode varchar2, 9 Home_Phone varchar2, 10 Work_Phone varchar2) 11 return number is 12 12 New_Customer_ID number(4); 13 13 begin 14 14 select Customer_Sequence.nextval 15 into New_Customer_ID 16 from dual; 17 17 insert into Customer 18 (Customer_ID, Salutation, Last_Name, First_Name, 19 Street_Address, City, State, Zipcode, Home_Telephone_Number, 20 Work_Telephone_Number) 21 values 22 (New_Customer_ID, Salutation, Last_Name, First_Name, 23 Street_Address, City, State, Zipcode, Home_Phone, Work_Phone); 24 24 return New_Customer_ID; 25 25 end; 26 / Function created.
If Oracle detects errors when you create a stored PL/SQL program, it issues a nondescript message indicating that errors occurred--;without providing any additional details. For example, here is what happens when you try to create a stored procedure with a syntax error:
SQL> CREATE OR REPLACE PROCEDURE show_inserts IS
2
2 max_records CONSTANT int := 100;
3 i int := 1;
4
4 BEGIN
5
5 dbms_output.enable;
6
6 FOR i IN 1..max_records LOOP
7
7 if (mod(i,10) = 0) then
8 INSERT INTO test_table
9 (record_number, current_date)
10 VALUES
11 (i, SYSDATE)
12 dbms_output.put_line('The value of i is ' || to_char(i));
13
13 else
14 NULL;
15
15 end if;
16
16 END LOOP;
17
17 END;
18 /
Warning: Procedure created with compilation errors.
To view the errors resulting from the attempted compilation of the PL/SQL code, you can use the SQL*Plus command show errors, which displays the specific PL/SQL compilation errors.
SQL> show errors
Errors for PROCEDURE SHOW_INSERTS:
LINE/COL ERROR
-------- -----------------------------------------------------------------
12/5 PLS-00103: Encountered the symbol "DBMS_OUTPUT" when expecting
one of the following:
;
; was inserted before "DBMS_OUTPUT" to continue.
When PL/SQL compiles a subprogram, the resulting compilation errors are stored in an Oracle data dictionary table. Instead of using the show errors command, you can query a data dictionary view named USER_ERRORS, which has this structure:
SQL> desc user_errors Name Null? Type ------------------------------- -------- ---- NAME NOT NULL VARCHAR2(30) TYPE VARCHAR2(12) SEQUENCE NOT NULL NUMBER LINE NOT NULL NUMBER POSITION NOT NULL NUMBER TEXT NOT NULL VARCHAR2(2000)
The Name column contains the name of the stored procedure that had compilation errors. The Sequence column is the order in which the error messages should be retrieved. The Line column contains the line number of the procedure's PL/SQL statement that caused a compilation error. Position contains the column position where the error was detected.
To query USER_ERRORS to determine the cause of the warning message, use this SELECT statement:
SQL> select line, position, text
2 from user_errors
3 where
4 name = 'SHOW_INSERTS'
5 order by sequence;
LINE POSITION TEXT
--------- --------- ----------------------------------------------------------
12 5 PLS-00103: Encountered the symbol "DBMS_OUTPUT"
when expecting one of
the following:
;
; was inserted before "DBMS_OUTPUT" to continue.
After a stored procedure has been created, you might want to look at the source code of a PL/SQL program. However, even if the SQL*Plus script that was used to create the stored procedure is not available, you can still retrieve the source code of a stored procedure by querying an Oracle data dictionary view.
The Oracle data dictionary is a group of tables that contain information about the Oracle database itself. Because these data dictionary tables are somewhat cryptic in structure, Oracle defines a set of views that provide a more coherent perspective of the data dictionary. One of these views is named USER_SOURCE.
TipBy default, the SYS account owns all Oracle data dictionary views. To query any one of them, you need to specify the SYS account as the owner. However, you can run a SQL*Plus script that will create synonyms that hide the ownership of these views. Simply invoke this script from SQL*Plus:
SQL> @c:\orawin\rdbms71\admin\catdbsyn
This script is run by default in the starter database.
If you DESCRIBE USER_SOURCE, here is what you will see:
SQL> describe USER_SOURCE Name Null? Type ------------------------------- -------- ---- NAME NOT NULL VARCHAR2(30) TYPE VARCHAR2(12) LINE NOT NULL NUMBER TEXT VARCHAR2(2000)
The Name column contains the procedure, function, package, or package body name. The Type column indicates whether the source belongs to a procedure, function, package, or package body. The line number of each PL/SQL source line is stored in the Line column. Text contains each PL/SQL program line.
As an example, suppose that you created a stored procedure named DELETE_AMPS that consists of the following code:
SQL> CREATE OR REPLACE PROCEDURE DELETE_AMPS IS 2 2 BEGIN 3 3 delete from Product 4 where 5 upper(Description) like '%AMP%'; 6 6 END; 7 / Procedure created.
If you want to see the source code of DELETE_AMPS, query the USER_SOURCE data dictionary view.
SQL> select text 2 from User_Source 3 where 4 name = 'DELETE_AMPS' 5 order by line; TEXT ------------------------------------------------- PROCEDURE Delete_Amps IS BEGIN delete from Product where upper(Description) like '%AMP%'; END; 6 rows selected.
Notice that Oracle squeezes out the blank lines from the PL/SQL subprogram when it is stored in the database.
You can query USER_OBJECTS to obtain a list of stored procedures, functions, packages, and package bodies owned by the Oracle account to which you are currently connected. If you wanted to see all the objects, regardless of ownership, you would query DBA_OBJECTS rather than USER_OBJECTS. The Object_Type column in DBA_OBJECTS indicates the type of the object: table, view, procedure, and so on.
To obtain a list of the types of database objects currently stored in the database, use the following query:
SQL> select distinct object_type 2 from sys.dba_objects; OBJECT_TYPE ------------- CLUSTER INDEX PACKAGE PACKAGE BODY PROCEDURE SEQUENCE SYNONYM TABLE TRIGGER VIEW 10 rows selected.
For instance, if you want to see the name and owner of each package, you would submit the following query:
SQL> select object_name, owner 2 from sys.dba_objects 3 where 4 object_type = 'PACKAGE' 5 order by object_name; OBJECT_NAME OWNER ---------------------------------------- ------------------------------ DBMS_ALERT SYS DBMS_DDL SYS DBMS_DEFER_SYS SYS DBMS_DESCRIBE SYS DBMS_EXPORT_EXTENSION SYS DBMS_IJOB SYS DBMS_IREFRESH SYS DBMS_ISNAPSHOT SYS DBMS_JOB SYS DBMS_LOCK SYS DBMS_OUTPUT SYS DBMS_PIPE SYS DBMS_REFRESH SYS DBMS_SESSION SYS DBMS_SNAPSHOT SYS DBMS_SQL SYS DBMS_STANDARD SYS DBMS_SYS_ERROR SYS DBMS_SYS_SQL SYS DBMS_TRANSACTION SYS DBMS_UTILITY SYS DIANA SYS DIUTIL SYS PIDL SYS STANDARD SYS 25 rows selected.
PL/SQL requires that you declare any identifier--;constant, variable, cursor, procedure, or function--; before using it elsewhere in a PL/SQL subprogram. This requirement can cause a problem when two subprograms reference each other, as shown in the following code:
SQL> set serveroutput on SQL> SQL> declare 2 2 function Medicare_Patient (Patient_ID IN varchar2) 3 return number is 4 4 status number; 5 Pat_ID varchar2(6); 6 6 begin 7 7 if Insurable_Patient (Pat_ID) = 2 then 8 status := 1; 9 end if; 10 10 return status; 11 11 end Medicare_Patient; 12 12 12 function Insurable_Patient (Patient_ID IN varchar2) 13 return number is 14 14 status number; 15 Pat_ID varchar2(6); 16 16 begin 17 17 if Medicare_Patient (Pat_ID) = 2 then 18 status := 1; 19 end if; 20 20 return status; 21 21 end Insurable_Patient; 22 22 -- Executable portion of anonymous block. 23 23 begin 24 24 dbms_output.enable; 25 25 end; 26 / declare * ERROR at line 1: ORA-06550: line 7, column 4: PLS-00313: 'INSURABLE_PATIENT' not declared in this scope ORA-06550: line 7, column 1: PL/SQL: Statement ignored
As you can see, PL/SQL doesn't recognize the reference to Insurable_Patient in the function Medicare_Patient because the declaration of Insurable_Patient occurs after the declaration of Medicare_Patient. To circumvent this dilemma, you include a forward declaration of the subprogram in the declare section. The forward declaration is a declaration of the subprogram, its arguments, and return type. Here is how to specify a forward declaration for Insurable_Patient for the preceding example:
SQL> set serveroutput on SQL> SQL> declare 2 2 function Insurable_Patient (Patient_ID IN varchar2) return number; 3 3 function Medicare_Patient (Patient_ID IN varchar2) 4 return number is 5 5 status number; 6 Pat_ID varchar2(6); 7 7 begin 8 8 if Insurable_Patient (Pat_ID) = 2 then 9 status := 1; 10 end if; 11 11 return status; 12 12 end Medicare_Patient; 13 13 13 function Insurable_Patient (Patient_ID IN varchar2) 14 return number is 15 15 status number; 16 Pat_ID varchar2(6); 17 17 begin 18 18 if Medicare_Patient (Pat_ID) = 2 then 19 status := 1; 20 end if; 21 21 return status; 22 22 end Insurable_Patient; 23 23 -- Executable portion of anonymous block. 24 24 begin 25 25 dbms_output.enable; 26 26 end; 27 / PL/SQL procedure successfully completed.
With release 7.1, the Oracle RDBMS enabled you to reference a stored function within a SQL statement. This feature is enormously powerful because it extends the functionality of a single SQL statement to include the logic contained in a stored function. Here is an elementary example of how this functionality is accomplished.
Oracle doesn't offer a built-in function for converting temperature from Fahrenheit to centigrade, but you can create a stored function to perform the conversion.
SQL> create or replace function DegF_to_DegC (Deg_F IN number) 2 return number is 3 3 Deg_C number; 4 4 begin 5 5 Deg_C := (5.0/9.0)*(Deg_F - 32); 6 6 return Deg_C; 7 7 end DegF_to_DegC; 8 / Function created.
After the stored function has been successfully created, you can employ it in a SELECT statement.
SQL> select body_temp, degf_to_degc(body_temp)
2 from patient;
BODY_TEMP DEGF_TO_DEGC(BODY_TEMP)
--------- -----------------------
99.2 37.333333
100.2 37.888889
103.8 39.888889
Although PL/SQL doesn't have any built-in support for communicating with the user, you can still use PL/SQL to provide results to a user or another program by
You've already seen an example of how PL/SQL can write to an intermediate table. When compiling PL/SQL stored procedures and functions, the PL/SQL engine itself writes error messages to a data dictionary table that the developer can query. If you want to provide output via SQL*Plus, using DBMS_OUTPUT is a good strategy. If you need to pass many values to a user or a program, writing the results to a table makes more sense.
For instance, assume that you want to create a stored procedure that adjusts the price--;downward--;of a specified product by some designated percentage.
SQL> create or replace procedure adjust_product_market_value 2 (Prod_ID varchar2, 3 Man_ID varchar2, 4 Pct number) IS 5 5 Current_Val number(7,2); 6 New_Current_Val number(7,2); 7 Comments varchar2(100); 8 8 begin 9 9 select Current_Used_Value 10 into Current_Val 11 from Product 12 where 13 Product_ID = Prod_ID and 14 Manufacturer_ID = Man_ID; 15 15 New_Current_Val := Current_Val * (1 - Pct); 16 16 update Product 17 set Current_Used_Value = New_Current_Val 18 where 19 Product_ID = Prod_ID and 20 Manufacturer_ID = Man_ID; 21 21 Comments := 'Price adjusted from ' || to_char(Current_Val,'$99999.99') 22 || ' to ' || to_char(New_Current_Val,'$99999.99'); 23 23 insert into market_value_adjustment 24 (Product_ID, Manufacturer_ID, Comments) 25 values 26 (Prod_ID, Man_ID, Comments); 27 27 27 END; 28 / Procedure created.
The procedure adds a record to a table named Market_Value_Adjustment and indicates the product, manufacturer, previous value, and current value.
SQL> execute adjust_product_market_value ('C2002', 'MIT501', 0.08);
PL/SQL procedure successfully completed.
SQL> select Product_ID, Manufacturer_ID, Comments
2 from Market_Value_Adjustment;
PRODUCT_ID MANUFA COMMENTS
------------ ------ ---------------------------------------------------------
C2002 MIT501 Price adjusted from $120.00 to $110.40
Another way that you can present information to a user is by using an Oracle-supplied package called DBMS_OUTPUT. In the directory C:\ORAWIN\RDBMS71\ADMIN, you will find a series of files named DBMS*.SQL. During the installation of Personal Oracle7, each file installs a package designed for a specific purpose. DBMSOTPT.SQL is used to create the DBMS_OUTPUT package. DBMS_OUTPUT furnishes a set of procedures and functions that enable a PL/SQL program to retrieve input and display output. Although PL/SQL doesn't have any built-in input/output capabilities, you can use DBMS_OUTPUT procedures and functions to perform input/output operations.
To provide output, you need to call two procedures: enable and put_line. Also, before using DBMS_OUTPUT, you need to set the SQL*Plus system variable SERVEROUTPUT to ON.
SQL> set serveroutput on
SQL> CREATE OR REPLACE PROCEDURE show_inserts IS
2
2 max_records CONSTANT int := 100;
3 i int := 1;
4
4 BEGIN
5
5 dbms_output.enable;
6
6 FOR i IN 1..max_records LOOP
7
7 if (mod(i,10) = 0) then
8 INSERT INTO test_table
9 (record_number, current_date)
10 VALUES
11 (i, SYSDATE);
12 dbms_output.put_line('The value of i is ' || to_char(i));
13
13 else
14 NULL;
15
15 end if;
16
16 END LOOP;
17
17 END;
18 /
Procedure created.
SQL> execute show_inserts;
The value of i is 10
The value of i is 20
The value of i is 30
The value of i is 40
The value of i is 50
The value of i is 60
The value of i is 70
The value of i is 80
The value of i is 90
The value of i is 100
PL/SQL procedure successfully completed.
As discussed in Chapter 12, "Dealing with Dates," the Oracle DATE datatype can store time to the nearest second. If you need greater accuracy, you can use a function in an Oracle-supplied package to obtain time to the nearest hundredth of a second. If you assign dbms_utility.get_time to a PL/SQL number variable, the result is a fairly large number. The last two digits of this number are hundredths of seconds. You can isolate the right-most two digits by converting the PL/SQL variable to a character string with the TO_CHAR function and then using SUBSTR to obtain the right-most two digits. Here is an example of how the function is called:
SQL> set serveroutput on
SQL>
SQL> declare
2
2 time_in_hundredths number;
3
3 begin
4
4 dbms_output.enable;
5
5 time_in_hundredths := dbms_utility.get_time;
6
6 dbms_output.put_line('Time in hundredths of a second: ' ||
to_char(time_in_hundredths));
7
7 end;
8 /
Time in hundredths of a second: 339339033
The method for invoking a stored procedure or function depends on the context.
For SQL*Plus, use the execute command (this syntax is for a stored procedure that doesn't have any arguments) in the following way:
execute show_inserts;
From a PL/SQL subprogram, simply reference the stored procedure or function with any required arguments.
Some excellent application development tools, such as PowerBuilder, provide comprehensive support for the Oracle database. On the other hand, some application development tools offer only partial support for Oracle-specific features. For instance, ObjectView, produced by KnowledgeWare, does not permit you to invoke a stored procedure from its scripting language. If you've made a significant investment in application development with such a product, migrating your application to another product offering greater capabilities might not be practical. Instead, here's a workaround you can use to invoke a stored procedure indirectly.
Suppose that you've created a stored procedure named Dis_Patients_Normal_Temp that is used to discharge all patients whose temperature is less than or equal to 98.6 degrees Fahrenheit. To simplify this example, the stored procedure has no arguments. Here are the statements used to create the stored procedure:
SQL> create or replace procedure Dis_Patients_Normal_Temp is 2 2 Normal_Temp constant number := 98.6; 3 3 begin 4 4 delete from Patient 5 where 6 Body_Temp <= Normal_Temp; 7 7 end; 8 / Procedure created.
However, the client application development tool that you're using doesn't enable you to invoke Dis_Patients_Normal_Temp directly. Here's what you can do as a workaround. Create a table named Invoke_Dis_Patients with a single column.
SQL> create table Invoke_Dis_Patients 2 (dummy number); Table created.
Next, create a database trigger to invoke the stored procedure, Dis_Patients_Normal_Temp, whenever a DELETE statement executes against the Invoke_Dis_Patients table. Chapter 23, "Enforcing Business Rules with Database Triggers," contains a thorough discussion of database triggers. Here is the text of the trigger:
SQL> create or replace trigger Delete_Invoke_Dis_Patients 2 after delete on Invoke_Dis_Patients 3 3 begin 4 4 Dis_Patients_Normal_Temp; 5 5 end; 6 /
Trigger created.
The first query of the Patient table returns the following five records:
SQL> select * from patient; PATIEN AGE SYSTOLIC DYASTOLIC BODY_TEMP ------ --------- --------- --------- --------- A2002 45 150 90 99.2 N3393 59 183 120 100.2 ER5533 33 130 80 98.3 E3893 81 173 101 103.8 UR3393 39 140 70 98.5
You invoke the stored procedure, Dis_Patients_Normal_Temp, by issuing a DELETE statement against the Invoke_Dis_Patients table, causing the Delete trigger to fire, which invokes the stored procedure. As you see, patients whose temperatures are below 98.6 are deleted from the Patient table.
SQL> delete from Invoke_Dis_Patients; 0 rows deleted. SQL> select * from patient; PATIEN AGE SYSTOLIC DYASTOLIC BODY_TEMP ------ --------- --------- --------- --------- A2002 45 150 90 99.2 N3393 59 183 120 100.2 E3893 81 173 101 103.8
A package is a group of related PL/SQL procedures and functions. Like the Ada programming language, a PL/SQL package consists of a package specification and a package body. You can construct packages that are application specific--;for instance, a package named patient_data would contain procedures and functions related to the manipulation and retrieval of hospital patient information. Furthermore, a package can contain procedures and functions that provide a common service, such as the conversion of location information from one coordinate system to another.
The general syntax for creating a package is
CREATE PACKAGE package-name IS declaration-section END package-name;
where package-name is the name of the package to be created and is subject to Oracle database object naming restrictions. The declaration-section consists of type, variable, cursor, procedure, and function declarations.
Here's a simple package for the Frayed Wires consumer electronics repair store:
SQL> create or replace package Repair_Store_Utilities is 2 2 type Product_Info is record 3 (Product_ID varchar2(12), 4 Mfgr_ID varchar2(6), 5 Max_Value number); 6 6 cursor Get_Max_Product_Price 7 return Product_Info; 8 8 procedure Get_Max_Repair_Price 9 (Repair_ID OUT number, 10 Item_Number OUT number, 11 Product_ID OUT varchar2, 12 Mfgr_ID OUT varchar2, 13 Max_Repair_Price OUT number); 14 14 function Get_New_Customer_ID 15 (Salutation varchar2, 16 Last_Name varchar2, 17 First_Name varchar2, 18 Street_Address varchar2, 19 City varchar2, 20 State varchar2, 21 Zipcode varchar2, 22 Home_Phone varchar2, 23 Work_Phone varchar2) 24 return number; 25 25 end Repair_Store_Utilities; 26 / Package created.
The Repair_Store_Utilities package contains three items: a cursor, a procedure, and a function. The package specification begins with a type definition that is used by the cursor, which has no parameters.
TipWhen you create packages or package bodies, the best approach is to use the OR REPLACE clause. Oracle also offers the DROP PACKAGE and DROP PACKAGE BODY statements, but the OR_REPLACE clause saves you the trouble of having to remember whether or not you've dropped a package before you attempt to create it.
A package body contains the public and private elements of a package. It hides the details of how cursors, procedures, and functions are actually implemented--;details that should be hidden from developers. The syntax to declare a package body is
CREATE PACKAGE BODY package-name IS declaration-section procedure-bodies; function-bodies; initialization-section END package-name;
where package-name is the name of the package to be created and is subject to Oracle database object-naming restrictions.
declaration-section consists of type, variable, and cursor declarations.
procedure-bodies consists of the executable sections of each procedure that was declared in the package specification.
function-bodies consists of the executable sections of each function that was declared in the package specification.
initialization-section is an optional section that is executed once when the package is first referenced.
Here's what the Repair_Store_Utilities package body looks like:
SQL> create or replace package body Repair_Store_Utilities is 2 2 cursor Get_Max_Product_Price return Product_Info 3 is 4 select Product_ID, Manufacturer_ID, Current_Used_Value 5 from Product 6 where 7 Current_Used_Value = 8 (select max(Current_Used_Value) 9 from Product); 10 10 procedure Get_Max_Repair_Price 11 (Repair_ID OUT number, 12 Item_Number OUT number, 13 Product_ID OUT varchar2, 14 Mfgr_ID OUT varchar2, 15 Max_Repair_Price OUT number) is 16 16 begin 17 17 select Repair_ID, Item_Number, Product_ID, Manufacturer_ID, 18 Estimated_Cost 19 into Repair_ID, Item_Number, Product_ID, Mfgr_ID, 20 Max_Repair_Price 21 from Repair_Item 22 where 23 Estimated_Cost = (select max(Estimated_Cost) from Repair_Item); 24 24 end Get_Max_Repair_Price; 25 25 25 function Get_New_Customer_ID 26 (Salutation varchar2, 27 Last_Name varchar2, 28 First_Name varchar2, 29 Street_Address varchar2, 30 City varchar2, 31 State varchar2, 32 Zipcode varchar2, 33 Home_Phone varchar2, 34 Work_Phone varchar2) 35 return number is 36 36 New_Customer_ID number(4); 37 37 begin 38 38 select Customer_Sequence.nextval 39 into New_Customer_ID 40 from dual; 41 41 insert into Customer 42 (Customer_ID, Salutation, Last_Name, First_Name, 43 Street_Address, City, State, Zipcode, Home_Telephone_Number, 44 Work_Telephone_Number) 45 values 46 (New_Customer_ID, Salutation, Last_Name, First_Name, 47 Street_Address, City, State, Zipcode, Home_Phone, Work_Phone); 48 48 return New_Customer_ID; 49 49 end; 50 50 begin 51 51 insert into utility_audit 52 (username, timestamp) 53 values 54 (user, sysdate); 55 55 commit; 56 56 end Repair_Store_Utilities; 57 / Package body created.
Now that the Repair_Store_Utilities package body has been created, you can construct an anonymous PL/SQL block that employs the procedure named Get_Max_Repair_Price in the package.
SQL> set serveroutput on
SQL>
SQL> declare
2
2 Repair_ID Repair_Item.Repair_ID%type;
3 Item_Number Repair_Item.Item_Number%type;
4 Product_ID Repair_Item.Product_ID%type;
5 Mfgr_ID Repair_Item.Manufacturer_ID%type;
6 Max_Price Repair_Item.Estimated_Cost%type;
7
7
7 begin
8
8 dbms_output.enable;
9
9 Repair_Store_Utilities.Get_Max_Repair_Price
10 (Repair_ID, Item_Number, Product_ID, Mfgr_ID, Max_Price);
11
11 dbms_output.put_line('Max estimated cost is ' ||
12 to_char(Max_Price,'9999.99'));
13
13 end;
14 /
Max estimated cost is 312.00
PL/SQL procedure successfully completed.
A package body has an optional initialization section that executes once--;the first time the package is referenced. In the Repair_Store_Utilities package, the initialization section consists of an INSERT statement that stores the current Oracle user's name and SYSDATE into the Utility_Audit table. The following anonymous PL/SQL block demonstrates how this works:
SQL> set serveroutput on
SQL>
SQL> delete from utility_audit;
0 rows deleted.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> declare
2
2 this_time date;
3 username varchar2(30);
4
4 begin
5
5 dbms_output.enable;
6
6 if (Repair_Store_Utilities.Get_Max_Product_Price%isopen = False) or
7 (Repair_Store_Utilities.Get_Max_Product_Price%isopen is Null) then
8 open Repair_Store_Utilities.Get_Max_Product_Price;
9 end if;
10
10 select username, timestamp
11 into username, this_time
12 from utility_audit;
13
13 dbms_output.put_line('Username: ' || username);
14 dbms_output.put_line('Timestamp: ' || this_time);
15
15 end;
16 /
Username: FRAYED_WIRES
Timestamp: 04-JUN-95
PL/SQL procedure successfully completed.
You can reference the procedures and functions contained in a package from SQL*Plus scripts, PL/SQL subprograms, client application scripts (such as Oracle Forms 4.5 or PowerBuilder), and database triggers. However, a database trigger cannot call any stored procedure, function, or packaged subprogram that contains a COMMIT, ROLLBACK, or SAVEPOINT statement. Therefore, if you want the flexibility of calling a package's subprograms from a database trigger, be sure that none of the procedures and functions in the package commit or roll back transactions.
This chapter focuses on the following concepts: