Для передачи информации между модулем и вызывающим блоком PL/SQL используются параметры.
Параметры модуля, являющиеся частью его заголовка (или сигнатуры), являются не менее важными компонентами модуля, чем находящиеся в нем исполняемые команды. Заголовок программы иногда называется контрактом между автором и пользователями. Конечно, автор должен позаботиться о том, чтобы модуль выполнял свою задачу. Но ведь модули создаются именно для того, чтобы их можно было вызывать повторно — в идеале более чем из одного модуля. Если список параметров плохо составлен, то другим программистам будет сложно применять такой модуль. В таком случае уже будет неважно, насколько хорошо он написан.
Многие разработчики не уделяют должного внимания наборам параметров своих модулей. Следующие рекомендации помогут вам сделать правильный выбор:
- Количество параметров. Если процедура или функция имеет слишком мало параметров, это снижает ее универсальность; с другой стороны, слишком большое количество параметров усложняет ее повторное использование. Конечно, количество параметров в основном определяется требованиями программы, но существуют разные варианты их определения (например, несколько параметров можно объединить в одну запись).
- Типы параметров. При выборе типа необходимо учитывать, для каких целей будут использоваться параметры: только для чтения, только для записи, для чтения и записи.
- Имена параметров. Параметрам следует присваивать простые имена, отражающие их назначение в модуле.
- Значения по умолчанию. Когда параметру следует задать значение по умолчанию, а когда нужно заставить программиста ввести определенное значение?
PL/SQL предоставляет много средств эффективного планирования. В этом разделе представлены все элементы определения параметров.
Определение параметров
Формальные параметры определяются в списке параметров программы. Синтаксис определения параметров очень близок к синтаксису объявления переменных в разделе объявлений блока PL/SQL. Впрочем, существуют два важных различия: во-первых, для параметров определяется режим передачи, которого нет у объявлений переменных; во-вторых, объявление параметра должно быть неограниченным.
Ограниченное объявление устанавливает некоторые ограничения для значений, которые могут присваиваться переменной, объявленной с этим типом. Например, следующее объявление переменной company_name ограничивает переменную 60 символами:
DECLARE
company_name VARCHAR2(60);
При объявлении параметра ограничивающая часть опускается:
PROCEDURE display_company (company_name IN VARCHAR2) IS ...
Формальные и фактические параметры
Очень важно понимать различия между формальными и фактическими параметрами. Формальные параметры представляют собой имена, объявленные в списке параметров заголовка модуля, тогда как фактические параметры — это значения и выражения, которые помещаются в список параметров при вызове модуля.
Различия между формальными и фактическими параметрами нам также поможет понять функция total_sales. Ее заголовок выглядит так:
FUNCTION total_sales
(company_id_in IN company.company_id%TYPE,
status_in IN order.status_code%TYPE DEFAULT NULL)
RETURN std_types.dollar_amount;
Формальные параметры total_sales:
company_id_in — первичный ключ (идентификатор компании).
status_in — статус заказов, включаемых в вычисление.
Эти формальные параметры не существуют за пределами модуля. Их можно рассматривать как своего рода «заместителей» реальных значений, передаваемых модулю при его использовании в программе (то есть фактических параметров).
При вызове total_sales необходимо предоставить два аргумента, которыми могут быть переменные, константы или литералы (для параметров в режимах OUT и IN OUT это должны быть переменные). В следующем примере переменная company_id содержит первичный ключ, указывающий на запись компании. В первых трех вызовах total_sales функции передаются жестко запрограммированные значения статуса заказов, а в последнем вызове статус не указан; в этом случае функция присваивает параметру status_in значение по умолчанию, указанное в заголовке:
new_sales := total_sales (company_id, 'N');
paid_sales := total_sales (company_id, 'P');
shipped_sales := total_sales (company_id, 'S');
all_sales := total_sales (company_id);
При вызове total_sales вычисляются значения всех фактических параметров. Результаты вычислений присваиваются соответствующим формальным параметрам внутри функции (обратите внимание: это относится только к параметрам IN и IN OUT; параметры режима OUT не копируются).
Формальные параметры и соответствующие им фактические параметры (указанные при вызове) должны относиться к одинаковым или совместимым типам данных. Во
многих ситуациях PL/SQL выполняет преобразования данных автоматически, однако лучше по возможности обходиться без неявных преобразований. Используйте такие функции, как TO_CHAR и TO_DATE, чтобы точно знать, какие данные получает модуль.
Режимы передачи параметров
При определении параметра также указывается допустимый способ его применения. Он задается с помощью одного из трех указанных в таблице режимов.
| Режим | Предназначение | Использование параметров |
| IN | Только для чтения | Значение параметра может применяться, но не может быть изменено в модуле. Если режим параметра не задан, используется режим IN |
| OUT | Только для записи | В модуле можно присвоить значение параметру, но нельзя использовать его. Впрочем, это «официальное» определение — на самом деле Oracle позволяет читать значение параметра OUT в подпрограмме |
| IN OUT | Для чтения и записи | В модуле можно использовать и изменять значение параметра |
Режим параметра указывается непосредственно после его имени, но перед типом данных и необязательным значением по умолчанию. В следующем заголовке процедуры задействованы все три режима передачи параметров:
PROCEDURE predict_activity (
last_date_in IN DATE,
task_desc_inout IN OUT VARCHAR2,
next_date_out OUT DATE)Процедура predict_activity принимает дату последнего действия (last_date) и описание этого действия (task_desc_inout). Возвращает она два значения: описание действия (возможно, модифицированное) и дату следующего действия (next_date_out). Поскольку параметр task_desk_inout передается в режиме IN OUT, программа может читать и изменять его значение.
Режим IN
Параметр IN позволяет передавать значения модулю, но не может использоваться для передачи информации из модуля вызывающему блоку PL/SQL. Другими словами, в контексте вызываемой программы параметры IN работают как константы. Значение формального параметра IN, как и значение константы, не может быть изменено внутри программы. Параметру IN нельзя присвоить новое значение или изменить его иным образом — компилятор выдаст сообщение об ошибке.
Режим IN используется по умолчанию; если режим параметра не задан, параметр автоматически считается определенным в режиме IN. Тем не менее я рекомендую всегда указывать режим параметра, чтобы предполагаемое использование было явно указано в коде.
В заголовке программы параметрам IN могут присваиваться значения по умолчанию (см. раздел «Значения по умолчанию»).
Фактическим значением параметра IN может быть переменная, именованная константа, литерал или сложное выражение. Все следующие вызовы display_title допустимы:
DECLARE
happy_title CONSTANT VARCHAR2(30) := 'HAPPY BIRTHDAY';
changing_title VARCHAR2(30) := 'Happy Anniversary';
spc CONSTANT VARCHAR2(1) := CHR(32); -- ASCII-код пробела
BEGIN
display_title ('Happy Birthday'); -- Литерал
display_title (happy_title); -- Константа
changing_title := happy_title;
display_title (changing_title); -- Переменная
display_title ('Happy' || spc || 'Birthday'); -- Выражение
display_title (INITCAP (happy_title)); -- Другое выражение
END;А если вам потребуется передать данные из своей программы? В таком случае используйте параметр OUT или IN OUT — или рассмотрите возможность преобразования процедуры в функцию.
Режим OUT
Как вы, вероятно, уже поняли, параметр OUT по смыслу противоположен параметру IN. Параметры OUT могут использоваться для возвращения значений из программы вызывающему блоку PL/SQL. Параметр OUT сходен с возвращаемым значением функции, но он включается в список параметров, и количество таких параметров не ограничено (строго говоря, PL/SQL разрешает использовать до 64 000 параметров, но на практике это вряд ли можно считать ограничением).
В программе параметр OUT работает как неинициализированная переменная. Собственно, параметр OUT вообще не содержит никакого значения до успешного завершения вызванной программы (если только вы не использовали ключевое слово NOCOPY. Во время выполнения программы все операции присваивания параметру OUT в действительности выполняются с внутренней копией параметра. Когда программа успешно завершается и возвращает управление вызывающему блоку, значение локальной копии перемещается в параметр OUT. После этого значение становится доступным в вызывающем блоке PL/SQL.
У правил, относящихся к параметрам OUT, есть несколько практических следствий:
- Параметрам OUT нельзя задавать значения по умолчанию. Значение параметра OUT может задаваться только в теле модуля.
- Все операции присваивания параметрам OUT отменяются при инициировании исключения в программе. Так как значение параметра OUT присваивается только в случае успешного завершения программы, все промежуточные присваивания игнорируются. Если обработчик не перехватит исключение и не присвоит значение параметру OUT, параметр останется неизменным. Переменная сохранит значение, которое она имела до вызова программы.
- Фактический параметр, соответствующий формальному параметру OUT, не может быть константой, литералом или выражением. Иначе говоря, он должен поддерживать присваивание.
Как упоминается выше в таблице, Oracle позволяет прочитать значение параметра OUT в подпрограмме. Это значение изначально всегда равно NULL, но после его присваивания в подпрограмме оно становится «видимым», как показывает следующий сценарий:
SQL> set serveroutput on
SQL> CREATE OR REPLACE PROCEDURE read_out (n OUT NUMBER)
2 IS
3 BEGIN
4 DBMS_OUTPUT.put_line ('n initial=' || n);
5 n := 1;
6 DBMS_OUTPUT.put_line ('n after assignment=' || n);
7 END;
8 /
Procedure created.
SQL> DECLARE
2 n NUMBER;
3 BEGIN
4 read_out (n);
5 END;
6 /
n initial=
n after assignment=1
Режим IN OUT
В параметре IN OUT можно передавать значения программе и возвращать их на сторону вызова (либо исходное, неизменное значение, либо новое значение, заданное в программе). На параметры IN OUT распространяются два ограничения параметров OUT:
- Параметр
IN OUTне может быть константой, литералом или выражением. - Фактический параметр
IN OUTдолжен быть переменной. Он не может быть константой, литералом или выражением, потому что эти форматы не могут использоватьсяPL/SQLв качестве приемника для размещения исходящих значений.
Других ограничений для параметров IN OUT нет.
Параметры IN OUT могут использоваться в обеих сторонах присваивания, потому что они работают как инициализированные переменные. PL/SQL не теряет значение параметра IN OUT в начале выполнения программы. Это значение может использоваться в программе там, где это необходимо.
Процедура combine_and_format_names объединяет имя и фамилию в заданном формате («LAST, FIRST» или «FIRST LAST»). Процедура получает имя и фамилию, которые преобразуются к верхнему регистру. В ней продемонстрированы все три режима параметров:
PROCEDURE combine_and_format_names (
first_name_inout IN OUT VARCHAR2,
last_name_inout IN OUT VARCHAR2,
full_name_out OUT VARCHAR2,
name_format_in IN VARCHAR2 := 'LAST, FIRST')
IS
BEGIN
/* Преобразование имени и фамилии к верхнему регистру. */
first_name_inout := UPPER (first_name_inout);
last_name_inout := UPPER (last_name_inout);
/* Объединение имени и фамилии в соответствии с форматной строкой. */
IF name_format_in = 'LAST, FIRST'
THEN
full_name_out := last_name_inout || ', ' || first_name_inout;
ELSIF name_format_in = 'FIRST LAST'
THEN
full_name_out := first_name_inout || ' ' || last_name_inout;
END IF;
END combine_and_format_names;
Параметры имени и фамилии должны задаваться в режиме IN OUT. Параметр full_name_ out должен быть параметром OUT, потому что процедура возвращает результат объединения имени и фамилии. Наконец, параметр name_format_in, содержащий форматную строку, объявляется в режиме IN, потому что он описывает способ форматирования, но никак не изменяется в процедуре.
Каждый режим параметров имеет свои характеристики и предназначение. Тщательно выбирайте режим, назначаемый вашим параметрам, чтобы они правильно использовались в модулях.
Определяйте формальные параметры в режимах
OUTиIN OUTтолько в процедурах. Функции должны возвращать всю свою информацию исключительно командойRETURN. Выполнение этой рекомендации упростит понимание и использование ваших подпрограмм. Кроме того, функции с параметрамиOUTиIN OUTне могут вызываться из командSQL.
Связывание формальных и фактических параметров в PL/SQL
Каким образом при выполнении программы определяется, какому формальному параметру должен соответствовать фактический параметр? PL/SQL предлагает два метода установления такого соответствия:
- по позиции (неявное связывание);
- по имени (явное связывание с указанием имени формального параметра и обозначения = >).
Позиционное связывание
Во всех приводившихся ранее примерах применялось связывание параметров в соответствии с их позицией. При использовании этого способа PL/SQL принимает во внимание относительные позиции параметров, то есть N-й фактический параметр в вызове программы связывается с N-м формальным параметром в заголовке программы. В следующем примере PL/SQL связывает первый фактический параметр :order. company_id с первым формальным параметром company_id_in, а второй фактический параметр 'N' — со вторым формальным параметром status_in:
new_sales := total_sales (:order.company_id, 'N');
FUNCTION total_sales
(company_id_in IN company.company_id%TYPE,
status_in IN order.status_code%TYPE := NULL)
RETURN std_types.dollar_amount;
Метод позиционного связывания параметров, безусловно, является более наглядным и понятным (рис. 3).
Рис. 3. Позиционное связывание параметров
связывание по имени
Чтобы установить соответствие параметров по имени, следует при вызове подпрограммы явно связать формальный параметр с фактическим. Для этого используется комбинация символов =>:
имя_формального_параметра => значение_параметраПоскольку имя формального параметра указано явно, PL/SQL уже не нужно учитывать порядок параметров. Поэтому в данном случае при вызове модуля не обязательно перечислять параметры в порядке их следования в заголовке. Функцию total_sales можно вызывать любым из двух способов:
new_sales :=
total_sales (company_id_in => order_pkg.company_id, status_in =>'N');
new_sales :=
total_sales (status_in =>'N', company_id_in => order_pkg.company_id);
В одном вызове можно комбинировать оба метода связывания фактических и формальных параметров:
:order.new_sales := total_sales (order_pkg.company_id, status_in =>'N');При этом все «позиционные» параметры должны быть перечислены перед «именованными», как в приведенном примере. Позиционному методу необходима начальная точка для отсчета позиций, которой может быть только первый параметр. Если разместить «именованные» параметры перед «позиционными», PL/SQL собьется со счета. Оба вызова total_sales, приведенные ниже, недопустимы. В первой команде «именованные» параметры указываются до «позиционных», а во второй используется позиционная запись, но параметры перечислены в неверном порядке. В этом случае PL/SQL пытается преобразовать 'N' к типу NUMBER (для параметра company_id):
l_new_sales := total_sales (company_id_in => order_pkg.company_id, 'N');
l_new_sales := total_sales ('N', company_id_in => order_pkg.company_id);
Преимущества связывания по имени
Возникает резонный вопрос: зачем использовать связывание по имени? У него есть два основных преимущества:
- Повышение информативности. При использовании связывания по имени вызов программы содержит сведения о формальных параметрах, с которыми ассоциируются фактические значения. Если вы не знаете, что делают модули, вызываемые приложением, наличие имен формальных параметров помогает понять назначение конкретного программного вызова. В некоторых средах разработки именно по этой причине связывание по имени считается стандартным. Эта причина особенно актуальна в том случае, когда имена формальных параметров строятся по схеме с присоединением суффикса режима. Только взглянув на вызов процедуры или функции, разработчик сразу видит, в каком направлении передаются те или иные данные.
- Гибкость при описании параметров. Вы имеете возможность размещать параметры в удобном для работы порядке (конечно, это не значит, что при вызове аргументы можно перечислять так, как вам заблагорассудится!) и включать в список только те из них, которые действительно необходимы. В сложных приложениях иногда создаются процедуры с десятками параметров. Но как вы знаете, любой параметр, значение которого определяется по умолчанию, при вызове модуля может быть опущен. Разработчик может передать процедуре только те значения, которые необходимы для решения текущей задачи.
Давайте посмотрим, как реализовать все эти преимущества в программе. Возьмем для примера следующий заголовок:
PROCEDURE business_as_usual (
advertising_budget_ in IN NUMBER
, contributions_inout IN OUT NUMBER
, merge_and_purge_on_in IN DATE DEFAULT SYSDATE
, obscene_ceo_bonus_out OUT NUMBER
, cut_corners_in IN VARCHAR2 DEFAULT 'WHENEVER POSSIBLE'
);Анализ списка параметров приводит нас к следующим выводам:
- Минимальное количество аргументов, которые должны передаваться
business_as_ usual, равно 3. Чтобы определить его, сложите количество параметров IN, не имеющих значения по умолчанию, с количеством параметров OUT и IN OUT. - При использовании связывания по позиции программа может вызываться с четырьмя или пятью аргументами, потому что последний параметр объявлен в режиме IN со значением по умолчанию.
- Для хранения значений, возвращаемых параметрами OUT и IN OUT, понадобятся как минимум две переменные.
С таким списком параметров программа может вызываться несколькими способами:
- Связывание только по позиции: задаются все фактические параметры. Обратите внимание, как трудно вспомнить, какой формальный параметр связывается с каждым из этих значений (и каков его смысл):
DECLARE
l_ceo_payoff NUMBER;
l_lobbying_dollars NUMBER := 100000;
BEGIN
/* Позиционная запись */
business_as_usual (50000000
, l_lobbying_dollars
, SYSDATE + 20
, l_ceo_payoff
, 'PAY OFF OSHA'
);
- Связывание только по позиции: минимальное количество фактических параметров. Понять смысл вызова все еще нелегко:
business_as_usual (50000000
, l_lobbying_dollars
, SYSDATE + 20
, l_ceo_payoff
);
- Связывание только по имени с сохранением исходного порядка. Обратите внимание: смысл вызова business_as_usual вполне понятен и не требует пояснений:
business_as_usual
(advertising_budget_in => 50000000
, contributions_inout => l_lobbying_dollars
, merge_and_purge_on_in => SYSDATE
, obscene_ceo_bonus_out => l_ceo_payoff
, cut_corners_in => 'DISBAND OSHA'
);- Все параметры IN пропускаются, и для них используются значения по умолчанию (другая важная возможность связывания по имени):
business_as_usual
(advertising_budget_in => 50000000
, contributions_inout => l_lobbying_dollars
, obscene_ceo_bonus_out => l_ceo_payoff
);
- Связывание по имени с изменением порядка следования фактических параметров и передачей неполного списка:
business_as_usual
(obscene_ceo_bonus_out => l_ceo_payoff
, merge_and_purge_on_in => SYSDATE
, advertising_budget_in => 50000000
, contributions_inout => l_lobbying_dollars
);- Объединение связывания по имени и по позиции. Список параметров начинается со связывания по позиции, а после перехода на связывание по имени вернуться к позиционному связыванию уже не удастся:
business_as_usual
(50000000
, l_lobbying_dollars
, merge_and_purge_on_in => SYSDATE
, obscene_ceo_bonus_out => l_ceo_payoff
);
Как видите, механизм передачи аргументов в PL/SQL весьма гибок. Как правило, связывание по имени способствует написанию кода, который лучше читается и создает меньше проблем с сопровождением. От вас потребуется совсем немного: найти и записать имена параметров.
Квалификатор режима параметра NOCOPY
PL/SQL предоставляет возможность изменить определение параметра при помощи конструкции NOCOPY. NOCOPY требует, чтобы компилятор PL/SQL не создавал копии аргументов OUT и IN — и в большинстве случаев компилятор это требование удовлетворяет. NOCOPY используется прежде всего для повышения эффективности передачи больших конструкций (например, коллекций) в аргументах IN OUT.
Значения по умолчанию
Как было показано в предыдущих примерах, параметрам IN можно задать значения по умолчанию. Если параметр IN имеет значение по умолчанию, включать этот параметр в вызов программы не обязательно. Значение по умолчанию параметра вычисляется и используется программой только в том случае, если параметр не был включен в список при вызове. Конечно, для всех параметров IN OUT фактические параметры должны включаться в список. Значение по умолчанию определяется для параметра так же, как для объявляемой переменной. Предусмотрены два способа задания значений по умолчанию — с ключевым словом DEFAULT и с оператором присваивания (:=):
PROCEDURE astrology_reading
(sign_in IN VARCHAR2 := 'LIBRA',
born_at_in IN DATE DEFAULT SYSDATE) IS
Значения по умолчанию позволяют вызывать программы с разным количеством фактических параметров. Программа использует значения по умолчанию для всех незаданных параметров, а для параметров в списке значения по умолчанию заменяются указанными значениями. Несколько примеров разных вариантов использования связывания по позиции:
BEGIN
astrology_reading ('SCORPIO',
TO_DATE ('12-24-2009 17:56:10', 'MM-DD-YYYY HH24:MI:SS'));
astrology_reading ('SCORPIO');
astrology_reading;
astrology_reading();
END;
В первом вызове оба параметра задаются явно. Во втором вызове задается только первый фактический параметр, поэтому born_at_in присваивается текущая дата и время. В третьем вызове параметры вообще не указаны, поэтому круглые скобки отсутствуют (то же относится и к последнему вызову, в который включены пустые круглые скобки). Оба значения по умолчанию используются в теле процедуры.
Чтобы пропустить начальные параметры, имеющие значения по умолчанию, необходимо использовать связывание по имени. Включая имена формальных параметров, можно указать только те параметры, для которых необходимо передать значения:
BEGIN
astrology_reading (
born_at_in =>
TO_DATE ('12-24-2009 17:56:10', 'MM-DD-YYYY HH24:MI:SS'));
END;
