Режимы передачи значений параметров
В 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 или соответствующей кнопки интерфейса можно быстро отправить код программы на компиляцию.
Пакеты
Объединенные общим функциональным назначением процедуры и функции принято оформлять в виде пакета PL/SQL. Можно считать, что пакетэто аналог библиотеки программ. Прием оформления родственных программ в библиотеки хорошо известен из практики разработки программного обеспечения. В информационной системе с развитой серверной бизнес-логикой могут быть тысячи процедур и функций на языке PL/SQL. Чтобы они не лежали в базе данных тысячами объектов, правильно объединить их по функциональному признаку в пакеты, дав им названия, соответствующие области применения. Например, в базе данных могут быть такие пакеты:
pk_clients (пакет для работы с клиентскими данными);
pk_stocks (пакет для работы со складами);
pk_orders (пакет для обработки заказов);
Для каждого пакета следует назначить ответственного за него программиста, который будет сопровождать пакет и развивать его функциональность. Часть кода, реализующего общесистемную логику, например, унифицированную обработку ошибок и ведение журналов изменения данных, можно выделить в отдельную группу пакетов ядра прикладной системы (kernel packages), назначив ответственными за них самых опытных программистов.
Спецификация и тело пакета
Пакет PL/SQL состоит из двух объектов базы данных: спецификации пакета (PACKAGE) и тела пакета (PACKAGE BODY). Команда создания спецификации пакета имеет следующий синтаксис:
CREATE [OR REPLACE] PACKAGE [имя_схемы.]имя_пакета {IS | AS}
спецификация пакета
END;
Команда создания тела пакета имеет следующий синтаксис:
CREATE [OR REPLACE] PACKAGE BODY [имя_схемы.]имя_пакета {IS | AS}
[спецификация локальных элементов пакета]
блоки PL/SQL реализации процедур и функций, объявленных в спецификации
блоки PL/SQL локальных процедур и функций
[BEGIN секция инициализации пакета]
END;
В спецификации пакета находится описание следующих программных элементов, доступных из других программ PL/SQL (то есть элементов, видимых извне):
пользовательские типы данных;
пользовательские исключения;
процедуры и функции;
переменные;
константы;
курсоры.
Эти программные элементы называются глобальными пакетными переменными, глобальными пакетными курсорами и т. п.
Для процедур и функций в спецификации пакета присутствуют только заголовкиназвания процедур и функций и описания их параметров. В спецификации пакета нет блоков PL/SQL, реализующих логику процедур и функций, вся она находится в теле пакета. Можно считать, что спецификация пакета является интерфейсной частьюаналогом заголовочных файлов (header files), имеющихся, например, в языке программирования C++.
В теле пакета могут быть объявлены все те же виды программных элементов, что и в спецификации пакета с той лишь разницей, что они не будут доступны из других программ PL/SQL (не видны извне тела пакета). Эти элементы называются локальными пакетными переменными, локальными пакетными процедурами и т. п.
Локальные программные элементы предназначены исключительно для использования только процедурами и функциями самого пакета. Тем самым в PL/SQL реализовано сокрытие, то есть принцип проектирования программного обеспечения, заключающийся в разграничении доступа различных программ к внутренним компонентам друг друга. Подчеркнем, разграничивается доступ именно к внутренним компонентам.
Покажем области видимости объявленных в пакетах переменных и программ:
CREATE OR REPLACE PACKAGE pkg1 AS
g_var1глобальная пакетная переменная
видна и в теле пакета и снаружи (причем может изменяться снаружи)
g_var1 INTEGER;
глобальная пакетная процедура, видна и в теле пакета и снаружи
PROCEDURE proc1;
END;
CREATE OR REPLACE PACKAGE BODY pkg1 AS
локальная переменная, видна внутри тела пакета, снаружи не видна
l_var2 DATE;
локальная функция, видна внутри тела пакета, снаружи не видна
FUNCTION function1 RETURN VARCHAR2 IS
BEGIN
RETURN TO_CHAR(SYSDATE,'DD.MM.YYYY HH24:MI:SS');
END;
реализация в теле логики процедуры proc1, объявленной в спецификации
PROCEDURE proc1 IS
l_p_var3локальная переменная процедуры proc1
видна только внутри процедуры proc1
l_p_var3 VARCHAR2(2000);
BEGIN
l_p_var3 := function1||' '||to_char(l_var2)||to_char(g_var1);
END;
END;
Достоинства использования пакетов
Сформулируем достоинства использования пакетов PL/SQL при разработке серверной бизнес-логики:
упрощение сопровождения и расширения программ PL/SQL, так как пакеты обеспечивают инкапсуляцию кода и позволяют группировать логически связанные процедуры и функции;
разграничение доступа различных пакетов к внутренним компонентам друг друга;
сохранение данных сессии пользователя в глобальных пакетных переменных, в том числе повышение производительности приложений за счет кэширования постоянно использующихся в программах PL/SQL данных, например, справочников (данные кэшируются в коллекциях-глобальных переменных пакетов);
исключение жестко кодируемых литералов (hard-coded literals).
Хороший стиль программирования на PL/SQL предусматривает даже для небольших проектов наличие спецификаций пакетов, в которых объявлены
все пользовательские типы данных;
все константы и переменные, которые инициализируются жестко кодируемыми литералами, в том числе магическими числами;
все SQL-запросы в виде объявлений явных курсоров;
все пользовательские исключения.
Настоятельно рекомендуется все объявления программных элементов такого рода всех программ PL/SQL, реализующих серверную бизнес-логику системы, собрать в одной или нескольких спецификациях пакетов, а не «размазывать» объявления типов, исключений, констант и т. п. по всему коду или переписывать одну и ту же команду SELECT INTO в нескольких местах. Иногда даже создают отдельные спецификации пакетов только для объявлений типов, переменных, исключений и курсоров без объявлений процедур и функций. Для таких спецификаций изначально не планируется создавать тела пакетов.
Ни в коем случае не следует расставлять по всему исходному коду PL/SQL жестко кодируемые литералы. Например, если в коде в сорока местах для вычисления сумм «чистыми» использовать выражения вида ()*0.87, то когда ставка подоходного налога перестанет быть равной 13%, надо будет найти все сорок мест и заменить 0.87 на новое значение. А самое интересное начнется, если почти везде по коду поменять значение литерала на новое, а где-то забыть и оставить старое. Чтобы не заниматься всем этим, правильно один раз объявить в спецификации пакета константу
g_c_tax_percent INTEGER := 13;
и во всем коде в дальнейшем использовать только ее. Если потребуется внести изменения, то новое число нужно будет указать только в одном местев значении константы.
Отношения между спецификацией и телом пакета
Отношения между спецификацией и телом пакета описываются следующим образом:
сначала создается спецификация пакета, затем его тело;
тело пакета не может существовать без спецификации и даже не создастся DDL-командой CREATE PACKAGE BODY с выдачей сообщения об ошибке;
спецификация пакета без тела существовать может, на объявленные в ней глобальные пакетные процедуры и функции можно ссылаться из других программ PL/SQL (ошибка обращения к такому бестелесному пакету возникнет только на этапе выполнения);
при перекомпиляции спецификации пакета автоматически перекомпилируется его тело, при перекомпиляции тела пакета его спецификация не перекомпилируется;
при удалении спецификации пакета автоматически удаляется его тело, при удалении тела пакета с его спецификацией ничего не происходит.
Эти отношения между спецификацией и телом пакета имеют следующие положительные аспекты, которые особенно полезны при большом объеме кода на PL/SQL в крупных проектах:
возможность создания на этапе прототипирования «заглушек» пакетов без тел, реализация объявленных в их спецификациях процедур и функций будет осуществлена позже;