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-ENDIF;
IF-THEN-ELSE-ENDIF;
IF-THEN-ELSIF-ENDIF;
IF-THEN-ELSIF-ELSE-ENDIF;
CASE-WHEN-THEN-END;
CASE-WHEN-THEN-ELSE-END;

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

LOOP-ENDLOOP;
WHILE-LOOP-ENDLOOP;
FOR-LOOP-ENDLOOP;
CONTINUE;
EXIT;
EXITWHEN;

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

GOTO;
NULL;
<<labels>>

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

setserveroutputon

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

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

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

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

DECLARE
empnameVARCHAR2(200);
BEGIN
SELECTename
INTOempname
FROMscott.emp
WHEREempno=7439;
EXCEPTION
WHENNO_DATA_FOUNDTHEN
DBMS_OUTPUT.put_line('No records found!');
WHENTOO_MANY_ROWSTHEN
DBMS_OUTPUT.put_line('Found more than one string!');
END;

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

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

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

DECLARE
empnameVARCHAR2(200);
CURSORc1IS
SELECTename
FROMscott.emp;
BEGIN
OPENc1;
LOOP
FETCHc1INTOempname;
EXITWHENc1%NOTFOUND;
-- работа со значением empname
ENDLOOP;
CLOSEc1;
END;

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

DECLARE
TYPEGenericCursorISREFCURSOR;
с1GenericCursor;
empnameVARCHAR2(200);
BEGIN
OPENc1FORSELECTenameFROMscott.emp;
LOOP
FETCHc1INTOempname;
EXITWHENc1%NOTFOUND;
-- работа со значением empname
ENDLOOP;
CLOSEc1;
END;

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

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

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

FUNCTIONget_employee_name(empidINTEGER,empcityVARCHAR2)RETURNVARCHAR2IS
TYPEGenericCursorISREFCURSOR;
c1GenericCursor;
empnameVARCHAR2(200);
BEGIN
OPENc1FOR'SELECT ename FROM employees WHERE id = :id AND city = :city'USINGempid,empcity;
-- цикл не используется, так как запрос вернёт не более одной строки
FETCHc1INTOempname;
CLOSEc1;

RETURNempname;
ENDget_employee_name;

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

DECLARE

BEGIN
FORrecIN(SELECTid,ename,1ASvalueFROMemployees)LOOP
dbms_output.put_line(rec.id||': '||rec.ename);
ENDLOOP;
END;

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

DECLARE
TYPEGenericCursorISREFCURSOR;
c1GenericCursor;
TYPEVarcharTableISTABLEOFVARCHAR2(200)INDEXBYBINARY_INTEGER;
-- объявили тип данных "Таблица строк", элементы которой нумеруются числами
empnamesVarcharTable;
-- объявили переменную созданного типа
BEGIN
OPENc1FORSELECTenameFROMemployees;
FETCHc1BULKCOLLECTINTOempnames;
CLOSEc1;
END;

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

createorreplacetypet_str_collastableofvarchar2(2000char);

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

DECLARE
l_str_collt_str_coll;
BEGIN
SELECTt.ename
BULKCOLLECT
INTOl_str_coll
FROMemployeest;
END;

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

DECLARE

BEGIN
UPDATEemployeesSEThire_date=SYSDATEWHEREid!=1;
INSERTINTOemployees(name,city)VALUES('SMITH','Тикси');
COMMIT;
END;

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

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

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

DECLARE
TYPEGenericCursorISREFCURSOR;
c1GenericCursor;
selVARCHAR2(4000);
bind_varVARCHAR2(200);
resultVARCHAR2(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:='Магадан';
ENDIF;

OPENc1FORselUSINGbind_var;
FETCHc1INTOresult;
CLOSEc1;
END;

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

DECLARE

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

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