the difference between in,out,inout modes when passing parameters to PL/SQL procedures and functions with examples.
Could anyone explain the difference between in,out,inout modes when passing parameters to Oracle PL/SQL procedures and functions with examples and also how to call them them?
When you declare a PL/SQL function or procedure, you have to specify whether the parameters will be read-only or modifiable. from the point of view of the procedure/function. In other words you have to decide (or know) whether the parameters will just be used to pass information into the procedure/function, just receive information from the procedure/function or both. IN
parameters are read only. OUT
parameters), and (IN OUT
parameters are modifiable.
Why does Oracle care? Well, it probably doesn't make much difference to the database other than the way the code is generated, but it could make a great deal of difference to your applications. These parameter modes are used to constrain the operations on the parameters by your procedure/function on the parameters to a certain extent, they affect what type of object the actual parameter can be and determine whether the value of the actual parameter is available in the procedure/function.IN
parameters are read only so the body of the procedure or function CANNOT assign a value to them but the value of the actual parameter used is available in the procedure/function and it can be a constant (or an expression).OUT
parameters are modifiable but the value of the actual parameter used is NOT available in the procedure/function, the actual parameter must be a variable rather than a constant (or expression) and the procedure or function can assign a value to the actual parameter.IN OUT
parameters are modifiable so the procedure or function can assign values to them, the value of the actual parameter used is available in the procedure/function, the actual parameter cannot be a constant or an expression.
Let's look at a few examples.
The following procedure which calculates an employees net pay has an IN parameter (emp_id) and an OUT parameter (net_pay).CREATE OR REPLACE
2 PROCEDURE calc_net_pay (emp_id IN NUMBER
3 ,net_pay OUT NUMBER) IS
5 err_msg CONSTANT VARCHAR2(45) := 'calc_net_pay: employee not found ';
9 SELECT rate*hours - deductions INTO net_pay
10 FROM emp where id = emp_id;
14 WHEN NO_DATA_FOUND THEN
18 WHEN TOO_MANY_ROWS THEN
20 'Duplicate pay details found for '||
23 END calc_net_pay;
To call we would need to have something like this:DECLARE
As the first formal parameter emp_id is an IN parameter we can make the actual parameter a constant but we have to declare a variable (emp_pay) to receive the value of net_pay calculated by the procedure.
If we changed the definition of the procedure to make net_pay an IN OUT parameter there would be little difference. However if we change it be an IN parameter then Oracle would generate an error when we compiled it (because we are not allowed to write to read-only parameters).create or replace procedure testit
p1 in number
,p2 in out number
,p3 out number
if p1 = 1 then dbms_output.put_line('P1 = 1 '); end if;
if p2 = 1 then dbms_output.put_line('P2 = 1 '); end if;
if p3 = 1 then dbms_output.put_line('P3 = 1 ');
elsif p3 IS NULL then dbms_output.put_line('P3 IS NULL');
The next example is designed to demonstrate the affect the different parameter modes have on the initial value of the formal procedure at run time.
We can call it with the following piece of code:DECLARE
p1 number :=1;
p2 number :=1;
p3 number :=1;
The output from this is:
P1 = 1
P2 = 1
P3 IS NULL
The reason for this is because, as we said earlier, for an OUT parameter the value of the variable which becomes the actual parameter at run time is not available to the called procedure or function.
See our series PL/SQL tutorials to learn more about PL/SQL
. These questions and answers on PL/SQL could also be helpful
For details of the PL/SQL training
available from our partners both classroom-based and on-line see the Oracle training page