How to execute PL SQL procedure with parameters ? | Parameterized procedure examples

In my previous articles I have given the examples of PL/SQL procedure and information about the PL SQL procedures. In this article I would like to throw light on different parameters for PL SQL procedure with step by step example. If you are facing the interview question you will always be asked by this question – What are different parameters of procedure and give the examples of parameterized procedure. Just remember that this is most asked interview question of PL SQL. We can also see how we can execute procedure with parameters in detail steps.

What you will find in this article?

What are different parameters of PL SQL Procedure?

How to execute PL SQL procedure with parameters ?

Few Notations and examples of PL SQL procedure with parameters

What are different parameters of PL SQL procedure?

In this section we will see the different types of parameters of PL SQL procedure. We will explain the details of parameterized procedure with few real world industry examples.

There are following 3 modes of PL SQL procedure with parameters :

How to execute PL SQL procedure with parameters ?

1. IN : You can call this as input mode or input parameter. The IN parameter will be the default parameter mode which provides the ability to user or programmer to pass the input value to the procedure. According to that input value user will get the results of the procedure accordingly. We have already seen one example in my previous article about input parameters in depth. But again I would like to repeat that example.

If you can see the example above . You will see the P_EMP_ID and P_AMOUNT are two input parameters. You can update the salary of employees according to passing parameters.

OUT parameter :

The output parameter will be used to return a value to the caller. There are so many times in business logic we require to return value to the caller . You can use bind variables to display the specified information.

Real life industry example :

If you want to create a procedure in which you want to pass the parameter and return first name and salary of employee. We require to use bind variable to return the first name and salary or to show it. The execution of procedure with OUT parameter is quite different.

create or replace procedure P_query_emp
(p_emp_id employees.employee_id%type,
p_f_name out employees.first_name%type,
p_sal out employees.salary%type
)
is

begin
select first_name,salary
into p_f_name,p_sal
from
employees
where employee_id=p_emp_id;

exception
when others then
dbms_output.put_line(sqlcode);
dbms_output.put_line(sqlerrm);

end;

Execution of procedure with out parameter :

–you should declare 2 bind variables
variable b_first_name varchar2(100)
variable b_sal number

execute P_query_emp(100,:b_first_name,:b_sal )

print b_first_name b_sal ;

–Way 2 : the other way to print the out parameter

declare
v_first_name employees.first_name%type;
v_sal employees.salary%type;
begin
query_emp(100,v_first_name,v_sal );
dbms_output.put_line(v_first_name);
dbms_output.put_line(v_sal);
end;

You can use above way to execute the procedures with out parameters.

IN OUT parameter :

The IN OUT parameter is used to provide input to the procedure and it will provide the output as modified value.

Real life example :

The best example for input and output parameters together is changing the format for the phone. If you want to change the format like country id and phone number then you can use input and output parameters together.

–The length of the telephone number is 12 and consider the country code is 91 and phone number is 919965555666

–we need a procedure to format the 919965555666to 91(99)65555666
create or replace procedure P_Telephone_Format
(p_tel in out varchar2) –Output parameter
is
begin
p_tel:=substr(p_tel,1,2)||'(‘||substr(p_tel,4,2)||’)’||substr(p_tel,7); –Format using substring function

end;

Execution of procedure :

variable b_telephone varchar2(20);

execute :b_telephone:=’919965555666′;

execute P_Telephone_Format(:b_telephone); –using bind variable

print b_telephone;

–another way

Output :

91(99)65555666

Execution using way 2 :

declare
v_tel varchar2(100):=’919965555666′;
begin
P_Telephone_Format(v_tel);
dbms_output.put_line(v_tel);
end;

Difference between the different parameters in PL SQL :

Default parameter :

The default parameter is used to give the default value to the actual parameters.

Only IN(Input) parameters use the default values to set at the time of execution.

It provides flexibility by combining the positional and named parameter passing.

Real life example :

We can create a table to add products and write a procedure to add products in that table.

---

–using the default value
–2 ways ( default value or := )
create or replace procedure P_add_products
(p_prod_id number,p_prod_name varchar2:=’NO PRODUCT’,p_prod_type varchar2 default ‘NO PRODUCT’)
is
begin

insert into T_products values (p_prod_id,p_prod_name,p_prod_type);
commit;

exception
when others then
dbms_output.put_line (‘error in insert statement’);
dbms_output.put_line (sqlcode);
dbms_output.put_line (sqlerrm);
end;

Execution of procedure :

execute P_add_products(10);

select * from T_products;

It will insert the default value specified in the procedure which is NO PRODUCT.

These are few examples of Procedure with parameters with real examples. If you like this article or if you have any issues with the same kindly comment in comments section.