Основы языка PL/SQL: структура блока PL/SQL и область видимости

из чего состоит блок программы на языке PL/SQL OracleКаждый язык, будь то естественный или компьютерный, имеет определенный синтаксис, лексикон и набор символов. Чтобы общаться на этом языке, необходимо изучить правила его использования. Многие с опаской приступают к изучению новых компьютерных языков, но обычно они очень просты, и PL/SQL не является исключением. Трудности общения на компьютерных языках связаны не с самим языком, а с компилятором или компьютером, с которым мы «общаемся». Компиляторы не обладают творческим, гибким мышлением, а их лексикон крайне ограничен. Разве что соображают они очень, очень быстро... но только в рамках заданных правил.

Если приказать PL/SQL «подкинь-ка мне еще с полдюжины записей», едва ли вы получите требуемое. С точки зрения синтаксиса, для использования PL/SQL нужно расставлять все точки над «i». Поэтому в данной статье блога изложены основные правила языка, которые помогут вам общаться с компилятором PL/SQLструктура блоков PL/SQL.

 



Структура блока PL/SQL

В PL/SQL, как и в большинстве других процедурных языков, наименьшей единицей группировки кода является блок. Он представляет собой фрагмент программного кода, определяющий границы выполнения и области видимости для объявлений переменных и обработки исключений. PL/SQL позволяет создавать как именованные, так и анонимные блоки (то есть блоки, не имеющие имени), которые представляют собой пакеты, процедуры, функции, триггеры или объектные типы.

Блок PL/SQL может содержать до четырех разделов, однако только один из них является обязательным.

Структура блока PL/SQL для процедуры показана на рис. 1.

Структура блока PL/SQL 

Рис. 1. Структура блока PL/SQL

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

Процедура PL/SQL, содержащая все четыре раздела

Рис. 2. Процедура, содержащая все четыре раздела

 

Анонимные блоки PL/SQL

Когда кто-то хочет остаться неизвестным, он не называет своего имени. То же можно сказать и об анонимном блоке PL/SQL, показанном на рис. 3: в нем вообще нет раздела заголовка, блок начинается ключевым словом DECLARE (или BEGIN). Анонимный блок не может быть вызван из другого блока, поскольку он не имеет идентификатора, по которому к нему можно было бы обратиться. Таким образом, анонимный блок представляет собой контейнер для хранения команд PL/SQL — обычно с вызовами процедур и функций. Поскольку анонимные блоки могут содержать собственные разделы объявлений и исключений, разработчики часто используют вложение анонимных блоков для ограничения области видимости идентификаторов и организации обработки исключений в более крупных программах.

Анонимный блок без разделов объявлений и исключений

Рис. 3. Анонимный блок без разделов объявлений и исключений

Общий синтаксис анонимного блока PL/SQL: 

[ DECLARE ... объявления ... ]
BEGIN ... одна или несколько исполняемых команд ...
[ EXCEPTION
   ... команды обработки исключений ... ]
END;

Квадратными скобками обозначаются необязательные составляющие синтаксиса. Анонимный блок обязательно содержит ключевые слова BEGIN и END, и между ними должна быть как минимум одна исполняемая команда. Несколько примеров:


BEGIN
   DBMS_OUTPUT.PUT_LINE(SYSDATE);
END; 
     DECLARE
        l_right_now VARCHAR2(9);
     BEGIN
        l_right_now := SYSDATE;
        DBMS_OUTPUT.PUT_LINE (l_right_now);
     END; 
     DECLARE
        l_right_now VARCHAR2(9);
     BEGIN
        l_right_now := SYSDATE;
        DBMS_OUTPUT.PUT_LINE (l_right_now);
     EXCEPTION
        WHEN VALUE_ERROR
        THEN
        DBMS_OUTPUT.PUT_LINE('l_right_now не хватает места '
              || ' для стандартного формата даты');
     END; 

Анонимный блок выполняет серию команд, а затем завершает свою работу, то есть по сути является аналогом процедуры. Фактически каждый анонимный блок является анонимной процедурой. Они используются в различных ситуациях, в которых код PL/SQL выполняется либо непосредственно, либо как часть другой программы. Типичные примеры:

Во всех случаях контекст — и возможно, механизм присваивания имени — предоставляется внешним объектом (будь то триггер, программа командной строки или откомпилированная программа). 

 

Именованные блоки PL/SQL

Хотя анонимные блоки PL/SQL применяются во многих приложениях Oracle, вероятно, большая часть написанного вами кода будет оформлена в виде именованных блоков. Ранее вы уже видели несколько примеров хранимых процедур (см. рис. 1) и знаете, что их главной особенностью является наличие заголовка. Заголовок процедуры выглядит так: 

