 |
|
PL/SQL Calling
Procedures
Oracle Tips by
Burleson
|
Stored procedures are typically called from a system’s GUI front
end, but can also be called from other stored PL/SQL objects and from
anonymous PL/SQL blocks.
Anonymous PL/SQL Blocks
It’s very common to create anonymous blocks of PL/SQL that call a
stored procedure, especially when the procedure is being tested.
Stored procedures can be called from any PL/SQL block. Consider the
block of PL/SQL in Listing 4.11, which is part of a test for the
Annual_Review() procedure.
Listing 4.11 An anonymous PL/SQL block that calls a
procedure.
DECLARE
nNewSalary number;
BEGIN
--
-- Set up a sample employee.
--
INSERT
INTO EMPLOYEES
(employee_num,
employee_ssn,
first_name,
middle_name,
last_name,
eff_hire_date,
eff_termination_date,
base_salary,
late_days,
warnings,
overtime_hours,
performance_rating)
VALUES (999,
999999999,
'Joe',
'Grant',
'Lewis',
'01/01/80',
NULL,
20000,
0,
0,
80,
10);
Annual_Review;
SELECT base_salary
INTO nNewSalary
FROM EMPLOYEES
WHERE employee_num = 999;
DBMS_Output.Put_Line ('Salary is: ' || to_char (nNewSalary));
END;
The highlighted portion of this example is a call to the
Annual_Review() procedure. This call is the heart and soul of the
test—after all, how can code be tested if it’s never run?
Stored PL/SQL Objects
In all but the simplest of systems, stored procedures are often
called by other stored procedures. Stored procedures can also be
called from stored functions and PL/SQL objects inside packages.
Listing 4.12 illustrates a call to a stored procedure from inside
another stored procedure.
Listing 4.12 Calling a stored procedure from another stored
procedure.
PROCEDURE Check_Code (vObjectName IN varchar2,
vOwner IN varchar2)
IS
iObjectExists integer := 0;
xMISSING_PARAMETER EXCEPTION;
xOBJECT_DOES_NOT_EXIST EXCEPTION;
BEGIN
IF vObjectName IS NULL THEN
RAISE xMISSING_PARAMETER;
END IF;
IF vOwner IS NULL THEN
RAISE xMISSING_PARAMETER;
END IF;
BEGIN
SELECT 1
INTO iObjectExists
FROM ALL_OBJECTS
WHERE owner = vOwner
AND object_name = vObjectName
AND object_type IN ('PROCEDURE',
'FUNCTION',
'PACKAGE BODY');
EXCEPTION
WHEN NO_DATA_FOUND THEN
RAISE xOBJECT_DOES_NOT_EXIST;
END;
Check_Source_For_Insert (vOwner => vOwner,
vObject => vObjectName);
EXCEPTION
WHEN xMISSING_PARAMETER THEN
DBMS_Output.Put_Line ('Both parameters are required.');
WHEN xOBJECT_DOES_NOT_EXIST THEN
DBMS_Output.Put_Line ('The named object does not exist.');
END Check_Code;
In this example, the Check_Source_For_Insert() procedure is
called from inside the Check_Code() procedure. This allows the
Check_Code() procedure to perform the functionality of the
Check_Source_For_Insert() procedure without including that
procedure’s logic.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
