Defining Formal Parameters : Parameters « Function Procedure Packages « Oracle PL/SQL Tutorial






A parameter is a value that you can pass from a block of statements to a function.

The Syntax for Defining a Parameter

parameter_name [MODE] parameter_type [:= value | DEFAULT value]

parameter_name is the name you assign to the parameter

parameter_type is the variable type you assign.

The optional MODE statement gives you complete control over your incoming parameters.

The simplest parameter can be coded as

(p_squared NUMBER)

p_squared is the parameter_name, and NUMBER is the parameter_type.

You can add a %TYPE after the parameter.

%TYPE will pick up the field type from the table.

(p_emptype employee.pay_type%TYPE)

Types of modes.

MODEHandling of Parameter
INRead-only
OUTWrite-only
IN OUTRead or write










27.14.Parameters
27.14.1.Defining Formal Parameters
27.14.2.There are three types of formal parameters in subprograms: IN, OUT, and IN OUT.
27.14.3.Define function with NUMBER type parameter
27.14.4.Function without parameter
27.14.5.Use IF/ELSIF/ELSE to verify the input parameter
27.14.6.Use ROWTYPE as the parameter
27.14.7.Passing parameters to procedures
27.14.8.Using Named Notation
27.14.9.Use mixed notation to avoid the second parameter, but keep the first and third
27.14.10.Parameter Modes
27.14.11.Positional Notation
27.14.12.Positional vs. named parameter passing.
27.14.13.Parameter Default Values
27.14.14.Specifying procedure or function parameters Positional notation
27.14.15.Table collection type parameter
27.14.16.Mixed Name and Position Notation Calls