PROCEDURE [схема.]имя [ ( параметр [, параметр ... ] ) ]
[AUTHID {DEFINER | CURRENT_USER}]

Заголовок функции в целом очень похож на него, но дополнительно содержит ключевое слово RETURN:

FUNCTION [схема.]имя [ ( параметр [, параметр ... ] ) ]
RETURN возвращаемый_тип
[AUTHID {DEFINER | CURRENT_USER}]
[DETERMINISTIC]
[PARALLEL ENABLE ...]
[PIPELINED [USING...] | AGGREGATE USING...]

Поскольку Oracle позволяет вызывать некоторые функции из SQL-команд, заголовок функции содержит больше необязательных компонентов, чем заголовок процедуры (в зависимости от функциональности и производительности исполнительной среды SQL).

 

Вложенные блоки PL/SQL

PL/SQL, как и языки Ada и Pascal, относится к категории языков с блочной структурой, то есть блоки PL/SQL могут вкладываться в другие блоки. С другой стороны, язык C тоже поддерживает блоки, но стандартный C не является строго блочно-структурированным языком, потому что вложение подпрограмм в нем не допускается.

В следующем примере PL/SQL показана процедура, содержащая анонимный вложенный блок: 


PROCEDURE calc_totals
IS
   year_total NUMBER;
BEGIN
   year_total := 0;
   /* Начало вложенного блока */
   DECLARE
      month_total NUMBER;
   BEGIN
      month_total := year_total / 12;
   END set_month_total;
   /* Конец вложенного блока */
END;

Ограничители /* и */ обозначают начало и конец комментариев. Анонимные блоки также могут вкладываться более чем на один уровень (рис. 4).

Вложенные анонимные блоки PL/SQL

 Рис. 4. Вложенные анонимные блоки

Главное преимущество вложенных блоков заключается в том, что они позволяют ограничивать области видимости и действия синтаксических элементов кода.

 

Область действия в PL/SQL

В любом языке программирования областью действия (scope) называется механизм определения «сущности», обозначаемой некоторым идентификатором. Если программа содержит более одного экземпляра идентификатора, то используемый экземпляр определяется языковыми правилами области действия. Управление областью видимости идентификаторов не только помогает контролировать поведение программы, но и уменьшает вероятность того, что программист по ошибке изменит значение не той переменной.

В PL/SQL переменные, исключения, модули и некоторые другие структуры являются локальными для блока, в котором они объявлены. Когда выполнение блока будет завершено, все эти структуры становятся недоступными. Например, в приведенной выше процедуре calc_totals можно обращаться к элементам внешнего блока (например, к переменной year_total), тогда как элементы, объявленные во внутреннем блоке, для внешнего блока недоступны.

У каждой переменной PL/SQL имеется некоторая область действия — участок программы (блок, подпрограмма или пакет), в котором можно ссылаться на эту переменную. Рассмотрим следующее определение пакета:


PACKAGE scope_demo
IS
   g_global NUMBER;
   
   PROCEDURE set_global (number_in IN NUMBER);
END scope_demo;

PACKAGE BODY scope_demo
IS
   PROCEDURE set_global (number_in IN NUMBER)
   IS
      l_salary NUMBER := 10000;
      l_count PLS_INTEGER;
   BEGIN

      <>
      DECLARE
         l_inner NUMBER;
      BEGIN
         SELECT COUNT (*)
            INTO l_count
            FROM employees
         WHERE department_id = l_inner AND salary > l_salary;
      END local_block;
      
      g_global := number_in;
   END set_global;
END scope_demo;

Переменная scope_demo.g_global может использоваться в любом блоке любой схемы, обладающем привилегией EXECUTE для scope_demo.

Переменная l_salary может использоваться только в процедуре set_global.

Переменная l_inner может использоваться только в локальном или вложенном блоке; обратите внимание на использование метки local_block для присваивания имени вложенному блоку.

Блок программы PL/SQL: структура, операторы и видимость переменных 

Уточнение ссылок на переменные и столбцы в командах SQL

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

PACKAGE BODY scope_demo
IS
   PROCEDURE set_global (number_in IN NUMBER)
   IS
      l_salary NUMBER := 10000;
      l_count PLS_INTEGER;
   BEGIN
   
      <>
      DECLARE
         l_inner PLS_INTEGER;
      BEGIN
         SELECT COUNT (*)
            INTO set_global.l_count
            FROM employees e
         WHERE e.department_id = local_block.l_inner
            AND e.salary > set_global.l_salary;
      END local_block;     
      scope_demo.g_global := set_global.number_in;
   END set_global;
END scope_demo;

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

Итак, теперь вы знаете об этой возможности — но зачем тратить время на уточнение имен? Для этого есть несколько очень веских причин:

Давайте поближе рассмотрим первые две из этих причин.

 

