Просмотр кода страницы PL/SQL

{{Карточка языка программирования
  |name = PL/SQL
  |logo = 
  |semantics = [[императивное программирование|императивное]], [[объектно-ориентированное программирование|объектно-ориентированное]] программирование
  |class = [[Процедурный язык программирования|процедурный]]
  |year = [[1992]]
  |designer = 
  |typing = [[Строгая типизация|строгая]], [[Статическая типизация|статическая]] 
  |implementations = 
  |dialects =
  |influenced_by = [[Ада (язык программирования)|Ada]]
  |influenced = [[PL/pgSQL]]
}}

'''PL/SQL''' ('''P'''rocedural '''L'''anguage '''/''' '''S'''tructured '''Q'''uery '''L'''anguage) — [[язык программирования]], [[Процедурное программирование|процедурное]] расширение языка [[SQL]], разработанное корпорацией [[Oracle Corporation|Oracle]]. Базируется на языке [[Ада (язык программирования)|Ада]]<ref>{{книга
 |автор         = McDonald, Connor
 |часть         = 
 |заглавие      = Mastering Oracle PL/SQL: practical solutions
 |оригинал      = 
 |ссылка        = https://books.google.com/books?id=E4yq9orqFTAC&pg=PA59&dq=Oracle+PL/SQL+ADA&hl=en&ei=RyVkTbCGGoSW8QPHv9XxCA&sa=X&oi=book_result&ct=result&resnum=9&ved=0CGIQ6AEwCA#v=onepage&q=Oracle%20PL%2FSQL%20ADA&f=false
 |ответственный = 
 |издание       = 
 |место         = 
 |издательство  = [[Springer Science+Business Media|Springer]]
 |год           = 2004
 |volume        = 
 |pages         = 
 |allpages      = 605
 |серия         = 
 |isbn          = 978-1590592175
 |тираж         = 
 |archive-date   = 2014-04-26
 |archive-url    = https://web.archive.org/web/20140426155027/http://books.google.com/books?id=E4yq9orqFTAC&pg=PA59&dq=Oracle+PL/SQL+ADA&hl=en&ei=RyVkTbCGGoSW8QPHv9XxCA&sa=X&oi=book_result&ct=result&resnum=9&ved=0CGIQ6AEwCA#v=onepage&q=Oracle%20PL%2FSQL%20ADA&f=false
}}</ref>. <!-- А не Модула??? Точно Ada, см. на EXCEPTION-WHEN, PRAGMA, и на источники, которых можно привести много -->

PL/SQL встроен в следующие [[СУБД]]: [[Oracle Database]] (начиная с версии 7), {{Не переведено|TimesTen||en|TimesTen}} (с версии 11.2.1) и [[IBM DB2]] (с версии 9.7)<ref>{{cite web
 |url         = http://www.ibm.com/developerworks/data/library/techarticle/dm-0907oracleappsondb2/index.html
 |title       = DB2 9.7: Run Oracle applications on DB2 9.7 for Linux, Unix, and Windows
 |first       = Serge
 |last        = Rielau
 |date        = 2010-05-26
 |work        = Developer Works
 |publisher   = [[IBM]]
 |access-date  = 2011-02-21
 |lang        = en
 |archive-url  = https://www.webcitation.org/61CqX2haI?url=http://www.ibm.com/developerworks/data/library/techarticle/dm-0907oracleappsondb2/index.html
 |archive-date = 2011-08-25
 |url-status     = dead
}}</ref>. Также PL/SQL используется как встроенный язык для [[RAD (программирование)|средства быстрой разработки]] [[Oracle Forms]], инструмента разработки отчётов [[Oracle Reports]] и в [[Oracle Application Express]].

== Функциональность ==
PL/SQL даёт возможность использовать [[Переменная (программирование)|переменные]], [[Оператор (программирование)|операторы]], [[Массив (программирование)|массивы]], [[Курсор (базы данных)|курсоры]] и [[Обработка исключений|исключения]]. Начиная с версии 8 доступна и [[Объектно-ориентированное программирование|объектно-ориентированная]] модель.

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

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

<source lang="plsql">DECLARE
-- Описания блока, переменные, типы, курсоры и т. п. (опционально)
BEGIN
-- Непосредственно код программы
EXCEPTION
-- Обработка исключений (опционально)
END;
/* Многострочные
комментарии… */
-- Однострочный комментарий</source>
<!-- {|
|<font style="color:#0000FF">DECLARE</font>
|-
| <font style="color:#006400">-- Описания блока, переменные, типы, курсоры и т. п. (опционально)</font>
|-
|<font style="color:#0000FF">BEGIN</font>
|-
| <font style="color:#006400">-- Непосредственно код программы</font>
|-
|<font style="color:#0000FF">EXCEPTION
|-
| <font style="color:#006400">-- Обработка исключений (опционально)</font>
|-
|<font style="color:#0000FF">END;</font>
|}

