PL/SQL Procedures

Procedures

  • Advantages of Subprograms
  • Provide Extensibility
    –        PL/SQL language can be tailored to suit the needs of the application
  • Promote reusability and maintainability
    –        Once validated, they can be used with confidence in any number of applications
    –        Simplifies maintenance/enhancement, as subprogram is only affected if definition changes
  • Provide Modularity
    –        Program can be broken down into manageable, well-defined logical modules
    –        Supports top-down design and stepwise refinement approach to problem solvingAid in abstraction
    –        Allow mental separation from particulars
    –        Stubs allow programmers to defer definition of procedures/functions until main
  • program is tested and debugged

Procedure  performs specific action
Stored in database and can be invoked or called by any anonymous block
Can take parameters
Datatype specifier in parameter declaration must be unconstrained
Has two parts
–        Specification
•        begins with keyword PROCEDURE, ends with procedure name or parameter list
–        Body
•        begins with keyword IS, ends with keyword END followed by optional procedure name

CREATE [OR REPLACE] PROCEDURE [(parameter1, … parameterN)] IS
[local declarations]
BEGIN
executable statements;
[EXCEPTION
exception handlers]
END [ ];

parameter stands for
variablename [IN|OUT|IN OUT] datatype [{:= | DEFAULT} value]

When a procedure is created, Oracle automatically performs these steps
–        Compiles the procedure
–        Stores the compiled code
–        Stores the procedure in the database

The PL/SQL compiler used to compile the code
If an error occurs, the procedure is created but it is invalid
Enforce Data Security
–        Can grant users access to a procedure that queries a table, but not grant access to the table itself
Improve Database Performance
–        Less amount of information sent over network
–        Compilation not required to execute code
–        Procedure present in shared pool so retrieval from disk not required
Memory Savings
–        Only one copy of procedure needs to be loaded in memory for execution by multiple users
Increase in Development Productivity
–        Can avoid redundant coding and increase productivity, by writing a single procedure
Integrity
–        Procedures need to be tested only once, to guarantee that it returns accurate results

Calling a Stored Procedure
Can call a procedure in a PL/SQL statement
–        Example
branch_sum(‘NYK’);
Can call a procedure from SQL*Plus
–        Example

SQL>  EXECUTE branch_sum(‘NYK’);

Examples of Procedures

1) –Procedure without parameters

create or  replace procedure pr1
as
s emp.sal%type; — s is the Local variable
Begin
select sal into s
from emp
where ename = ‘SMITH’;
dbms_output.put_line(s);
end;

Parameter Modes for Procedures and Functions
Used to define the behavior of formal parameters
Can be used with any subprogram
Three parameter modes
–        IN (Default)
–        OUT
–        IN OUT
IN
–        allows values to be passed to the subprogram being called
–        inside the subprogram it acts like a constant
–        actual corresponding parameter can be a constant, literal, initialized variable or expression
–        can be initialized to default values

2) –Supplying parameters to a procedure which are by default of  IN type

create or replace procedure pr2
(En  IN Emp.Empno%type,     Name  IN Emp.ename%type,          S Emp.Sal%type)
is
Begin
Insert into Emp(empno,ename,sal)
Values(En,Name,S);
dbms_output.put_line(‘One record inserted through procedure’);
End;     

3) Giving default values to the parameters

Due to default value given the parameter becomes optional also.

But if any other value is given then it takes it.
create or replace procedure pr3 (Eno emp.empno%type, N emp.ename%type,   S emp.sal%type,      dno emp.deptno%type DEFAULT 10)
is
Begin
Insert into emp (empno,ename,sal,deptno)
values(Eno,N,S,dno);
dbms_output.put_line(‘Record inserted’);
End;

— While executing
–exec pr3 (1,’o’,800) —–> (No deptno parameter given!!!)

4) –Cannot give size to the parameters

create or replace procedure pr4 (name  char(8), marks number)
is
Begin
if marks >= 35 then
dbms_output.put_line(‘Passed’);
else
dbms_output.put_line(‘Failed’);
end if;
dbms_output.put_line(name);
End;

Named notations

create or replace procedure dept_rec

(p_deptno in number, p_dname in dept.dname%type default ‘Accounts’,
p_loc dept.loc%type)
is
begin
Insert into dept
values(p_deptno, p_dname, p_loc);
dbms_output.put_line(‘Done’);
end;

To exceute such a parameter we have to use the named notation

exec dept_rec(p_loc => ‘Pune’, p_deptno => 10);

Mutiple ways of passing the parameters  —

1. Positional Method  —    exec dept_rec(20,’Purchase’,’Mumbai’);
2. Named Method  –
exec dept_rec(p_loc => ‘Bangalore’,p_deptno => 30);
exec dept_rec(p_deptno =>20, p_dname => ‘Inspection’, p_loc => ‘Pune’);
exec dept_rec(p_dname =>’Stores’, p_loc => ‘Mumbai’, p_deptno => 10);

3. Combination Method
After positional there can be named parameters, but after named there cannot be positional parameters.
exec dept_rec(80, p_loc =>’Mumbai’, p_dname =>’Marketing’);
exec dept_rec(p_deptno =>90, ‘Pune’,’Research’);  — Wrong


OUT parameter
–        allows values to be returned to the caller of a subprogram
–        inside the subprogram it acts like an uninitialized variable
–        actual corresponding parameter must be a variable; it cannot be a constant or expression
–        its value cannot be assigned to another variable or reassigned to itself