Удобство чтения

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

Без уточнения ссылок вам будет намного сложнее различать переменные и столбцы. С уточнениями сразу видно, к чему относится та или иная ссылка.

«Один момент… Мы используем четко определенные схемы назначения имен, при помощи которых мы различаем строки и столбцы. Имена всех локальных переменных начинаются с „l_“, поэтому мы сразу видим, что идентификатор представляет локальную переменную».

Да, все правильно; все мы должны иметь (и соблюдать) правила назначения имен, чтобы имена идентификаторов содержали дополнительную информацию о них (что это — параметр, переменная? К какому типу данных она относится?).

Безусловно, правила назначения имен полезны, но они еще не гарантируют, что компилятор PL/SQL всегда будет интерпретировать ваши идентификаторы именно так, как вы задумали.

 

Предотвращение ошибок

Если не уточнять ссылки на переменные PL/SQL во встроенных командах SQL, код, который правильно работает сегодня, может внезапно утратить работоспособность в будущем. И разработчику будет очень трудно понять, что же пошло не так.

Вернемся к встроенной команде SQL без уточнения ссылок: 

SELECT COUNT (*)
   INTO l_count
   FROM employees
WHERE department_id = l_inner AND salary > l_salary;

Сегодня идентификатор l_salary однозначно представляет переменную l_salary, объявленную в процедуре set_global. Я тестирую свою программу — она работает! Программа поставляется клиентам, все довольны.

А через два года пользователи просят своего администратора базы данных добавить в таблицу employees столбец, которому по случайности присваивается имя «l_salary». Видите проблему?

Во встроенной команде SQL база данных Oracle всегда начинает поиск соответствия для неуточненных идентификаторов со столбцов таблиц. Если найти столбец с указанным именем не удалось, Oracle переходит к поиску среди переменных PL/SQL в области действия. После добавления в таблицу employee столбца l_salary моей неуточненной ссылке l_salary в команде SELECT ставится в соответствие не переменная PL/SQL, а столбец таблицы. Результат?

Пакет scope_demo по-прежнему компилируется без ошибок, но секция WHERE запроса ведет себя не так, как ожидалось. База данных не использует значение переменной l_salary, а сравнивает значение столбца salary в строке таблицы employees со значением столбца l_salary той же строки. Отыскать подобную ошибку бывает очень непросто!

Не полагайтесь только на правила назначения имен для предотвращения «коллизий» между идентификаторами; уточняйте ссылки на все имена столбцов и переменных во встроенных командах SQL. Это существенно снизит риск непредсказуемого поведения программ в будущем при возможных модификациях таблиц.

 

Видимость переменных PL/SQL

Важным свойством переменной, связанным с областью ее действия, является видимость. Данное свойство определяет, можно ли обращаться к переменной только по ее имени, или же к имени необходимо добавлять префикс.

 

«Видимые» идентификаторы

Начнем с тривиального случая:


DECLARE
   first_day DATE;
   last_day DATE;
BEGIN
   first_day := SYSDATE;
   last_day := ADD_MONTHS (first_day, 6);
END;

Обе переменные first_day и last_day объявляются в том же блоке, где они используются, поэтому при обращении к ним указаны только имена без уточняющих префиксов. Такие идентификаторы называются видимыми. В общем случае видимым идентификатором может быть:

 

Уточненные идентификаторы

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

Дополнительное уточнение может определять владельца объекта. Например, выражение

scott.price_util.compute_means 

обозначает процедуру compute_means пакета price_util, принадлежащего пользователю Oracle с учетной записью scott.

 

Уточнение идентификаторов именами модулей

PL/SQL предоставляет несколько способов уточнения идентификаторов для логического разрешения ссылок. Так, использование пакетов позволяет создавать переменные с глобальной областью действия. Допустим, имеется пакет company_pkg и в спецификации пакета объявлена переменная с именем last_company_id


PACKAGE company_pkg
IS
   last_company_id NUMBER;
   ...
END company_pkg;

На переменную можно ссылаться за пределами пакета — необходимо лишь указать перед ее именем имя пакета:

IF new_company_id = company_pkg.last_company_id THEN 

По умолчанию значение, присвоенное переменной пакетного уровня, продолжает действовать на протяжении текущего сеанса базы данных; оно не выходит из области действия вплоть до разрыва подключения.

Идентификатор также можно уточнить именем модуля, в котором он определен:


PROCEDURE calc_totals
IS
   salary NUMBER;
BEGIN
   ...
   DECLARE
      salary NUMBER;
   BEGIN
      salary := calc_totals.salary;
   END;
   ...
END;

