4. Функции, процедуры, пакеты

1. Процедуры

Синтаксис:

CREATE [OR REPLACE] PROCEDURE
  procedureName [(
    argName1 [IN | OUT | IN OUT] type1 [:= defaultValue],
    argName2 [IN | OUT | IN OUT] type2 [:= defaultValue],
    -- аргументы
  )] IS | AS
  -- локальные переменные и процедуры/функции
  -- процедуры и функции должны быть написаны в самом низу
BEGIN
  -- код подпрограммы
END [procedureName];

Вместо := можно использовать и DEFAULT.

Режимы IN | OUT | IN OUT:

Пример:

CREATE TABLE dept AS SELECT * FROM departments;

CREATE PROCEDURE addDept IS
  deptID   dept.department_id%TYPE;
  deptName dept.department_name%TYPE;
BEGIN
  deptID   := 280;
  deptName := 'ST-Curriculum';
  INSERT INTO Dept(Department_ID, Department_Name)
  VALUES          (deptID,        deptName);

  DBMS_OUTPUT.PUT_LINE('Inserted '|| SQL%ROWCOUNT ||' row(s).');
END;

Подпрограмма может быть вызвана внутри BEGIN...END;, с помощью операторов EXECUTE и CALL (CALL круче — он может так же вызывать процедуры/функции, написанные на C/Java).

-- вне блока BEGIN..END:
EXECUTE someProcedure(arg1, arg2, ...)
CALL someOtherProcedure(arg1, arg2, ...)

-- внутри блока BEGIN..END:
BEGIN
  anotherProcedure(arg1, arg2, ...);
END;

Информация о процедурах/функциях/пакетах/триггерах может быть найдена в словаре USER_SOURCE.

DESCRIBE USER_SOURCE

Name          Null? Type           
------------- ----- -------------- 
NAME                VARCHAR2(128)  
TYPE                VARCHAR2(12)   
LINE                NUMBER         
TEXT                VARCHAR2(4000) 
ORIGIN_CON_ID       NUMBER

2. Функции

Синтаксис:

CREATE [OR REPLACE] FUNCTION
  functionName [(
    argName1 [IN | OUT | IN OUT] type1 [:= defaultValue],
    argName2 [IN | OUT | IN OUT] type2 [:= defaultValue],
    -- аргументы
  )] RETURN typeName IS | AS
  -- локальные переменные и процедуры/функции
  -- процедуры и функции должны быть написаны в самом низу
BEGIN
  -- код функции
END [functionName];

Передавать параметры в функцию/процедуру можно по очереди, а можно указать имя параметра через =>:

BEGIN
  var := someFunction(42,  arg9 => 31);
  --                  arg1 arg9
END;

Функции так же можно использовать:

Ограничения вызова функций из SQL-выражений:

Контролирование сторонних эффектов при вызове функций из SQL выражений. Функции, вызываемые из:

Вызовы подпрограмм, нарушающих данные ограничения, так же не допустимы в этих функциях.

3. Пакеты

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

Синтаксис создания спецификации пакета:

CREATE [OR REPLACE] PACKAGE packageName IS | AS
  -- объявление public типов данных и переменных
  -- спецификация подпрограмм
END [packageName];

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

Синтаксис создания тела пакета:

CREATE [OR REPLACE] PACKAGE BODY packageName IS | AS
  -- объявление private типов данных и переменных
  -- тела подпрограмм
[BEGIN операторы инициализации]
END [packageName];

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

Forward declaration (прямое объявление) — это private спецификация подпрограмм, разделённых точкой с запятой (нужна для того, чтобы можно было использовать подпрограммы до их описания в пакете).

Уровень чистоты – это степень, в которой функция свободна от сторонних эффектов.

Сторонние эффекты – доступ к таблицам базы данных, переменным пакета и т.д. для чтения или записи.

Контролировать сторонние эффекты очень важно, потому что они могут:

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

Устойчивое состояние пакета. Набор пакетных переменных и значений определяет состояние пакета. Состояние пакета: