title-icon
Яндекс.Метрика

PL/SQL

26.01.2022


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

PL/SQL встроен в следующие СУБД: Oracle Database (начиная с версии 7), TimesTen (с версии 11.2.1) и IBM DB2 (с версии 9.7). Также 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; 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.