Navigate

Working With Procedures In Oracle

Working With Procedures In Oracle

Procedure is group of PLSQL statements that can be called by a name.

PREREQUISITES:

To create or replace a procedure:

  • In your own schema, you must have the CREATE PROCEDURE system privilege.
  • In another user's schema, you must have the CREATE ANY PROCEDURE system privilege.

FEATURES:

  • Usually, procedures are compiled and stored in database.
  • Once stored in database they become a stored procedure schema object.
  • Then they can be referenced or called by any application connected to Oracle database.
  • At the time of invocation stored procedure can accept parameters.

STRUCTURES:

Procedures follow the basic PLSQL block structure. It consists of following components:

  • Declarative Part (Optional)
  • Executable Part (Mandatory)
  • Exception-Handling Part (Optional)

Declarative Part:

This part is used to declare the variables and constants used in the application logic.

Executable Part:

Start with BEGIN and end with END keyword. It contains the application logic.

Exception-Handling Part:

Handles error conditions, that may be raised in the executable part of the block.

Simple Example:

--Working With Procedures In Oracle : Simple Example

create or replace procedure sp_display_emp_details
is
  cursor cur_emp
  is
  select employee_id,
         last_name,
         job_id,
         salary,
         department_id
  from employees
  where department_id = 30;
  v_emp_rec cur_emp%rowtype;
begin
  open cur_emp;
  loop
    fetch cur_emp into v_emp_rec;
    exit when cur_emp%notfound;
    dbms_output.put_line('Employee_id = '||v_emp_rec.employee_id||' Name = '||v_emp_rec.last_name||' Job = '||v_emp_rec.job_id||' Salary = '||v_emp_rec.salary||' Departmentid = '||v_emp_rec.department_id);
  end loop;
  close cur_emp;
exception
  when others then
    dbms_output.put_line('Error - > '||sqlerrm);
end;
/
Procedure SP_DISPLAY_EMP_DETAILS compiled
Execute the procedure:
set serveroutput on;
exec SP_DISPLAY_EMP_DETAILS;
Output:

Inserting data through Procedure:

Table Setup:
--Working With Procedures In Oracle : Example : Inserting Data through Procedures

create table emp_details_entry
as
select a.employee_id,
       a.last_name,
       a.job_id,
       a.salary,
       a.department_id,
       b.department_name
from employees a, departments b
where a.department_id = b.department_id
and 1 = 2;
Procedure Setup:
--Working With Procedures In Oracle : Example : Inserting Data through Procedures

create or replace procedure sp_insert_emp_details
is
  cursor cur_emp
  is
  select a.employee_id,
         a.last_name,
         a.job_id,
         a.salary,
         a.department_id,
         b.department_name
  from employees a, 
       departments b
  where a.department_id = b.department_id
  and a.department_id = 30;
  v_emp_rec cur_emp%rowtype;
  v_count number := 0;
begin
  execute immediate 'truncate table emp_details_entry';
  open cur_emp;
  loop
    fetch cur_emp into v_emp_rec;
    exit when cur_emp%notfound;
    insert into emp_details_entry(employee_id, last_name, job_id, salary, department_id, department_name)
    values(v_emp_rec.employee_id, v_emp_rec.last_name, v_emp_rec.job_id, v_emp_rec.salary, v_emp_rec.department_id, v_emp_rec.department_name);
    v_count := v_count + sql%rowcount;
  end loop;
  close cur_emp;
  dbms_output.put_line('No of record inserted into emp_details_entry table --> '||v_count);
exception
  when others then
    dbms_output.put_line('Error - > '||sqlerrm);
end;
/
Executing the Procedure:
exec SP_INSERT_EMP_DETAILS;

PL/SQL procedure successfully completed.

No of record inserted into emp_details_entry table --> 6

Querying the table:

select * from emp_details_entry;

Updating data through Procedure:

--Working With Procedures In Oracle : Example : Updating Data through Procedure

create or replace procedure sp_update_emp_details
is
  cursor cur_emp
  is
  select employee_id,
         last_name,
         job_id,
         salary,
         department_id,
         department_name
  from emp_details_entry;
  v_emp_rec cur_emp%rowtype;
  v_count number := 0;
