Procedure and Function Examples

Procedures:-
A Procedure is a named PL/SQL block which is compiled and stored in
the database for repeated execution.

Basic Syntax :
------------

Create or replace procedure <procedure_name>
is
begin
..............
..............
.............
end;
/
Ex 1:
-----------
Create or replace procedure p1
is
begin
dbms_output.put_line('Hello World');
end;
/

Procedure created.

To execute the procedure:
----------------------------
Exec command is used to execute the procedure.

SQL> Exec p1
Hello World

A procedure can have three types of parameters.
1) IN Parameter
2) OUT Parameter
3) IN OUT Parameter
In Parameters are used to accept values from the user.
Ex 2:
---------
Create a procedure which accepts two numbers and display its sum.

create or replace procedure add_num ( a IN number,
b IN number)
is
c number(3);
begin
c := a+b;
dbms_output.put_line(' The sum is '||c);
end;
/

Procedure created.

To execute the procedure:
--------------------------
SQL> exec add_num (10,20)


Ex 3:
--------

Create a Procedure which accepts an empno and increments his salary by 1000.
create or replace procedure inc_sal ( a in number)
is
begin
update emp set sal = sal+1000
where empno = a;
end;
/

Procedure created.

TO execute the procedure:
---------------------------

SQL> exec inc_sal(7900)

We can improve the above procedure code by using %type attribute in
 procedure parameters.

The above procedure can be re-written as below :

create or replace procedure inc_sal ( a in emp.empno%type)
is
begin
update emp set sal = sal+1000
where empno = a;
end;
/


Create a procedure which accepts deptno and display ename and salary
of employees working in that department.
create or replace procedure display_emp1 (l_deptno emp.deptno%type)
is
cursor c1
is select ename,sal from emp
where deptno = l_deptno;

begin

for emp_rec in c1 loop
dbms_output.put_line(emp_rec.ename||'...'||emp_rec.sal);
end loop;

end;
=====================================================================

Functions:-
Function is a PL/SQL block which must and should return single value.
Syntax:
-----------

Create or replace function <Function_name>
( <Par_name> <mode> <datatype>,
,, ,, ,, )
return datatype
is
Begin
..........
.........
end;
/

ex1:
-----

Create a function which accepts two numbers and display its sum.

create or replace function add_num_f1 ( a number, b number)
return number
is
c number(5);
begin

c :=a+b;
return c;
end;
/
To invoke a function from a pl/Sql block:
---------------------------------------------

declare
n number(5);
begin

n := add_num_f1(20,40);

dbms_output.put_line('The sum is '||n);
end;
/
We can invoke functions from select stmt:
----------------------------------------------
select add_num_f1(30,50) from dual;
Functions can be invoked as part of an expression:
----------------------------------------------------

select 100 + add_num_f1(50,10) from dual;
Ex2:
------

create a function which accepts sal and returns tax value ( 10% of sal is tax).

create or replace function cal_tax ( a number)
is
begin

return a*10/100;
end;
/

Note: A function can return a value using return statement.
Ex 3:
----------

Have a look at the following function:



create or replace function add_num_f2 ( a number, b number)
return number
is
c number(5);
begin

insert into dept values (50,'HR','FAISALABAD')

c :=a+b;
return c;
end;
/
The above function gets created.

The above function can be invoked from the pl/SQL block

declare
n number(5);
begin

n := add_num_f2(20,40);

dbms_output.put_line('The sum is '||n);
end;
/


But, we cannot invoke the above function using select stmt.

ex:

select add_num_f2(30,50) from dual; -- will give us error.

Note: So, functions with dml commands cannot be invoked from select stmt.

------------------------

TO see the list of all the functions
select object_name from user_objects
where object_type = 'FUNCTION';
----------------------

To drop a function

drop function <function_name>;

ex:

drop function add_num_f2;

-----------------------

Functions are mainly used for calculation purposes.
Rest of the activities, prefer procedures.Pro