Oracle PL/SQL Пакеты — Oracle PL/SQL •MySQL •MariaDB •SQL Server •SQLite

В этом учебном пособии вы узнаете, как создавать пакеты и тела пакетов в Oracle PL/SQL с синтаксисом и примерами.

Описание

В Oracle PL/SQL набор элементов: процедур, функций, определения типов; объявления переменных, констант можно объединить в пакет. После написания пакет PL/SQL компилируется, а затем сохраняется в базе данных Oracle, где его содержимое может использоваться многими приложениями.

Что такое пакет Oracle PL/SQL?

Синтаксис
Пример пакета PL/SQL

Преимущества пакетов Oracle PL/SQL
Понимание спецификации пакета.
Понимание тела пакета.
Некоторые примеры функций пакета.
Частные и публичные предметы в пакетах.
Перегрузка подпрограмм пакета.
Как пакет STANDARD определяет среду Oracle PL/SQL
Руководство по написанию пакетов

Что такое пакет Oracle PL/SQL?

Пакет Oracle PL/SQL - это объект схемы, который группирует логически связанные типы, элементы и подпрограммы. Пакеты обычно состоят из двух частей: спецификации и тела, хотя иногда тело не нужно. Спецификация - это интерфейс для ваших приложений.
В спицификации пакета объявляются типы, переменные, константы, исключения, курсоры и подпрограммы, доступные для использования.
Тело пакета полностью определяет курсоры и подпрограммы и реализует спецификацию.

Как показано на рисунке, вы можете думать о спецификации как о рабочем интерфейсе, а о теле - как о «черном ящике». Вы можете отлаживать, улучшать или изменять тело пакета без изменения интерфейса (спецификации) пакета.

Для создания пакетов используйте оператор CREATE PACKAGE.

Синтаксис

Синтаксис CREATE PACKAGE в Oracle 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];]

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 - спецификация вызова

Спецификация пакета содержит публичные объявления, которые видны вашему приложению. Вы должны объявить подпрограммы в конце спецификации после всех других элементов (кроме прагм, которые вызывают конкретную функцию; такие прагмы должны следовать спецификации функции).

Тело пакета содержит детали реализации и приватные объявления, которые скрыты от вашего приложения. За декларативной частью тела пакета следует необязательная часть инициализации, которая обычно содержит операторы, которые инициализируют переменные пакета.

Пример пакета Oracle PL/SQL

В приведенном ниже примере, вы определяете тип запись, курсор и две процедуры по трудоустройству. Обратите внимание, что процедура hire_employee использует последовательность базы данных empno_seq и функцию SYSDATE для вставки нового номера сотрудника и дату приема на работу соответственно.

CREATE OR REPLACE PACKAGE emp_actions AS  -- спецификация
   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  -- тело
   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;

CREATE OR REPLACE PACKAGE emp_actions AS -- спецификация

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 -- тело

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;

Только объявления в спецификации пакета видны и доступны для приложений. Детали реализации в теле пакета скрыты и недоступны. Таким образом, вы можете изменить тело (реализацию) без перекомпиляции вызывающих программ.

Преимущества пакетов Oracle PL/SQL

Модульность

Пакеты позволяют инкапсулировать логически связанные типы, элементы и подпрограммы в именованный модуль PL/SQL. Каждый пакет прост для понимания, а интерфейсы между пакетами просты, понятны и хорошо определены. Это помогает разработке приложений.

Более простой дизайн приложений

При разработке приложения все, что вам первоначально нужно, это информация об интерфейсе в спецификации пакета. Вы можете кодировать и компилировать спецификацию без ее тела. Затем сохраненные подпрограммы, которые ссылаются на пакет, также могут быть скомпилированы.

Сокрытие информации

С помощью пакетов вы можете указать, какие типы, элементы и подпрограммы являются общедоступными (видимыми и доступными) или приватными (скрытыми и недоступными). Например, если пакет содержит четыре подпрограммы, три могут быть открытыми и одна закрытая. Пакет скрывает реализацию закрытой подпрограммы, поэтому при изменении реализации затрагивается только пакет (не ваше приложение). Это упрощает обслуживание и улучшение. Кроме того, скрывая детали реализации от пользователей, вы защищаете целостность пакета.

