Live ORACLE

 -- create the audit table to track changes
CREATE TABLE emp_audit (
 date_of_action DATE,
 user_id        VARCHAR2(20),
 package_name   VARCHAR2(30));

CREATE OR REPLACE PACKAGE emp_admin
AS
 -- Declare externally visible types, cursor, exception
 TYPE emprectyp IS RECORD(emp_id NUMBER,
                           sal NUMBER);

 CURSOR desc_salary RETURN emprectyp;

 invalid_salary  EXCEPTION;

 -- Declare externally callable subprograms
 FUNCTION hire_employee(last_name  VARCHAR2,first_name  VARCHAR2,email  VARCHAR2,phone_number  VARCHAR2,job_id  VARCHAR2,salary  NUMBER,commission_pct  NUMBER,manager_id  NUMBER,department_id  NUMBER) RETURN NUMBER;

 PROCEDURE fire_employee(emp_id  NUMBER); -- overloaded subprogram

 PROCEDURE fire_employee(emp_email  VARCHAR2); -- overloaded subprogram

 PROCEDURE raise_salary(emp_id  NUMBER,amount  NUMBER);

 FUNCTION nth_highest_salary(n  NUMBER) RETURN EMPRECTYP;
END emp_admin;
/

CREATE OR REPLACE PACKAGE BODY emp_admin
AS
 number_hired  NUMBER;  -- visible only in this package

 -- Fully define cursor specified in package
 CURSOR desc_salary RETURN emprectyp IS
   SELECT   employee_id,
            salary
   FROM     employees
   ORDER BY salary DESC;

 -- Fully define subprograms specified in package
 FUNCTION Hire_employee
      (last_name       VARCHAR2,
       first_name      VARCHAR2,
       email           VARCHAR2,
       phone_number    VARCHAR2,
       job_id          VARCHAR2,
       salary          NUMBER,
       commission_pct  NUMBER,
       manager_id      NUMBER,
       department_id   NUMBER)
 RETURN NUMBER
 IS
   new_emp_id  NUMBER;
 BEGIN
   SELECT employees_seq.nextval
   INTO   new_emp_id
   FROM   dual;
   INSERT INTO employees
   VALUES     (new_emp_id,
               last_name,
               first_name,
               email,
               phone_number,
               SYSDATE,
               job_id,
               salary,
               commission_pct,
               manager_id,
               department_id);
   number_hired := number_hired
                     + 1;
   dbms_output.Put_line('The number of employees hired is '
                        ||To_char(number_hired));
   RETURN new_emp_id;
 END hire_employee;

 PROCEDURE Fire_employee
      (emp_id  NUMBER)
 IS
 BEGIN
   DELETE FROM employees
   WHERE       employee_id = emp_id;
 END fire_employee;

 PROCEDURE Fire_employee
      (emp_email  VARCHAR2)
 IS
 BEGIN
   DELETE FROM employees
   WHERE       email = emp_email;
 END fire_employee;

 -- Define local function, available only inside package
 FUNCTION Sal_ok
      (jobid  VARCHAR2,
       sal    NUMBER)
 RETURN BOOLEAN
 IS
   min_sal  NUMBER;
   max_sal  NUMBER;
 BEGIN
   SELECT Min(salary),
          Max(salary)
   INTO   min_sal,max_sal
   FROM   employees
   WHERE  job_id = jobid;
   RETURN (sal >= min_sal)
          AND (sal <= max_sal);
 END sal_ok;

 PROCEDURE Raise_salary
      (emp_id  NUMBER,
       amount  NUMBER)
 IS
   sal    NUMBER(8,2);
   jobid  VARCHAR2(10);
 BEGIN
   SELECT job_id,
          salary
   INTO   jobid,sal
   FROM   employees
   WHERE  employee_id = emp_id;
   IF Sal_ok(jobid,sal
                     + amount) THEN
     UPDATE employees
     SET    salary = salary
                       + amount
     WHERE  employee_id = emp_id;
   ELSE
     RAISE invalid_salary;
   END IF;
 EXCEPTION  -- exception-handling part starts here

   WHEN invalid_salary THEN
     dbms_output.Put_line('The salary is out of the specified range.');
 END raise_salary;

 FUNCTION Nth_highest_salary
      (n  NUMBER)
 RETURN EMPRECTYP
 IS
   emp_rec  EMPRECTYP;
 BEGIN
   OPEN desc_salary;
   FOR i IN 1.. n LOOP
     FETCH desc_salary INTO emp_rec;
   END LOOP;
   CLOSE desc_salary;
   RETURN emp_rec;
 END nth_highest_salary;