5)

create or replace procedure pr5(Name IN varchar2, Salary OUT number)
Is
Begin
Select sal into Salary
from emp
where ename = Name;
End;

 –Steps for displaying the OUT parameter
–1) Compiling the procedure.
–2) Declare the bind variable on SQL prompt as variable payment number
— Bind variables are of SQL* plus environment which are used to hold the return
–value given by the procedure or function.
–3)Now execute the proc — exec pr5(‘SMITH’, :payment)
–4)To display the value of payment — print payment
–5)Bind variables are session specific.Their existence is removed as the session –ends.

Using local variable for out parameter when procedure is called inside another block.

create or replace procedure p1(n in emp.ename%type, s out emp.sal%type)
is
begin
select sal into s
from emp
where ename = n;
end;

declare
x emp.sal%type;
begin
p1(‘SMITH’,x);
dbms_output.put_line(x);
end;

6) IN OUT parameter
–        allows initial values to be passed and returns updated values to the caller
–        inside the subprogram it acts like an initialized variable
–        actual corresponding parameter must be a variable; it cannot be a constant or expression
–        can be assigned a value and its value can be assigned to another variable

a)

create or replace procedure pr6 (x IN OUT number)
Is
Begin
x := (x * x);
End;

/*pr6 procedure cannot be executed independently on sql prompt.
It has to be called inside a plsql block. It actually gives the square value to the variable of that plsql block.
In short IN OUT type of paramter makes a procedure similar to function, as the function also returns the value to the calling environment.*/

b)

declare
a number;
Begin
a := &a;
dbms_output.put_line(a);
pr6(a);

/*When a is given as a parameter , it’s status is of IN OUT. So IN means the user input value and OUT means the changes square figure due to the procedure pr6. After the procedure is called with a as parameter then a value gets changed. At this time a acts as a OUT parameter, since the procedure is giving the changed value to a.*/
dbms_output.put_line(a);
End;

7) IN OUT example from with respect to database
a)
create or replace procedure salrise(salary IN OUT number) is
Begin
salary :=  salary (salary * 0.20);
End;
/*Salrise procedure will increment the sal by 20% and give the value to the calling plsql block.*/

b)

Declare
n emp.ename%type;
s emp.sal%type;
Begin
n := ‘&n’;
select sal into s
from emp
where ename = n;
dbms_output.put_line(‘The old salary is  ‘ || s);
/*Now calling the procdure Salrise and giving s as a IN parameter*/
Salrise(s);
/*After giving the salary as a parameter the salary value gets incremented by 20%  */
dbms_output.put_line(‘The changed salary is  ‘|| s);
/*updating the table*/
Update emp
set sal = s
where ename = n;
dbms_output.put_line(‘Salary of  ‘ || n || ‘  is updated in the table.’);
Exception
When NO_DATA_FOUND then
dbms_output.put_line(‘No such name’);
end;

————————————————————————————————–

Declaring Sub procedures
Create table log_table(username varchar2(30), log_date date);
Create or replace procedure delete_emp(p_ename emp.ename%type)
Is
/*Creating a sub procedure */
Procedure log_emp
is
Begin
Insert into log_table
Values(user, SYSDATE);
End;

Begin
Delete from emp
where ename = p_ename;
log_emp;
End;

Handled or Unhandled Exceptions for a procedure getting called in another.

create or replace procedure innerproc(x number,y number)
is
begin
dbms_output.put_line(x/y);
exception
when zero_divide then
dbms_output.put_line(‘y cannot be negative’);
end;

create or replace procedure outerproc
as
begin
dbms_output.put_line(‘AA’);
/*Calling the innerproc precedure.*/
innerproc(4,0);
dbms_output.put_line(‘cc’);
exception
when others then
dbms_output.put_line(‘A Run Time Error has occurred.’);
end;

If the innerproc procedure is not having any error handler then the error handler outer procedure gets executed.

AUTHID CURRENT_USER

If a procedure reads data from table and performs some DML then AUTHID CURRENT_USER is applicable. Due to this clause the owner of the procedure only can execute this procedure and read or perform DML. Even if the owner has given execute privilege of such a procedure to any other user, still the other user cannot execute the procedure. So from data security point of view this clause is helpful.
The following scenario will explain this concept.

Case study —-
Log on as System/Manager (DBA Login)

create user a1 identified by a1;
grant resource, create session to a1;

——————————————————-
create user a2 identified by a2;
grant resource, create session to a2;

———————————————————-

Without AUTHID CURRENTUSER
conn a1/a1
create table t1(a number);
insert into t1 values(1);
commit;

———————————————————–

create or replace procedure t1_data
is
x number;
begin
select a into x from t1;
dbms_output.put_line(x);
end;

———————————————-

grant execute on t1_data to a2;
————————————————-

conn a2/a2
exec a1.t1_data — Data is shown.

With AUTHID CURRENTUSER

conn a1/a1

create or replace procedure t1_data1
AUTHID CURRENT_USER
is
x number;
begin
select a into x from t1;
dbms_output.put_line(x);
end;

—————————————
grant execute on t1_data1 to a2;

——————————————-

conn a2/a2
exec a1.t1_data1 — Data is not shown. Throws error.

Posted in UncategorizedTagged