Добавленная функциональность

Пакетные общедоступные переменные и курсоры сохраняются в течение сеанса. Таким образом, они могут быть общими для всех подпрограмм, которые выполняются в среде. Кроме того, они позволяют поддерживать данные между транзакциями, не сохраняя их в базе данных.

Лучшая производительность

Когда вы вызываете пакетную подпрограмму в первый раз, весь пакет загружается в память. Поэтому последующие вызовы связанных подпрограмм в пакете не требуют дискового ввода-вывода. Кроме того, пакеты останавливают каскадные зависимости и тем самым избегают ненужной перекомпиляции. Например, если вы измените реализацию пакетной функции, Oracle не нужно перекомпилировать вызывающие подпрограммы, поскольку они не зависят от тела пакета.

Понимание спецификации пакета.

Спецификация пакета содержит публичные объявления элементов. Область этих объявлений является локальной для вашей схемы базы данных и глобальной для пакета. Итак, объявленные элементы доступны из вашего приложения и из любой точки пакета.

В спецификации перечислены элементы пакета, доступные приложениям. Вся информация, необходимая вашему приложению для использования элементов, содержится в спецификации. Например, следующее объявление показывает, что функция с именем fac принимает один аргумент типа INTEGER и возвращает значение типа INTEGER:

FUNCTION fac (n INTEGER) RETURN INTEGER;  -- returns n!

1	FUNCTION fac (n INTEGER) RETURN INTEGER; -- returns n!

Это вся информация, необходимая для вызова функции. Вам не нужно рассматривать его базовую реализацию (например, итеративную или рекурсивную).

Только подпрограммы и курсоры имеют базовую реализацию. Таким образом, если спецификация объявляет только типы, константы, переменные, исключения и спецификации вызовов, то тело пакета не требуется. Рассмотрим следующий пакет без тела:

CREATE PACKAGE trans_data AS  -- bodiless package
   TYPE TimeRec IS RECORD (
      minutes SMALLINT,
      hours   SMALLINT);
   TYPE TransRec IS RECORD (
      category VARCHAR2,
      account  INT,
      amount   REAL,
      time_of  TimeRec);
   minimum_balance    CONSTANT REAL := 10.00;
   number_processed   INT;
   insufficient_funds EXCEPTION;
END trans_data;

CREATE PACKAGE trans_data AS -- bodiless package

TYPE TimeRec IS RECORD (

minutes SMALLINT,

hours SMALLINT);

TYPE TransRec IS RECORD (

category VARCHAR2,

account INT,

amount REAL,

time_of TimeRec);

minimum_balance CONSTANT REAL := 10.00;

number_processed INT;

insufficient_funds EXCEPTION;

END trans_data;

Пакет trans_data не нуждается в теле, потому что типы, константы, переменные и исключения не имеют базовой реализации. Такие пакеты позволяют вам определять глобальные переменные - используемые подпрограммами и триггерами базы данных - которые сохраняются в течение сеанса.

Ссылка на содержание пакета

Чтобы ссылаться на типы, элементы, подпрограммы и спецификации вызовов, объявленные в спецификации пакета, используйте точечную нотацию следующим образом:

package_name.type_name

package_name.item_name

package_name.subprogram_name

package_name.call_spec_name

Вы можете ссылаться на содержимое пакета с помощью триггеров базы данных, хранимых подпрограмм, прикладных программ 3GL и различных инструментов Oracle. Например, вы можете вызвать процедуру пакета hire_employee из PL/SQL Developer следующим образом:

BEGIN
  emp_actions.hire_employee('VLADIMIR', 'MEDIC', ...);
END;

BEGIN

emp_actions.hire_employee('VLADIMIR', 'MEDIC', ...);

END;

Ограничения

Вы не можете ссылаться на удаленные переменные пакета прямо или косвенно. Например, вы не можете вызвать следующую процедуру удаленно, потому что она ссылается на переменную пакета, когда параметр инициализируется:

CREATE PACKAGE random AS
   seed NUMBER;
   PROCEDURE initialize (starter IN NUMBER := seed, ...);

