Основы PL SQL: структура, функции, триггеры, переменные, записи
PL/SQL (Programming Language for SQL) — язык программирования от компании Oracle, предоставляющий средства для сложной обработки данных. В этой статье мы рассмотрим основы PL/SQL. Начнём с программных единиц.
Программные единицы PL/SQL
К программным единицам в PL/SQL относят: 1. Процедуру — подпрограмму, выполняющую специфическое действие (CREATE PROCEDURE); 2. Функцию — подпрограмму, вычисляющую значение (CREATE FUNCTION). 3. PL/SQL-пакеты — объект БД, группирующий логически связанные типы, подпрограммы и программные объекты PL/SQL. Как правило, пакеты состоят из 2 частей — тела и спецификации. Спецификация представляет собой интерфейс с вашими приложениями (объявляет типы, константы, переменные, исключения, подпрограммы, курсоры — всё, что доступно для использования в пакете). Вторая часть — это тело пакета, полностью определяющая подпрограммы и курсоры, реализуя тем самым спецификацию пакета. 4. Динамический SQL: a. Native Dynamic SQL (NDS); b. DBMS_SQL. 5. Опции компилятора. 6. Триггеры — хранимые процедуры особого типа, которые юзер не вызывает непосредственно, т. к. их исполнение обусловлено действиями по модификации данных. Триггеры бывают разные: BEFORE INSERT, BEFORE UPDATE, AFTER INSERT и пр. 7. Управление зависимостями. 8. Хинты либо подсказки (Oracle Hints) — средства, которые позволяют оказывать явное влияние на план запроса. Подсказки определяют общие подходы и цели, оптимизирующие план исполнения запроса, в том числе методы и правила доступа к данным (например, указание метода и порядка соединения таблиц, индекса для доступа к таблице).
Блочная структура PL/SQL
В блоке PL/SQL может содержаться до 4-х разделов, но лишь один считается обязательным. 1. Заголовок. Применяется лишь в именованных блоках, служит для определения способа вызова программы либо именованного блока. 2. Раздел объявлений. Включает описания переменных, вложенных блоков и курсоров. 3. Раздел исключений. Служит для обработки исключений (предупреждений и ошибок). 4. Исполняемый раздел. Речь идёт о командах, которые выполняются ядром PL/SQL в процессе работы приложения. Обратите внимание, что это ОБЯЗАТЕЛЬНЫЙ раздел.
Давайте посмотрим на структуру PL/SQL-блока для процедуры:
Следующее изображение — это пример процедуры, содержащей все 4 раздела:
Хранимая процедура
Под хранимой процедурой понимают некоторый перечень инструкций, которые написаны на PL/SQL. Вызов такой процедуры обеспечивает выполнение содержащихся в этой процедуре инструкций. Сама она хранится в БД, поэтому и считается хранимой. Состоит из тела и спецификации.
Спецификация содержит имя процедуры, а также описание её выходных и входных данных, которые являются формальными параметрами либо формальными аргументами. Когда при вызове процедуры указывают параметры командной строки либо иные входные данные, указанные значения называют фактическими параметрами/фактическими аргументами.
Рассмотрим пример спецификации:
В примере выше перед нами процедура с 3-мя формальными параметрами. IN после имени параметра значит, что в процессе вызова процедура может из данного параметра считать входное значение. OUT значит, что процедура может применять этот параметр в целях возврата значения в ту программу, из которой она вызывалась. Комбинация IN OUT значит, что параметр может использоваться в качестве передачи значения процедуре, а также в целях возврата значения.
Что касается тела процедуры, то это блок кода PL/SQL.
Хранимые функции
Функция PL/SQL напоминает процедуру PL/SQL: у неё тоже есть тело и спецификация. Основное различие — функция нужна для возврата значения, которое можно применять в более крупном операторе SQL.
Триггеры
Триггером называют процедуру PL/SQL, выполняемую автоматически, когда происходит некоторое событие, которое называется триггерным событием.
К примеру, мы можем писать триггеры, которые срабатывают в процессе выполнения над таблицей операций UPDATE, INSERT либо DELETE; во время выдачи команд DDL; во время входа пользователя в систему либо его выхода; во время запуска либо остановки БД; во время возникновения ошибок.
Существуют 3 различия между процедурами PL/SQL и триггерами: 1. Триггеры невозможно вызвать из кода программы. Они вызываются автоматически как ответ на некоторое событие. 2. У триггеров нет списка параметров. 3. Спецификации триггера и процедуры немного отличаются.
Переменные в PL/SQL
Переменными называют именованные контейнеры. Они способны содержать информацию разных видов. С учётом помещаемой информации данных они имеют разные типы данных, а чтобы отличать эти данные друг от друга, присваиваются имена. К примеру, числа в PL/SQL хранятся в переменных типа NUMBER, текст — в переменных CHAR либо VARCHAR2. Что касается синтаксиса объявления переменной, то в PL/SQL он имеет любую из нижеперечисленных форм записи:
Здесь имя_переменной — любой правильный PL/SQL идентификатор. Такой идентификатор должен: 1. Иметь в длину не больше 30 символов, причём не иметь в записи пробелов и знаков табуляции. 2. Начинаться с буквы. 3. Состоять лишь из букв, цифр 0-9, символа подчеркивания, знака доллара и знака фунта. 4. Не совпадать с зарезервированными словами SQL и PL/SQL, имеющими спецзначение. Пример неправильной записи для переменной — слово BEGIN, обозначающее начало выполняемой секции основного PL/SQL-блока.
Следующее, это тип_данных — любой допустимый тип данных в SQL либо PL/SQL. Здесь обратите внимание, что модификатору NOT NULL требуется, чтобы переменная имела значение. И если модификатор указан, то переменной должно присвоиться значение по умолчанию.
Объявляем константы PL/SQL
Запись объявления константы выглядит следующим образом:
В отличие, скажем, от тех же переменных, константам обязательно присваивается значение, причём это значение нельзя будет поменять в течение всего срока жизни константы. При этом константы весьма полезны при поддержании безопасности и дисциплины во время разработки приложений повышенной сложности. К примеру, если вы желаете гарантировать, что PL/SQL-процедура не будет модифицировать данные, передаваемые ей, то вы можете объявить их константами. А если процедура всё-таки попытается модифицировать эти данные, PL/SQL возбудит исключение.
Записи в PL/SQL
Record PL/SQL
Давайте посмотрим, как применять спецификатор записи Record в PL/SQL с синтаксисом.
Record (запись) — это группа связанных элементов данных, которые хранятся в полях, причём каждая имеет своё имя и тип данных. При этом мы можем применять Record как переменную, способную содержать строку таблицы либо некоторые столбцы из строки таблицы.
Определяем и объявляем Record в PL/SQL
Объявить запись поможет атрибут %ROWTYPE, представляющий строку в таблице БД без непосредственного перечисления всех столбцов. При этом ваш код будет продолжать функционировать даже после добавления в таблицу столбцов. Но если вы желаете представить подмножество столбцов в таблице либо столбцы из различных таблиц, можно определить VIEW либо объявить CURSOR в целях выбора правильных столбцов и любых нужных объединений, а потом задействовать атрибут %ROWTYPE к CURSOR либо VIEW.
Посмотрим на синтаксис объявления переменной в PL/SQL и определения типа Record:
Мы видим следующие параметры: 1) type_rec_name – имя конкретного типа Record; 2) var_rec – имя переменной Record-типа; 3) field_1, field_2,… field_n – поля Record-типа; 4) datatype – тип данных для полей Record-типа. Может быть любой из списка:
Обзор типов данных PL/SQL
При объявлении переменной или константы необходимо задать ее тип данных (PL/SQL, за некоторыми исключениями, относится к языкам со статической типизацией — см. ниже врезку). В PL/SQL определен широкий набор скалярных и составных типов данных; кроме того, вы можете создавать пользовательские типы данных. Многие типы данных PL/SQL (например, BOOLEAN и NATURAL ) не поддерживаются столбцами баз данных, но в коде PL/SQL эти типы весьма полезны.
PL/SQL поддерживает распространенный «джентльменский набор» типов данных, а так-же ряд других типов. В этом разделе приводится краткий обзор различных предопределенных типов данных.
ЧТО ТАКОЕ «СТАТИЧЕСКАЯ ТИПИЗАЦИЯ »?
Статической (или сильной) типизацией называется проверка типов во время компиляции (а не на стадии выполнения программы). К числу языков программирования, использующих сильную типизацию, относятся PL/SQL, Ada, C и Pascal. Языки с динамической типизацией (такие, как JavaScript, Perl или Ruby) выполняют большинство проверок типов во время выполнения. Статическая типизация позволяет выявлять ошибки во время компиляции, что повышает надежность программ. Преимуществом статической типизации является и ускорение выполнения программ. Оптимизирующий компилятор, который знает точные типы данных, может подбирать более эффективные ассемблерные решения и генерировать высокооптимизированный машинный код. Динамическая типизация тоже обладает своими преимуществами: например, метаклассы и интроспекция проще реализуются на базе динамической типизации.
Символьные типы данных
Для очень длинных символьных строк в PL/SQL предусмотрены типы данных CLOB (Character Large Object) и NCLOB (NLS Character Large Object). По соображениям совместимости PL/SQL также поддерживает тип данных LONG. Эти типы данных позволяют сохранять и обрабатывать очень большие объемы данных; так, в Oracle11g тип LOB способен хранить до 128 терабайт информации.
Использование типа LONG ограничивается множеством правил. Мы не рекомендуем применять его в Oracle8 и последующих версиях.
Числовые типы данных
В PL/SQL поддерживаются как вещественные, так и целочисленные типы данных. Тип NUMBER давно был основным типом для работы с числовыми данными; он может применяться для работы с целыми и вещественными данными как с фиксированной, так и с плавающей запятой. Пример типичного объявления NUMBER :
Из-за своей внутренней десятичной природы тип NUMBER особенно удобен при работе с денежными суммами. В отличие от двоичного представления, ему не присущи ошибки округления. Например, если сохранить в нем значение 0.95, позднее вы прочитаете именно эту величину, а не приближенную (скажем, 0.949999968).
Дата, время и интервалы
Логические данные
Логические переменные помогают писать понятный, легко читаемый код даже в тех случаях, когда он содержит очень сложные логические выражения. Пример объявления логической переменной с присваиванием ей значения по умолчанию:
Двоичные данные
Типы данных ROWID и UROWID
Тип данных REF CURSOR
Тип данных REF CURSOR позволяет объявлять курсорные переменные, которые могут использоваться со статическими и динамическими SQL-командами для улучшения гибкости программного кода. Тип REF CURSOR существует в двух формах: сильной и слабой. PL/SQL относится к категории языков со статической типизацией, а слабый тип REF CURSOR является одной из немногочисленных конструкций с динамической типизацией.
В следующем примере объявления сильной формы REF CURSOR курсорная переменная связывается с конкретной структурой записи с помощью атрибута %ROWTYPE :
Типы данных для поддержки интернет-технологий
В Oracle9i появилась встроенная поддержка ряда технологий и типов данных, связанных с Интернетом, в частности XML (eXtensible Markup Language) и URI (Universal Resource Identifier). В Oracle имеются специализированные типы для работы с данными XML и URI, а также специальный класс идентификаторов URI ( DBUri-REF ), который используется для доступа к базе данных. Кроме того, в Oracle появился новый набор типов данных, обеспечивающий хранение внешних и внутренних URI и обращение к ним из базы данных.
Типы данных «Any»
Эта группа типов данных, появившаяся в Oracle9i, заметно отличается от любых других типов данных Oracle. Типы данных Any позволяют динамически инкапсулировать описания типов, экземпляры данных и наборы экземпляров данных любого другого типа SQL. С помощью этих объектных типов (и определенных для них методов) можно, к примеру, определить тип данных, хранимых во вложенной таблице, не обращаясь к объявлению типа этой таблицы!
Пользовательские типы данных
Из встроенных типов данных Oracle и пользовательских типов можно строить типы данных произвольной сложности, которые с большой точностью отражают структуру и поведение данных в конкретных системах. В базе данных Oracle они получили еще название абстрактных типов данных.
Основы языка PL/SQL
Хотя язык SQL и является легким в изучении и обладает массой мощных функциональных возможностей, он не позволяет создавать такие процедурные конструкции, которые возможны в языках третьего поколения вроде C. Язык PL/SQL является собственным расширением языка SQL от Oracle и предлагает функциональность серьезного языка программирования. Одно из главных его преимуществ состоит в том, что он позволяет использовать в базе данных такие программные единицы, как процедуры и пакеты, и тем самым увеличивать возможность повторного использования кода и его производительность.
Базовый блок PL/SQL
Блоком в 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
После оператора BEGIN можно начинать вводить все свои желаемые SQL-операторы. Выглядеть эти операторы должны точно так же, как обычные операторы SQL. При использовании операторов SELECT и INSERT в PL/SQL, правда, необходимо помнить об особенностях, о которых более подробно речь пойдет в следующих разделах.
Использование оператора SELECT в PL/SQL
При использовании оператора SELECT в PL/SQL нужно сохранять извлекаемые значения в переменных, как показано ниже:
Использование DML-операторов в PL/SQL
Любые операторы INSERT, DELETE или UPDATE работают в PL/SQL точно так же, как в обычном SQL. Однако в PL/SQL после каждого из них можно также применять оператор COMMIT, как показано ниже:
Обработка ошибок
В PL/SQL любая ошибка или предупреждение называется исключением (exception). В PL/SQL есть кое-какие определенные внутренне ошибки, но также допускается определять и свои собственные. При возникновении любой ошибки инициируется исключение, и управление переходит в отвечающий за обработку исключений раздел программы PL/SQL. В случае определения своих собственных ошибочных ситуаций необходимо обеспечивать инициирование исключений за счет применения специального оператора RAISE.
Ниже приведен пример использования оператора RAISE для обработки исключений:
Управляющие структуры в PL/SQL
В PL/SQL предлагается несколько видов управляющих структур (control structures), которые позволяют обеспечивать итерацию кода или условное выполнение определенных операторов. Все они кратко описаны в последующих разделах моего блога.
Условное управление
Главной разновидностью условной управляющей структуры в PL/SQL является оператор IF, который обеспечивает условное выполнение операторов. Он может применяться в одной из трех следующих форм: IF-THEN, IF-THEN-ELSE и IF-THEN-ELSEIF. Ниже приведен пример простого оператора IF-THEN-ELSEIF:
Конструкции циклов в 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
Записи (records) в PL/SQL позволяют воспринимать взаимосвязанные данные как одно целое. Они могут содержать поля, каждое из которых может представлять отдельный элемент. Можно использовать атрибут ROW%TYPE и с его помощью объявлять записью столбцы определенной таблицы, что подразумевает применение таблицы в качестве шаблона курсора, а можно создавать и свои собственные записи. Ниже приведен простой пример записи:
Для ссылки на отдельное поле внутри записи применяется точечное обозначение, как показано ниже:
Использование курсоров
Курсором (cursor) в Oracle называется указатель на область в памяти, в которой содержится результирующий набор SQL-запроса, позволяющий индивидуально обрабатывать содержащиеся в результирующем наборе строки. Курсоры, которые используются Oracle при выполнении DML-операторов, называются неявными, а курсоры, которые создают и используют разработчики приложений — явными.
Неявные курсоры
Неявные курсоры автоматически применяются Oracle всякий раз, когда в коде PL/SQL используется оператор SELECT. Они могут использоваться лишь в тех операторах, которые возвращают одну строку. В случае если SQL-оператор возвращает более одной строки, будет выдаваться сообщение об ошибке.
В приведенном ниже блоке кода PL/SQL оператор SELECT, например, предусматривает применение неявного курсора:
Явные курсоры
Явные курсоры создаются разработчиком приложения и облегчают операции с набором строк, которые могут обрабатываться друг за другом. Они применяются всегда, когда известно, что SQL-оператор будет возвращать более одной строки. Обратите внимание, что явный курсор необходимо всегда объявлять в начале блока PL/SQL внутри раздела DECLARE, в отличие от неявного курсора, на который никогда не нужно ссылаться в коде.
После объявления явного курсора он будет проходить через следующие этапы обработки.
В листинге А.4 показан пример создания курсора и затем его использования внутри цикла.
Атрибуты курсоров
В примере, приведенном в листинге А.4, для указания того, когда цикл должен завершаться, используется специальный атрибут курсора %NOTFOUND. Атрибуты курсоров очень полезны при работе с явными курсорами. Наиболее важные из них перечислены ниже.
Курсорный цикл FOR
Обычно при использовании явных курсоров требуется открывать курсор, извлекать данные и по завершении закрывать курсор. Курсорный цикл FOR позволяет выполнять эти процедуры по открытию, извлечению и закрытию автоматически, чем очень сильно упрощает дело. В листинге А.5 показан пример применения конструкции курсорного цикла FOR.
Курсорные переменные
Курсорные переменные указывают на текущую строку в многострочном результирующем наборе. В отличие от обычного курсора, однако, курсорная переменная является динамической, что позволяет присваивать ей новые значения и передавать ее другим процедурами и функциям. Создаются курсорные переменные в PL/SQL следующим образом.
Сначала определяется тип REF CURSOR, как показано ниже:
Затем объявляются сами курсорные переменные типа EmpCurType в анонимном блоке кода PL/SQL либо в процедуре (или функции):
Процедуры, функции и пакеты
Процедуры в PL/SQL могут применяться для выполнения различных DML-операций. Ниже приведен пример простой процедуры Oracle:
В отличие от процедур, функции в PL/SQL возвращают значение, как показано в следующем примере:
Пакеты (packages) в Oracle представляют собой объекты, которые обычно состоят из нескольких взаимосвязанных процедур и функций и, как правило, применяются для выполнения какой-нибудь функции приложения путем вызова всех находящихся внутри пакета взаимосвязанных процедур и функций. Пакеты являются чрезвычайно мощным средством, поскольку могут содержать большие объемы функционального кода и многократно выполняться несколькими пользователями.
Каждый пакет обычно состоит из двух частей: спецификации и тела. В спецификации пакета объявляются все входящие в его состав переменные, курсоры и подпрограммы (процедуры и функции), а в теле пакета содержится фактический код этих курсоров и подпрограмм.
В листинге А.6 приведен пример простого пакета Oracle.
При желании использовать пакет emp_pkg для награждения какого-то сотрудника надбавкой к зарплате, все, что потребуется сделать — выполнить следующую команду:
Основы языка PL/SQL: структура блока PL/SQL и область видимости
Каждый язык, будь то естественный или компьютерный, имеет определенный синтаксис, лексикон и набор символов. Чтобы общаться на этом языке, необходимо изучить правила его использования. Многие с опаской приступают к изучению новых компьютерных языков, но обычно они очень просты, и PL/SQL не является исключением. Трудности общения на компьютерных языках связаны не с самим языком, а с компилятором или компьютером, с которым мы «общаемся». Компиляторы не обладают творческим, гибким мышлением, а их лексикон крайне ограничен. Разве что соображают они очень, очень быстро. но только в рамках заданных правил.
Если приказать PL/SQL «подкинь-ка мне еще с полдюжины записей», едва ли вы получите требуемое. С точки зрения синтаксиса, для использования PL/SQL нужно расставлять все точки над «i». Поэтому в данной статье блога изложены основные правила языка, которые помогут вам общаться с компилятором PL/SQL — структура блоков PL/SQL.
Структура блока PL/SQL
В PL/SQL, как и в большинстве других процедурных языков, наименьшей единицей группировки кода является блок. Он представляет собой фрагмент программного кода, определяющий границы выполнения и области видимости для объявлений переменных и обработки исключений. PL/SQL позволяет создавать как именованные, так и анонимные блоки (то есть блоки, не имеющие имени), которые представляют собой пакеты, процедуры, функции, триггеры или объектные типы.
Блок PL/SQL может содержать до четырех разделов, однако только один из них является обязательным.
Структура блока PL/SQL для процедуры показана на рис. 1.
Рис. 1. Структура блока PL/SQL
На рис. 2 показана процедура, содержащая все четыре раздела. Этот конкретный блок начинается с ключевого слова PROCEDURE и, как и все блоки, завершается ключевым словом END.
Рис. 2. Процедура, содержащая все четыре раздела
Анонимные блоки PL/SQL
Когда кто-то хочет остаться неизвестным, он не называет своего имени. То же можно сказать и об анонимном блоке PL/SQL, показанном на рис. 3: в нем вообще нет раздела заголовка, блок начинается ключевым словом DECLARE (или BEGIN ). Анонимный блок не может быть вызван из другого блока, поскольку он не имеет идентификатора, по которому к нему можно было бы обратиться. Таким образом, анонимный блок представляет собой контейнер для хранения команд PL/SQL — обычно с вызовами процедур и функций. Поскольку анонимные блоки могут содержать собственные разделы объявлений и исключений, разработчики часто используют вложение анонимных блоков для ограничения области видимости идентификаторов и организации обработки исключений в более крупных программах.
Рис. 3. Анонимный блок без разделов объявлений и исключений
Общий синтаксис анонимного блока PL/SQL:
Квадратными скобками обозначаются необязательные составляющие синтаксиса. Анонимный блок обязательно содержит ключевые слова BEGIN и END, и между ними должна быть как минимум одна исполняемая команда. Несколько примеров:
Анонимный блок выполняет серию команд, а затем завершает свою работу, то есть по сути является аналогом процедуры. Фактически каждый анонимный блок является анонимной процедурой. Они используются в различных ситуациях, в которых код PL/SQL выполняется либо непосредственно, либо как часть другой программы. Типичные примеры:
Во всех случаях контекст — и возможно, механизм присваивания имени — предоставляется внешним объектом (будь то триггер, программа командной строки или откомпилированная программа).
Именованные блоки PL/SQL
Хотя анонимные блоки PL/SQL применяются во многих приложениях Oracle, вероятно, большая часть написанного вами кода будет оформлена в виде именованных блоков. Ранее вы уже видели несколько примеров хранимых процедур (см. рис. 1) и знаете, что их главной особенностью является наличие заголовка. Заголовок процедуры выглядит так:
Заголовок функции в целом очень похож на него, но дополнительно содержит ключевое слово RETURN:
Поскольку Oracle позволяет вызывать некоторые функции из SQL-команд, заголовок функции содержит больше необязательных компонентов, чем заголовок процедуры (в зависимости от функциональности и производительности исполнительной среды SQL).
Вложенные блоки PL/SQL
PL/SQL, как и языки Ada и Pascal, относится к категории языков с блочной структурой, то есть блоки PL/SQL могут вкладываться в другие блоки. С другой стороны, язык C тоже поддерживает блоки, но стандартный C не является строго блочно-структурированным языком, потому что вложение подпрограмм в нем не допускается.
В следующем примере PL/SQL показана процедура, содержащая анонимный вложенный блок:
Ограничители /* и */ обозначают начало и конец комментариев. Анонимные блоки также могут вкладываться более чем на один уровень (рис. 4).
Рис. 4. Вложенные анонимные блоки
Главное преимущество вложенных блоков заключается в том, что они позволяют ограничивать области видимости и действия синтаксических элементов кода.
Область действия в PL/SQL
В PL/SQL переменные, исключения, модули и некоторые другие структуры являются локальными для блока, в котором они объявлены. Когда выполнение блока будет завершено, все эти структуры становятся недоступными. Например, в приведенной выше процедуре calc_totals можно обращаться к элементам внешнего блока (например, к переменной year_total), тогда как элементы, объявленные во внутреннем блоке, для внешнего блока недоступны.
У каждой переменной PL/SQL имеется некоторая область действия — участок программы (блок, подпрограмма или пакет), в котором можно ссылаться на эту переменную. Рассмотрим следующее определение пакета:
Переменная scope_demo.g_global может использоваться в любом блоке любой схемы, обладающем привилегией EXECUTE для scope_demo.
Переменная l_salary может использоваться только в процедуре set_global.
Переменная l_inner может использоваться только в локальном или вложенном блоке; обратите внимание на использование метки local_block для присваивания имени вложенному блоку.
Уточнение ссылок на переменные и столбцы в командах SQL
Ссылки на переменные и столбцы в предыдущем примере не уточнялись именами области действия. Далее приводится другая версия того же пакета, но на этот раз с уточнением ссылок (выделены полужирным шрифтом):
В новой версии каждая ссылка на столбец и переменную уточняется псевдонимом таблицы, именем пакета, именем процедуры или меткой вложенного блока.
Итак, теперь вы знаете об этой возможности — но зачем тратить время на уточнение имен? Для этого есть несколько очень веских причин:
Давайте поближе рассмотрим первые две из этих причин.
Удобство чтения
Практически любая команда SQL, встроенная в программу PL/SQL, содержит ссылки на столбцы и переменные. В небольших, простых командах SQL различать эти ссылки относительно просто. Однако во многих приложениях используются очень длинные, исключительно сложные команды SQL с десятками и даже сотнями ссылок на столбцы и переменные.
Без уточнения ссылок вам будет намного сложнее различать переменные и столбцы. С уточнениями сразу видно, к чему относится та или иная ссылка.
«Один момент… Мы используем четко определенные схемы назначения имен, при помощи которых мы различаем строки и столбцы. Имена всех локальных переменных начинаются с „l_“, поэтому мы сразу видим, что идентификатор представляет локальную переменную».
Да, все правильно; все мы должны иметь (и соблюдать) правила назначения имен, чтобы имена идентификаторов содержали дополнительную информацию о них (что это — параметр, переменная? К какому типу данных она относится?).
Безусловно, правила назначения имен полезны, но они еще не гарантируют, что компилятор 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
Важным свойством переменной, связанным с областью ее действия, является видимость. Данное свойство определяет, можно ли обращаться к переменной только по ее имени, или же к имени необходимо добавлять префикс.
«Видимые» идентификаторы
Начнем с тривиального случая:
Обе переменные first_day и last_day объявляются в том же блоке, где они используются, поэтому при обращении к ним указаны только имена без уточняющих префиксов. Такие идентификаторы называются видимыми. В общем случае видимым идентификатором может быть:
Уточненные идентификаторы
Типичным примером идентификаторов, невидимых в области кода, где они используются, являются идентификаторы, объявленные в спецификации пакета (имена переменных, типы данных, имена процедур и функций). Чтобы обратиться к такому объекту, необходимо указать перед его именем префикс и точку (аналогичным образом имя столбца уточняется именем таблицы, в которой он содержится). Например:
Дополнительное уточнение может определять владельца объекта. Например, выражение
обозначает процедуру compute_means пакета price_util, принадлежащего пользователю Oracle с учетной записью scott.
Уточнение идентификаторов именами модулей
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). Вложенная программа представляет собой процедуру или функцию, которая полностью размещается в разделе объявлений внешнего блока. Вложенная программа может обращаться ко всем переменным и параметрам, объявленным ранее во внешнем блоке, как показывает следующий пример:
Вложенные программы упрощают чтение и сопровождение кода, а также позволяют повторно использовать логику, задействованную в нескольких местах блока.
