Oracle PL/SQL Packages, Stored Procedures and Functions

A package is a schema object that groups logically related PL/SQL types, variables, and subprograms. Packages usually have two parts, a specification (spec) and a body. The specification is the interface to the package. It declares the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package. The body defines the queries for the cursors and the code for the subprograms.

A procedure is a subprogram that performs a specific action. You specify the name of the procedure, its parameters, its local variables, and the BEGIN-END block that contains its code and handles any exceptions.

A function is a subprogram that computes a value. Functions and procedures are structured alike, except that functions have a RETURN clause.

How To Create a PL/SQL Package?

To create package specs, use the PL/SQL statement CREATE PACKAGE. A CREATE PACKAGE BODY statement defines the package body.

CREATE OR REPLACE PACKAGE personnel IS

  FUNCTION count_employee
  return number;
 
  FUNCTION f_get_employee_by_id
  (
   i_emp_id number
  )
  RETURN SYS_REFCURSOR;

  PROCEDURE p_get_employee_by_id
  (
     i_emp_id   in number,
     emp_refcur out SYS_REFCURSOR
  );
 
end personnel;

/

CREATE OR REPLACE PACKAGE BODY personnel IS

  FUNCTION count_employee
  return number
  IS
   num number(4);
  BEGIN
   select count(*) into num from dev.employee;
    return num;
   
  END count_employee;
 
  FUNCTION f_get_employee_by_id
  (
   i_emp_id number
  )
  RETURN SYS_REFCURSOR
  IS
    emp_refcur      SYS_REFCURSOR;
  BEGIN
   
    OPEN emp_refcur FOR
    SELECT * from dev.employee where id = i_emp_id;
    RETURN emp_refcur;

  END f_get_employee_by_id;

  PROCEDURE p_get_employee_by_id
  (
     i_emp_id   in number,
     emp_refcur out SYS_REFCURSOR
  )
  AS
  BEGIN
   
    OPEN emp_refcur FOR
    SELECT * from dev.employee where id = i_emp_id;
   
  END p_get_employee_by_id;
 
END personnel;

/