В первом объявлении создается переменная salary, областью действия которой является вся процедура. Однако затем во вложенном блоке объявляется другой идентификатор с тем же именем. Поэтому ссылка на переменную salary во внутреннем блоке всегда сначала разрешается по объявлению в этом блоке, где переменная видима безо всяких уточнений. Чтобы во внутреннем блоке обратиться к переменной salary, объявленной на уровне процедуры, необходимо уточнить ее имя именем процедуры (cal_totals.salary).

Этот метод уточнения идентификаторов работает и в других контекстах. Что произойдет при выполнении следующей процедуры (order_id — первичный ключ таблицы orders): 


PROCEDURE remove_order (order_id IN NUMBER)
IS
BEGIN
   DELETE orders WHERE order_id = order_id; -- Катастрофа!
END;

Этот фрагмент удалит из таблицы orders все записи независимо от переданного значения order_id. Дело в том, что механизм разрешения имен SQL сначала проверяет имена столбцов и только потом переходит к идентификаторам PL/SQL. Условие WHERE (order_id = order_id) всегда истинно, поэтому все данные пропадают.

Возможное решение проблемы выглядит так: 


PROCEDURE remove_order (order_id IN NUMBER)
IS
BEGIN
   DELETE orders WHERE order_id = remove_order.order_id;
END;

В этом случае при разборе имя переменной будет интерпретировано правильно. (Решение работает даже при наличии в пакете функции с именем remove_order.order_id.)

В PL/SQL установлен целый ряд правил разрешения конфликтов имен, а этой проблеме уделяется серьезное внимание. И хотя знать эти правила полезно, лучше использовать уникальные идентификаторы, чтобы избежать подобных конфликтов. Старайтесь писать надежный код! Если же вы не хотите уточнять каждую переменную, чтобы обеспечить ее уникальность, вам придется тщательно проработать схему назначения имен для предотвращения подобных конфликтов.

 

Вложенные программы

Завершая тему вложения, области действия и видимости, стоит упомянуть о такой полезной возможности PL/SQL, как вложенные программы (nested programs). Вложенная программа представляет собой процедуру или функцию, которая полностью размещается в разделе объявлений внешнего блока. Вложенная программа может обращаться ко всем переменным и параметрам, объявленным ранее во внешнем блоке, как показывает следующий пример: 


PROCEDURE calc_totals (fudge_factor_in IN NUMBER)
IS
   subtotal NUMBER := 0;
   /* Начало вложенного блока (в данном случае процедуры).
   | Обратите внимание: процедура полностью размещается
   | в разделе объявлений calc_totals.
   */
   PROCEDURE compute_running_total (increment_in IN PLS_INTEGER)
   IS
   BEGIN
      /* Переменная subtotal (см. выше) видима и находится в области действия */
      subtotal := subtotal + increment_in * fudge_factor_in;
   END;
   /* Конец вложенного блока */
BEGIN
   FOR month_idx IN 1..12
   LOOP
      compute_running_total (month_idx);
   END LOOP;
   DBMS_OUTPUT.PUT_LINE('Годовой итог: ' || subtotal);
END;

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

Вас заинтересует / Intresting for you:

Встроенные методы коллекций PL...
Встроенные методы коллекций PL... 14738 просмотров sepia Tue, 29 Oct 2019, 09:54:01
Управление приложениями PL/SQL...
Управление приложениями PL/SQL... 4633 просмотров Stas Belkov Thu, 16 Jul 2020, 06:20:48
Основы языка PL/SQL: использов...
Основы языка PL/SQL: использов... 4693 просмотров Ирина Светлова Tue, 06 Feb 2018, 14:04:03
Работа с числами в PL/SQL на п...
Работа с числами в PL/SQL на п... 44825 просмотров Antoniy Mon, 28 May 2018, 16:45:11
Печать
Войдите чтобы комментировать

OraCool аватар
OraCool ответил в теме #9504 4 года 5 мес. назад

Myk пишет: Соглашусь с VaaPa. Грамотное изложение материала и хорошее оформление всегда радует глаз и стимулирует Моцк!))

Полностью разделяю мнение!)
Gwen аватар
Gwen ответил в теме #9265 5 года 5 мес. назад
Разжевали все до мелочей! Научиться программированию на PL/SQL не так и сложно, как казалось! ;-)
apv аватар
apv ответил в теме #9076 5 года 9 мес. назад
Хорошая статейка, спору нет! :-)
Myk аватар
Myk ответил в теме #9075 5 года 9 мес. назад
Соглашусь с VaaPa. Грамотное изложение материала и хорошее оформление всегда радует глаз и стимулирует Моцк!))
VaaPa аватар
VaaPa ответил в теме #8964 6 года 2 нед. назад
Потрудились видно, что изрядно. Зато и результат впечатляет. Не встречал еще более вменяемой, понятной и легко читаемой вводной статьи по языку программирования PL/SQL. Зачетно в общем!! :-)