 |
|
PL/SQL Stored Procedures
Oracle Tips by Burleson
|
A well-designed stored procedure is written to achieve one purpose
and perform only the actions necessary to achieve that purpose.
Procedures can accept and return values to their calling application
with ease through the use of parameters. Table 1.1 shows stored
procedure parameter types and functionality.
Table 1.1
The three types of procedure and function parameters.
| Parameter Type |
Functionality |
| IN |
IN
parameters pass a value to the procedure. This value cannot be
modified by the procedure. |
| OUT |
OUT
parameters are used to pass a value back to a calling block of
PL/SQL code. The value in this parameter can never be read by the
procedure. |
| IN OUT |
IN
OUT parameters are used to pass a value to the procedure. This
value can then be modified by the procedure and the resulting
value passed back to the calling PL/SQL block. |
Listing 1.9 presents a typical stored procedure. This procedure
accepts a social security number as a parameter, queries the
ENROLLED_COURSES table to determine the total number of credits
and credit hours for the student, calculates the student’s grade point
average by dividing the total number of credits by the total number of
credit hours, and updates the student’s master record in the
STUDENTS table.
Listing 1.9 A typical stored procedure.
PROCEDURE Calculate_GPA (nSSN IN integer)
IS
nOverallGPA number := 0;
iSumCredits integer := 0;
iTotalHours integer := 0;
iLogicStep integer := 0;
BEGIN
SELECT sum (credit_hours),
sum (decode (course_grade,
'A', 4,
'B', 3,
'C', 2,
'D', 1, 0))
INTO iTotalHours,
iSumCredits
FROM ENROLLED_COURSES
WHERE ssn = nSSN;
iLogicStep := 1;
nOverAllGPA := iSumCredits / iTotalHours;
iLogicStep := 2;
UPDATE STUDENTS
SET overall_gpa = nOverAllGPA
WHERE ssn = nSSN;
EXCEPTION
WHEN OTHERS THEN
SYSTEM_LOG.Log_Error (obj_name => 'Calculate_GPA',
obj_step => iLogicStep,
ora_error => substr (SQLERRM, 1, 65));
END Calculate_GPA;
This is an excerpt from the book "High
Performance Oracle Database Automation" by
Jonathan Ingram and Donald K. Burleson, Series Editor.
|