begin
  open cur_emp;
  loop
    fetch cur_emp into v_emp_rec;
    exit when cur_emp%notfound;
    if v_emp_rec.last_name in ('Raphaely') then
      update emp_details_entry
      set salary = salary * 1.05
      where employee_id = v_emp_rec.employee_id;
      v_count := v_count + sql%rowcount;
    elsif v_emp_rec.salary < 2700 then 
      update emp_details_entry
      set salary = salary * 1.10
      where employee_id = v_emp_rec.employee_id;
      v_count := v_count + sql%rowcount;
    end if;
  end loop;
  close cur_emp;
  dbms_output.put_line('No of record updated in emp_details_entry table --> '||v_count);
exception
  when others then
    dbms_output.put_line('Error - > '||sqlerrm);
end;
/
exec SP_UPDATE_EMP_DETAILS;
No of record updated in emp_details_entry table --> 3

PL/SQL procedure successfully completed.
select * from emp_details_entry;

Deleting data through Procedure:

--Working With Procedures In Oracle : Example : Deleting Data through Procedure

create or replace procedure sp_delete_emp_details
is
  cursor cur_emp
  is
  select employee_id,
         last_name,
         job_id,
         salary,
         department_id,
         department_name
  from emp_details_entry;
  v_emp_rec cur_emp%rowtype;
  v_count number := 0;
begin
  open cur_emp;
  loop
    fetch cur_emp into v_emp_rec;
    exit when cur_emp%notfound;
    if v_emp_rec.last_name in ('Tobias') then
      delete emp_details_entry
      where employee_id = v_emp_rec.employee_id;
      v_count := v_count + sql%rowcount;
    end if;
  end loop;
  close cur_emp;
  dbms_output.put_line('No of record deleted from emp_details_entry table --> '||v_count);
exception
  when others then
    dbms_output.put_line('Error - > '||sqlerrm);
end;
/
exec sp_delete_emp_details;
No of record deleted from emp_details_entry table --> 1

PL/SQL procedure successfully completed.
select * from emp_details_entry;

PARAMETERS:

Information in the subprogram is passed through parameters.

Types of Parameters:

  • Formal Parameter
  • Actual Parameter
Formal Parameters:

If a parameter is declared in a subprogram and is referenced in the subprogram body. Then it is called as a Formal parameter.

Actual Parameters:

When the parameters are passed from the calling subprogram they are called Actual parameters.

In addition, actual and its respective formal parameters should be of same data type.

Example of Formal Parameters:

--Working With Procedures In Oracle : Example : Parameters

create or replace procedure sp_formal_param_test(p_deptno employees.department_id%type)
is
  cursor cur_emp
  is
  select a.employee_id,
       a.last_name,
       a.job_id,
       a.salary,
       a.department_id,
       b.department_name
  from employees a, 
       departments b
  where a.department_id = p_deptno     --Formal parameter--
  and a.department_id = b.department_id;
  v_emp_rec cur_emp%rowtype;
  v_count number := 0;
begin
  execute immediate 'truncate table emp_details_entry';
  open cur_emp;
  loop
    fetch cur_emp into v_emp_rec;
    exit when cur_emp%notfound;
    insert into emp_details_entry(employee_id, last_name, job_id, salary, department_id, department_name)
    values(v_emp_rec.employee_id, v_emp_rec.last_name, v_emp_rec.job_id, v_emp_rec.salary, v_emp_rec.department_id, v_emp_rec.department_name);
    v_count := v_count + sql%rowcount;
  end loop;
  close cur_emp;
  dbms_output.put_line('No of record inserted into emp_details_entry table --> '||v_count);
exception
  when others then
    dbms_output.put_line('Error - > '||sqlerrm);
end;
/
set serveroutput on;
exec SP_FORMAL_PARAM_TEST(30);  --Actual Parameter--
No of record inserted into emp_details_entry table --> 6

PL/SQL procedure successfully completed.

Modes of Parameters:

  • IN
  • OUT
  • IN/OUT