CREATE PACKAGE random AS

seed NUMBER;

PROCEDURE initialize (starter IN NUMBER := seed, ...);

Кроме того, внутри пакета вы не можете ссылаться на переменные хоста.

Понимание тела пакета

Тело пакета реализует спецификацию пакета. То есть тело пакета содержит реализацию каждого курсора и подпрограммы, объявленных в спецификации пакета. Помните, что подпрограммы, определенные в теле пакета, доступны вне пакета, только если их спецификации также указаны в спецификации пакета.

Чтобы сопоставить спецификации и тела подпрограмм, PL/SQL выполняет сравнение их заголовков по определениям. Таким образом, за исключением пробелов, заголовки должны соответствовать слово в слово. В противном случае PL/SQL вызывает исключение, как показано в следующем примере:

CREATE PACKAGE emp_actions AS
   ...
   PROCEDURE calc_bonus (date_hired emp.hiredate%TYPE, ...);
END emp_actions;
 
CREATE PACKAGE BODY emp_actions AS
   ...                           
   PROCEDURE calc_bonus (date_hired DATE, ...) IS 
     /* объявление параметра вызывает исключение, потому что 'DATE'
      не соответствует 'emp.hiredate%TYPE' слово в слово
     */
   BEGIN ... END;
END emp_actions;

CREATE PACKAGE emp_actions AS

...

PROCEDURE calc_bonus (date_hired emp.hiredate%TYPE, ...);

END emp_actions;

CREATE PACKAGE BODY emp_actions AS

...

PROCEDURE calc_bonus (date_hired DATE, ...) IS

/* объявление параметра вызывает исключение, потому что 'DATE'

не соответствует 'emp.hiredate%TYPE' слово в слово

BEGIN ... END;

END emp_actions;

Тело пакета также может содержать частные объявления, которые определяют типы и элементы, необходимые для внутренней работы пакета. Область этих объявлений является локальной для тела пакета. Поэтому объявленные типы и элементы недоступны, кроме как внутри тела пакета. В отличие от спецификации пакета, декларативная часть тела пакета может содержать тела подпрограммы.

За декларативной частью тела пакета следует дополнительная часть инициализации, которая обычно содержит операторы, которые инициализируют некоторые переменные, ранее объявленные в пакете.

Часть инициализации пакета играет второстепенную роль, потому что, в отличие от подпрограмм, пакет не может быть вызван или передан параметрами. В результате часть инициализации пакета запускается только один раз, при первом обращении к пакету.

Помните, если спецификация пакета объявляет только типы, константы, переменные, исключения и спецификации вызовов, тело пакета не требуется. Однако тело все еще можно использовать для инициализации элементов, объявленных в спецификации пакета.

Некоторые примеры функций пакета

Рассмотрим пакет с именем emp_actions ниже. Спецификация пакета объявляет следующие типы, элементы и подпрограммы:

Types EmpRecTyp and DeptRecTyp
Cursor desc_salary
Exception invalid_salary
Functions hire_employee and nth_highest_salary
Procedures fire_employee and raise_salary

Types EmpRecTyp and DeptRecTyp

Cursor desc_salary

Exception invalid_salary

Functions hire_employee and nth_highest_salary

Procedures fire_employee and raise_salary

После написания пакета вы можете разрабатывать приложения, которые ссылаются на его типы, вызывать его подпрограммы, использовать его курсор и вызывать его исключение. Когда вы создаете пакет, он сохраняется в базе данных Oracle для общего пользования.

CREATE PACKAGE emp_actions AS
   /* Объявление внешних видимых: типов, курсора, исключения. */
   TYPE EmpRecTyp IS RECORD (emp_id INT, salary REAL);
   TYPE DeptRecTyp IS RECORD (dept_id INT, location VARCHAR2);
   CURSOR desc_salary RETURN EmpRecTyp;
   invalid_salary EXCEPTION;
 
   /* Объявление внешних вызываемых подпрограмм. */
   FUNCTION hire_employee (
      ename  VARCHAR2,
      job    VARCHAR2,
      mgr    REAL,
      sal    REAL,
      comm   REAL,
      deptno REAL) RETURN INT;
   PROCEDURE fire_employee (emp_id INT);
   PROCEDURE raise_salary (emp_id INT, grade INT, amount REAL);
   FUNCTION nth_highest_salary (n INT) RETURN EmpRecTyp;
