Что такое неименованный блок oracle
Oracle/PLSQL оператор EXECUTE IMMEDIATE подготавливает (анализирует) и немедленно выполняет динамический SQL-запрос или анонимный PL/SQL блок.
Основным аргументом EXECUTE IMMEDIATE является строка, содержащая SQL-запрос для выполнения. Вы можете создать строку, используя конкатенацию, или использовать предопределенную строку.
Динамическая строка может содержать любой оператор SQL (без последней точки с запятой), за исключением многострочных запросов или любой PL/SQL блок (с последней точкой с запятой).
Строка dynamic_string также может содержать заполнители, произвольные имена, которым предшествует двоеточие, для аргументов связывания bind_argument . В этом случае вы указываете, какие переменные PL/SQL соответствуют заполнителям, с помощью операторов INTO, USING и RETURNING INTO. Во время выполнения аргументы связывания заменяют соответствующие заполнители в динамической строке. Каждый заполнитель должен быть связан с аргументом связывания в предложении USING и/или предложении RETURNING INTO.
Обработка ошибок
В PL/SQL любая ошибка или предупреждение называется исключением (exception). В PL/SQL есть кое-какие определенные внутренне ошибки, но также допускается определять и свои собственные. При возникновении любой ошибки инициируется исключение, и управление переходит в отвечающий за обработку исключений раздел программы PL/SQL. В случае определения своих собственных ошибочных ситуаций необходимо обеспечивать инициирование исключений за счет применения специального оператора RAISE.
Ниже приведен пример использования оператора RAISE для обработки исключений:
Уточнение ссылок на переменные и столбцы в командах SQL
Ссылки на переменные и столбцы в предыдущем примере не уточнялись именами области действия. Далее приводится другая версия того же пакета, но на этот раз с уточнением ссылок (выделены полужирным шрифтом):
В новой версии каждая ссылка на столбец и переменную уточняется псевдонимом таблицы, именем пакета, именем процедуры или меткой вложенного блока.
Итак, теперь вы знаете об этой возможности — но зачем тратить время на уточнение имен? Для этого есть несколько очень веских причин:
- Удобство чтения кода.
- Предотвращение ошибок, возникающих при совпадении имен переменных с именами столбцов.
- Возможность использования детализированных зависимостей появилась в Oracle11g.
Давайте поближе рассмотрим первые две из этих причин.
Область действия в PL/SQL
В любом языке программирования областью действия (scope) называется механизм определения «сущности», обозначаемой некоторым идентификатором. Если программа содержит более одного экземпляра идентификатора, то используемый экземпляр определяется языковыми правилами области действия . Управление областью видимости идентификаторов не только помогает контролировать поведение программы, но и уменьшает вероятность того, что программист по ошибке изменит значение не той переменной.
В PL/SQL переменные, исключения, модули и некоторые другие структуры являются локальными для блока, в котором они объявлены. Когда выполнение блока будет завершено, все эти структуры становятся недоступными. Например, в приведенной выше процедуре calc_totals можно обращаться к элементам внешнего блока (например, к переменной year_total), тогда как элементы, объявленные во внутреннем блоке, для внешнего блока недоступны.
У каждой переменной PL/SQL имеется некоторая область действия — участок программы (блок, подпрограмма или пакет), в котором можно ссылаться на эту переменную. Рассмотрим следующее определение пакета:
Переменная scope_demo.g_global может использоваться в любом блоке любой схемы, обладающем привилегией EXECUTE для scope_demo.
Переменная l_salary может использоваться только в процедуре set_global.
Переменная l_inner может использоваться только в локальном или вложенном блоке; обратите внимание на использование метки local_block для присваивания имени вложенному блоку.
Написание исполняемых операторов PL/SQL
После оператора BEGIN можно начинать вводить все свои желаемые SQL-операторы. Выглядеть эти операторы должны точно так же, как обычные операторы SQL. При использовании операторов SELECT и INSERT в PL/SQL, правда, необходимо помнить об особенностях, о которых более подробно речь пойдет в следующих разделах.
Предотвращение ошибок
Если не уточнять ссылки на переменные PL/SQL во встроенных командах SQL, код, который правильно работает сегодня, может внезапно утратить работоспособность в будущем. И разработчику будет очень трудно понять, что же пошло не так.
Вернемся к встроенной команде SQL без уточнения ссылок:
Сегодня идентификатор 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
В PL/SQL, как и в большинстве других процедурных языков, наименьшей единицей группировки кода является блок. Он представляет собой фрагмент программного кода, определяющий границы выполнения и области видимости для объявлений переменных и обработки исключений. PL/SQL позволяет создавать как именованные, так и анонимные блоки (то есть блоки, не имеющие имени), которые представляют собой пакеты, процедуры, функции, триггеры или объектные типы.
Блок PL/SQL может содержать до четырех разделов, однако только один из них является обязательным.
- Заголовок. Используется только в именованных блоках, определяет способ вызова именованного блока или программы. Не обязателен.
- Раздел объявлений. Содержит описания переменных, курсоров и вложенных блоков, на которые имеются ссылки в исполняемом разделе и разделе исключений. Не обязателен.
- Исполняемый раздел. Команды, выполняемые ядром PL/SQL во время работы приложения. Обязателен.
- Раздел исключений. Обрабатывает исключения (предупреждения и ошибки). Не обязателен.
Структура блока PL/SQL для процедуры показана на рис. 1.
Рис. 1. Структура блока PL/SQL
На рис. 2 показана процедура, содержащая все четыре раздела. Этот конкретный блок начинается с ключевого слова PROCEDURE и, как и все блоки, завершается ключевым словом END.
Рис. 2. Процедура, содержащая все четыре раздела
Уточнение идентификаторов именами модулей
PL/SQL предоставляет несколько способов уточнения идентификаторов для логического разрешения ссылок. Так, использование пакетов позволяет создавать переменные с глобальной областью действия. Допустим, имеется пакет company_pkg и в спецификации пакета объявлена переменная с именем last_company_id:
На переменную можно ссылаться за пределами пакета — необходимо лишь указать перед ее именем имя пакета:
По умолчанию значение, присвоенное переменной пакетного уровня, продолжает действовать на протяжении текущего сеанса базы данных; оно не выходит из области действия вплоть до разрыва подключения.
Идентификатор также можно уточнить именем модуля, в котором он определен:
В первом объявлении создается переменная salary, областью действия которой является вся процедура. Однако затем во вложенном блоке объявляется другой идентификатор с тем же именем. Поэтому ссылка на переменную salary во внутреннем блоке всегда сначала разрешается по объявлению в этом блоке, где переменная видима безо всяких уточнений. Чтобы во внутреннем блоке обратиться к переменной salary, объявленной на уровне процедуры, необходимо уточнить ее имя именем процедуры (cal_totals.salary).
Этот метод уточнения идентификаторов работает и в других контекстах. Что произойдет при выполнении следующей процедуры (order_id — первичный ключ таблицы orders):
Этот фрагмент удалит из таблицы orders все записи независимо от переданного значения order_id. Дело в том, что механизм разрешения имен SQL сначала проверяет имена столбцов и только потом переходит к идентификаторам PL/SQL. Условие WHERE (order_id = order_id) всегда истинно, поэтому все данные пропадают.
Возможное решение проблемы выглядит так:
В этом случае при разборе имя переменной будет интерпретировано правильно. (Решение работает даже при наличии в пакете функции с именем remove_order.order_id.)
В PL/SQL установлен целый ряд правил разрешения конфликтов имен, а этой проблеме уделяется серьезное внимание. И хотя знать эти правила полезно, лучше использовать уникальные идентификаторы, чтобы избежать подобных конфликтов. Старайтесь писать надежный код! Если же вы не хотите уточнять каждую переменную, чтобы обеспечить ее уникальность, вам придется тщательно проработать схему назначения имен для предотвращения подобных конфликтов.
Вложенные программы
Завершая тему вложения, области действия и видимости, стоит упомянуть о такой полезной возможности PL/SQL, как вложенные программы (nested programs). Вложенная программа представляет собой процедуру или функцию, которая полностью размещается в разделе объявлений внешнего блока. Вложенная программа может обращаться ко всем переменным и параметрам, объявленным ранее во внешнем блоке, как показывает следующий пример:
Вложенные программы упрощают чтение и сопровождение кода, а также позволяют повторно использовать логику, задействованную в нескольких местах блока.
Продолжаем разговор о PL/SQL. Начнем рассмотрение организации и синтаксиса программы на PL/SQL: структурой блока, набором символов, а также правилами для идентификаторов, разделителей операторов и комментариев.
Как и в большинстве процедурных языков, в PL/SQL наименьшей значимой единицей группировки кода является блок.
Блок - это конструкция, обеспечивающая выполнение фрагмента кода и определяющая границы видимости переменных и область действия обработчиков исключений. PL/SQL позволяет создавать анонимные блоки (блоки кода, не имеющие названия) и именованные блоки (это могут быть процедуры, функции или триггеры).
В последующих статьях моего блога мы рассмотрим структуру блока и подробно остановимся на анонимных блоках. Различные виды именованных блоков будут описаны далее.
Курсорный цикл FOR
Обычно при использовании явных курсоров требуется открывать курсор, извлекать данные и по завершении закрывать курсор. Курсорный цикл FOR позволяет выполнять эти процедуры по открытию, извлечению и закрытию автоматически, чем очень сильно упрощает дело. В листинге А.5 показан пример применения конструкции курсорного цикла FOR.
Анонимные блоки PL/SQL
Когда кто-то хочет остаться неизвестным, он не называет своего имени. То же можно сказать и об анонимном блоке PL/SQL, показанном на рис. 3: в нем вообще нет раздела заголовка, блок начинается ключевым словом DECLARE (или BEGIN ). Анонимный блок не может быть вызван из другого блока, поскольку он не имеет идентификатора, по которому к нему можно было бы обратиться. Таким образом, анонимный блок представляет собой контейнер для хранения команд PL/SQL — обычно с вызовами процедур и функций. Поскольку анонимные блоки могут содержать собственные разделы объявлений и исключений, разработчики часто используют вложение анонимных блоков для ограничения области видимости идентификаторов и организации обработки исключений в более крупных программах.
Рис. 3. Анонимный блок без разделов объявлений и исключений
Общий синтаксис анонимного блока PL/SQL:
Квадратными скобками обозначаются необязательные составляющие синтаксиса. Анонимный блок обязательно содержит ключевые слова BEGIN и END, и между ними должна быть как минимум одна исполняемая команда. Несколько примеров:
- Простейший анонимный блок:
- Анонимный блок с добавлением раздела объявлений:
- Тот же блок, но с разделом исключений:
Анонимный блок выполняет серию команд, а затем завершает свою работу, то есть по сути является аналогом процедуры. Фактически каждый анонимный блок является анонимной процедурой. Они используются в различных ситуациях, в которых код PL/SQL выполняется либо непосредственно, либо как часть другой программы. Типичные примеры:
- Триггеры баз данных. Триггеры выполняют анонимные блоки при наступлении определенных событий.
- Специализированные команды или сценарии. В SQL*Plus и других аналогичных средах анонимные блоки активизируются из кода, введенного вручную, или из сценариев, называемых хранимыми программами. Кроме того, команда SQL*Plus EXECUTE преобразует свой аргумент в анонимный блок, заключая его между ключевыми словами BEGIN и END.
- Откомпилированная программа 3GL. В Pro*C и OCI анонимные блоки используются для внедрения вызовов хранимых программ во внешний код.
Во всех случаях контекст — и возможно, механизм присваивания имени — предоставляется внешним объектом (будь то триггер, программа командной строки или откомпилированная программа).
Видимость переменных PL/SQL
Важным свойством переменной, связанным с областью ее действия, является видимость. Данное свойство определяет, можно ли обращаться к переменной только по ее имени, или же к имени необходимо добавлять префикс.
Курсорные переменные
Курсорные переменные указывают на текущую строку в многострочном результирующем наборе. В отличие от обычного курсора, однако, курсорная переменная является динамической, что позволяет присваивать ей новые значения и передавать ее другим процедурами и функциям. Создаются курсорные переменные в PL/SQL следующим образом.
Сначала определяется тип REF CURSOR, как показано ниже:
Затем объявляются сами курсорные переменные типа EmpCurType в анонимном блоке кода PL/SQL либо в процедуре (или функции):
Анонимные блоки PL/SQL
Если кто-то хочет сохранить анонимность, он не называет своего имени. Именно так и поступает анонимный блок в PL/SQL (см. схему ниже): в нем просто отсутствует раздел заголовка, такой блок начинается с DECLARE или BEGIN . Это означает, что его нельзя будет вызвать из какого-то другого блока, так как не на что установить ссылку. Анонимные блоки служат контейнерами для операторов PL/SQL и обычно включают в себя вызовы процедур и функций.
В общем виде синтаксис анонимного блока PL/SQL будет таким:
[ DECLARE
BEGIN
. один или несколько исполняемых операторов .. .
[ EXCEPTION
. операторы обработки исключений . ]
В квадратные скобки заключены необязательные элементы конструкции. В блоке должны быть операторы BEGIN и END, а также хотя бы один исполняемый оператор. Рассмотрим несколько примеров анонимных блоков:
• Наиболее короткий анонимный блок:
• Блок с такой же функциональностью, в который добавлен раздел объявлений:
Анонимный блок, не имеющий разделов объявлений и исключений (только исполнение):
Хотя язык SQL и является легким в изучении и обладает массой мощных функциональных возможностей, он не позволяет создавать такие процедурные конструкции, которые возможны в языках третьего поколения вроде C. Язык PL/SQL является собственным расширением языка SQL от Oracle и предлагает функциональность серьезного языка программирования. Одно из главных его преимуществ состоит в том, что он позволяет использовать в базе данных такие программные единицы, как процедуры и пакеты, и тем самым увеличивать возможность повторного использования кода и его производительность.
Атрибуты курсоров
В примере, приведенном в листинге А.4, для указания того, когда цикл должен завершаться, используется специальный атрибут курсора %NOTFOUND. Атрибуты курсоров очень полезны при работе с явными курсорами. Наиболее важные из них перечислены ниже.
- %ISOPEN. Булевский атрибут, который после завершения выполнения SQL-оператора возвращает false. До тех пор, пока курсор остается открытым, он возвращает true.
- %FOUND. Булевский атрибут, который выполняет проверку на предмет наличия подходящих для SQL-оператора строк, т.е. остались ли у курсора еще какие-то строки для извлечения.
- %NOTFOUND. Булевский атрибут, который сообщает о том, что не удалось обнаружить ни одной подходящей для SQL-оператора строки, т.е. у курсора больше не осталось никаких строк для извлечения.
- %ROWCOUNT. Атрибут, который возвращает информацию о том, сколько курсору удалось извлечь строк на текущий момент.
Некоторые примеры динамического SQL
Рассмотрим несколько примеров использования Oracle/PLSQL оператора EXECUTE IMMEDIATE, чтобы понять как использовать EXECUTE IMMEDIATE в Oracle/PLSQL.
Описание команд в комментариях (--).
Продолжаем PL/SQL! Итак, как известно, наилучший способ изучить язык программирования - это погрузиться в него с головой! То есть начать писать программы на том языке, который вы собственно хотите изучить. По этому, помаленьку приступим к изучению самого языка. В PL/SQL действует соглашение, что все символы приводятся к верхнему регистру по этому объявления типа:
Одинаковы! По этому, например, я взял сразу за правило все писать в верхнем регистре сразу, так меньше путаницы! Так же, естественно, запрещено использовать зарезервированные имена встроенных функций и пакетов! Каждый законченный оператор обрамляется символом ";". Вообще, если говорить прямо, например когда, я начал работать с PL/SQL, уже на первом этапе я четко увидел, что сам PL/SQL очень похож на язык Pascal. По этому, когда я со всем этим занимался, я как раз работал очень много на Pascal и для меня не было особых трудов привыкнуть к PL/SQL! Он мне дался довольно легко и без лишних усилий, что думаю получится и у вас! Итак, давайте разберемся для начала со всеми специальными символами, я думаю это будет полезно! Обычно разбор всех языков с этого и начинается!
Самое интересное, нафига, такое количество способов, сказать не равно! Хотя != по моему вполне достаточно! Я например больше люблю вот так <>! :) Но вообще кому как нравится! Так же замечу, что вложенные комментарии не допускаются! Проще использовать /* */ как в языке C! А теперь про самое интересное - блоки PL/SQL.
Блоки PL/SQL, могут быть, как я уже говорил "именованными" и "не именованными". Блок PL/SQL является фундаментальной программной конструкцией! Программирование модулями позволяет разрабатывать легко читаемый код и программировать сверху вниз. Неименованный блок PL/SQL, имеет три раздела - Declaration (объявления), Body (тело) и, как правило, Exception (исключения).
Стандартная конструкция неименованного блока:
Итак, запускаем блокнот и SQL*Plus, пришло время написать и запустить вечную мантру программистов - правильно. "Hello World!". Итак, код реализующий данную вечную и незыблемую мантру таков:
Получаем после компиляции:
Можете написать, что ни будь свое и попробовать, что получиться! Еще раз проштудируйте специальные символы, так как далее мы будем ими часто пользоваться!
Каждый язык, будь то естественный или компьютерный, имеет определенный синтаксис, лексикон и набор символов. Чтобы общаться на этом языке, необходимо изучить правила его использования. Многие с опаской приступают к изучению новых компьютерных языков, но обычно они очень просты, и PL/SQL не является исключением. Трудности общения на компьютерных языках связаны не с самим языком, а с компилятором или компьютером, с которым мы «общаемся». Компиляторы не обладают творческим, гибким мышлением, а их лексикон крайне ограничен. Разве что соображают они очень, очень быстро. но только в рамках заданных правил.
Если приказать PL/SQL «подкинь-ка мне еще с полдюжины записей», едва ли вы получите требуемое. С точки зрения синтаксиса, для использования PL/SQL нужно расставлять все точки над «i». Поэтому в данной статье блога изложены основные правила языка, которые помогут вам общаться с компилятором PL/SQL — структура блоков PL/SQL.
Использование DML-операторов в PL/SQL
Любые операторы INSERT, DELETE или UPDATE работают в PL/SQL точно так же, как в обычном SQL. Однако в PL/SQL после каждого из них можно также применять оператор COMMIT, как показано ниже:
Записи в PL/SQL
Записи (records) в PL/SQL позволяют воспринимать взаимосвязанные данные как одно целое. Они могут содержать поля, каждое из которых может представлять отдельный элемент. Можно использовать атрибут ROW%TYPE и с его помощью объявлять записью столбцы определенной таблицы, что подразумевает применение таблицы в качестве шаблона курсора, а можно создавать и свои собственные записи. Ниже приведен простой пример записи:
Для ссылки на отдельное поле внутри записи применяется точечное обозначение, как показано ниже:
Использование курсоров
Курсором (cursor) в Oracle называется указатель на область в памяти, в которой содержится результирующий набор SQL-запроса, позволяющий индивидуально обрабатывать содержащиеся в результирующем наборе строки. Курсоры, которые используются Oracle при выполнении DML-операторов, называются неявными, а курсоры, которые создают и используют разработчики приложений — явными.
Уточненные идентификаторы
Типичным примером идентификаторов, невидимых в области кода, где они используются, являются идентификаторы, объявленные в спецификации пакета (имена переменных, типы данных, имена процедур и функций). Чтобы обратиться к такому объекту, необходимо указать перед его именем префикс и точку (аналогичным образом имя столбца уточняется именем таблицы, в которой он содержится). Например:
- price_util.compute_means — программа с именем compute_means из пакета price_util.
- math.pi — константа с именем pi, объявленная и инициализированная в пакете math.
Дополнительное уточнение может определять владельца объекта. Например, выражение
обозначает процедуру compute_means пакета price_util, принадлежащего пользователю Oracle с учетной записью scott.
Параметры или аргументы
dynamic_string Строковый литерал, переменная или выражение, представляющее один оператор SQL или блок PL/SQL. Он должен иметь тип CHAR или VARCHAR2, а не NCHAR или NVARCHAR2. BULK COLLECT Сохраняет значения результатов в одной или нескольких коллекциях для более быстрых запросов, чем циклы с операторами FETCH. INTO Используется только для однострочных запросов, в этом разделе указываются переменные или записи, в которые извлекаются значения столбцов. Для каждого значения, полученного запросом, в предложении INTO должна быть соответствующая тип-совместимая переменная или поле. define_variable Переменная, в которой сохраняется значение выбранного столбца. record_name Пользовательская запись или запись %ROWTYPE, в которой сохраняется выбранная строка. bind_argument Выражение, значение которого передается в динамический оператор SQL, или переменная, в которой сохраняется значение, возвращаемое динамическим оператором SQL. collection_name Объявленная коллекция, в которую извлекаются значения select_item из dynamic_string . Для каждого select_item должна быть соответствующая, совместимая с типом коллекция в списке. host_array_name Массив (объявленный в хост-среде PL/SQL и переданный PL/SQL как переменная связывания), в который извлекаются значения select_item. Для каждого select_item должен быть соответствующий, совместимый с типом массив в списке. Массивы хоста должны начинаться с двоеточия. USING По умолчанию - IN. Определяет список входных и/или выходных аргументов привязки. returning_clause Возвращает значения из вставленных строк, устраняя необходимость SELECT строки после. Вы можете извлечь значения столбца в переменные или в коллекции. Вы не можете использовать предложение RETURNING для удаленной или параллельной вставки. Если инструкция не влияет ни на какие строки, значения переменных, указанных в предложении RETURNING, не определены.
Процедуры, функции и пакеты
Процедуры в PL/SQL могут применяться для выполнения различных DML-операций. Ниже приведен пример простой процедуры Oracle:
В отличие от процедур, функции в PL/SQL возвращают значение, как показано в следующем примере:
Пакеты (packages) в Oracle представляют собой объекты, которые обычно состоят из нескольких взаимосвязанных процедур и функций и, как правило, применяются для выполнения какой-нибудь функции приложения путем вызова всех находящихся внутри пакета взаимосвязанных процедур и функций. Пакеты являются чрезвычайно мощным средством, поскольку могут содержать большие объемы функционального кода и многократно выполняться несколькими пользователями.
Каждый пакет обычно состоит из двух частей: спецификации и тела. В спецификации пакета объявляются все входящие в его состав переменные, курсоры и подпрограммы (процедуры и функции), а в теле пакета содержится фактический код этих курсоров и подпрограмм.
В листинге А.6 приведен пример простого пакета Oracle.
При желании использовать пакет emp_pkg для награждения какого-то сотрудника надбавкой к зарплате, все, что потребуется сделать — выполнить следующую команду:
«Видимые» идентификаторы
Начнем с тривиального случая:
Обе переменные first_day и last_day объявляются в том же блоке, где они используются, поэтому при обращении к ним указаны только имена без уточняющих префиксов. Такие идентификаторы называются видимыми. В общем случае видимым идентификатором может быть:
- идентификатор, объявленный в текущем блоке;
- идентификатор, объявленный в блоке, который включает текущий блок;
- отдельный объект базы данных (таблица, представление и т. д.) или объект PL/SQL (процедура, функция), владельцем которого вы являетесь;
- отдельный объект базы данных или объект PL/SQL, на который у вас имеются соответствующие привилегии и который определяется видимым синонимом;
- индексная переменная цикла (видима и доступна только внутри цикла). PL/SQL также позволяет обращаться к существующим объектам, которые не находятся в пределах непосредственной видимости блока. О том, как это делается, рассказано в следующем разделе.
Объявление переменных в PL/SQL
В операторе DECLARE можно объявлять как переменные, так и константы. Прежде чем использовать какую-либо переменную ее нужно обязательно объявить. Переменная в PL/SQL может представлять собой как переменную встроенного типа, такого как DATE, NUMBER, VARCHAR2 или CHAR, так и составного вроде VARRAY. Помимо этого, в PL/SQL еще применяются такие типы данных, как BINARY_INTEGER и BOOLEAN.
Ниже приведены некоторые типичные примеры объявления переменной в PL/SQL:
Помимо переменных также можно объявлять и константы, как показано в следующем примере:
Еще можно использовать атрибут %TYPE и с его помощью указывать при объявлении переменной, что ее тип данных должен совпадать с типом данных определенного столбца таблицы:
Посредством атрибута %ROWTYPE можно указывать, что тип данных записи (строки) должен совпадать с типом данных определенной таблицы базы данных. Например, в следующем коде указано, что запись DeptRecord должна содержать все те же столбцы, что и таблица department, а типы данных и длина этих столбцов в ней должны выглядеть абсолютно идентично:
Условное управление
Главной разновидностью условной управляющей структуры в PL/SQL является оператор IF, который обеспечивает условное выполнение операторов. Он может применяться в одной из трех следующих форм: IF-THEN, IF-THEN-ELSE и IF-THEN-ELSEIF. Ниже приведен пример простого оператора IF-THEN-ELSEIF:
Вложенные блоки PL/SQL
PL/SQL, как и языки Ada и Pascal, относится к категории языков с блочной структурой, то есть блоки PL/SQL могут вкладываться в другие блоки. С другой стороны, язык C тоже поддерживает блоки, но стандартный C не является строго блочно-структурированным языком, потому что вложение подпрограмм в нем не допускается.
В следующем примере PL/SQL показана процедура, содержащая анонимный вложенный блок:
Ограничители /* и */ обозначают начало и конец комментариев. Анонимные блоки также могут вкладываться более чем на один уровень (рис. 4).
Рис. 4. Вложенные анонимные блоки
Главное преимущество вложенных блоков заключается в том, что они позволяют ограничивать области видимости и действия синтаксических элементов кода.
Именованные блоки PL/SQL
Хотя анонимные блоки PL/SQL применяются во многих приложениях Oracle, вероятно, большая часть написанного вами кода будет оформлена в виде именованных блоков. Ранее вы уже видели несколько примеров хранимых процедур (см. рис. 1) и знаете, что их главной особенностью является наличие заголовка. Заголовок процедуры выглядит так:
Заголовок функции в целом очень похож на него, но дополнительно содержит ключевое слово RETURN:
Поскольку Oracle позволяет вызывать некоторые функции из SQL-команд, заголовок функции содержит больше необязательных компонентов, чем заголовок процедуры (в зависимости от функциональности и производительности исполнительной среды SQL).
Управляющие структуры в PL/SQL
В PL/SQL предлагается несколько видов управляющих структур (control structures), которые позволяют обеспечивать итерацию кода или условное выполнение определенных операторов. Все они кратко описаны в последующих разделах моего блога.
Разделы блока PL/SQL
Блок PL/SQL может включать в себя до четырех разделов (см. схему ниже), лишь один из которых является обязательным.
Заголовок
IS
BEGIN
EXCEPTION
END;
Заголовок
Используется только для именованных блоков. Заголовок определяет, каким образом будет вызываться именованный блок или про-грамма. Необязательный раздел.
Раздел объявлений
Определяет переменные, курсоры и подблоки, которые упоминаются в разделах исполнения и исключений. Необязательный раздел.
Раздел исполнения
Содержит операторы, которые будет выполнять ядро PL/SQL при исполнении блока. Обязательный раздел.
Удобство чтения
Практически любая команда SQL, встроенная в программу PL/SQL, содержит ссылки на столбцы и переменные. В небольших, простых командах SQL различать эти ссылки относительно просто. Однако во многих приложениях используются очень длинные, исключительно сложные команды SQL с десятками и даже сотнями ссылок на столбцы и переменные.
Без уточнения ссылок вам будет намного сложнее различать переменные и столбцы. С уточнениями сразу видно, к чему относится та или иная ссылка.
«Один момент… Мы используем четко определенные схемы назначения имен, при помощи которых мы различаем строки и столбцы. Имена всех локальных переменных начинаются с „l_“, поэтому мы сразу видим, что идентификатор представляет локальную переменную».
Да, все правильно; все мы должны иметь (и соблюдать) правила назначения имен, чтобы имена идентификаторов содержали дополнительную информацию о них (что это — параметр, переменная? К какому типу данных она относится?).
Безусловно, правила назначения имен полезны, но они еще не гарантируют, что компилятор PL/SQL всегда будет интерпретировать ваши идентификаторы именно так, как вы задумали.
Синтаксис
Синтаксис Oracle/PLSQL оператора EXECUTE IMMEDIATE для передачи значения в переменную или строку:
EXECUTE IMMEDIATE dynamic_string
[ INTO <[define_variable[, define_variable] . | record_name>]
[USING [IN | OUT | IN OUT] bind_argument ]
returning_clause;
или синтаксис Oracle/PLSQL оператора EXECUTE IMMEDIATE для передачи значения в коллекцию
EXECUTE IMMEDIATE dynamic_string
[[ BULK COLLECT] INTO ]
[USING [IN | OUT | IN OUT] bind_argument]
returning_clause;
Конструкции циклов в PL/SQL
Конструкции циклов в PL/SQL позволяют обеспечивать итеративное выполнение кода либо заданное количество раз, либо до тех пор, пока определенное условие не станет истинным или ложным. В следующих подразделах описываются основные виды этих конструкций.
Простой цикл
Конструкция простого цикла подразумевает помещение набора SQL-операторов между ключевыми словами LOOP и END LOOP. Оператор EXIT завершает цикл. Конструкция простого цикла применяется тогда, когда точно неизвестно, сколько раз должен выполняться цикл. В случае ее применения решение о том, когда цикл должен завершаться, принимается на основании содержащейся между операторами LOOP и END LOOP логики.
В следующем примере цикл будет выполняться до тех пор, пока значение quality_grade не достигнет 6:
Еще один простой вид цикла позволяет выполнять конструкция LOOP. EXIT. WHEN, в которой длительность цикла регулируется оператором WHEN. Внутри WHEN указывается условие, и когда это условие становится истинным, цикл завершается. Ниже показан простой пример:
Цикл WHILE
Цикл WHILE указывает, что определенный оператор должен выполняться до тех пор, пока определенное условие остается истинным. Обратите внимание на то, что условие вычисляется за пределами цикла, и вычисляется оно всякий раз, когда выполняются операторы, указанные между операторами LOOP и END LOOP. Когда условие перестает быть истинным, происходит выход из цикла. Ниже приведен пример цикла WHILE:
Цикл FOR
Цикл FOR применяется тогда, когда требуется, чтобы оператор выполнялся определенное количество раз. Он имитирует классический цикл do, который существует в большинстве языков программирования. Ниже приведен пример цикла FOR:
Базовый блок PL/SQL
Блоком в PL/SQL называется исполняемая программа. Блок кода PL/SQL, независимо от того, инкапсулируется он внутри какой-то программной единицы наподобие процедуры или задается в виде анонимного блока в свободной форме, состоит из следующих структур, которые представляют собой четыре ключевых оператора, только два из которых являются обязательными.
- DECLARE . Этот оператор является необязательным и представляет собой то место, в котором при желании объявляются переменные и курсоры программы.
- BEGIN . Этот оператор является обязательным и указывает, что далее будут идти операторы SQL и PL/SQL, т.е. обозначает начало блока кода PL/SQL.
- EXCEPTION . Этот оператор является необязательным и описывает методы обработки ошибок.
- END . Этот оператор является обязательными и обозначает конец блока кода PL/SQL.
Ниже приведен пример простого блока кода PL/SQL:
Неявные курсоры
В приведенном ниже блоке кода PL/SQL оператор SELECT, например, предусматривает применение неявного курсора:
Явные курсоры
Явные курсоры создаются разработчиком приложения и облегчают операции с набором строк, которые могут обрабатываться друг за другом. Они применяются всегда, когда известно, что SQL-оператор будет возвращать более одной строки. Обратите внимание, что явный курсор необходимо всегда объявлять в начале блока PL/SQL внутри раздела DECLARE, в отличие от неявного курсора, на который никогда не нужно ссылаться в коде.
После объявления явного курсора он будет проходить через следующие этапы обработки.
- Конструкция OPEN будет определять строки, которые находятся в курсоре, и делать их доступными для программы PL/SQL.
- Команда FETCH будет извлекать данные из курсора в указанную переменную.
- По завершении процесса обработки курсор должен всегда закрываться явным образом.
В листинге А.4 показан пример создания курсора и затем его использования внутри цикла.
Примеры:
Использование оператора SELECT в PL/SQL
При использовании оператора SELECT в PL/SQL нужно сохранять извлекаемые значения в переменных, как показано ниже:
Читайте также: