PL/SQL

PL/SQL
Семантика	императивное, объектно-ориентированное программирование
Класс языка	язык программирования
Тип исполнения	процедурный
Появился в	1992
Система типов	строгая, статическая
Испытал влияние	Ada
Повлиял на	PL/pgSQL
Сайт	oracle.com/techno… (англ.)

PL/SQL (Procedural Language / Structured Query Language) — язык программирования, процедурное расширение языка SQL, разработанное корпорацией Oracle. Базируется на языке Ада[1].

PL/SQL встроен в следующие СУБД: Oracle Database (начиная с версии 7), TimesTen^[англ.] (с версии 11.2.1) и IBM DB2 (с версии 9.7)[2]. Также PL/SQL используется как встроенный язык для средства быстрой разработки Oracle Forms, инструмента разработки отчётов Oracle Reports и в Oracle Application Express.

Функциональность

PL/SQL даёт возможность использовать переменные, операторы, массивы, курсоры и исключения. Начиная с версии 8 доступна и объектно-ориентированная модель.

Стандартный SQL является специализированным декларативным языком программирования. На язык наложены определённые ограничения, такие как, например, отсутствие прямой поддержки циклов. PL/SQL же, как полный по Тьюрингу язык, позволяет разработчикам обрабатывать данные в реляционной базе, используя императивный стиль программирования. Операторы SQL могут быть легко вызваны непосредственно из PL/SQL-процедуры, функции или триггера (иногда с некоторыми ограничениями).

Базовая структура кода

Программа на PL/SQL состоит из блоков (анонимных или поименованных). Блок может содержать вложенные блоки, называемые иногда подблоками. Общая форма PL/SQL-блока:

DECLARE
-- Описания блока, переменные, типы, курсоры и т. п. (опционально)
BEGIN
-- Непосредственно код программы
EXCEPTION
-- Обработка исключений (опционально)
END;
/* Многострочные
комментарии… */
-- Однострочный комментарий

Язык PL/SQL позволяет определять следующие типы именованных блоков:

• процедуры;
• функции;
• объекты;
• пакеты.

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

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

Типы данных

Язык PL/SQL поддерживает следующие категории типов:

• встроенные типы данных, включая коллекции и записи;
  • скалярные;
  • составные;
  • ссылочные;
  • LOB-типы;
• объектные типы данных.

Операторы управления

• операторы выбора:

IF - THEN - END IF;
IF - THEN - ELSE - END IF;
IF - THEN - ELSIF - END IF;
IF - THEN - ELSIF - ELSE - END IF;
CASE - WHEN - THEN - END;
CASE - WHEN - THEN - ELSE - END;

• операторы цикла:

LOOP - END LOOP;
WHILE - LOOP - END LOOP;
FOR - LOOP - END LOOP;
CONTINUE;
EXIT;
EXIT WHEN;

• операторы безусловного перехода:

GOTO;
NULL;
<<labels>>

Пример программы

Программа, выводящая в консоли SQL*Plus строчку «Hello, World!» с использованием инициализированной переменной.

set serveroutput on

declare
    hello varchar2(50) := 'Hello, world!';
begin
    dbms_output.put_line(hello);
end;

Работа с базой данных

Статический SQL

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

Запрос одной строки из базы данных

Используется SQL-выражение SELECT, дополненное предложением INTO, в котором указываются переменные, куда запишутся запрошенные данные. Количество и тип этих переменных должны соответствовать количеству (до версии Oracle 9 включительно переменных могло быть больше) и типу полей (хотя при определённых несоответствиях типов может произойти их неявное приведение).

В случае, если запрос вернул нулевое число строк, выбрасывается исключение NO_DATA_FOUND. В случае, если строк больше, чем одна, выбрасывается исключение TOO_MANY_ROWS. Эти исключения, вообще говоря, следует обрабатывать в соответствующей части блока за исключением случаев, когда предполагается, что они не могут быть выброшены. Например, при запросе данных из таблицы по их первичному ключу обработчик исключения TOO_MANY_ROWS не нужен. Также в случае использования агрегированных функций, например MAX() или MIN() - исключение NO_DATA_FOUND не будет сгенерировано, а переменная получит в качестве результата NULL

