Declaring and Passing Subprogram Parameters

A subprogram heading can declare formal parameters. Each formal parameter declaration can specify a mode and a default value. When you invoke the subprogram, you can pass actual parameters to it.

What are the types of parameter?

• Formal parameters: The variables declared in the subprogram header and referenced in its execution part are known as Formal parameters.
• Actual parameters: the variables or expressions that you pass to the subprogram when you invoke it are known as Actual parameters.

Corresponding formal and actual parameters must have compatible data types.

Example of Actual and Formal Parameter

SET SERVEROUTPUT ON;
DECLARE 
FNAME VARCHAR2(10) := 'David'; 
LNAME VARCHAR2(10) := 'Miller'; 

FUNCTION FULLNAME
(
FIRST_NAME VARCHAR2, --Formal parameter
LAST_NAME VARCHAR2   --Formal parameter
)  
RETURN VARCHAR2
AS
FULL_NAME VARCHAR2(20);
BEGIN
FULL_NAME := FIRST_NAME || ' ' || LAST_NAME ;
RETURN FULL_NAME;
END;

BEGIN
DBMS_OUTPUT.PUT_LINE(FULLNAME
(
FNAME,  --Actual parameter
LNAME   --Actual parameter
)); 
END;
/

Output:

anonymous block completed
David Miller

What are the types of parameter modes in subprogram?

There are three types of parameters modes in procedure:

• IN Mode: An IN parameter lets you pass a value to the subprogram being invoked. Inside the subprogram, an IN parameter acts like a constant. It cannot be assigned a value.

You can pass a constant, literal, initialized variable, or expression as an IN parameter.

Usage of IN Mode:

Replace the Function header in above code with the below code and execute the program again. You will see it will give the same output as executed without IN mode because IN is the Default mode.

FUNCTION FULLNAME
(
FIRST_NAME IN VARCHAR2, --IN Mode, Formal parameter
LAST_NAME IN VARCHAR2   --IN Mode, Formal parameter
)



• OUT Mode: An OUT parameter returns a value to the caller of a subprogram. Inside the subprogram, an OUT parameter acts like a variable. You can change its value and reference the value after assigning it

You must pass a variable, not a constant or an expression, to an OUT parameter. Its previous value is lost unless you specify the NOCOPY keyword or the subprogram exits with an unhandled exception

The initial value of an OUT parameter is NULL; therefore, the data type of an OUT parameter cannot be a subtype defined as NOT NULL, such as the built-in subtype NATURALN or POSITIVEN. Otherwise, when you invoke the subprogram, PL/SQL raises VALUE_ERROR.

Before exiting a subprogram, assign values to all OUT formal parameters. Otherwise, the corresponding actual parameters will be null. If you exit successfully, PL/SQL assigns values to the actual parameters. If you exit with an unhandled exception, PL/SQL does not assign values to the actual parameters.

Usage of OUT Mode:

SET SERVEROUTPUT ON;
DECLARE 
FNAME VARCHAR2(10) := 'David'; 
LNAME VARCHAR2(10) := 'Miller';
FULLNAME VARCHAR2(20);

PROCEDURE GETFULLNAME
(
FIRST_NAME IN VARCHAR2, --IN Mode, Formal parameter
LAST_NAME IN VARCHAR2,  --IN Mode, Formal parameter
FULL_NAME OUT VARCHAR2  --OUT Mode, Formal parameter
) 
AS
BEGIN
FULL_NAME := FIRST_NAME || ' ' || LAST_NAME ;
END;

BEGIN
GETFULLNAME(FNAME,LNAME, FULLNAME);  --Invoking Function with Actual parameter
DBMS_OUTPUT.PUT_LINE(FULLNAME);
END;
/



• IN OUT Mode: An IN OUT parameter passes an initial value to a subprogram and returns an updated value to the caller. It can be assigned a value and its value can be read. Typically, an IN OUT parameter is a string buffer or numeric accumulator, that is read inside the subprogram and then updated.

The actual parameter that corresponds to an IN OUT formal parameter must be a variable, not a constant or an expression.

If you exit a subprogram successfully, PL/SQL assigns values to the actual parameters. If you exit with an unhandled exception, PL/SQL does not assign values to the actual parameters.

Usage of IN OUT Mode:

SET SERVEROUTPUT ON;
DECLARE 
SQUARE_NUM NUMBER := 10 ;
PROCEDURE GETSQUARE
(
NUM IN OUT NUMBER --IN OUT Mode, Formal parameter
)
AS
BEGIN
NUM := NUM * NUM ;
END;

