While working
on an ERP implementation project, there is a very high chance that you might
require to create custom pl/sql packages to meet business requirements. Through
the below (and subsequent posts) I’d try and explain how to create a simple
pl/sql package
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. Spec 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.
Creating a Package Spec:
The
following statement creates the specification of a package - emp_mgmt
CREATE OR REPLACE PACKAGE emp_mgmt AS
FUNCTION hire ( last_name
VARCHAR2,
job_id VARCHAR2,
manager_id
NUMBER, salary NUMBER,
commission_pct
NUMBER,
department_id NUMBER
)
RETURN NUMBER;
FUNCTION create_dept ( department_id NUMBER,
location_id NUMBER
)
RETURN NUMBER;
PROCEDURE remove_emp(employee_id NUMBER);
PROCEDURE remove_dept(department_id NUMBER);
PROCEDURE increase_sal(employee_id NUMBER,
salary_incr NUMBER);
PROCEDURE increase_comm(employee_id NUMBER,
comm_incr NUMBER);
no_comm EXCEPTION;
no_sal
EXCEPTION;
END emp_mgmt;
/
The specification for
the emp_mgmt package declares the following public program objects:
- The functions hire and create_dept
- The procedures remove_emp, remove_dept, increase_sal, and increase_comm
- The exceptions no_comm and no_sal
All of these objects are available to users who have access
to the package. After creating the package, you can develop applications that
call any of these public procedures or functions or raise any of the public
exceptions of the package.
Before you can call this package's procedures and functions, you
must define these procedures and functions in the package body.
Creating a Package Body:
CREATE OR REPLACE PACKAGE BODY emp_mgmt AS
tot_emps NUMBER;
tot_depts NUMBER;
FUNCTION hire
( last_name VARCHAR2,
job_id
VARCHAR2,
manager_id NUMBER,
salary
NUMBER,
commission_pct NUMBER,
department_id
NUMBER
)
RETURN
NUMBER IS new_empno NUMBER;
BEGIN
SELECT
employees_seq.NEXTVAL
INTO new_empno
FROM DUAL;
INSERT
INTO employees
VALUES (new_empno, 'First', 'Last','first.example@oracle.com',
'(415)555-0100','18-JUN-02','IT_PROG',90000000,00,
100,110);
tot_emps := tot_emps + 1;
RETURN(new_empno);
END;
FUNCTION create_dept(department_id NUMBER,
location_id NUMBER)
RETURN
NUMBER IS
new_deptno NUMBER;
BEGIN
SELECT departments_seq.NEXTVAL
INTO new_deptno
FROM dual;
INSERT INTO departments
VALUES (new_deptno, 'department name', 100, 1700);
tot_depts := tot_depts + 1;
RETURN(new_deptno);
END;
PROCEDURE remove_emp (employee_id NUMBER) IS
BEGIN
DELETE FROM employees
WHERE employees.employee_id = remove_emp.employee_id;
tot_emps := tot_emps - 1;
END;
PROCEDURE remove_dept(department_id NUMBER) IS
BEGIN
DELETE FROM departments
WHERE departments.department_id = remove_dept.department_id;
tot_depts := tot_depts - 1;
SELECT COUNT(*) INTO tot_emps FROM employees;
END;
PROCEDURE increase_sal(employee_id NUMBER,
salary_incr NUMBER) IS
curr_sal NUMBER;
BEGIN
SELECT salary INTO curr_sal FROM employees
WHERE employees.employee_id = increase_sal.employee_id;
IF
curr_sal IS NULL
THEN RAISE no_sal;
ELSE
UPDATE employees
SET salary = salary + salary_incr
WHERE employee_id = employee_id;
END
IF;
END;
PROCEDURE increase_comm(employee_id NUMBER,
comm_incr NUMBER) IS
curr_comm NUMBER;
BEGIN
SELECT
commission_pct
INTO curr_comm
FROM employees
WHERE employees.employee_id = increase_comm.employee_id;
IF
curr_comm IS NULL
THEN RAISE no_comm;
ELSE
UPDATE employees
SET commission_pct = commission_pct + comm_incr;
END
IF;
END;
END emp_mgmt;
/
The package body defines the public program objects declared in
the package specification:
- The functions hire and create_dept
- The procedures remove_emp, remove_dept, increase_sal, and increase_comm
These objects are defined in
the package body, so you can change their definitions without causing the
database to invalidate dependent schema objects. For example, if you subsequently
change the definition of hire, then the database need not recompile increase_all_comms before
executing it.
The package body in this
example also declares private program objects, the variables tot_emps and tot_depts.
These objects are declared in the package body rather than the package
specification, so they are accessible to other objects in the package, but they
are not accessible outside the package. For example, you cannot develop an
application that explicitly changes the value of the variable tot_depts. However, the function create_dept is
part of the package, so create_dept can change the value of tot_depts.
Some guidelines for Writing PL/SQL Packages
When
writing packages, keep them general so they can be reused in future
applications. Become familiar with the packages that Oracle supplies, and avoid
writing packages that duplicate features already provided by Oracle.
Design and define package specs before the
package bodies. Place in a spec only
those things that must be visible to invoking programs. That way, other
developers cannot build unsafe dependencies on your implementation details.
To
reduce the need for recompiling when code is changed, place as few items as
possible in a package spec. Changes to a package body do not require
recompiling invoking subprograms. Changes to a package spec require the
database to recompile every stored subprogram that references the package.
No comments:
Post a Comment