BEGIN  -- initialization part starts here

INSERT INTO emp_audit
 VALUES     (SYSDATE,
             USER,
             'EMP_ADMIN');
 number_hired := 0;
END emp_admin;
/

-- calling the package procedures
DECLARE
 new_emp_id  NUMBER(6);
BEGIN
 new_emp_id := emp_admin.Hire_employee('Belden','Enrique','EBELDEN','555.111.2222',
                                       'ST_CLERK',2500,.1,101,110);
 dbms_output.Put_line('The new employee id is '
                      ||To_char(new_emp_id));
 emp_admin.Raise_salary(new_emp_id,100);
 dbms_output.Put_line('The 10th highest salary is '
                      ||To_char(emp_admin.Nth_highest_salary(10).sal)
                      ||', belonging to employee: '
                      ||To_char(emp_admin.Nth_highest_salary(10).emp_id));
 emp_admin.Fire_employee(new_emp_id);
-- you could also delete the newly added employee as follows:
--  emp_admin.fire_employee('EBELDEN');
END;
/ 

The number of employees hired is 1
The new employee id is 207
The 10th highest salary is 11500, belonging to employee: 168

PL/SQL procedure successfully completed.

Creating the emp_admin Package

CREATE PACKAGE emp_bonus
AS
 PROCEDURE calc_bonus(date_hired  employees.hire_date%TYPE);
END emp_bonus;
/

CREATE PACKAGE BODY emp_bonus
AS
 -- the following parameter declaration raises an exception
 -- because 'DATE' does not match employees.hire_date%TYPE
 -- PROCEDURE calc_bonus (date_hired DATE) IS
 -- the following is correct because there is an exact match
 PROCEDURE Calc_bonus
      (date_hired  employees.hire_date%TYPE)
 IS
 BEGIN
   dbms_output.Put_line('Employees hired on '
                        ||date_hired
                        ||' get bonus.');
 END;
END emp_bonus;
/  

Matching Package Specifications and Bodies

CREATE PACKAGE trans_data
AS -- bodiless package

TYPE timerec IS RECORD(minutes SMALLINT,
                        hours SMALLINT);

TYPE transrec IS RECORD(category VARCHAR2(10),
                         account INT,
                         amount REAL,
                         time_of TIMEREC);

minimum_balance  CONSTANT REAL := 10.00;

number_processed  INT;

insufficient_funds  EXCEPTION;
END trans_data;
/  

Package created.

A Simple Package Specification Without a Body

DECLARE
TYPE empcurtyp IS REF CURSOR;
c1  EMPCURTYP;
c2  EMPCURTYP;
PROCEDURE Get_emp_data
    (emp_cv1  IN OUT EMPCURTYP,
     emp_cv2  IN OUT EMPCURTYP)
IS
 emp_rec  employees%ROWTYPE;
BEGIN
 OPEN emp_cv1 FOR
   SELECT *
   FROM   employees;
 emp_cv2 := emp_cv1;
 FETCH emp_cv1 INTO emp_rec; -- fetches first row
 FETCH emp_cv1 INTO emp_rec; -- fetches second row
 FETCH emp_cv2 INTO emp_rec; -- fetches third row
 CLOSE emp_cv1;
 dbms_output.Put_line('The following raises an invalid cursor');
-- FETCH emp_cv2 INTO emp_rec; raises invalid cursor when get_emp_data is called
END;
BEGIN
Get_emp_data(c1,c2);
END;
/  

The following raises an invalid cursor

PL/SQL procedure successfully completed.

Aliasing from Assigning Cursor Variables to Same Work Area

 DECLARE
n  NUMBER := 10;
PROCEDURE Do_something
   (n1  IN NUMBER,
    n2  IN OUT NUMBER,
    n3  IN OUT NOCOPY NUMBER)
IS
BEGIN
n2 := 20;

dbms_output.Put_line(n1); -- prints 10

n3 := 30;

dbms_output.Put_line(n1); -- prints 30

END;
BEGIN
Do_something(n,n,n);

dbms_output.Put_line(n); -- prints 20

END;
/  

10
30
20

PL/SQL procedure successfully completed.

Aliasing Passing Same Parameter Multiple Times