the difference between in,out,inout modes when passing parameters to PL/SQL procedures and functions with examples.

by devi
(Bangalore)

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
4
5 err_msg CONSTANT VARCHAR2(45) := 'calc_net_pay: employee not found ';
6
7 BEGIN
8
9 SELECT rate*hours - deductions INTO net_pay
10 FROM emp where id = emp_id;
11
12 EXCEPTION
13
14 WHEN NO_DATA_FOUND THEN
15 DBMS_OUTPUT.PUT_LINE(err_msg||emp_id);
16 RAISE;
17
18 WHEN TOO_MANY_ROWS THEN
19 DBMS_OUTPUT.PUT_LINE(
20 'Duplicate pay details found for '||
21 emp_id);
22
23 END calc_net_pay;


To call we would need to have something like this:
DECLARE
  emp_pay NUMBER;
BEGIN
  calc_net_pay(1,emp_pay);
END;


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
) is
begin
dbms_output.put_line('100 ');
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');
end if;
end;


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;
BEGIN
testit (p1,p2,p3);
END;


The output from this is:
100
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.

Comments for the difference between in,out,inout modes when passing parameters to PL/SQL procedures and functions with examples.

Average Rating starstarstarstarstar

Click here to add your own comments

Dec 21, 2015
Rating
starstarstarstarstar
Quality of the explanation
by: Pranay Joshi

Thanks for the providing such easy and clear explanation of the topics. Your work is really very nice. Though I am not a ardent programmer but still i find it very easy to learn from your work. Thanks once again.

Click here to add your own comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.