Microsoft.NET

……………………………………………….Expertise in .NET Technologies

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.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: