122-126

Previous

Table of Contents

Next

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.

Notation

PL/SQL supports two types of notation when calling procedures with parameters: named notation and positional notation .

Named Notation

Named notation is calling a stored procedure by specifying both the parameter names and each parameter s value. Listing 4.13 illustrates the use of named notation when calling a stored procedure.

Listing 4.13 Calling a procedure using named notation.

 Check_Source_For_Insert (vOwner  => 'JSCHMOE',                          vObject => 'CALCULATE_GPA'); 

When using named notation, the parameters for a procedure can be specified in any order (after all, the developer knows which values are intended for which parameters). Obviously, the use of named notation requires that the developer knows the names of the parameters and specifies the parameter names in procedure calls.

Positional Notation

Positional notation is calling a stored procedure by simply passing parameter values and assuming that the values will be associated with parameters in the order of declaration. The first value passed is associated with the first parameter, the second value with the second parameter, and so forth. Listing 4.14 illustrates a call to a stored procedure using positional notation.

Listing 4.14 Calling a stored procedure using positional notation.

 Check_Source_For_Insert ('JSCHMOE',                          'CALCULATE_GPA'); 

When using positional notation, the values must be passed to the stored procedure in the proper order. This requires that developers know the order of the parameters.

Mixing Notations

It s possible to use both named notation and positional notation in a single call to a stored procedure, as shown in Listing 4.15.

Listing 4.15 Mixing named and positional notation.

 Approve_For_Credit (999999999,                     9032012912,                     'C',                     iBounced_Checks => 0,                     iOverdrafts     => 0); 

When mixing notation like this, it s important to keep in mind that positional notation can be used only before named notation has been used. If even one parameter is populated by using named notation, all subsequent parameters must also be populated using named notation.

---

Named Or Positional Notation?
Consider again the code in Listing 4.15. While you may have figured out that the first parameter is a social security number, you probably have no idea what the second and third parameters are supposed to represent. If the code had used named notation (or if variables were being passed instead of literal values), you would be able to see that the second parameter holds an account number and the third parameter indicates whether the account is a checking or savings account.

I prefer the use of named notation when calling procedures and functions. While somewhat more work is involved during development, it s much clearer down the road to see what values are being passed to which parameters. And when combined with meaningfully named variables, named notation contributes a great deal to making the code self-documenting .

---

Procedure Structure

Stored procedures contain the following five components :

    Procedure declaration ” Defines the name of the procedure and the parameters the procedure accepts.

    Variable declarations ” Defines the variables, constants, and user -defined exceptions used by the procedure.

    Executable declarations ” Defines local procedures and functions that can be executed by the procedure.

    Body ” Defines the internal logic of the procedure.

    Exception handling ” Defines logic for handling exceptions that might occur during execution.

Previous

Table of Contents

Next