: <font style="color:#006400">/* Многострочные</font>

: <font style="color:#006400">комментарии… */</font>

: <font style="color:#006400">--Однострочный комментарий</font> -->

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

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

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

=== Типы данных ===
Язык PL/SQL поддерживает следующие категории типов:
* встроенные типы данных, включая коллекции и записи;
** скалярные;
** составные;
** ссылочные;
** [[LOB|LOB-типы]];
* объектные типы данных.

=== Операторы управления ===
* операторы выбора:
<source lang="plsql">
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;</source>
* операторы цикла:
<source lang="plsql">LOOP - END LOOP;
WHILE - LOOP - END LOOP;
FOR - LOOP - END LOOP;
CONTINUE;
EXIT;
EXIT WHEN;</source>
* операторы безусловного перехода:
<source lang="plsql">GOTO;
NULL;
<<labels>></source>

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

<source lang="plsql">
set serveroutput on

declare
    hello varchar2(50) := 'Hello, world!';
begin
    dbms_output.put_line(hello);
end;
</source>
{{викиучебник|en:Oracle_Programming/SQL_Cheatsheet#PL.2FSQL}}

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

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

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

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

<source lang="plsql">
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;
</source>

==== Запрос нескольких строк из базы данных ====
Для последовательного считывания нескольких строк можно использовать [[Курсор (базы данных)|курсоры]] PL/SQL. Под курсором подразумевается указатель на очередную строку в результатах запроса. Открытие и закрытие курсора осуществляется операторами <code>OPEN</code> и <code>CLOSE</code>. Считывание значений, на которые указывает курсор, и его перевод на следующую строку осуществляется оператором <code>FETCH</code>.

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

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

<source lang="plsql">
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;
</source>

==== Использование указателей на курсоры ====
Для большей гибкости удобно вместо курсора использовать [[Указатель (тип данных)|указатель]] на курсор с разными курсорами. В таком случае курсор с запросом определяются неявно при вызове операции <code>OPEN</code> для указателя на курсор с помощью предложения <code>FOR</code>. Один указатель на курсор можно использовать со многими курсорами и, соответственно, со многими запросами.

<source lang="plsql">
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;
</source>

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

Для предотвращения лишних разборов используется техника связанных переменных ({{lang-en|bind variables}}), то есть переменных непосредственно в теле запроса, значения которых подставляются только при открытии [[Курсор (базы данных)|курсора]] для запроса. Связанные переменные обозначаются именем, предварённым символом двоеточия. При открытии курсора значения переменных указываются с помощью предложения <code>USING</code>. При первом выполнении участка кода, использующего технику связанных переменных, запрос будет разобран в СУБД, для него будет создан [[План выполнения запроса|план выполнения]] (это будет происходить сравнительно долго); при последующих выполнениях будет использоваться уже созданный план выполнения, и запрос будет быстро возвращать значения.

Пример функции со связанными переменными:
<source lang="plsql">
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;
</source>

==== Неявное определение курсора в цикле ====
Иногда вместо того, чтобы объявлять курсор или указатель на него, удобно воспользоваться неявным определением курсора и неявным определением переменной типа [[Запись (тип данных)|запись]] (<code>RECORD</code>):

<source lang="plsql">
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;
</source>

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

<source lang="plsql">
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;
</source>'''Пакетный запрос многих значений'''

Также можно запросить много значений и поместить их в заранее подготовленную коллекцию (или несколько коллекций). Для этого у нас должен быть объявлен соответствующий тип коллекции, например коллекция строк: <syntaxhighlight lang="sql">
create or replace type t_str_coll as table of varchar2(2000 char);
</syntaxhighlight>Тогда, мы можем поместить все ename из таблички employees в нашу коллекцию таким образом:<syntaxhighlight lang="plsql">
DECLARE
  l_str_coll t_str_coll;
BEGIN
  SELECT t.ename
    BULK COLLECT 
    INTO l_str_coll
    FROM employees t;
END;
</syntaxhighlight>

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

<source lang="plsql">
DECLARE

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

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

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

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

<source lang="plsql">
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;

</source>

==== Динамические DML- и DDL-операции ====
Динамические операции [[Data Manipulation Language|DML]] и [[Data Definition Language|DDL]] выполняются с помощью оператора <code>EXECUTE IMMEDIATE</code>.

<source lang="plsql">
DECLARE

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

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

== Примечания ==
{{Примечания}}


[[Категория:Oracle Database]]
[[Категория:Процедурные расширения SQL]]