IN MODE:
  • This is the default type of parameter mode
  • It passes values to a subprogram
  • The formal parameter acts like a constant
  • Formal parameter cannot be assigned a value
  • IN formal parameter is initialized to the actual parameter with which it is called.
  • Otherwise it will consider the default value with which it is explicitly initialized.
  • The actual parameter can be a constant, initialized variable, literal or expression
OUT MODE:
  • Explicitly specified
  • Returns values to the caller
  • The formal parameters act like uninitialized variable
  • Formal parameters cannot be used in an expression, must be assigned a value
  • Actual parameter must be a variable
IN/OUT MODE:
  • It must be explicitly specified
  • Passes initial values to a subprogram, returns updated values to the caller.
  • The formal parameter acts like an initialized variable
  • Formal parameter should be assigned a value
  • Actual parameter must be a variable
  • Like an IN parameter, an IN OUT formal parameter is initialized to the actual parameter with which it was called.
  • Like an OUT parameter, an IN OUT formal parameter is is modifiable by the called program.
  • In addition, the last value of the formal parameter is passed to the calling program's actual parameter if the called program terminates without an exception.
    • If a handled exception occurs, the actual parameter takes on the last value to which formal parameter was set.
    • In case an unhandled exception occurs, the value of the actual parameter remains what it was prior to the call.

Tabular form of comparison of all modes of parameters:

FeaturesINOUTIN/OUT
The formal parameter initialized to:The actual parameter valueActual parameter valueThe actual parameter value
Formal parameter modifiable by the called program?NoYesYes
Post normal termination of the called program, actual parameter contains:The original actual parameter value prior to the callThe last value of the formal parameterLast value of the formal parameter
After a handled exception in the called program, actual parameter contains:Original actual parameter value prior to the callLast value of the formal parameterThe last value of the formal parameter
Post an unhandled exception in the called program, actual parameter contains:The original actual parameter value prior to the callOriginal actual parameter value prior to the callThe original actual parameter value prior to the call
Working With Procedures In Oracle : Comparison Between IN/OUT/IN-OUT :

Datatypes of a formal parameter consists of one of the following:

  • Unconstrained type
  • Constrained type

Example of Modes of Parameters:

Unconstrained Type: Number, Varchar2

Constrained Type: %Type, %Rowtype

Example of IN and OUT parameters:
--Working With Procedures In Oracle : Example : IN and OUT Parameters

create or replace procedure sp_formal_inout_param_test
(
 p_deptno in employees.department_id%type, 
 p_count out number
)
is
  cursor cur_emp
  is
  select a.employee_id,
       a.last_name,
       a.job_id,
       a.salary,
       a.department_id,
       b.department_name
  from employees a, 
       departments b
  where a.department_id = p_deptno     --Formal parameter--
  and a.department_id = b.department_id;
  v_emp_rec cur_emp%rowtype;
begin
  p_count := 0;
  execute immediate 'truncate table emp_details_entry';
  open cur_emp;
  loop
    fetch cur_emp into v_emp_rec;
    exit when cur_emp%notfound;
    insert into emp_details_entry(employee_id, last_name, job_id, salary, department_id, department_name)
    values(v_emp_rec.employee_id, v_emp_rec.last_name, v_emp_rec.job_id, v_emp_rec.salary, v_emp_rec.department_id, v_emp_rec.department_name);
    p_count := p_count + sql%rowcount;
  end loop;
  close cur_emp;
exception
  when others then
    dbms_output.put_line('Error - > '||sqlerrm);
end;
/
Calling Part:
declare
  v_count number := 0;
begin
  sp_formal_inout_param_test(30, v_count);  --Actual Parameters--
  dbms_output.put_line('Total number of record inserted -> '||v_count);
end;
/
Output:
Total number of record inserted -> 6

PL/SQL procedure successfully completed.
Example of IN/OUT Parameter:
--Working With Procedures In Oracle : Example : IN/OUT Parameters

