Parameters allow you to pass values into (IN), pass values out of (OUT), or pass values into and out of (IN OUT) a cursor, function, or procedure.
Parameters follow the same PL/SQL identifier naming rules as variables.
The following table shows the parameter prefix values.
| Prefix | Description |
|---|---|
| ai | Argument IN |
| ao | Argument OUT |
| aio | Argument IN OUT |
ai means argument in.
The following table shows Parameter Name Examples Using Prefixing Scheme
| Scope IN | Scope OUT | Scope IN OUT |
|---|---|---|
| ain_id | aon_id | aion_id |
| aiv_name aov_name aiov_name | ||
| aid_birth_date | aod_birth_date | aiod_birth_date |
| aiv_gender | aov_gender | aiov_gender |
The following code create a package named PARAMETER, which shows how to declare parameter , its syntax and parameter scope.
create or replace package PARAMETER as
FUNCTION in_out_inout(aiv_in in varchar2,
aov_out out varchar2,
aiov_inout in out varchar2)
return varchar2;
PROCEDURE in_out_inout(aiv_in in varchar2,
aov_out out varchar2,
aiov_inout in out varchar2);
end PARAMETER;
/
The syntax used to declare the parameters in the function and procedure is as follows:
(
<parameter_name_1> [IN][OUT] <data_type_1>,
<parameter_name_2> [IN][OUT] <data_type_2>,...
<parameter_name_N> [IN][OUT] <data_type_N>)
where <parameter_name> is the name of the parameter; the scope is IN, OUT, or IN OUT; <data_type> is the data type of the parameter.
We can use column or row anchors to specify the data type %TYPE or %ROWTYPE.
The parameter keywords IN and OUT determine the accessibility of the parameters.
IN makes your parameters' data available to the called cursor, function, or procedure.
An IN parameter can be used to pass a value into a cursor, function, or procedure.
OUT allows the called function or procedure to set the parameter's value within the called PL/SQL block.
An OUT parameter can be used to pass a value out of a function or procedure.
The combination of IN and OUT allows both levels of accessibility.
An IN OUT parameter can be used to do both.