PL/SQL Parameters   «Prev  Next»

Lesson 5The IN, OUT, and IN OUT parameters
Objective Place a parameter in a procedure.

Oracle IN, OUT, and IN OUT Parameters

In the previous modules, you learned to create three different types of PL/SQL stored objects: functions, procedures, and triggers. A later lesson covers a fourth type of stored object: the PL/SQL package. This lesson covers how to use a parameter in a procedure.
There are three modes of parameters:
  1. IN: A parameter that comes into a PL/SQL stored object. It cannot be modified within the object.
  2. OUT: A parameter that is sent from the PL/SQL stored object to the executing environment. It can be modified within the object.
  3. IN OUT: A parameter that is brought into the PL/SQL stored object and also sent out. It can be modified within the object.

The following series of images shows you the syntax for using parameters with a procedure, a sample of a procedure that specifies parameters, and then shows you the three methods that can be used to specify parameters when calling the procedure.

Parameter Syntax and Examples for Oracle Procedures

1) Parameter Syntax 1
1) 
CREATE [OR REPLACE] PROCEDURE procedure_name
[(parameter_name {IN | OUT | IN OUT} datatype
  [ {:= |DEFAULT} value]...)]
BEGIN
  Procedure body
END;

This shows the syntax of placing parameters into a procedure. Optional default value can be coded as :='value' or as DEFAULT 'value'. To use positional method, however, you must list parameters with default values last, so that values listed in the call are assigned to the first (non-default) parameters.

2) Parameter Syntax 2
2) 
CREATE OR REPLACE PROCEDURE DO_ADDRESS
(I_CUST_ID IN NUMBER,
ADDRESS_LINE2 OUT VARCHAR2 := 'Not found.')
BEGIN
  (...PL/SQL block here ..)
END;

Here is a partial example of a procedure containing parameters. The procedure is named DO_ADDRESS. It has two parameters, one IN, and one OUT. The OUT parameter has a default value listed.

3) Parameter Syntax 3
3) 
BEGIN
DECLARE
CUSTOMER NUMBER;
FORMATTED_ADDR VARCHAR2;
Here is a partial example of a PL/SQL block that calls the DO_ADDRESS procedure. Two local variables are declared first. The CUSTOMER variable (in purple) will be used to pass a value into the IN parameter, I_CUST_ID. The FORMATTED_ADDR variable (in green) will be used to receive a value from the OUT parameter ADDRESS_LINE2.

4) Parameter Syntax 4
4) 
BEGIN
DECLARE
CUSTOMER NUMBER
FORMATTED_ADDR VARCHAR2;

There are actually three ways to specify the parameters when calling a procedure. This example shows the most common method: list parameters by position. In other words, the first variable is assigned to the first parameter, the second variable to the second parameter. etc.

5) Parameter Syntax 5
5) 
DO_ADDRESS(CUSTOMER, FORMATTED_ADDR);
END;

An advantage to this method is that the actual names of the parameters in the called procedure are not used, so they can change without having to change the call to the procedure. A disadvantage is that the procedure cannot revise the order or number of pararmeters in the procedure without affecting the call. (One exception: if you only add parameters at the end of the list of parameters and all new parameters have default values, you do not need to modify the call.)

6) Parameter Syntax 6 6)
An advantage to this method is that the actual names of the parameters in the called procedure are not used, so they can change without having to change the call to the procedure. A disadvantage is that the procedure cannot revise the order or number of parameters in the procedure without affecting the call. (One exception: if you only add parameters at the end of the list of parameters and all new parameters have default values, you do not need to modify the call.)

7) Parameter Syntax 7
7) 
BEGIN
DECLARE
CUSTOMER NUMBER;
FORMATTED_ADDR VARCHAR2

The third method of specifying parameters is by using a mixture of position and assignment methods. If you use this method, positional assignments must be done first, followed by assigned parameters. An advantage is that you can use either method as appropriate. A disadvantage is that the code may appear confusing to other programmers.

Summary of the workflow and images displayed above

  1. This shows the syntax of placing parameters into a procedure.
  2. Here is a partial example of a procedure containing parameters.
  3. Here is a partial example of a PL/SQL block that calls the DO_ADDRESS procedure.
  4. There are actually three ways to specify the parameters when calling a procedure.
  5. An advantage to this method is that the actual names of the parameters in the called procedure are not used,
  6. An advantage to this method is that the actual names of the parameters in the called procedure are not used,
  7. The third method of specifying parameters is by using a mixture of position and assignment methods.

As described in the series of images above, you can call a procedure and list the parameters needed by using any of three methods. The most common method is first, where you list each parameter value in the same order as it is defined in the procedure.
The next lesson explores use of parameters and the RETURN command in a function.

In Out Parameters - Exercise

The following exercise asks you to match the correct parameters with the corresponding definitions. Click the link below to match the correct parameter modes to their uses.
In Out Parameters - Exercise

SEMrush Software Target 5SEMrush Software Banner 5