create or replace procedure sp_formal_in_out_param_test
(
 p_deptno in out employees.department_id%type
)
is
  cursor cur_emp
  is
  select a.employee_id,
       a.last_name,
       a.job_id,
       a.salary,
       a.department_id,
       b.department_name
  from employees a, 
       departments b
  where a.department_id = p_deptno     --Formal parameter--
  and a.department_id = b.department_id;
  v_emp_rec cur_emp%rowtype;
  v_count number := 0;
begin
  execute immediate 'truncate table emp_details_entry';
  open cur_emp;
  loop
    fetch cur_emp into v_emp_rec;
    exit when cur_emp%notfound;
    insert into emp_details_entry(employee_id, last_name, job_id, salary, department_id, department_name)
    values(v_emp_rec.employee_id, v_emp_rec.last_name, v_emp_rec.job_id, v_emp_rec.salary, v_emp_rec.department_id, v_emp_rec.department_name);
    v_count := v_count + sql%rowcount;
  end loop;
  close cur_emp;
  p_deptno := v_count;
exception
  when others then
    dbms_output.put_line('Error - > '||sqlerrm);
end;
/
Calling Part:
declare
  v_count number := 30;
begin
  sp_formal_in_out_param_test(v_count);  --Actual Parameter--
  dbms_output.put_line('Total number of record inserted -> '||v_count);
end;
/
Output:
Total number of record inserted -> 6

PL/SQL procedure successfully completed.

Notation of Parameters:

When calling a procedure you can write the actual parameter by using:

  • Positional Notation
  • Named Notation
  • Mixed Notation
Positional NotationNamed NotationMixed Notation
Same parameter can be specified in the same order as they are declared in the procedureSpecify the name of each parameter along with its valueYou specify the first parameter with positional notation, then switched to named notation for last parameters
You must change your code if the procedure's parameter list changedAn arrow serves as the association operator. The order of parameters is not significantUse this notation to call procedures that have some required parameters, followed by some optional parameters

Examples of Notation:

--Working With Procedures In Oracle : Example : Notations

create or replace procedure sp_notation_test
(
 p_deptno in employees.department_id%type,
 p_jobid  in employees.job_id%type,
 p_salary in employees.salary%type
)
is
  cursor cur_emp
  is
  select a.employee_id,
       a.last_name,
       a.job_id,
       a.salary,
       a.department_id,
       b.department_name
  from employees a, 
       departments b
  where a.department_id = p_deptno     --Formal parameter--
  and a.job_id = p_jobid
  and a.department_id = b.department_id;
  v_emp_rec cur_emp%rowtype;
  v_count number := 0;
begin
  execute immediate 'truncate table emp_details_entry';
  open cur_emp;
  loop
    fetch cur_emp into v_emp_rec;
    exit when cur_emp%notfound;
    if v_emp_rec.job_id = 'ST_CLERK' then
      insert into emp_details_entry(employee_id, last_name, job_id, salary, department_id, department_name)
      values(v_emp_rec.employee_id, v_emp_rec.last_name, v_emp_rec.job_id, v_emp_rec.salary, v_emp_rec.department_id, v_emp_rec.department_name);
      v_count := v_count + sql%rowcount;
    elsif (v_emp_rec.job_id = 'SH_CLERK' and v_emp_rec.salary > p_salary) then
      insert into emp_details_entry(employee_id, last_name, job_id, salary, department_id, department_name)
      values(v_emp_rec.employee_id, v_emp_rec.last_name, v_emp_rec.job_id, v_emp_rec.salary, v_emp_rec.department_id, v_emp_rec.department_name);
      v_count := v_count + sql%rowcount;
    end if;
  end loop;
  close cur_emp;
  dbms_output.put_line('Total no of records inserted -> '||v_count);
exception
  when others then
    dbms_output.put_line('Error - > '||sqlerrm);
end;
/
Calling Part:
select count(1) cnt
from employees
where department_id = 50
and job_id = 'ST_CLERK';

Cnt:20
select count(1)
from employees
where department_id = 50
and job_id = 'SH_CLERK'
and salary > 4000;
Cnt: 2
Positional Notation:
--Working With Procedures In Oracle : Example : Calling of Positional Notation

