Parameter Syntax and Examples for Oracle Procedures

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.

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.

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.

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.

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.)

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.)

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.