END emp_actions;
 
CREATE PACKAGE BODY emp_actions AS
   number_hired INT;  -- видна только в этом пакете
 
   /* Полностью определенный курсор, указанный в пакете. */
   CURSOR desc_salary RETURN EmpRecTyp IS
      SELECT empno, sal FROM emp ORDER BY sal DESC;
 
   /* Полностью определенная подпрограмма, указанная в пакете. */
   FUNCTION hire_employee (
      ename  VARCHAR2,
      job    VARCHAR2,
      mgr    REAL,
      sal    REAL,
      comm   REAL,
      deptno REAL) RETURN INT IS
      new_empno INT;
   BEGIN
      SELECT empno_seq.NEXTVAL INTO new_empno FROM dual;
      INSERT INTO emp VALUES (new_empno, ename, job,
         mgr, SYSDATE, sal, comm, deptno);
      number_hired := number_hired + 1;
      RETURN new_empno;
   END hire_employee;
 
   PROCEDURE fire_employee (emp_id INT) IS
   BEGIN
      DELETE FROM emp WHERE empno = emp_id;
   END fire_employee;

   /* Определенная локальная функция, доступна только внутри пакета. */
   FUNCTION sal_ok (rank INT, salary REAL) RETURN BOOLEAN IS
      min_sal REAL;
      max_sal REAL;
   BEGIN
      SELECT losal, hisal INTO min_sal, max_sal FROM salgrade
         WHERE grade = rank;
      RETURN (salary >= min_sal) AND (salary <= max_sal);
   END sal_ok;

   PROCEDURE raise_salary (emp_id INT, grade INT, amount REAL) IS
      salary REAL;
   BEGIN
      SELECT sal INTO salary FROM emp WHERE empno = emp_id;
      IF sal_ok(grade, salary + amount) THEN
         UPDATE emp SET sal = sal + amount WHERE empno = emp_id;
      ELSE
         RAISE invalid_salary;
      END IF;
   END raise_salary;
 
   FUNCTION nth_highest_salary (n INT) 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  -- часть инициализации начинается здесь
   INSERT INTO emp_audit VALUES (SYSDATE, USER, 'EMP_ACTIONS');
   number_hired := 0;
END emp_actions;

CREATE PACKAGE emp_actions AS

/* Объявление внешних видимых: типов, курсора, исключения. */

TYPE EmpRecTyp IS RECORD (emp_id INT, salary REAL);

TYPE DeptRecTyp IS RECORD (dept_id INT, location VARCHAR2);

CURSOR desc_salary RETURN EmpRecTyp;

invalid_salary EXCEPTION;

/* Объявление внешних вызываемых подпрограмм. */

FUNCTION hire_employee (

ename VARCHAR2,

job VARCHAR2,

mgr REAL,

sal REAL,

comm REAL,

deptno REAL) RETURN INT;

PROCEDURE fire_employee (emp_id INT);

PROCEDURE raise_salary (emp_id INT, grade INT, amount REAL);

FUNCTION nth_highest_salary (n INT) RETURN EmpRecTyp;

END emp_actions;

CREATE PACKAGE BODY emp_actions AS

number_hired INT; -- видна только в этом пакете

/* Полностью определенный курсор, указанный в пакете. */

CURSOR desc_salary RETURN EmpRecTyp IS

SELECT empno, sal FROM emp ORDER BY sal DESC;

/* Полностью определенная подпрограмма, указанная в пакете. */

FUNCTION hire_employee (

ename VARCHAR2,

job VARCHAR2,

mgr REAL,

sal REAL,

comm REAL,

deptno REAL) RETURN INT IS

new_empno INT;

BEGIN

SELECT empno_seq.NEXTVAL INTO new_empno FROM dual;

INSERT INTO emp VALUES (new_empno, ename, job,

mgr, SYSDATE, sal, comm, deptno);

number_hired := number_hired + 1;