declare
  v_deptno employees.department_id%type;
  v_jobid  employees.job_id%type;
  v_salary employees.salary%type;
begin
  v_deptno := 50;
  v_jobid  := 'ST_CLERK';
  v_salary := 5000;
  sp_notation_test(v_deptno, v_jobid, v_salary);
  v_deptno := 50;
  v_jobid  := 'SH_CLERK';
  v_salary := 4000;
  sp_notation_test(v_deptno, v_jobid, v_salary);
end;
/
Output:
Total no of records inserted -> 20
Total no of records inserted -> 2

PL/SQL procedure successfully completed.
Named Notation:
--Working With Procedures In Oracle : Example : Calling of Named Notation

begin
  sp_notation_test(p_jobid => 'ST_CLERK', p_deptno => 50, p_salary => 5000);

  sp_notation_test(p_jobid => 'SH_CLERK', p_deptno => 50, p_salary => 4000);
end;
/
Output:
Total no of records inserted -> 20
Total no of records inserted -> 2

PL/SQL procedure successfully completed.
Mixed Notation:
--Working With Procedures In Oracle : Example : Calling of Mixed Notation

declare
  v_deptno employees.department_id%type := 50;
begin
  sp_notation_test(v_deptno, p_jobid => 'ST_CLERK', p_salary => 5000);

  sp_notation_test(v_deptno, p_salary => 4000, p_jobid => 'SH_CLERK');
end;
/
Output:
Total no of records inserted -> 20
Total no of records inserted -> 2

PL/SQL procedure successfully completed.

Data Dictionary:

1:
SELECT OBJECT_NAME, OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, STATUS, EDITIONABLE
FROM USER_OBJECTS
WHERE OBJECT_NAME = 'SP_NOTATION_TEST';
2:
SELECT OBJECT_NAME, PROCEDURE_NAME, OBJECT_ID, SUBPROGRAM_ID, OVERLOAD, OBJECT_TYPE, AGGREGATE, PIPELINED, DETERMINISTIC, AUTHID, RESULT_CACHE
FROM USER_PROCEDURES
WHERE OBJECT_NAME = 'SP_NOTATION_TEST';
3:
SELECT OBJECT_NAME, PACKAGE_NAME, OBJECT_ID, OVERLOAD, SUBPROGRAM_ID, ARGUMENT_NAME, POSITION, SEQUENCE, DATA_TYPE, IN_OUT
FROM USER_ARGUMENTS
WHERE OBJECT_NAME = 'SP_NOTATION_TEST';
4:
SELECT * FROM USER_DEPENDENCIES
WHERE NAME = 'SP_NOTATION_TEST'
AND REFERENCED_OWNER NOT IN ('SYS', 'PUBLIC');
5:
SELECT NAME, TYPE, SEQUENCE, LINE, POSITION, TEXT LARGE_ERROR_DESCRIPTION, ATTRIBUTE, MESSAGE_NUMBER
FROM USER_ERRORS
WHERE NAME = 'SP_NOTATION_TEST';
SELECT * FROM USER_SOURCE
WHERE NAME = 'SP_NOTATION_TEST';
SELECT * FROM USER_OBJECT_SIZE
WHERE NAME = 'SP_NOTATION_TEST';

Removing a Procedure:

drop procedure sp_notation_test;

Examples of Different types of Procedures:

Example 1:

Procedure in a PLSQL Block:

set serveroutput on;
declare
  procedure sp_plsql_block(p_deptno employees.department_id%type)
  is
  begin
    for i in (select employee_id,
                     last_name,
                     salary,
                     department_id
              from employees
              where department_id = p_deptno)
     loop
       dbms_output.put_line('Employee_id = '||i.employee_id||' Name = '||i.last_name||' Salary = '||i.salary||' Department_id = '||i.department_id);
     end loop;
  exception
    when others then
      dbms_output.put_line('Error in sp_plsql_block - > '||sqlerrm);
  end;
begin
  sp_plsql_block(30);
exception
  when others then
    dbms_output.put_line('Error in outer block - > '||sqlerrm);
end;
/

