117-122 | High Performance Oracle Database Automation: Creating Oracle Applications with SQL and PL/SQL

Table of Contents

Datatypes

Like variables and constants, parameters for procedures must have a datatype specified. The datatype for a parameter can be either a scalar or user-defined datatype. Parameters of a user -defined datatype must make a reference to a type definition, typically inside a package spec.

Constraining Parameters

While parameters must have a datatype specified, it s not possible to constrain the length of a parameter. That is, if you define a parameter of datatype varchar2 , that parameter can accept between 0 and 2,000 characters via that parameter! Likewise, you cannot require that a parameter be passed to the procedure.

If you must constrain parameters, explicit checks must be made inside your procedure, like the ones shown in Listing 4.7.

Listing 4.7 Checking the values of parameters.

 PROCEDURE Test_Parameters (vString  IN     varchar2,                            nBalance IN     number) IS   xSTRING_TOO_LONG    EXCEPTION;   xNEGATIVE_BALANCE   EXCEPTION; BEGIN   IF (length (vString) > 20) THEN      RAISE xSTRING_TOO_LONG;   END IF;   IF (nBalance < 0) THEN      RAISE xNEGATIVE_BALANCE;   END IF; END Test_Parameters;

%TYPE Parameters

Parameters can reference the datatype of a column in a table using %TYPE , as shown in Listing 4.8.

Listing 4.8 Defining a parameter using %TYPE .

 PROCEDURE Calculate_GPA (nSSN IN     STUDENTS.ssn%TYPE);

If a parameter references the datatype of a column this way and the datatype of the column changes, the datatype of the parameter changes to correspond to the column s datatype.

%ROWTYPE Parameters

Parameters can also reference the structure of a table or record by using %ROWTYPE , as shown in Listing 4.9.

Listing 4.9 Defining a parameter using %ROWTYPE .

 PROCEDURE Print_Diploma (nStudent_rec IN     STUDENTS%ROWTYPE);

Parameters defined using %ROWTYPE change their definition if the referenced record type or row structure changes.

Parameter Types

There are three types of parameters for stored procedures: IN , OUT , and IN OUT . Each parameter type is described in Table 4.1.

**Table 4.1** The three parameter types.

Type	Description
IN	IN parameters are used to pass a value to the procedure. The procedure is not able to alter the value of the parameter in any way. This is the most commonly used type of parameter.
OUT	OUT parameters are used to return a value from the procedure. The procedure can assign a value to the parameter but can never read the value contained in the parameter.
IN OUT	IN OUT parameters are used to pass a value to the procedure, which the procedure can then alter. The procedure is able to read values from and write values to the parameter.

If a type is not specified for a parameter, the parameter defaults to type IN .

Default Values

IN parameters (and only IN parameters) can be given a default value by using either the assignment operator (:=) or the DEFAULT statement, as shown in Listing 4.10.

Listing 4.10 Default values for parameters.

 PROCEDURE Raise_Salary (nEmployeeID IN     number,                         nRaiseAmt   IN     number DEFAULT .001); PROCEDURE Raise_Salary (nEmployeeID IN     number,                         nRaiseAmt   IN     number := .001);

When a NULL value is passed for a parameter with a default value, the parameter s value is set to the default value. If a value is passed for the parameter, the default value has no effect.

Calling Procedures

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?

Table of Contents