Previous Table of Contents Next


Other Uses

The potential use of scripts to automate a task inside an Oracle database is limited only by the imagination of the developer. For instance, a developer might need to write a simple script that changes area codes to keep up with the rapid growth of the nation’s phone system, as shown in Listing 1.8.

Listing 1.8 A simple script to update area codes inside phone numbers.

UPDATE STUDENTS
SET    home_phone = to_number ('&&1') ||
       substr (to_char (home_phone, 4, 7))
WHERE  substr (to_char (home_phone), 4, 3) = '&&2'
AND    substr (to_char (home_phone), 1, 3) = '&&3'
/

This script replaces the area code prefixes for all phone numbers in the area code specified by &&3 and the local calling area &&2 with the area code specified in &&1. The script concatenates the last seven digits of the phone number (the local calling area and extension) with the new area code.

Stored PL/SQL Objects

Stored PL/SQL allows you, the application developer, to write code once and enforce your business logic inside the database. This provides a tremendous advantage in an environment where business rules are regularly subject to change. The modular nature of these objects allows business rules to be enabled and disabled quite easily.

All stored PL/SQL objects have certain common traits, including:

  Their source code is stored within the ALL_SOURCE view (with the exception of database triggers, whose source code is stored in the ALL_TRIGGERS view).
  They execute with the authorities of the user who created the object.
  They incorporate both SQL and PL/SQL statements.
  Their access is controlled like all other objects in the database. The ability of an individual user to execute a specific stored object (or all stored objects) must be granted to a user.

Procedures

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;

Functions

The most common use of functions is to hold both simple and complex mathematical equations that are performed frequently by an application, but other simple tasks can also be accomplished. Well-designed functions, like well-designed procedures, are coded to perform one task. Like procedures, functions can accept and return values to calling objects via parameters, but returning values from a function via parameters is typically viewed as a poor coding practice. Listing 1.10 presents a typical stored function.

Listing 1.10 A typical stored function.

FUNCTION Calculate_GPA (nSSN IN     integer)

RETURN NUMBER

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;
   RETURN nOverAllGPA;

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 function closely resembles the procedural implementation of Calculate_GPA( ), but the function does have one advantage that the procedure doesn’t—the function can be called inside an SQL statement, as shown in Listing 1.11.

Listing 1.11 Use of the Calculate_GPA() function in an SQL statement.

UPDATE STUDENTS
SET    overall_gpa = Calculate_GPA (nSSN => ssn);


Previous Table of Contents Next