PL/SQL

Материал из Википедии — свободной энциклопедии
Перейти к: навигация, поиск
PL/SQL
Семантика:

императивное, объектно-ориентированное программирование

Тип исполнения:

процедурный

Появился в:

1992

Система типов:

строгая, статическая

Испытал влияние:

Ada

Повлиял на:

PL/pgSQL

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.

Функциональность[править | править исходный текст]

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

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

Базовая структура кода[править | править исходный текст]

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

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

Типы данных[править | править исходный текст]

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

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

Операторы управления[править | править исходный текст]

  • операторы выбора:
IF - THEN - END IF;
IF - THEN - ELSE - END IF;
IF - THEN - ELSIF - END IF;
CASE - WHEN - THEN - END CASE;
  • операторы цикла:
LOOP - END LOOP;
WHILE - LOOP - END LOOP;
FOR - LOOP - END LOOP;
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 не нужен.

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;

Выполнение операций 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 в том, что динамические запросы, разумеется, не могут быть проверены на этапе компиляции. Если, например, используемой в запросе таблицы не существует, то при работе выполнении операции 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
  2. Rielau, Serge DB2 9.7: Run Oracle applications on DB2 9.7 for Linux, Unix, and Windows (англ.). Developer Works. IBM (26 May 2010). Проверено 21 февраля 2011. Архивировано из первоисточника 25 августа 2011.

Ссылки[править | править исходный текст]