Output:

Employee_id = 114 Name = Raphaely Salary = 11000 Department_id = 30
Employee_id = 115 Name = Khoo Salary = 3100 Department_id = 30
Employee_id = 116 Name = Baida Salary = 2900 Department_id = 30
Employee_id = 117 Name = Tobias Salary = 2800 Department_id = 30
Employee_id = 118 Name = Himuro Salary = 2600 Department_id = 30
Employee_id = 119 Name = Colmenares Salary = 2500 Department_id = 30

PL/SQL procedure successfully completed.
Example 2:

Creating a Procedure inside a Procedure:

create or replace procedure sp_proc_inside_proc
is
  procedure sp_plsql_block(p_deptno employees.department_id%type)
  is
  begin
    for i in (select employee_id,
                     last_name,
                     salary,
                     department_id
              from employees
              where department_id = p_deptno)
     loop
       dbms_output.put_line('Employee_id = '||i.employee_id||' Name = '||i.last_name||' Salary = '||i.salary||' Department_id = '||i.department_id);
     end loop;
  exception
    when others then
      dbms_output.put_line('Error in sp_plsql_block - > '||sqlerrm);
  end;
begin
  sp_plsql_block(30);
exception
  when others then
    dbms_output.put_line('Error in outer block - > '||sqlerrm);
end;
/
Calling:
exec sp_proc_inside_proc;
Output:
Employee_id = 114 Name = Raphaely Salary = 11000 Department_id = 30
Employee_id = 115 Name = Khoo Salary = 3100 Department_id = 30
Employee_id = 116 Name = Baida Salary = 2900 Department_id = 30
Employee_id = 117 Name = Tobias Salary = 2800 Department_id = 30
Employee_id = 118 Name = Himuro Salary = 2600 Department_id = 30
Employee_id = 119 Name = Colmenares Salary = 2500 Department_id = 30

PL/SQL procedure successfully completed.
Example 3:

Defining a Procedure inside a Function:

create or replace function sp_proc_inside_fun
  return sys_refcursor
is
  v_rec sys_refcursor;
  procedure sp_plsql_block(p_deptno employees.department_id%type,
                           p_refcur out sys_refcursor)
  is
    v_refcur sys_refcursor;
  begin
    open v_refcur
    for
    select employee_id,
           last_name,
           salary,
           department_id
    from employees
    where department_id = p_deptno;
    p_refcur := v_refcur;
  exception
    when others then
      dbms_output.put_line('Error in sp_plsql_block - > '||sqlerrm);
  end;
begin
  sp_plsql_block(30, v_rec);
  return v_rec;
exception
  when others then
    dbms_output.put_line('Error in sp_proc_inside_fun - > '||sqlerrm);
    return null;
end;
/
Calling:
declare
  v_record      sys_refcursor;
  cursor c1
  is
  select employee_id,
           last_name,
           salary,
           department_id
    from employees
    where 1 = 2;
    v_rec_disp c1%rowtype;
begin
  v_record := sp_proc_inside_fun;
  loop
    fetch v_record into v_rec_disp;
    exit when v_record%notfound;
    dbms_output.put_line('Employee_id = '||v_rec_disp.employee_id||' Name = '||v_rec_disp.last_name||' Salary = '||v_rec_disp.salary||' Department_id = '||v_rec_disp.department_id);
  end loop;
end;
/
Output:
Employee_id = 114 Name = Raphaely Salary = 11000 Department_id = 30
Employee_id = 115 Name = Khoo Salary = 3100 Department_id = 30
Employee_id = 116 Name = Baida Salary = 2900 Department_id = 30
Employee_id = 117 Name = Tobias Salary = 2800 Department_id = 30
Employee_id = 118 Name = Himuro Salary = 2600 Department_id = 30
Employee_id = 119 Name = Colmenares Salary = 2500 Department_id = 30


PL/SQL procedure successfully completed.

RELATED TOPICS:

Author

Related Post
  1. The Ultimate Guide to PLSQL Compiler
  2. Working with Stored Procedure Parameters
  3. How to compile invalid trigger in Oracle?