RETURN new_empno;

END hire_employee;

PROCEDURE fire_employee (emp_id INT) IS

BEGIN

DELETE FROM emp WHERE empno = emp_id;

END fire_employee;

/* Определенная локальная функция, доступна только внутри пакета. */

FUNCTION sal_ok (rank INT, salary REAL) RETURN BOOLEAN IS

min_sal REAL;

max_sal REAL;

BEGIN

SELECT losal, hisal INTO min_sal, max_sal FROM salgrade

WHERE grade = rank;

RETURN (salary >= min_sal) AND (salary <= max_sal);

END sal_ok;

PROCEDURE raise_salary (emp_id INT, grade INT, amount REAL) IS

salary REAL;

BEGIN

SELECT sal INTO salary FROM emp WHERE empno = emp_id;

IF sal_ok(grade, salary + amount) THEN

UPDATE emp SET sal = sal + amount WHERE empno = emp_id;

ELSE

RAISE invalid_salary;

END IF;

END raise_salary;

FUNCTION nth_highest_salary (n INT) 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 -- часть инициализации начинается здесь

INSERT INTO emp_audit VALUES (SYSDATE, USER, 'EMP_ACTIONS');

number_hired := 0;

END emp_actions;

Помните, что часть инициализации пакета запускается только один раз, когда вы впервые ссылаетесь на пакет. Итак, в последнем примере в таблицу базы данных emp_audit вставляется только одна строка. Аналогично, переменная number_hired инициализируется только один раз.

Каждый раз, когда вызывается процедура hire_employee, переменная number_hired обновляется. Тем не менее, число, сохраняемое в number_hired, зависит от конкретной сессии. То есть количество отражает количество новых сотрудников, обработанных одним пользователем, а не количество, обработанное всеми пользователями.

Пример пакета банковских операций

Следующий пример - это ваш пакет некоторых типичных банковских операций. Предположим, что дебетовые и кредитные транзакции вводятся в нерабочее время через банкоматы, а затем применяются к счетам на следующее утро.

CREATE PACKAGE bank_transactions AS
   /* Объявление внешней видимой константы. */
   minimum_balance  CONSTANT REAL := 100.00;
   /* Объявление внешней вызываемой процедуры. */
   PROCEDURE apply_transactions;
   PROCEDURE enter_transaction (
      acct   INT,
      kind   CHAR,
      amount REAL);
END bank_transactions;

