Programming Oracle with PL/SQL - Learn About Procedure and Function in Oracle with example
A PL_SQL Block are broadly divided into two categories that are anonymous block and named block .
Anonymous Block:-
Anonymous Block - These blocks have no name and are generally stored in a host file or entered directly into SQL*Plus and executed just once. Most of my example uses salary table and here also i used this table. The following example shows how anonymous block work with oracle.
SQL> select * from salary;
ROLLNO EMPNAME DESIGN BPAY DA TA PF NETSAL
---------- --------------- --------------- ---------- ---------- ---------- ---------- ----------
10001 S.Krishnan HOD 25000 1500 1200 2250 27000
10002 K.K.Omana Asst.Manager 19500 1500 1200 1800 22000
10003 Anishkumar.K Asst.Manager 19500 1500 1200 1800 22000
10004 Girishkumar.K Asst.Manager 19500 1500 1200 1800 22000
SQL> declare increase number :=10;
2 begin
3 update salary set netsal=netsal*(100+increase)/100;
4 commit;
5 end;
6 /
PL/SQL procedure successfully completed. ---> it is an example for anonymous block
SQL> select * from salary;
ROLLNO EMPNAME DESIGN BPAY DA TA PF NETSAL
---------- --------------- --------------- ---------- ---------- ---------- ---------- ----------
10001 S.Krishnan HOD 25000 1500 1200 2250 29700
10002 K.K.Omana Asst.Manager 19500 1500 1200 1800 24200
10003 Anishkumar.K Asst.Manager 19500 1500 1200 1800 24200
10004 Girishkumar.K Asst.Manager 19500 1500 1200 1800 24200
Named Pl-SQL Block:-
A pl-sql named block is also known as PL-SQL subprogram that can be called anywhere in the pl-sql application and is saved in the data dictionary.
SQL> create procedure inc_sal(increase number) as
2 begin
3 update salary set netsal=netsal*(100+increase)/100;
4 commit;
5 end;
6 /
Procedure created.
SQL> execute inc_sal(12); ---> calling the procedure
PL/SQL procedure successfully completed.
SQL> select * from salary;
ROLLNO EMPNAME DESIGN BPAY DA TA PF NETSAL
---------- --------------- --------------- ---------- ---------- ---------- ---------- ----------
10001 S.Krishnan HOD 25000 1500 1200 2250 33264
10002 K.K.Omana Asst.Manager 19500 1500 1200 1800 27104
10003 Anishkumar.K Asst.Manager 19500 1500 1200 1800 27104
10004 Girishkumar.K Asst.Manager 19500 1500 1200 1800 27104
PL-SQL subpogram have been devided into two,
1. Procedure
2. Function
Differences between Anonymous Blocks and Subprograms:-
Procedure:-
A procedure is a block of code that carries out some action. It can, optionally, be defined
with a number of arguments. These arguments are replaced with the actual parameters
given when the procedure is invoked. The arguments can be IN arguments, meaning
that they are used to pass data into the procedure (if you not specify the variable type it will assume as IN type) , or OUT arguments, meaning that they are modified by the procedure and after execution the new values are passed out of the procedure. Arguments can also be IN-OUT, where the one variable serves both purposes. Within a procedure, you can define any number of variables that, unlike the arguments, are private to the procedure. To run a procedure, either call it from within a PL/SQL block or use the interactive EXECUTE command
The order that the variables are passed is important as PL/SQL will assign them to the procedure’s variables in the order that they were passed to the procedure.
POSITIONAL vs. NOTATIONAL parameters
A procedure can be communicated by passing parameters to it. The parameters passed to a procedure may follow either positional notation or named notation.
Example:-
If a procedure is defined as GROSS (ESAL NUMBER, ECOM NUMBER)
If we call this procedure as GROSS (ESA, ECO) then parameters used are called positional parameters. For Notational Parameters we use the following syntax
GROSS (ECOM => ECO, ESAL => ESA)
Example.1
A simple procedure and calling it from command mode by execute command,
SQL> select * from salary;
ROLLNO EMPNAME DESIGN BPAY DA TA PF NETSAL
---------- --------------- --------------- ---------- ---------- ---------- ---------- ----------
10001 S.Krishnan HOD 25000 1500 1200 2250 27000
10002 K.K.Omana Asst.Manager 19500 1500 1200 1800 22000
10003 Anishkumar.K Asst.Manager 19500 1500 1200 1800 22000
10004 Girishkumar.K Asst.Manager 19500 1500 1200 1800 22000
SQL> create or replace procedure inserttosal (prollno number, pname varchar ,pbpay number)
2 as
3 begin
4 insert into salary(rollno,empname,bpay) values(prollno, pname,pbpay);
5 commit;
6 end;
7 /
Procedure created.
SQL> exec inserttosal(10005,'Rakesh.K',22000); --> proc called by exec command
PL/SQL procedure successfully completed.
SQL> select * from salary;
ROLLNO EMPNAME DESIGN BPAY DA TA PF NETSAL
---------- --------------- --------------- ---------- ---------- ---------- ---------- ----------
10001 S.Krishnan HOD 25000 1500 1200 2250 27000
10002 K.K.Omana Asst.Manager 19500 1500 1200 1800 22000
10003 Anishkumar.K Asst.Manager 19500 1500 1200 1800 22000
10004 Girishkumar.K Asst.Manager 19500 1500 1200 1800 22000
10005 Rakesh.K 22000 ---> new row inserted by proc
SQL>
Example.2
Here procedure is called from a pl-sql block,
SQL> create or replace procedure pro_salinsert (prollno IN number,pempname IN varchar,
2 pdesign In varchar , pbpay IN number ) is
3 pda number(6,2);
4 pta number(6,2);
5 ppf number(6,2);
6 pnetsal number(8,2);
7 begin
8 pda := pbpay*8/100;
9 pta := pbpay*10/100;
10 ppf := pbpay*7/100;
11 pnetsal := pbpay + pda + pta - ppf;
12 insert into salary values
13 (prollno,pempname,pdesign,pbpay,pda,pta,ppf,pnetsal);
14 end;
15 /
Procedure created.
SQL> set lines 450
SQL> select * from salary;
ROLLNO EMPNAME DESIGN BPAY DA TA PF NETSAL
---------- --------------- --------------- ---------- ---------- ---------- ---------- ----------
10001 S.Krishnan HOD 25000 1500 1200 2250 33264
10002 K.K.Omana Asst.Manager 19500 1500 1200 1800 27104
10003 Anishkumar.K Asst.Manager 19500 1500 1200 1800 27104
10004 Girishkumar.K Asst.Manager 19500 1500 1200 1800 27104
SQL> ed insert_by_proc
declare
prollno salary.rollno%type;
Example.3
In this example, i used OUT variable in a procedure,
SQL> create or replace procedure pro_circlearea ( r in number , area out number ) is
2 begin
3 area := 3.14159*r*r;
4 end;
5 /
Procedure created.
SQL> create table circle (
2 radius number(5,2), area number(7,2));
Table created.
SQL> ed circleinsert
declare
pradius circle.radius%type;
parea circle.area%type;
begin
pradius :=&pradius;
pro_circlearea(r => pradius, area => parea); --> here i used NOTATIONAL parameters
insert into circle values (pradius,parea);
end;
/
SQL> @circleinsert
Enter value for pradius: 10
old 8: pradius :=&pradius;
new 8: pradius :=10;
PL/SQL procedure successfully completed.
SQL> select * from circle;
RADIUS AREA
--------- ----------
10 314.16
Function:-
A function is similar in concept to a procedure, but it does not have OUT arguments and cannot be invoked with the EXECUTE command. It returns a single value, with the RETURN statement.Anything that a function can do, a procedure could also do. Functions are generally used to support specific operations: small code blocks, that will be used many times.Procedures are more commonly used to divide code into modules, and may contain long and complex processes.
Example.1
Here i created a function to calculate the area of the circle,
SQL> create or replace function func_circlearea
2 (r number) return number is
3 area number(7,2);
4 begin
5 area := 3.14159*r*r;
6 return area;
7 end;
8 /
Function created.
SQL> ed circleinsert
declare
pradius circle.radius%type;
parea circle.area%type;
begin
pradius :=&pradius;
parea := func_circlearea(pradius); ---> function is called here
insert into circle values (pradius,parea);
end;
/
SQL> @circleinsert
Enter value for pradius: 10
old 8: pradius :=&pradius;
new 8: pradius :=10;
PL/SQL procedure successfully completed.
SQL> select * from circle;
RADIUS AREA
---------- ----------
10 314.16
SQL>
you can also retreive the information by,
SQL> select func_circlearea(11) from dual;
FUNC_CIRCLEAREA(11)
-------------------
380.13
Note:- It will not insert the records into circle table , but display the return value of the function.
A PL_SQL Block are broadly divided into two categories that are anonymous block and named block .
Anonymous Block:-
Anonymous Block - These blocks have no name and are generally stored in a host file or entered directly into SQL*Plus and executed just once. Most of my example uses salary table and here also i used this table. The following example shows how anonymous block work with oracle.
SQL> select * from salary;
ROLLNO EMPNAME DESIGN BPAY DA TA PF NETSAL
---------- --------------- --------------- ---------- ---------- ---------- ---------- ----------
10001 S.Krishnan HOD 25000 1500 1200 2250 27000
10002 K.K.Omana Asst.Manager 19500 1500 1200 1800 22000
10003 Anishkumar.K Asst.Manager 19500 1500 1200 1800 22000
10004 Girishkumar.K Asst.Manager 19500 1500 1200 1800 22000
SQL> declare increase number :=10;
2 begin
3 update salary set netsal=netsal*(100+increase)/100;
4 commit;
5 end;
6 /
PL/SQL procedure successfully completed. ---> it is an example for anonymous block
SQL> select * from salary;
ROLLNO EMPNAME DESIGN BPAY DA TA PF NETSAL
---------- --------------- --------------- ---------- ---------- ---------- ---------- ----------
10001 S.Krishnan HOD 25000 1500 1200 2250 29700
10002 K.K.Omana Asst.Manager 19500 1500 1200 1800 24200
10003 Anishkumar.K Asst.Manager 19500 1500 1200 1800 24200
10004 Girishkumar.K Asst.Manager 19500 1500 1200 1800 24200
Named Pl-SQL Block:-
A pl-sql named block is also known as PL-SQL subprogram that can be called anywhere in the pl-sql application and is saved in the data dictionary.
The same example that we used for anonymous pl-sql block can be used for explaining name pl-sql block.
SQL> create procedure inc_sal(increase number) as
2 begin
3 update salary set netsal=netsal*(100+increase)/100;
4 commit;
5 end;
6 /
Procedure created.
SQL> execute inc_sal(12); ---> calling the procedure
PL/SQL procedure successfully completed.
SQL> select * from salary;
ROLLNO EMPNAME DESIGN BPAY DA TA PF NETSAL
---------- --------------- --------------- ---------- ---------- ---------- ---------- ----------
10001 S.Krishnan HOD 25000 1500 1200 2250 33264
10002 K.K.Omana Asst.Manager 19500 1500 1200 1800 27104
10003 Anishkumar.K Asst.Manager 19500 1500 1200 1800 27104
10004 Girishkumar.K Asst.Manager 19500 1500 1200 1800 27104
1. Procedure
2. Function
Differences between Anonymous Blocks and Subprograms:-
Anonymous blocks
|
Subprograms
|
Unnamed
PL/SQL blocks
|
Named
PL/SQL blocks
|
Compiled
every time
|
Compiled
only once
|
Does
not store in database
|
Stores
in database
|
Cannot
be invoked by other applications
|
These
are named and therefore can be invoked by other applications
|
Do
not return values
|
Subprogram
called functions must return values
|
Cannot
take parameters
|
Can
take parameters
|
Procedure:-
A procedure is a block of code that carries out some action. It can, optionally, be defined
with a number of arguments. These arguments are replaced with the actual parameters
given when the procedure is invoked. The arguments can be IN arguments, meaning
that they are used to pass data into the procedure (if you not specify the variable type it will assume as IN type) , or OUT arguments, meaning that they are modified by the procedure and after execution the new values are passed out of the procedure. Arguments can also be IN-OUT, where the one variable serves both purposes. Within a procedure, you can define any number of variables that, unlike the arguments, are private to the procedure. To run a procedure, either call it from within a PL/SQL block or use the interactive EXECUTE command
The order that the variables are passed is important as PL/SQL will assign them to the procedure’s variables in the order that they were passed to the procedure.
POSITIONAL vs. NOTATIONAL parameters
A procedure can be communicated by passing parameters to it. The parameters passed to a procedure may follow either positional notation or named notation.
Example:-
If a procedure is defined as GROSS (ESAL NUMBER, ECOM NUMBER)
If we call this procedure as GROSS (ESA, ECO) then parameters used are called positional parameters. For Notational Parameters we use the following syntax
GROSS (ECOM => ECO, ESAL => ESA)
Example.1
A simple procedure and calling it from command mode by execute command,
SQL> select * from salary;
ROLLNO EMPNAME DESIGN BPAY DA TA PF NETSAL
---------- --------------- --------------- ---------- ---------- ---------- ---------- ----------
10001 S.Krishnan HOD 25000 1500 1200 2250 27000
10002 K.K.Omana Asst.Manager 19500 1500 1200 1800 22000
10003 Anishkumar.K Asst.Manager 19500 1500 1200 1800 22000
10004 Girishkumar.K Asst.Manager 19500 1500 1200 1800 22000
SQL> create or replace procedure inserttosal (prollno number, pname varchar ,pbpay number)
2 as
3 begin
4 insert into salary(rollno,empname,bpay) values(prollno, pname,pbpay);
5 commit;
6 end;
7 /
Procedure created.
SQL> exec inserttosal(10005,'Rakesh.K',22000); --> proc called by exec command
PL/SQL procedure successfully completed.
SQL> select * from salary;
ROLLNO EMPNAME DESIGN BPAY DA TA PF NETSAL
---------- --------------- --------------- ---------- ---------- ---------- ---------- ----------
10001 S.Krishnan HOD 25000 1500 1200 2250 27000
10002 K.K.Omana Asst.Manager 19500 1500 1200 1800 22000
10003 Anishkumar.K Asst.Manager 19500 1500 1200 1800 22000
10004 Girishkumar.K Asst.Manager 19500 1500 1200 1800 22000
10005 Rakesh.K 22000 ---> new row inserted by proc
SQL>
Example.2
Here procedure is called from a pl-sql block,
SQL> create or replace procedure pro_salinsert (prollno IN number,pempname IN varchar,
2 pdesign In varchar , pbpay IN number ) is
3 pda number(6,2);
4 pta number(6,2);
5 ppf number(6,2);
6 pnetsal number(8,2);
7 begin
8 pda := pbpay*8/100;
9 pta := pbpay*10/100;
10 ppf := pbpay*7/100;
11 pnetsal := pbpay + pda + pta - ppf;
12 insert into salary values
13 (prollno,pempname,pdesign,pbpay,pda,pta,ppf,pnetsal);
14 end;
15 /
Procedure created.
SQL> set lines 450
SQL> select * from salary;
ROLLNO EMPNAME DESIGN BPAY DA TA PF NETSAL
---------- --------------- --------------- ---------- ---------- ---------- ---------- ----------
10001 S.Krishnan HOD 25000 1500 1200 2250 33264
10002 K.K.Omana Asst.Manager 19500 1500 1200 1800 27104
10003 Anishkumar.K Asst.Manager 19500 1500 1200 1800 27104
10004 Girishkumar.K Asst.Manager 19500 1500 1200 1800 27104
SQL> ed insert_by_proc
declare
prollno salary.rollno%type;
pempname salary.empname%type;
pdesign salary.design%type;
pbpay salary.bpay%type;
begin
prollno := &prollno;
pempname := '&pempname';
pdesign := '&pdesign';
pbpay := &pbpay;
pro_salinsert (prollno , pempname , pdesign ,pbpay); ---> procedure called here
end;
/
SQL> @insert_by_proc
Enter value for prollno: 10005
old 8: prollno := &prollno;
new 8: prollno := 10005;
Enter value for pempname: Aneesh.K
old 9: pempname := '&pempname';
new 9: pempname := 'Aneesh.K';
Enter value for pdesign: Typist
old 10: pdesign := '&pdesign';
new 10: pdesign := 'Typist';
Enter value for pbpay: 15000
old 11: pbpay := &pbpay;
new 11: pbpay := 15000;
PL/SQL procedure successfully completed.
SQL> select * from salary;
ROLLNO EMPNAME DESIGN BPAY DA TA PF NETSAL
---------- --------------- --------------- ---------- ---------- ---------- ---------- ----------
10001 S.Krishnan HOD 25000 1500 1200 2250 33264
10002 K.K.Omana Asst.Manager 19500 1500 1200 1800 27104
10003 Anishkumar.K Asst.Manager 19500 1500 1200 1800 27104
10004 Girishkumar.K Asst.Manager 19500 1500 1200 1800 27104
10005 Aneesh.K Typist 15000 1200 1500 1050 16650
SQL>
Example.3
In this example, i used OUT variable in a procedure,
SQL> create or replace procedure pro_circlearea ( r in number , area out number ) is
2 begin
3 area := 3.14159*r*r;
4 end;
5 /
Procedure created.
SQL> create table circle (
2 radius number(5,2), area number(7,2));
Table created.
SQL> ed circleinsert
declare
pradius circle.radius%type;
parea circle.area%type;
begin
pradius :=&pradius;
pro_circlearea(r => pradius, area => parea); --> here i used NOTATIONAL parameters
insert into circle values (pradius,parea);
end;
/
SQL> @circleinsert
Enter value for pradius: 10
old 8: pradius :=&pradius;
new 8: pradius :=10;
PL/SQL procedure successfully completed.
SQL> select * from circle;
RADIUS AREA
--------- ----------
10 314.16
Function:-
A function is similar in concept to a procedure, but it does not have OUT arguments and cannot be invoked with the EXECUTE command. It returns a single value, with the RETURN statement.Anything that a function can do, a procedure could also do. Functions are generally used to support specific operations: small code blocks, that will be used many times.Procedures are more commonly used to divide code into modules, and may contain long and complex processes.
Example.1
Here i created a function to calculate the area of the circle,
SQL> create or replace function func_circlearea
2 (r number) return number is
3 area number(7,2);
4 begin
5 area := 3.14159*r*r;
6 return area;
7 end;
8 /
Function created.
SQL> ed circleinsert
declare
pradius circle.radius%type;
parea circle.area%type;
begin
pradius :=&pradius;
parea := func_circlearea(pradius); ---> function is called here
insert into circle values (pradius,parea);
end;
/
SQL> @circleinsert
Enter value for pradius: 10
old 8: pradius :=&pradius;
new 8: pradius :=10;
PL/SQL procedure successfully completed.
SQL> select * from circle;
RADIUS AREA
---------- ----------
10 314.16
SQL>
you can also retreive the information by,
SQL> select func_circlearea(11) from dual;
FUNC_CIRCLEAREA(11)
-------------------
380.13
Note:- It will not insert the records into circle table , but display the return value of the function.
No comments:
Post a Comment