Иван Сергеевич Задворьев
Язык PL/SQL
Хранимые программы
Виды хранимых программ
В PL/SQL имеются следующие виды хранимых программ:
процедура (procedure) – программа, которая выполняет одно или несколько действий и вызывается как исполняемая команда PL/SQL;
функция (function) – программа, которая возвращает одно значение и используется как выражение PL/SQL;
пакет (package) – набор процедур, функций, переменных, констант и типов данных, объединенных общим функциональным назначением;
триггер (trigger) – программа, которая автоматически запускается при наступлении событий, указанных при создании триггера.
Создание, изменение и удаление хранимых программ
Хранимые программы являются объектами баз данных Oracle. Как и другие объекты баз данных, хранимые программы создаются DDL-командами CREATE, изменяются DDL-командами ALTER и удаляются DDL-командами DROP.
Чтобы создать хранимую процедуру в своей схеме, пользователю необходимо иметь системную привилегию CREATE PROCEDURE или роль с этой привилегией, например, роль RESOURCE. Привилегии CREATE FUNCTION в Oracle SQL нет, привилегия CREATE PROCEDURE позволяет создавать и процедуры, и функции, и пакеты.
Для создания этих хранимых программ в схемах других пользователей требуется наличие системной привилегии CREATE ANY PROCEDURE, предоставленной явно или через роль. Для создания триггеров требуются отдельные привилегии CREATE TRIGGER и CREATE ANY TRIGGER.
DDL-команды CREATE для создания хранимых программ PL/SQL имеют необязательные ключевые слова CREATE [OR REPLACE], указывающую на замену существующей программы новой программой с тем же именем. Если слова OR REPLACE не указаны в команде CREATE, а хранимая программа с таким именем в базе данных уже есть, то создание программы завершится с ошибкой.
SQL> CREATE PROCEDURE proc1 AS
2 BEGIN
3 NULL;
4 END;
5 /
Procedure created.
SQL> CREATE PROCEDURE proc1 AS
2 BEGIN
3 NULL;
4 END;
5 /
CREATE PROCEDURE proc1 AS
*
ERROR at line 1:
ORA-00955: name is already used by an existing object
SQL> CREATE OR REPLACE PROCEDURE proc1 AS
2 BEGIN
3 NULL;
4 END;
5 /
Procedure created.
Можно было бы сначала удалить существующую программу, а потом создать новую с тем же именем, но рекомендуется так не делать по следующей причине.
Для хранимых программ PL/SQL пользователям и ролям базы данных предоставляются объектные привилегии на их выполнение. Если удалить хранимую программу, то эти привилегии пропадут (правильнее сказать – автоматически отзовутся в связи с удалением объекта доступа). После того, как хранимая программа с таким же именем заново будет создана, привилегии эти сами по себе не восстановятся, владельцу программы придется предоставлять их другим пользователям снова. При пересоздании хранимой программы DDL-командой CREATE OR REPLACE с привилегиями на ее выполнение ничего не происходит.
Находящиеся в базе данных хранимые программы можно перекомпилировать с помощью DDL-команды ALTER:
SQL> ALTER PROCEDURE proc1 COMPILE;
Procedure altered.
Как и другие объекты базы данных, хранимые программы могут быть удалены. Пользователю не требуются дополнительные привилегии для удаления программ в своей схеме, для удаления программ в схеме другого пользователя необходимо наличие привилегии DROP ANY PROCEDURE.
SQL> DROP PROCEDURE proc1;
Procedure dropped.
Процедуры и функции
Функция отличается от процедуры тем, что функция возвращает значение указанного при создании функции типа данных, а процедура ничего не возвращает. Вызов функции всегда включается в некоторое выражение, то есть возвращаемый функцией результат обязательно нужно куда-то деть – присвоить его значение некоторой переменной или передать в качестве параметра другой функции или процедуре. Функции на PL/SQL можно использовать в предложениях SQL наряду со встроенными функциями языка SQL.
Обычно процедуры и функции создаются для решения определенных небольших задач. При продуманной структуре исходного кода каждая процедура или функция со всеми разделами и вложенными блоками должна умещаться на одном экране (максимум 30-40 строк). Если код процедуры или функции разрастается, то имеет смысл продумать его декомпозицию, использовать пакеты или перегружаемые программы.
Процедуры
Команда создания процедуры имеет следующий синтаксис:
CREATE [OR REPLACE]
– раздел заголовка блока PL/SQL
PROCEDURE
[имя схемы.]имя процедуры
[(имя параметра [{IN | OUT | IN OUT}] тип данных
[,имя параметра [{IN | OUT | IN OUT}] тип данных …])]
{IS | AS}
остальные разделы блока PL/SQL (объявлений,исполняемый,обработки исключений)
В процедурах не используется ключевое слово DECLARE – объявление пользовательских типов данных, переменных, курсоров начинается сразу после ключевого слова AS. Областью видимости объявленных здесь элементов будет являться вся процедура. В разделе объявлений процедуры можно реализовать и другую процедуру или функцию, которые будут видны только внутри родительской процедуры:
CREATE OR REPLACE PROCEDURE proc2 AS
FUNCTION nested_proc RETURN INTEGER IS
BEGIN
NULL;
END;
BEGIN
nested_proc();
END;
Пусть таблица tab1 создана следующей DDL-командой:
CREATE TABLE tab1 (at1 NUMBER, at2 DATE);
Создадим процедуру insRec, которая заносит в таблицу 1/2 переданного значения числового параметра и текущую дату.
SQL> CREATE OR REPLACE PROCEDURE insRec(p_arg1 IN NUMBER) AS
2 coeff CONSTANT NUMBER := 0.5;
3 BEGIN
4 INSERT INTO tab1 VALUES(coeff*p_arg1,SYSDATE);
5 END;
/
Procedure created.
После создания процедуру можно вызвать из любого блока PL/SQL, указав ее имя и параметры.
SQL> DECLARE
2 l_arg1 NUMBER := 240;
3 BEGIN
4 insRec(l_arg1);
5 END;
6 /
PL/SQL procedure successfully completed.
SQL> SELECT * FROM Tab1;
AT1 AT2
– –
120 04.05.2015
В SQL*Plus для вызова процедур есть команда EXECUTE.
SQL> EXECUTE insRec(100);
PL/SQL procedure successfully completed.
SQL> SELECT * FROM Tab1;
AT1 AT2
– –
120 04.05.2015
50 04.05.2015
В процедурах можно использовать команду RETURN. Как только в потоке команд в процедуре встретится команда RETURN, выполнение процедуры прекращается и управление передается вызвавшему процедуру блоку.
Функции
Команда создания функции имеет следующий синтаксис:
CREATE [OR REPLACE] FUNCTION
– раздел заголовка блока PL/SQL
[имя схемы.]имя функции
[(имя параметра [{IN | OUT | INOUT}] тип данных
[,имя параметра [{IN | OUT | INOUT}] тип данных …])] RETURN тип данных AS
остальные разделы блока PL/SQL (объявлений,исполняемый,обработки исключений)
Пусть таблица tab1 создана и заполнена следующим образом:
CREATE TABLE tab1 (at1 NUMBER, at2 DATE);
INSERT INTO tab1 VALUES(5, SYSDATE);
INSERT INTO tab1 VALUES(6, SYSDATE);
INSERT INTO tab1 VALUES(7, SYSDATE+1);
Создадим функцию, которая вычисляет сумму значений столбцов таблицы, таких, что дата попадает в заданный интервал.
SQL> CREATE OR REPLACE FUNCTION sumRecInt(arg1 IN DATE,
2 arg2 IN DATE) RETURN NUMBER AS
3 sum_var NUMBER := 0;
4 BEGIN
5 SELECT SUM(at1) INTO sum_var FROM tab1
6 WHERE at2 BETWEEN arg1 AND arg2;
7 RETURN sum_var;
8 END;
9 /
Function created.
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE(sumRecInt(SYSDATE-1/2, SYSDATE+1/2));
3 END;
4 /
11
PL/SQL procedure successfully completed.
Ход вычислений функции обязательно должен завершаться вызовом в ее теле команды RETURN возвращаемое значение. Если этого не произойдет, то возникнет ошибка этапа выполнения:
SQL> CREATE FUNCTION func2 RETURN INTEGER AS
2 BEGIN
3 NULL;
4 END;
5 /
Function created.
SQL> BEGIN
2 DBMS_OUTPUT.PUT_LINE(func2);
3 END;
4 /
BEGIN
*
ERROR at line 1:
ORA-06503: PL/SQL: Function returned without value
ORA-06512: at "U1.FUNC2", line 3
ORA-06512: at line 2
Иногда на лекциях студентами задается вопрос, поддерживаются ли в PL/SQL рекурсивные функции, то есть функции, вызывающие сами себя. Поддерживаются, приведем пример наиболее понятной на все времена рекурсивной функции:
SQL> CREATE OR REPLACE FUNCTION factorial(n IN INTEGER) RETURN INTEGER IS
2 BEGIN
3 IF n=0 THEN
4 RETURN 1;
5 ELSE
6 RETURN n*factorial(n-1);
7 END IF;
8 END;
9 /
Function created.
SQL> DECLARE
2 l_number INTEGER := 3;
3 BEGIN
4 DBMS_OUTPUT.PUT_LINE(factorial(l_number));
5 DBMS_OUTPUT.PUT_LINE(factorial(COS(0)));
6 END;
7 /
6
1
PL/SQL procedure successfully completed.
Параметры процедур и функций
Процедуры и функции могут иметь параметры, для которых указываются имена, типы данных и режимы передачи значений.
Важно понимать различия между формальными и фактическими параметрами. Формальные параметры указываются в списке параметров заголовка программы при ее объявлении, тогда как фактические параметры – это значения и выражения, которые помещаются в список параметров при ее вызове. Иными словами, значения фактических параметров передаются при вызове внутрь процедур и функций, где становятся значениями формальных параметров. Фактическим параметром при первом вызове функции factorial являлась переменная l_number, объявленная в вызывающем блоке. Эта переменная имела значение 3, которое и было использовано внутри функции (3!=6). При втором вызове функции factorial фактическим параметром являлось выражение COS(0). Как известно, 1!=1;
Соответствие формальных и фактических параметров
Соответствие между формальными и фактическими параметрами можно устанавливать двумя способами:
связывание по позиции (неявное связывание);
связывание по имени.
При неявном связывании фактические параметры указываются в круглых скобках после имени программы в той же последовательности, в которой были перечислены формальные параметры при создании программы.
Связывание формальных и фактических параметров по имени осуществляется с помощью конструкций вида
имя формального параметра => имя фактического параметра
С точки зрения выполнения программы нет разницы между используемыми ней способами установления соответствия между параметрами, которые обычно определяются принятым стилем программирования, корпоративными стандартами кодирования и рядом других факторов. В программах с небольшим числом формальных параметров оправдано использование соответствия параметров по позиции. В программах с большим числом формальных параметров связывание параметров по имени более информативно, более ясно показывает связь между формальными и фактическими параметрами.
SQL> CREATE PROCEDURE print(phrase IN VARCHAR2,punctuation_mark IN CHAR) IS
2 BEGIN
3 DBMS_OUTPUT.PUT_LINE(phrase||' '||punctuation_mark);
4 END;
5 /
Procedure created.
SQL> BEGIN
2 print('Hello,world','!');
3 END;
4 /
Hello,world !
PL/SQL procedure successfully completed.
SQL> BEGIN
2 print(punctuation_mark=>'!',phrase=>'Hello,world');
3 END;
4 /
Hello,world !
PL/SQL procedure successfully completed.
Если у программы в будущем появятся новые формальные параметры, то код, в котором она вызывается со связыванием параметров по имени, останется работоспособным. Если для новых параметров указаны значения по умолчанию, то они будут использованы как фактические параметры, если значения по умолчанию отсутствуют, то новые параметры получат значения NULL. А вот все вызовы со связыванием по позиции при появлении у вызываемой программы новых формальных параметров потребуется изменить так, чтобы фактических параметров снова стало столько же, сколько формальных.
Отметим, что при хорошем стиле программирования не принято объявлять процедуры и функции с большим (больше 10) числом параметров скалярных типов данных. В этом случае надо использовать небольшое число параметров составных типов данных (записи PL/SQL или коллекции).
Режимы передачи значений параметров
В PL/SQL есть три режима передачи значений параметров.
Таблица 5. Режимы передачи значений параметров в PL/SQL.
Режим
Предназначение
Использование
IN
только для чтения
переданное значение параметра может читаться, но не может быть изменено внутри процедуры или функции
OUT
для записи
для записи как в неинициализированную переменную (значению параметра внутри процедуры или функции сразу присваивается значение NULL, в дальнейшем оно может изменяться)
IN OUT
для чтения и записи
передается значение, которое можно читать и изменять внутри процедуры или функции
В большинстве случаев параметры передаются в процедуры и функции в режиме IN (именно этот режим используется по умолчанию). Режимы передачи параметров OUT и IN OUT в свою очередь позволяют, например, реализовать возвращение нескольких значений для функции.
Часто функции возвращают код завершения своей работы, который указывается как параметр команды RETURN в теле функции (например, ноль – успешное завершение, ненулевое значение – номер ошибки). По смыслу функции получить от нее помимо результата еще что-то, например, диагностическое сообщение с подробностями к коду завершения, невозможно. В команде RETURN может быть указан только один параметр. Выходом является использование формального параметра с режимом передачи OUT. В теле функции следует предусмотреть формирование и запись в этот параметр текстов сообщений, и после каждого вызова функции эти сообщения будут доступны в вызывающем коде в переменных-фактических параметрах.
Основное отличие режима передачи OUT от режима IN OUT заключается в том, что OUT-параметр становится неинициализированным при передаче внутрь процедуры или функции, то есть то значение, которое имела во внешнем блоке переменная-фактический параметр, теряется (становится равным NULL). Это верно во всех случаях, за исключением ситуации, когда внутри процедуры или функции инициируется необработанное в ней исключение. Тогда во внешнем блоке у переменной-фактического параметра для формального OUT-параметра сохранится то ее значение, которое было до передачи. У фактических параметров для формальных IN OUT-параметров значение в NULL не сбрасывается. Если значение фактического параметра внутри программы не меняли, то и после завершения вызова программы оно будет таким же, каким оно было до передачи в программу. Приведем примеры передачи значений параметров в различных режимах.
SQL> CREATE OR REPLACE PROCEDURE test(p1 IN INTEGER,
2 p2 OUT INTEGER,
3 p3 IN OUT INTEGER) IS
4 BEGIN
5 p2 := 11;
6 p3 := 12;
7 END;
8 /
Procedure created.
SQL> DECLARE
2 l_arg1 INTEGER := 5;
3 l_arg2 INTEGER := 6;
4 l_arg3 INTEGER := 7;
5 BEGIN
6 DBMS_OUTPUT.PUT_LINE('before l_arg2='||l_arg2);
7 DBMS_OUTPUT.PUT_LINE('before l_arg3='||l_arg3);
8 test(p1 => l_arg1,p2 => l_arg2,p3 => l_arg3);
9 DBMS_OUTPUT.PUT_LINE('after l_arg2='||l_arg2);
10 DBMS_OUTPUT.PUT_LINE('after l_arg3='||l_arg3);
11 END;
12 /
before l_arg2=6
before l_arg3=6
after l_arg2=11
after l_arg3=12
PL/SQL procedure successfully completed.
Видно, что значение переменной l_arg2, которое было до вызова процедуры test равным 6, внутри процедуры было изменено на 11. Значение переменной l_arg3 после вызова процедуры стало равным 12.
Изменим код процедуры test, заменив ее исполняемый блок пустой командой NULL (то есть с параметрами в коде процедуры никаких действий осуществляться не будет) и вызовем ее еще раз с такими же значениями фактических параметров:
SQL> CREATE OR REPLACE PROCEDURE test(p1 IN INTEGER,
2 p2 OUT INTEGER,
3 p3 IN OUT INTEGER) IS
4 BEGIN
5 NULL;
6 END;
7 /
Procedure created.
SQL> DECLARE
2 l_arg1 INTEGER := 5;
3 l_arg2 INTEGER := 6;
4 l_arg3 INTEGER := 7;
5 BEGIN
6 DBMS_OUTPUT.PUT_LINE('before l_arg2='||l_arg2);
7 DBMS_OUTPUT.PUT_LINE('before l_arg3='||l_arg3);
8 test(p1 => l_arg1,p2 => l_arg2,p3 => l_arg3);
9 DBMS_OUTPUT.PUT_LINE('after l_arg2='||l_arg2);
10 DBMS_OUTPUT.PUT_LINE('after l_arg3='||l_arg3);
11 END;
12 /
before l_arg2=6
before l_arg3=7
after l_arg2=
after l_arg3=7
PL/SQL procedure successfully completed.
Как и ожидалось, значение переменной l_arg2, переданной в процедуру test как OUT-параметр, стало NULL. Значение переменной l_arg3 не изменилось.
Способы передачи значений параметров
Виртуальная машина PL/SQL во время выполнения программ PL/SQL применяет два способа передачи значений параметров:
по ссылке – с соответствующим формальным параметром связывается указатель, а не фактическое значение (после этого и формальный и фактический параметры ссылаются на ячейку памяти, содержащую значение параметра);
по значению – значение фактического параметра копируется в соответствующий формальный параметр (если впоследствии программа завершается без необработанных исключений, то значение формального параметра присваивается обратно фактическому).
Понятно, что для режима передачи значений параметров IN используется передача параметров по ссылке (ведь IN-параметры не изменяются внутри процедур и функций, поэтому значение достаточно только читать по ссылке). Для режимов OUT и IN OUT обычно используется передача по значению.
Ошибки компиляции программ PL/SQL
На практике в большинстве случаев первая попытка откомпилировать программу на языке PL/SQL приводит к получению сообщения о наличии ошибок в ее коде. Чтобы увидеть выявленные компилятором ошибки, можно воспользоваться командой утилиты SQL*Plus SHOW ERRORS. Если команда SHOW ERRORS используется без параметров, то возвращаются ошибки последней компилированной программы.
Создадим процедуру PL/SQL с синтаксической ошибкой (пропущен символ ; после команды NULL):
SQL> CREATE PROCEDURE proc1 AS
2 BEGIN
3 NULL
4 END;
5 /
Warning: Procedure created with compilation errors.
SQL> SHOW ERRORS
Errors for PROCEDURE PROC1:
LINE/COL ERROR
– –
4/1 PLS-00103: Encountered the symbol "END" when expecting one of the
following: ; The symbol ";" was substituted for "END" to continue.
Попробуем создать процедуру c другой ошибкой:
SQL> CREATE OR REPLACE PROCEDURE test(p1 IN INTEGER,
2 p2 OUT INTEGER,
3 p3 IN OUT INTEGER) IS
4 l_p INTEGER := 10;
5 BEGIN
6 p1 := l_p;
7 END;
8 /
Warning: Procedure created with compilation errors.
SQL> SHOW ERRORS
Errors for PROCEDURE TEST:
LINE/COL ERROR
– –
6/3 PL/SQL: Statement ignored
6/3 PLS-00363: expression 'P1' cannot be used as an assignment target
В коде процедуры test имеется семантическая (смысловая) ошибка – попытка изменить значение параметра с режимом передачи IN. Компилятор PL/SQL при анализе кода проверяет отсутствие таких параметров в левой части команд присваивания, в конструкциях SELECT INTO и в других местах кода, где значения таких параметров может быть изменено.
В обоих случаях процедуры proc1 и test как новые объекты базы данных создавались, но с ошибками (Procedure created with compilation errors). Такие объекты базы данных получают статус INVALID и непригодны для использования.
Попытка вызвать процедуру test приведет к ошибке:
SQL> DECLARE
2 l_arg1 INTEGER :=5;
3 l_arg2 INTEGER :=6;
4 l_arg3 INTEGER :=7;
5 BEGIN
6 test(p1 => l_arg1,p2 => l_arg2,p3 => l_arg3);
7 END;
8 /
test(p1 => l_arg1,p2 => l_arg2,p3 => l_arg3);
*
ERROR at line 6:
ORA-06550: line 6, column 3:
PLS-00905: object USER1.TEST is invalid
ORA-06550: line 6, column 3:
PL/SQL: Statement ignored
Хранимая программа PL/SQL может получить статус INVALID как из-за наличия в ее коде синтаксических и семантических ошибок, так и по другим причинам, например, если какие-то объекты базы данных, к которым есть обращения в коде программы, стали недоступными (были удалены, были отозваны привилегии доступа к ним и т. п.).
Отладка программ на PL/SQL
Исправлять ошибки, выявленные компилятором PL/SQL в ходе анализа кода, обычно довольно просто. Для исправления выявленных пользователями ошибок этапа выполнения, следует использовать отладчик PL/SQL. Для удобства отладки можно порекомендовать использовать специализированные средства, например, интегрированную среду разработки Quest SQL Navigator, в которой есть и breakpoints, и watches, и step into, и step over – в общем, все средства, достаточные для эффективной отладки программ на процедурном языке программирования.
Для использования отладчика отлаживаемую программу PL/SQL необходимо перекомпилировать с опцией добавления отладочной информации.
SQL> ALTER PROCEDURE insRec COMPILE DEBUG;
Procedure altered.
Редактировать код хранимых программ по опыту авторов также рекомендуется в специализированном Stored Program Editor, который есть в Quest SQL Navigator, TOAD, PL/SQL Developer и Oracle SQL Developer:
после открытия в редакторе исходного текста хранимой программы с ошибками курсор в тексте сразу позиционируется на место ошибки с отображением сообщения об ошибке;
в Stored Program Editor редактируется актуальная версия кода, которая находится в словаре-справочнике данных базы данных Oracle;
есть стандартные для современных IDE подсветка синтаксиса и автодополнение кода, что очень удобно;
нажатием клавиш Ctrl+S или соответствующей кнопки интерфейса можно быстро отправить код программы на компиляцию.