CREATE PACKAGE BODY bank_transactions AS
   /* Объявите глобальной переменной для хранения статуса транзакции. */
   new_status  VARCHAR2(70) := 'Unknown';

   /* Используйте предварительные объявления, потому что apply_transactions
       вызывает credit_account и debit_account, которые еще не объявлены, когда вызовы сделаны. */
   PROCEDURE credit_account (acct INT, credit REAL);
   PROCEDURE debit_account (acct INT, debit REAL);

   /* Полностью определить процедуры, указанные в пакете. */
   PROCEDURE apply_transactions IS
   
   /* Применить ожидающие транзакции в таблице transactions
       к таблице accounts. Используйте курсор для выборки строк. */
      CURSOR trans_cursor IS
         SELECT acct_id, kind, amount FROM transactions
            WHERE status = 'Pending'
            ORDER BY time_tag
            FOR UPDATE OF status;  -- для блокировки строки
   BEGIN
      FOR trans IN trans_cursor LOOP
         IF trans.kind = 'D' THEN
            debit_account(trans.acct_id, trans.amount);
         ELSIF trans.kind = 'C' THEN
            credit_account(trans.acct_id, trans.amount);
         ELSE
            new_status := 'Rejected';
         END IF;
         UPDATE transactions SET status = new_status
            WHERE CURRENT OF trans_cursor;
      END LOOP;
   END apply_transactions;

   PROCEDURE enter_transaction (
   /* Добавить транзакцию в таблицу transactions. */
      acct   INT,
      kind   CHAR,
      amount REAL) IS
   BEGIN
      INSERT INTO transactions
         VALUES (acct, kind, amount, 'Pending', SYSDATE);
   END enter_transaction;

   /* Определите локальные процедуры, доступные только в пакете. */
   PROCEDURE do_journal_entry (
   /* Запись транзакции в журнале. */
      acct    INT,
      kind    CHAR,
      new_bal REAL) IS
   BEGIN
      INSERT INTO journal
         VALUES (acct, kind, new_bal, sysdate);
      IF kind = 'D' THEN
         new_status := 'Debit applied';
      ELSE
         new_status := 'Credit applied';
      END IF;
   END do_journal_entry;

   PROCEDURE credit_account (acct INT, credit REAL) IS
   /* Кредитный счет, кроме плохого номер счета (который не существует). */
      old_balance REAL;
      new_balance REAL;
   BEGIN
      SELECT balance INTO old_balance FROM accounts
         WHERE acct_id = acct
         FOR UPDATE OF balance;  -- для блокировки строки
      new_balance := old_balance + credit;
      UPDATE accounts SET balance = new_balance
         WHERE acct_id = acct;
      do_journal_entry(acct, 'C', new_balance);
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         new_status := 'Bad account number';
      WHEN OTHERS THEN
         new_status := SUBSTR(SQLERRM,1,70);
   END credit_account;

   PROCEDURE debit_account (acct INT, debit REAL) IS
   /* Дебетовый счет, если номер счета не является плохим или
       на счету недостаточно средств. */
      old_balance REAL;
      new_balance REAL;
      insufficient_funds EXCEPTION;
   BEGIN
      SELECT balance INTO old_balance FROM accounts
         WHERE acct_id = acct
         FOR UPDATE OF balance;  -- для блокировки строки
      new_balance := old_balance - debit;
      IF new_balance >= minimum_balance THEN
         UPDATE accounts SET balance = new_balance
            WHERE acct_id = acct;
         do_journal_entry(acct, 'D', new_balance);
      ELSE
         RAISE insufficient_funds;
      END IF;
   EXCEPTION
      WHEN NO_DATA_FOUND THEN
         new_status := 'Bad account number';
      WHEN insufficient_funds THEN
         new_status := 'Insufficient funds';
      WHEN OTHERS THEN
         new_status := SUBSTR(SQLERRM,1,70);
   END debit_account;
END bank_transactions;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

CREATE PACKAGE bank_transactions AS

/* Объявление внешней видимой константы. */

minimum_balance CONSTANT REAL := 100.00;

/* Объявление внешней вызываемой процедуры. */

PROCEDURE apply_transactions;

PROCEDURE enter_transaction (

acct INT,

kind CHAR,

amount REAL);

END bank_transactions;

CREATE PACKAGE BODY bank_transactions AS

/* Объявите глобальной переменной для хранения статуса транзакции. */

new_status VARCHAR2(70) := 'Unknown';

/* Используйте предварительные объявления, потому что apply_transactions

вызывает credit_account и debit_account, которые еще не объявлены, когда вызовы сделаны. */

PROCEDURE credit_account (acct INT, credit REAL);

PROCEDURE debit_account (acct INT, debit REAL);

/* Полностью определить процедуры, указанные в пакете. */

PROCEDURE apply_transactions IS

/* Применить ожидающие транзакции в таблице transactions

к таблице accounts. Используйте курсор для выборки строк. */

CURSOR trans_cursor IS

SELECT acct_id, kind, amount FROM transactions

WHERE status = 'Pending'

ORDER BY time_tag

FOR UPDATE OF status; -- для блокировки строки

BEGIN

FOR trans IN trans_cursor LOOP

IF trans.kind = 'D' THEN

debit_account(trans.acct_id, trans.amount);

ELSIF trans.kind = 'C' THEN

credit_account(trans.acct_id, trans.amount);

ELSE

new_status := 'Rejected';

END IF;

UPDATE transactions SET status = new_status

WHERE CURRENT OF trans_cursor;

END LOOP;

END apply_transactions;

PROCEDURE enter_transaction (

/* Добавить транзакцию в таблицу transactions. */

acct INT,

kind CHAR,

amount REAL) IS

BEGIN

INSERT INTO transactions

VALUES (acct, kind, amount, 'Pending', SYSDATE);

END enter_transaction;