DECLARE
    empname   VARCHAR2(200);
BEGIN
    SELECT ename 
        INTO empname        
        FROM scott.emp
        WHERE empno = 7439;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.put_line('No records found!');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.put_line('Found more than one string!');
END;

Запрос нескольких строк из базы данных

Для последовательного считывания нескольких строк можно использовать курсоры PL/SQL. Под курсором подразумевается указатель на очередную строку в результатах запроса. Открытие и закрытие курсора осуществляется операторами OPEN и CLOSE. Считывание значений, на которые указывает курсор, и его перевод на следующую строку осуществляется оператором FETCH.

Считывание данных из запроса оформляется как цикл. Когда курсор дойдёт до конца результатов запроса, очередной вызов оператора FETCH не считает новых данных, а атрибут <имя_курсора>%NOTFOUND принимает значение TRUE. Это событие используется для прерывания работы цикла.

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

DECLARE
    empname VARCHAR2(200);
    CURSOR c1 IS
        SELECT ename
            FROM scott.emp;
BEGIN
    OPEN c1;
        LOOP
            FETCH c1 INTO empname;
            EXIT WHEN c1%NOTFOUND;
            -- работа со значением empname
        END LOOP;
    CLOSE c1;
END;

Использование указателей на курсоры

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

DECLARE
    TYPE GenericCursor IS REF CURSOR;
    с1 GenericCursor;
    empname VARCHAR2(200);
BEGIN
    OPEN c1 FOR SELECT ename FROM scott.emp;
        LOOP
            FETCH c1 INTO empname;
            EXIT WHEN c1%NOTFOUND;
            -- работа со значением empname
        END LOOP;
    CLOSE c1;
END;

Использование связанных переменных

Как при использовании курсоров, так и при использовании указателей на курсоры рекомендуется при формировании запросов не включать туда конкретные константы (кроме тех случаев, когда эти константы действительно будут сохраняться во всех подобных запросах). Связано это с тем, что при последовательном выполнении двух запросов, отличающихся только константой (например, SELECT ename FROM employees WHERE id = 20 и SELECT ename FROM employees WHERE id = 21), СУБД производит разбор каждого запроса отдельно, хотя на самом деле план выполнения у таких запросов общий. Такие повторные разборы могут существенно снизить производительность приложения.

Для предотвращения лишних разборов используется техника связанных переменных (англ. bind variables), то есть переменных непосредственно в теле запроса, значения которых подставляются только при открытии курсора для запроса. Связанные переменные обозначаются именем, предварённым символом двоеточия. При открытии курсора значения переменных указываются с помощью предложения USING. При первом выполнении участка кода, использующего технику связанных переменных, запрос будет разобран в СУБД, для него будет создан план выполнения (это будет происходить сравнительно долго); при последующих выполнениях будет использоваться уже созданный план выполнения, и запрос будет быстро возвращать значения.

Пример функции со связанными переменными:

FUNCTION get_employee_name (empid INTEGER, empcity VARCHAR2) RETURN VARCHAR2 IS
    TYPE GenericCursor IS REF CURSOR;
    c1 GenericCursor;
    empname VARCHAR2(200);
BEGIN
    OPEN c1 FOR 'SELECT ename FROM employees WHERE id = :id AND city = :city' USING empid, empcity;
        -- цикл не используется, так как запрос вернёт не более одной строки
        FETCH c1 INTO empname;
    CLOSE c1;

    RETURN empname;
END get_employee_name;

Неявное определение курсора в цикле

Иногда вместо того, чтобы объявлять курсор или указатель на него, удобно воспользоваться неявным определением курсора и неявным определением переменной типа запись (RECORD):

DECLARE

BEGIN
    FOR rec IN (SELECT id, ename, 1 AS value FROM employees) LOOP
        dbms_output.put_line(rec.id || ': ' || rec.ename);
    END LOOP;
