 |
|
Constraining Parameters
Oracle Tips by
Burleson
|
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.
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor. |
