SUBPROGRAMS PL/SQL – Part 3
Posted by Ravi Varma Thumati on November 11, 2009
So, far we have seen PL/SQL Block (Anonymous Block) which are executed by interactively entering the block at the SQL prompt or by writing the PL/SQL statements in a user_named file and executing the block at SQL prompt using @ command. The block needed to compile at every time it is run and only the user who created the block can use the block.
Any PL/SQL block consists of some hundreds statements, in such cases it is necessary to break the entire block into smaller modules depending on your requirements. So, your block became more easy to understand and efficient to perform operation and maintenance. Stored procedures / Subprograms are such kind of named PL/SQL Block. Basically it is Sophisticated Business rules and application logic.
Stored subprogram are compiled at time of creation and stored in the database itself. The source code is also stored in the database. Any user with necessary privileges can use the stored subprogram.
Procedures and Functions are subprograms having group of SQL, PL/SQL and Java–enables Statements you to move code that enforce the business rules from your application to database. It takes a set of parameters given to them by the calling program and performs a set of actions.
The only real difference between a procedure and a function is that a function will include a single return value. Both functions and procedures can modify and return data passed to them as a parameter.
A procedure or function that has been stored in the library cache is referred to as a stored procedure or a stored function. A stored procedure or stored function has the following characteristics:
- It has a name: This is the name by which the stored procedure or function is called and referenced.
- It takes parameters: These are the values sent to the stored procedure or function from the application.
- It returns values: A stored procedure or function can return one or more values based on the purpose of the procedure or function.
- Procedures
A procedure is a one kind of subprogram, which is designed and created to perform a specific operation on data in your database. A procedure takes zero or more input parameters and returns zero or more output parameters.
The syntax of a creation of procedure is as follows:
Syntax:
CREATE OR REPLACE PROCEDURE procedure_name
[(argument1 [IN/OUT/IN OUT] datatype,
argument2 [IN/OUT/IN OUT] datatype,…)] IS
[<local variable declarations>]
BEGIN
Executable Statements
[EXCEPTION
Optional Exception Handler(s)
]
END;
The procedure is made up of two parts: the declaration and the body of the procedure. The declaration begins with the keyword PROCEDURE and ends with the last parameter declaration. The body begins with the keyword IS and ends with the keyword END. The procedure body is further divided into three parts: declarative, executable and exception part same as PL/SQL block.
The declaration section is used to assign name and define parameter list, which variables are passed to the procedure and which values are returned from the procedure back to the calling program.
Parameters can be define in following format
Argument [parameter mode] datatype
There are three types of parameters mode: IN, OUT and IN OUT
IN Mode
- Default parameter mode.
- Used to pass values to the procedure.
- Formal parameter can be a constant, literal, initialized variable or expression.
- Used for reading purpose
OUT Mode
- Used to return values to the caller.
- Formal parameter cannot be used in an expression, but should be assigned a value.
- Used for writing purpose
IN OUT Mode
- Used to pass values to the procedure as well as return values to the caller
- Formal parameter acts like an initialized variable and should be assigned a value.
- Used for both reading and writing purpose
In a procedure declaration, it is illegal to constrain char and varchar parameter with length and number parameter with precision and scale.
The body of the procedure is where the real work is done. The body is made up of the PL/SQL statements that perform the desired task.
The EXCEPTION Section
In both procedures and functions, you can add optional exception handlers. These exception handlers allow you to return additional information based on certain conditions (such as no data found or some user-specified condition). By using exception handlers and allowing the stored procedure to notify you of some special conditions, you can minimize the amount of return-value checking that must be done in the application code. Because the work to determine that no data has been selected has already been done by the RDBMS engine, you can save on resources if you take advantage of this information.
Example
Create a procedure, which receives a number and display whether it is odd or even. Create or replace procedure oddeven (num in number) is
a number(3);
Begin
a := mod(num,2);
If a = 0 then
dbms_output.put_line( num ||’ is even number’);
Else
dbms_output.put_line( num ||’ is odd number’);
End if;
End;
/
Procedure created.
Execution of procedure
Procedure is executed from a SQL prompt as per follows and one can execute procedure from caller program also.
SQL > execute/exec procedure_name(parameter list)
For example above created procedure is executed as follows
SQL> Exec example1 (7)
7 is odd number
Example
Make a procedure, which will accept a number and return it’s Square.
Create or replace procedure square_no(num in number,ans out number)
Is
Begin
ans:=num*num;
End;
/
Procedure created.
To execute above procedure we make one block, which is known as a Caller Block
Declare
ret number(10);
no number(5) := &no;
Begin
square_no (no, ret);
dbms_output.put_line(‘Square of ‘||no||’ is : ‘|| ret);
End;
/
Output
Enter value for no: 10
old 3: no number(5) := &no;
new 3: no number(5) := 10;
Square of 10 is : 100
Procedures Related to Table
Example
Pass employee no and name and store into employee table.
Create or replace procedure emp_add (eno emp.empno%type, enm
emp.ename%type)
Is
Begin
Insert into emp(empno,ename) values(eno,enm);
Exception
When Dup_val_on_index then
dbms_output.put_line(‘Employee Number already Exist’);
End;
/
To run above procedure
SQL> exec emp_add(1013,’DAXESH’);
Example
Create a Procedure, which receives employee number and display employee name, Designation and salary.
Create or replace procedure empdata(eno in number)
Is
enm varchar2(20);
jb varchar2(20);
sal number(10,2);
Begin
Select ename, job, salary into enm, jb, sal from emp where
empno= eno;
dbms_output.put_line(‘employee name : ‘|| enm);
dbms_output.put_line(‘employee designation: ‘|| jb);
dbms_output.put_line(‘employee salary : ‘|| sal);
End;
/
Procedure created.
Output
SQL>exec empdata(1051)
employee name : RISHI
employee designation: ANALYST
employee salary : 5000
Example
Write a PL/SQL block, which will use a user-defined procedure, which accept employee number
and return employee name and department name in a out parameter.
Create or replace procedure emp_data(eno number, enm out varchar2,
dnm out varchar2)
Is
Begin
select ename, dname into enm, dnm from emp, dept where emp.deptno
= dept.deptno and empno = eno;
End;
/
Procedure created
Block to execute procedure
Declare
employee varchar2(30);
department varchar2(20);
eno number(4);
Begin
eno :=&employeenumber;
emp_data(eno, employee, department);
dbms_output.put_line(‘Employee Number ‘||eno);
dbms_output.put_line(‘Employee Name ‘||employee);
dbms_output.put_line(‘Department Name ‘||department);
End;
/
Output
Enter value for employeenumber: 1011
old 6: eno :=&EmployeeNumber;
new 6: eno :=1011;
Employee Number 1011
Employee Name TEJAS
Department Name RESEARCH
Example
Create a procedure, which receives department number and get total Salary of that Department.
Create or replace procedure dept_total(dno in numbe, total out
number)
Is
Begin
Select sum(salary) into total from emp where deptno= dno;
dbms_output.put_line(‘Total salary of Department ‘|| dno ||
‘ is ‘ || total);
End;
/
Procedure created.
Block to execute procedure
Declare
dn number(5) := &no;
tot number;
Begin
dept_total(dn,tot);
End;
/
Output
Enter value for dn: 10
old 2: dn number(5) := &dn;
new 2: dn number(5) := 10;
Total salary of Department 10 is 235300
Example
Write procedure to accept Department number and display Name, Designation and Age of each employee belonging to such Department.
Create or replace procedure dept_list(dno number)
Is
cursor c1 is select * from emp where deptno = dno;
erec emp%rowtype;
Begin
For erec in c1
loop
dbms_output.put_line(‘Emp. Name : ‘ || erec.ename);
dbms_output.put_line(‘Designation : ‘|| erec.desg);
dbms_output.put_line(‘Age : ‘|| round((sysdate-erec.bdate)/
365,0);
dbms_output.put_line(‘=============================’);
End loop;
End;
/
Procedure created.
Output
SQL>exec dept_list(20);
Emp. Name : AANSHI
Designation : ANALYST
Age : 21
=========================
Emp. Name : TEJAS
Designation : MANAGER
Age : 27
=========================
Emp. Name : DAXESH
Designation : MANAGER
Age : 24
=========================
Example
Create a procedure, which will accept Deptno and Display no of employee under different grade.
Create or replace procedure empcount(dno in number)
Is
Cursor c1 is select grade, count(*) from emp where deptno = dno
group by grade;
grd varchar2(3);
noofemp number(3);
Begin
Open c1;
dbms_output.put_line(‘Grade ‘||’No of employee’);
Loop
Fetch c1 into vgrade, noofemp;
Exit when c1%notfound;
dbms_output.put_line(grd||’ ‘||noofemp);
End loop;
Close c1;
End;
/
Procedure created.
Output
SQL>exec empcount(30);
Grade No of employee
A 1
B 2
C 2
D 1
Functions
A function, like a procedure, is a set of PL/SQL statements that form a subprogram. The subprogram is designed and created to perform a specific operation on data. A function takes zero or more input parameters and returns just one output value. If more than one output value is required, a procedure should be used. The syntax of a function is as follows:
Syntax
CREATE OR REPLACE Function function_name
[(argument1 [IN/OUT/IN OUT] datatype,
argument2 [IN/OUT/IN OUT] datatype,…)] RETURN datatype IS
[<local variable declarations>]
BEGIN
PL/SQL Statements
[EXCEPTION
Optional Exception Handler(s)]
END [function_name];
/
As with a procedure, a function is made up of two parts: the declaration and the body. The declaration begins with the keyword Function and ends with RETURN statement. The body begins with the keyword IS and ends with the keyword END.
The difference between a procedure and a function is the return value. A function has the return declaration as well as a RETURN function within the body of that function that returns a value. This RETURN function is used to pass a return value to the calling program.
- If you do not intend to return a value to the calling program, or you want to return more than one value, use a procedure.
How Procedures and Functions Operate
Procedures and functions use the same basic syntax in the program body with the exception of the
RETURN keyword, which can only be used by functions. The body itself is made up of PL/SQL blocks that perform the desired function and return the desired data to the calling program. The goal of the body of the procedure is both to minimize the amount of data to be transmitted across the network (to and from the calling program) and to perform the PL/SQL statements in the most efficient manner possible.
Example
Create a function to get cube of passed number
Create or replace function cube(no number) return number
Is
ans number(4);
Begin
ans := no * no * no;
return ans;
End;
/
Function created.
Output
SQL> select cube(5) from dual;
CUBE(5)
———-
125
Example
Write a Function to find out maximum salary for the passed designation.
Create or replace function maxjob(des varchar2) return number Is
maxsal number(7,2);
Begin
select max(sal) into maxsal from emp where job = des;
return maxsal;
End;
/
Function created.
Output
SQL> SELECT MAXJOB(‘ANALYST’) FROM DUAL;
MAXJOB(‘ANALYST’)
————————-
6725
Example
Create a Function to find out existence of employee whose name is passed as a parameter
Create or replace function empchk(enm varchar2) return boolean Is
erec emp%rowtype;
Begin
select * into erec from emp where ename = enm;
return true;
Exception
When no_data_found then
return false;
When too_manu_rows then
Return true;
End;
/
Function created.
Block to execute procedure (Caller Program)
Declare
nm emp.ename%type;
b Boolean;
Begin
nm := &employeename
b :=empchk(nm);
if b = true then
dbms_output.put_line(‘Employee Exist’);
else
dbms_output.put_line(‘Employee not exist’);
end if;
End;
/
Example
Write a Function to find out Total salary for the passed department Name.
Create or replace function totsalary(dnm varchar2) return number
Is
totsal emp.sal%type;
Begin
select sum(sal) into totsal from emp,dept where dept.deptno
= emp.deptno and dname = dnm;
return totsal;
End;
/
Function created.
Block to execute procedure
Declare
tot number;
dnm dept.dname%type := &departmentname;
Begin
tot = totalsalary(dnm);
dbms_output.put_line(‘Total Salary of ‘|| dnm || ‘ is ‘ ||
tot);
End;
/
Example
Write a Function to find out No Of Employee who joined between dates passed.
Create or replace function noofemp(date1 date,date2 date) return
number
Is
noofemp number(7);
Begin
select count(*) into noofemp from emp where hiredate between
date1 and date2;
return noofemp;
End;
/
Function created.
Output
SQL> select noofemp(’20-dec-80′,’20-dec-81′) from dual;
NOOFEMP(’20-DEC-80′,’20-DEC-81′)
————————————————
11
Example
Write a function to check whether passed number is Odd or Even.
Create or replace function oddeven(no number) return varchar2 is
Begin
If mod(no,2)=0 then
return
Else
return no || ‘ is odd’;
End if;
End;
/
Function created.
Output
SQL>Select oddeven(10) from dual;
ODDEVEN(10)
—————-
10 is Even
SQL> select oddeven(11) from dual;
ODDEVEN(11)
—————-
11 is Odd
Example
Write a Function to find out total annual income for the employee, who’s number we passed.
Create or replace function ann_income(eno number) return number
Is
total number(9,2);
Begin
Select (salary + nvl(comm,0))*12 into total from emp where
empno = eno;
return total;
End;
/
Function created.
Output
SQL> select ann_income(1010) from dual;
ANN_SAL(1010)
——————-
60000
Example
Create a function, which receives three arguments, first two as a number and third as a arithmetic
operator and return proper answer, In case of invalid operator display appropriate message.
Create or replace function calc(a number, b number, c char, x
boolean) return number is
d number;
invalid_opt exception;
Begin
If c =’+’ then
d := a + b;
Elsif c=’-’ then
d := a-b;
Elsif c=’/’ then
d := a/b;
Elsif c=’*’ then
d := a*b;
Else
Raise invaid_opt;
End if;
x:=true;
Return d;
Exception
When value_error then
dbms_output.put_line(‘cannot perform calculation’);
x:=false;
return 0;
When invalid_opt then
x:=false;
return 0;
End;
/
Block to execute procedure
Declare
n1 number;
n2 number;
ans number;
op varchar2;
valid Boolean;
Begin
n1 := &firstnumber;
n2 := &secondnumber;
op := &operator
ans = (n1,n2,op,valid);
if valid then
dbms_output.put_line(n1 op n2 || ‘ is ‘ || ans);
end if;
End;
/
Packages
A package is an Oracle object, which holds other objects within it. Objects commonly held within a package are procedures, functions, variables, constants, cursors and exceptions. It is a way of creating generic, encapsulation, re-useable code.
A package once written and debugged is compiled and stored in Oracle’s system tables held in an Oracle Database. All users who have execute permissions on the Oracle Database can use the package.
Packages can contain PL/SQL blocks of code, which have been written to perform some process entirely on their own. Some PL/SQL blocks of code do not require any kind of input from other PL/SQL block. These are the package’s standalone subprograms. Alternatively, a package can contain a subprogram that requires input from another PL/SQL block to perform its programmed processes successfully. These are also subprograms of the package but these subprograms are not standalone.
Subprograms held within a package can be called from other stored programs, like triggers, or any other Interactive Oracle program like SQL * Plus.
Unlike the stored programs, the package itself cannot be called, passed parameters to, or nested.
Use of Packages
Packages offer the following advantages:
- Packages enable the organization of commercial applications into efficient modules. Each package is easily understood, and the interfaces between packages are simple, clear, and well defined.
- Packages allow granting of privileges efficiently.
- A package’s public variables and cursors persist for the duration of the session. Therefore, all cursors and procedures that execute in this environment can share them.
- Packages enable the overloading of procedures and functions when required.
- Packages improve performance by loading multiple objects into memory at once. Therefore, subsequent calls to related subprograms in the package require no I/O.
- Packages promote code reuse through the use of libraries that contain stored procedures and functions, thereby reducing redundant coding.
Components of Package
A package has usually two components, a specification and a body. A package’s specification declares the types (variables of the Record type), memory variables, constants, exceptions, cursors, and subprograms that are available for use.
A package’s body fully defines cursors, functions, and procedures and thus implements the specification.
Package Specification
The package specification contains:
- Name of the package
- Names of the datatypes of any arguments
- This declaration is local to the database and global to the package
This means that procedures, functions, variables, constants, cursors, exceptions, and other objects, declared in a package are accessible from anywhere in the package. Therefore, all the information a package needs, to execute a stored subprogram, is contained in the package specifications itself.
The following is an example of the creation of a package specification. In this example, the specification declares a function and a procedure:
Example
Create Or Replace Package Pack1 As
Function Cube(x Number) Return Number;
Procedure EmpProc(Dno Number);
End Pack1;
/
Package created.
Package Body
The body of a package contains the definition of public objects that are declared in the specification. The body can also contain other object declarations that are private to the package. The objects declared privately in the package body are not accessible to other objects outside the package. Unlike package specification, the package body can contain subprogram bodies. After the package is written, debugged, compiled and stored in the database applications can reference the package’s types, call its subprograms, use its cursors, or raise its exceptions.
Create Or Replace Package Body Pack1 As
Function Cube(x Number) Return Number is
Begin
Return x*x*x;
End;
Procedure EmpProc(Dno Number) is
Erec Emp%Rowtype;
Cursor c1 is Select *from Emp where Deptno=Dno;
Begin
Open c1;
Loop
Fetch c1 into Erec;
Exit when c1%NotFound;
Dbms_output.Put_line(‘Emp.No. :’||Erec.Empno);
Dbms_output.put_line(‘Name :’||Erec.Ename);
Dbms_output.put_line(‘Dept.No.:’||Erec.Deptno);
Dbms_output.put_line(‘Salary :’||Erec.Salary);
Dbms_output.put_line(‘———————————‘);
End Loop;
End;
End Pack1;
/
Package Body created
- Package specification name and Package Body name must be same. Otherwise Package Body will give you compilation error.
To Execute Package’s Function
Declare
No Number:=&No;
Res Number;
Begin
Res:=Pack1.Cube(No);
Dbms_output.put_line(‘Cube of ‘||No||’ is:’||Res);
End;
/
Output
Enter value for No:3
Cube of 3 is 9
To Execute Package’s Procedure:
SQL>Exec Pack1.EmpProc(20)
Or
SQL> Declare
No Number:=&No;
Begin
Pack1.EmpProc(No);
End;
/
Enter value for No:20
This will display all the record of Deptno 20 of Employee table.