/* Определите локальные процедуры, доступные только в пакете. */

PROCEDURE do_journal_entry (

/* Запись транзакции в журнале. */

acct INT,

kind CHAR,

new_bal REAL) IS

BEGIN

INSERT INTO journal

VALUES (acct, kind, new_bal, sysdate);

IF kind = 'D' THEN

new_status := 'Debit applied';

ELSE

new_status := 'Credit applied';

END IF;

END do_journal_entry;

PROCEDURE credit_account (acct INT, credit REAL) IS

/* Кредитный счет, кроме плохого номер счета (который не существует). */

old_balance REAL;

new_balance REAL;

BEGIN

SELECT balance INTO old_balance FROM accounts

WHERE acct_id = acct

FOR UPDATE OF balance; -- для блокировки строки

new_balance := old_balance + credit;

UPDATE accounts SET balance = new_balance

WHERE acct_id = acct;

do_journal_entry(acct, 'C', new_balance);

EXCEPTION

WHEN NO_DATA_FOUND THEN

new_status := 'Bad account number';

WHEN OTHERS THEN

new_status := SUBSTR(SQLERRM,1,70);

END credit_account;

PROCEDURE debit_account (acct INT, debit REAL) IS

/* Дебетовый счет, если номер счета не является плохим или

на счету недостаточно средств. */

old_balance REAL;

new_balance REAL;

insufficient_funds EXCEPTION;

BEGIN

SELECT balance INTO old_balance FROM accounts

WHERE acct_id = acct

FOR UPDATE OF balance; -- для блокировки строки

new_balance := old_balance - debit;

IF new_balance >= minimum_balance THEN

UPDATE accounts SET balance = new_balance

WHERE acct_id = acct;

do_journal_entry(acct, 'D', new_balance);

ELSE

RAISE insufficient_funds;

END IF;

EXCEPTION

WHEN NO_DATA_FOUND THEN

new_status := 'Bad account number';

WHEN insufficient_funds THEN

new_status := 'Insufficient funds';

WHEN OTHERS THEN

new_status := SUBSTR(SQLERRM,1,70);

END debit_account;

END bank_transactions;

В этом примере пакета часть инициализации не используется.

Приватные и публичные элементы в пакетах

Посмотрите еще раз на пакет emp_actions. Тело пакета объявляет переменную с именем number_hired, которая инициализируется 0 - нулем. В отличие от элементов, объявленных в спецификации emp_actions, элементы, объявленные в теле, ограничены для использования в пакете. Поэтому код PL/SQL вне пакета не может ссылаться на переменную number_hired. Такие элементы называются приватными.

Однако элементы, объявленные в спецификации emp_actions, такие как исключение invalid_salary, видны вне пакета. Поэтому любой код PL/SQL может ссылаться на исключение invalid_salary. Такие элементы называются публичными.

Когда вы должны поддерживать элементы в течение сеанса или между транзакциями, поместите их в декларативную часть тела пакета. Например, значение number_hired сохраняется между вызовами hire_employee в одном и том же сеансе. Значение теряется при завершении сеанса.

Если вы также должны сделать элементы публичными, поместите их в спецификацию пакета. Например, константа minimum_balance, объявленная в спецификации пакета bank_transactions, доступна для общего пользования.

Перегрузка подпрограмм пакета

PL/SQL позволяет двум или более подпрограммам пакета иметь одинаковое имя. Эта опция полезна, когда вы хотите, чтобы подпрограмма принимала аналогичные наборы параметров, которые имеют разные типы данных. Например, следующий пакет определяет две процедуры с именем journalize:

CREATE PACKAGE journal_entries AS
   ...
   PROCEDURE journalize (amount REAL, trans_date VARCHAR2);
   PROCEDURE journalize (amount REAL, trans_date INT);
END journal_entries;

CREATE PACKAGE BODY journal_entries AS
   ...
   PROCEDURE journalize (amount REAL, trans_date VARCHAR2) IS
   BEGIN
      INSERT INTO journal
         VALUES (amount, TO_DATE(trans_date, 'DD-MON-YYYY'));
   END journalize;

   PROCEDURE journalize (amount REAL, trans_date INT) IS
   BEGIN
      INSERT INTO journal
         VALUES (amount, TO_DATE(trans_date, 'J'));
   END journalize;