BEGIN
GETSQUARE(SQUARE_NUM);  --Invoking Procedure
DBMS_OUTPUT.PUT_LINE(SQUARE_NUM);
END;
/

Summary of Subprogram Parameter Modes

IN	OUT	IN OUT
The default	Must be specified	Must be specified
Passes a value to the subprogram	Returns a value to the caller	Passes an initial value to the subprogram and returns an updated value to the caller
Formal parameter acts like a constant	Formal parameter acts like an uninitialized variable	Formal parameter acts like an initialized variable
Formal parameter cannot be assigned a value	Formal parameter must be assigned a value	Formal parameter should be assigned a value
Actual parameter can be a constant, initialized variable, literal, or expression	Actual parameter must be a variable	Actual parameter must be a variable
Actual parameter is passed by reference (the caller passes the subprogram a pointer to the value)	Actual parameter is passed by value (the subprogram passes the caller a copy of the value) unless NOCOPY is specified	Actual parameter is passed by value (the caller passes the subprogram a copy of the value and the subprogram passes the caller a copy of the value) unless NOCOPY is specified

What are the ways of passing Actual Parameters in Subprogram?

There are three ways we can pass the actual parameter:

• Positional
• Positional
• Mixed Notation

Summary of Positional, Named, or Mixed Notation

Notation	Description	Usage Notes
Positional	Specify the same parameters in the same order as the procedure declares them.	Compact and readable, but has these disadvantages: If you specify the parameters (especially literals) in the wrong order, the bug can be hard to detect. If the procedure's parameter list changes, you must change your code.
Named	Specify the name and value of each parameter, using the association operator, =>. Order of parameters is insignificant.	More verbose than positional notation, but easier to read and maintain. You can sometimes avoid changing your code if the procedure's parameter list changes (for example, if parameters are reordered or a new optional parameter is added). Safer than positional notation when you invoke an API that you did not define, or define an API for others to use.
Mixed	Start with positional notation, then use named notation for the remaining parameters.	Recommended when you invoke procedures that have required parameters followed by optional parameters, and you must specify only a few of the optional parameters.

Usage of Positional, Positional, or Mixed Notation

SET SERVEROUTPUT ON;
DECLARE 
FNAME VARCHAR2(10) := 'David'; 
LNAME VARCHAR2(10) := 'Miller'; 

FUNCTION FULLNAME
(
FIRST_NAME VARCHAR2, --Formal parameter
LAST_NAME VARCHAR2   --Formal parameter
)  
RETURN VARCHAR2
AS
FULL_NAME VARCHAR2(20);
BEGIN
FULL_NAME := FIRST_NAME || ' ' || LAST_NAME ;
RETURN FULL_NAME;
END;

BEGIN
DBMS_OUTPUT.PUT_LINE(FULLNAME(FNAME, LNAME)); -- Positional notation:
DBMS_OUTPUT.PUT_LINE(FULLNAME(LAST_NAME => LNAME, FIRST_NAME => FNAME)); -- Named notation:
DBMS_OUTPUT.PUT_LINE(FULLNAME(FNAME, LAST_NAME => LNAME)); -- Mixed notation:
END;
/

Usage of DEFAULT value in subprogram:

SET SERVEROUTPUT ON;
DECLARE 
FNAME VARCHAR2(10) := 'David'; 
LNAME VARCHAR2(10) := 'Miller'; 

FUNCTION FULLNAME
(
FIRST_NAME VARCHAR2, --Formal parameter
MIDDLE_NAME VARCHAR2 DEFAULT 'Jhon', --Formal parameter
LAST_NAME VARCHAR2   --Formal parameter
)  
RETURN VARCHAR2
AS
FULL_NAME VARCHAR2(20);
BEGIN
FULL_NAME := FIRST_NAME || ' ' || MIDDLE_NAME || ' ' || LAST_NAME ;
RETURN FULL_NAME;
END;

BEGIN
DBMS_OUTPUT.PUT_LINE(FULLNAME(FNAME, 'Jhon', LNAME)); -- Positional notation:
DBMS_OUTPUT.PUT_LINE(FULLNAME(LAST_NAME => LNAME, FIRST_NAME => FNAME)); -- Named notation:
DBMS_OUTPUT.PUT_LINE(FULLNAME(FNAME, LAST_NAME => LNAME)); -- Mixed notation:
END;
/

Next Article: PL/SQL Packages

For any questions, suggestions or feedback, Please write to us. Thanks for Reading :)