END;

Пакетный запрос многих строк

При запросе большого числа строк можно увеличить производительность, если вместо поочерёдного зачитывания строк результата, зачитать их всех сразу, значительно снизив тем самым количество переключений контекста от PL/SQL к SQL и обратно. Для пакетного чтения необходимо снабдить оператор FETCH инструкцией BULK COLLECT. Данные при этом должны записываться не в переменные, а в ассоциативные коллекции:

DECLARE
    TYPE GenericCursor IS REF CURSOR;
    c1 GenericCursor;
    TYPE VarcharTable IS TABLE OF VARCHAR2(200) INDEX BY BINARY_INTEGER;
    -- объявили тип данных "Таблица строк", элементы которой нумеруются числами
    empnames VarcharTable;
    -- объявили переменную созданного типа
BEGIN
    OPEN c1 FOR SELECT ename FROM employees;
        FETCH c1 BULK COLLECT INTO empnames;
    CLOSE c1;
END;

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

create or replace type t_str_coll as table of varchar2(2000 char);

Тогда, мы можем поместить все ename из таблички employees в нашу коллекцию таким образом:

DECLARE
  l_str_coll t_str_coll;
BEGIN
  SELECT t.ename
    BULK COLLECT 
    INTO l_str_coll
    FROM employees t;
END;

Выполнение операций DML

Операции DML, как правило, выполняются точно так же, как и в SQL:

DECLARE

BEGIN
    UPDATE employees SET hire_date = SYSDATE WHERE id != 1;
    INSERT INTO employees (name, city) VALUES ('SMITH', 'Тикси');
    COMMIT;
END;

Динамический SQL

Основная статья: Динамический SQL

Динамические запросы

Для большей гибкости часто статические запросы заменяются запросами, формируемыми динамически. Недостаток динамического SQL в том, что динамические запросы, разумеется, не могут быть проверены на этапе компиляции. Если, например, используемой в запросе таблицы не существует, то при выполнении операции OPEN возникнет исключение.

Классическая задача, требующая применения динамического конструирования SQL-запросов, — отчёты в интерфейсах, где пользователь может выбрать разные условия, по которым следует сформировать отчёт.

Ниже приведён анонимный блок кода, который в зависимости от некоего условия запрашивает имя сотрудника либо по ключу, либо по городу.

DECLARE
    TYPE GenericCursor IS REF CURSOR;
    c1 GenericCursor;
    sel VARCHAR2(4000);
    bind_var VARCHAR2(200);
    result VARCHAR2(200);
BEGIN
    sel := 'SELECT name FROM employees WHERE 1 = 1';
    IF ... THEN
        sel := sel || ' AND id = :1';
        bind_var := 12;
    ELSE
        sel := sel || ' AND city = :1';
        bind_var := 'Магадан';
    END IF;

    OPEN c1 FOR sel USING bind_var;
        FETCH c1 INTO result;
    CLOSE c1;
END;

Динамические DML- и DDL-операции

Динамические операции DML и DDL выполняются с помощью оператора EXECUTE IMMEDIATE.

DECLARE

BEGIN
    EXECUTE IMMEDIATE 'DELETE FROM employees';
    EXECUTE IMMEDIATE 'DROP TABLE employees';
    -- COMMIT или ROLLBACK не нужен, потому что DDL-операция завершила транзакцию
END;

Допускается использование связанных переменных, их значения также указываются в предложении USING.

Примечания

1. McDonald, Connor. Mastering Oracle PL/SQL: practical solutions. — Springer, 2004. — 605 p. — ISBN 978-1590592175. Архивировано 26 апреля 2014 года.
2. Rielau, Serge. DB2 9.7: Run Oracle applications on DB2 9.7 for Linux, Unix, and Windows (англ.). Developer Works. IBM (26 мая 2010). Дата обращения: 21 февраля 2011. Архивировано из оригинала 25 августа 2011 года.