END journal_entries;

CREATE PACKAGE journal_entries AS

...

PROCEDURE journalize (amount REAL, trans_date VARCHAR2);

PROCEDURE journalize (amount REAL, trans_date INT);

END journal_entries;

CREATE PACKAGE BODY journal_entries AS

...

PROCEDURE journalize (amount REAL, trans_date VARCHAR2) IS

BEGIN

INSERT INTO journal

VALUES (amount, TO_DATE(trans_date, 'DD-MON-YYYY'));

END journalize;

PROCEDURE journalize (amount REAL, trans_date INT) IS

BEGIN

INSERT INTO journal

VALUES (amount, TO_DATE(trans_date, 'J'));

END journalize;

END journal_entries;

Первая процедура принимает trans_date как символьную строку, в то время как вторая процедура принимает ее как число (юлианский день). Каждая процедура обрабатывает данные соответствующим образом.

Как пакет STANDARD определяет среду Oracle PL/SQL

Пакет с именем STANDARD определяет среду PL/SQL. Спецификация пакета глобально объявляет типы, исключения и подпрограммы, которые автоматически доступны для программ PL/SQL. Например, пакет STANDARD объявляет функцию ABS, которая возвращает абсолютное значение своего аргумента, следующим образом:

FUNCTION ABS (n NUMBER) RETURN NUMBER;

1	FUNCTION ABS (n NUMBER) RETURN NUMBER;

Содержимое пакета STANDARD непосредственно видно приложениям. Вам не нужно указывать ссылки на его содержимое, добавляя префикс имени пакета. Например, вы можете вызывать ABS из триггера базы данных, хранимой подпрограммы, инструмента Oracle или приложения 3GL следующим образом:

abs_diff := ABS(x - y);

1	abs_diff := ABS(x - y);

Если вы повторно объявите ABS в программе PL/SQL, ваша локальная декларация переопределяет глобальную декларацию. Однако вы все равно можете вызвать встроенную функцию, указав ссылку на ABS следующим образом:

abs_diff := STANDARD.ABS(x - y);

1	abs_diff := STANDARD.ABS(x - y);

Большинство встроенных функций перегружены. Например, пакет STANDARD содержит следующие объявления:

FUNCTION TO_CHAR (right DATE) RETURN VARCHAR2;
FUNCTION TO_CHAR (left NUMBER) RETURN VARCHAR2;
FUNCTION TO_CHAR (left DATE, right VARCHAR2) RETURN VARCHAR2;
FUNCTION TO_CHAR (left NUMBER, right VARCHAR2) RETURN VARCHAR2;

FUNCTION TO_CHAR (right DATE) RETURN VARCHAR2;

FUNCTION TO_CHAR (left NUMBER) RETURN VARCHAR2;

FUNCTION TO_CHAR (left DATE, right VARCHAR2) RETURN VARCHAR2;

FUNCTION TO_CHAR (left NUMBER, right VARCHAR2) RETURN VARCHAR2;

PL/SQL разрешает вызов TO_CHAR путем сопоставления числа и типов данных формальных и фактических параметров.

Руководство по написанию пакетов

При написании пакетов держите их как можно более общими, чтобы их можно было использовать в будущих приложениях. Избегайте написания пакетов, которые дублируют некоторые функции, уже предоставленные Oracle.

Спецификации пакета отражают дизайн вашего приложения. Определяйте их перед телом пакета. Поместите в спецификацию только те типы, элементы и подпрограммы, которые должны быть видны пользователям пакета. Таким образом, другие разработчики не могут неправильно использовать пакет, основывая свой код на нерелевантных деталях реализации.

Чтобы уменьшить необходимость перекомпиляции при изменении кода, поместите как можно меньше элементов в спецификацию пакета. Изменения в теле пакета не требуют, чтобы Oracle перекомпилировал зависимые процедуры. Однако изменения в спецификации пакета требуют, чтобы Oracle перекомпилировал каждую сохраненную подпрограмму, которая ссылается на пакет.

Оригинал статьи.