PL/SQL Пакеты

Определение Пакет – это объект схемы данных, объединяющий набор типов, объектов и подпрограмм PL/SQL.

Структура пакета

Создание спецификации CREATE [OR REPLACE] PACKAGE package_name [AUTHID {CURRENT_USER | DEFINER}] {IS | AS} [PRAGMA SERIALLY_REUSABLE;] [collection_type_definition...] [record_type_definition...] [subtype_definition...] [collection_declaration...] [constant_declaration...] [exception_declaration...] [object_declaration...] [record_declaration...] [variable_declaration...] [cursor_spec...] [function_spec...] [procedure_spec...] [call_spec...] [PRAGMA RESTRICT_REFERENCES(assertions)...] END [package_name];

Создание тела пакета [CREATE [OR REPLACE] PACKAGE BODY package_name {IS | AS} [PRAGMA SERIALLY_REUSABLE;] [collection_type_definition...] [record_type_definition...] [subtype_definition...] [collection_declaration...] [constant_declaration...] [exception_declaration...] [object_declaration...] [record_declaration...] [variable_declaration...] [cursor_body...] [function_spec...] [procedure_spec...] [call_spec...] [BEGIN sequence_of_statements] END [package_name];]

Создание пакета Объявленные переменные в спецификации: public Объявленные переменные в теле: private

Пример создания спецификации CREATE OR REPLACE PACKAGE emp_actions AS -- spec TYPE EmpRecTyp IS RECORD (emp_id INT, salary REAL); CURSOR desc_salary RETURN EmpRecTyp; PROCEDURE hire_employee ( ename VARCHAR2, job VARCHAR2, mgr NUMBER, sal NUMBER, comm NUMBER, deptno NUMBER); PROCEDURE fire_employee (emp_id NUMBER); END emp_actions;

Пример создания тела пакета CREATE OR REPLACE PACKAGE BODY emp_actions AS -- body CURSOR desc_salary RETURN EmpRecTyp IS SELECT empno, sal FROM emp ORDER BY sal DESC; PROCEDURE hire_employee ( ename VARCHAR2, job VARCHAR2, mgr NUMBER, sal NUMBER, comm NUMBER, deptno NUMBER) IS BEGIN INSERT INTO emp VALUES (empno_seq.NEXTVAL, ename, job, mgr, SYSDATE, sal, comm, deptno); END hire_employee; PROCEDURE fire_employee (emp_id NUMBER) IS BEGIN DELETE FROM emp WHERE empno = emp_id; END fire_employee; END emp_actions;

Пример работы с пакетом: begin dbms_output.put_line(my_package.my_var); my_package.employee_procedure; end;