Язык программирования oracle sql
Основы языка 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 для награждения какого-то сотрудника надбавкой к зарплате, все, что потребуется сделать — выполнить следующую команду:
Oracle SQL
СУБД Oracle — система управления реляционными базами данных, разработанная корпорацией Oracle.
Oracle является наиболее популярной из реализаций SQL; он был исторически первой коммерческой реализацией SQL и первой реализацией, совместимой со стандартом SQL‘93.
Oracle поддерживает ряд различных платформ, включая Windows, Linux, Max OS X и Sun Solaris.
Процедурное расширение SQL, разработанное Oracle, называется PL/SQL (Procedural Language/Structured Query Language) и основано на синтаксисе языков Ada и Pascal. Третьим ключевым языком, использующийся в СУБД Oracle наравне с SQL и PL/SQL, является Java.
PL/SQL поддерживает программные блоки (в виде функций, процедур и пакетов, которые хранятся в базе данных в скомпилированном виде и могут быть выполнены позднее создания, или анонимных блоков, которые не могут быть скомпилированы и используются только с виде скриптов). PL/SQL поддерживает разнообразные типы данных для хранения чисел, строк и дат, операторы управления потоком вычислений (в т.ч. условные переходы и циклы) и три типа контейнеров (коллекций) — массивы переменной длины, ассоциативные массивы и вложенные таблицы.
Для администрирования БД и разработки приложений для СУБД Oracle может использоваться ряд инструментов. Примерами программного обеспечения, предоставляемого корпорацией Oracle, являются:
Примерами программного обеспечения сторонних разработчиков, являются:
Примеры:
Hello, World!:
Факториал:
SQL не поддерживает циклы, рекурсии или пользовательские функции. Данный пример демонстрирует возможный обходной путь, использующий:
Строка “0! = 1” не войдет в набор строк, полученный в результате, т.к. попытка вычислить ln(0) приводит к исключению.
Числа Фибоначчи:
SQL не поддерживает циклы или рекурсии, кроме того, конкатенация полей из разных строк таблицы или запроса не является стандартной агрегатной функцией. Данный пример использует:
Факториал:
Числа Фибоначчи:
Hello, World!:
Факториал:
Этот пример демонстрирует итеративное вычисление факториала средствами PL/SQL.
Числа Фибоначчи:
Этот пример использует итеративное определение чисел Фибоначчи. Уже вычисленные числа хранятся в структуре данных varray — аналоге массива.
Квадратное уравнение:
Этот пример тестировался в SQL*Plus, TOAD и PL/SQL Developer.
Чистый SQL позволяет вводить переменные в процессе исполнения запроса в виде заменяемых переменных. Для определения такой переменной ее имя (в данном случае A, B и C) следует использовать с амперсандом & перед ним каждый раз, когда нужно сослаться на эту переменную. Когда запрос выполняется, пользователь получает запрос на ввод значений всех заменяемых переменных, использованных в запросе. После ввода значений каждая ссылка на такую переменную заменяется на ее значение, и полученный запрос выполняется.
Следует отметить, что ссылки заменяются на значения “как есть”, поэтому отрицательные коэффициенты следует вводить в скобках.
Первая строка примера задает символ для десятичного разделителя, который используется при преобразовании чисел-корней в строки.
CamelCase:
Введение в язык программирования PL/SQL
PL/SQL — это сокращение от «Procedural Language extensions to the Structured Query Language», что в переводе с английского означает «процедурные языковые расширения для SQL». SQL — повсеместно распространенный язык для выборки и обновления информации (вопреки названию) в реляционных базах данных. Компания Oracle Corporation разработала PL/SQL для преодоления некоторых ограничений SQL, а также для того, чтобы предоставить более полное совершенное решение для разработчиков ответственных приложений баз данных Oracle. В этой статье вы познакомитесь с PL/SQL, его происхождением и разными версиями. В блоге приведена краткая сводка PL/SQL в последней версии Oracle, Oracle Database 12с. Ну а завершим ознакомление с языком списком дополнительных ресурсов для разработчиков PL/SQL и некоторыми рекомендациями.
Что такое PL/SQL?
Язык PL/SQL обладает следующими определяющими характеристиками, которые можно отнести к его несомненным достоинствам:
История PL/SQL
В отрасли программного обеспечения компания Oracle является лидером в использовании декларативного, непроцедурного подхода к проектированию баз данных и приложений. Технология Oracle Server считается одной из самых прогрессивных, мощных и надежных реляционных баз данных в мире. Средства разработки приложений от Oracle (такие, как Oracle Forms) обеспечивают высокую производительность за счет применения визуального проектирования — подхода, в котором многие характеристики программ и их элементов определяются по умолчанию, что избавляет программиста от огромного объема рутинной работы.
Истоки PL/SQL
Вначале Oracle-разработчиков в полной мере удовлетворял декларативный подход SQL в сочетании с новаторской реляционной технологией. Но с развитием отрасли возрастали и требования к средствам разработки. Все чаще разработчики желали «проникнуть вовнутрь» продуктов. Им нужно было встраивать в свои формы и сценарии достаточно сложные формулы, исключения и правила.
Выпущенная в 1988 году версия Oracle 6 стала важным шагом в развитии технологии баз данных Oracle. Ключевым компонентом новой версии стало так называемое «процедурное дополнение», или PL/SQL. Примерно в то же время появилось долгожданное обновление SQL*Forms версии 2.3. Сейчас этот продукт называется Oracle Forms или Forms Developer). В SQL*Forms 3.0 был впервые интегрирован язык PL/SQL, позволяющий разработчику просто и естественно программировать процедурную логику.
Возможности первой версии PL/SQL были весьма ограниченными. На стороне сервера этот язык использовался только для написания сценариев «пакетной обработки» данных, состоящих из процедурных команд и инструкций SQL. В то время еще нельзя было строить модульные приложения или сохранять бизнес-правила на сервере. Технология SQL*Forms 3.0 позволяла создавать процедуры и функции на стороне клиента, хотя поддержка этих функций еще не была документирована, и поэтому многие разработчики ими не пользовались. Кроме того, в этой версии PL/SQL не поддерживались массивы и отсутствовало взаимодействие с операционной системой (для ввода и вывода). Так что до полноценного языка программирования было еще очень далеко.
Однако, несмотря на все ограничения, PL/SQL был очень тепло и даже с энтузиазмом принят сообществом разработчиков. Уж очень остро стала ощущаться потребность хотя бы в таких элементарных средствах, как условная команда IF в SQL*Forms. Необходимость в пакетном выполнении последовательности команд SQL стала безотлагательной.
В то время лишь немногие разработчики понимали, что исходная мотивация и побудительные причины для развития PL/SQL выходили за пределы потребности программного управления в таких продуктах, как SQL *Forms. Еще на ранней стадии жизненного цикла базы данных Oracle и ее инструментария компания Oracle Corporation выявила две ключевые слабости своей архитектуры: плохую переносимость и проблемы с полномочиями выполнения.
Улучшение переносимости приложений
Тем, кто знаком с маркетинговой и технической стратегией Oracle Corporation, может показаться странным сама постановка вопроса. Ведь с начала 1980-х годов именно переносимость была одной из сильных сторон решений Oracle. На момент выхода PL/SQL реляционные базы данных на основе языка C работали во многих операционных системах и на множестве аппаратных платформ. SQL*Plus и SQL*Forms легко адаптировались к разнообразным терминальным конфигурациям. Однако для решения многих задач по-прежнему требовались более совершенные и точные средства управления таких языков, как COBOL, C и FORTRAN. И стоило разработчику выйти за рамки платформенно-нейтрального инструментария Oracle, приложение утрачивало переносимость.
Язык PL/SQL расширяет диапазон требований к приложениям, которые полностью реализуются программными средствами, независимыми от операционной системы. В настоящее время для создания таких универсальных приложений применяется Java и другие языки программирования. Тем не менее PL/SQL занимает особое место как один из пионеров в этой области и, конечно, продолжает применяться разработчиками для создания легко переносимого кода приложений баз данных.
Улучшенная защита приложений и защита целостности транзакций
Язык PL/SQL обеспечивает строгий контроль за выполнением логических транзакций. Одним из средств такого контроля является система полномочий на выполнение. Вместо того чтобы выдавать разрешения на обновление таблиц ролям или пользователям, вы выдаете разрешение только на выполнение процедуры, которая управляет и предоставляет доступ к необходимым структурам данных. Владельцем процедуры является другая схема базы данных Oracle («определитель»), которой, в свою очередь, предоставляются разрешения на обновление таблиц, участвующих в транзакции. Таким образом, процедура становится «привратником» транзакции. Программный код (будь то приложение Oracle Forms или исполняемый файл Pro*C) может выполняться только посредством вызова процедуры, и это гарантирует целостность транзакций приложения.
Скромное начало, постоянное усовершенствование
Язык SQL, каким бы мощным он ни был, не обладает гибкостью и мощью, необходимой для создания полноценных приложений. В то же время PL/SQL, оставаясь в пределах не зависимого от операционной системы окружения Oracle, позволяет разрабатывать высокоэффективные приложения, удовлетворяющие потребностям пользователей.
Со времени своего появления PL/SQL прошел очень длинный путь. Разработчики, которые пользовались его первой версией, слишком уж часто вынуждены были говорить: «В PL/SQL это сделать невозможно». Сейчас это утверждение из факта постепенно превращается в отговорку. Если вы сталкиваетесь с задачей, которую, как вам кажется, нельзя решить средствами PL/SQL, не пытайтесь убедить в этом свое начальство. Копайте глубже, исследуйте возможности самого языка и пакетов PL/SQL, и необходимые средства, скорее всего, найдутся.
За прошедшие годы компания Oracle Corporation продемонстрировала свою приверженность PL/SQL. С выходом каждой новой версии базы данных Oracle в PL/SQL вводились все новые фундаментальные усовершенствования. Было разработано множество внешних (и встроенных) пакетов, расширяющих функциональность PL/SQL. В частности, язык был дополнен объектно-ориентированными возможностями, разнообразными структурами данных, усовершенствованный компилятор оптимизировал код и выдавал предупреждения о возможных проблемах с качеством и быстродействием кода. В целом язык был заметно углублен и расширен.
В следующем разделе представлены примеры программ PL/SQL, которые познакомят читателя с основами программирования PL/SQL.
Основы языка PL/SQL
Если вы только приступаете к изучению программирования или еще не освоили ни PL/SQL, ни даже SQL, может показаться, что перед вами очень сложная задача. Но это не так. Она наверняка окажется намного проще, чем вы думаете.
Для оптимизма есть два основания:
Рассмотрим несколько примеров, демонстрирующих применение ключевых элементов структуры и функциональности PL/SQL.
Интеграция с SQL
Одним из важнейших аспектов PL/SQL является его тесная интеграция с SQL. Для выполнения SQL-инструкций в программах на PL/SQL не требуется никакой промежуточной программной «прослойки» вроде ODBC (Open Database Connectivity) или JDBC ( Java Database Connectivity). Инструкция UPDATE или SELECT просто вставляется в программный код, как в следующем примере.
Теперь посмотрим, что делает этот код. Его подробное описание дано в следующей таблице.
| Строки | Описание |
| 1–3 | Объявление так называемого анонимного блока PL/SQL, в котором объявляется целочисленная переменная для хранения данных о количестве книг, автором или соавтором является Стивен Фейерштейн. |
| 4 | Ключевое слово BEGIN указывает на начало исполняемого раздела — кода, который будет выполнен при передаче этого блока в SQL*Plus |
| 5–8 | Запрос, определяющий общее количество книг, автором или соавтором которых является Стивен Фейерштейн. Особенно интересна строка 6: использованная в ней секция INTO на самом деле не является частью инструкции SQL, а связывает базу данных с локальными переменными PL/SQL |
| 10–13 | Для вывода количества книг используется встроенная процедура DBMS_OUTPUT.PUT_LINE (то есть процедура из пакета DBMS_OUTPUT, входящего в состав Oracle) |
| 15 | Однострочный комментарий, объясняющий назначение инструкции UPDATE |
| 16–18 | Чтобы изменить написание имени автора на Stephen, необходимо обновить таблицу books. Поиск всех вхождений слова STEVEN и замена их на STEPHEN осуществляется встроенной функцией REPLACE |
Управляющие конструкции и логические условия
Полный набор команд циклов и итеративных вычислений. К этой группе относятся команды FOR, WHILE и LOOP.
GOTO. Да, в PL/SQL есть даже GOTO — команда безусловной передачи управления из одной точки программы в другую. Впрочем, это не означает, что ей следует пользоваться.
Следующая процедура (многократно используемый блок кода, который можно вызывать по имени) демонстрирует работу отдельных команд:
Структура программного кода описана в следующей таблице.
| Строки | Описание |
| 1–2 | Заголовок процедуры, уменьшающей баланс банковского счета с целью оплаты счетов. В строке 2 перечислен список параметров процедуры, состоящий из одного входного значения (идентификационного номера банковского счета) |
| 3–4 | Раздел объявлений процедуры. Обратите внимание: вместо ключевого слова DECLARE, как в предыдущем примере, я использую ключевое слово IS (или AS) для отделения заголовка от объявлений |
| 6–15 | Пример простого цикла LOOP. Команда EXIT (строка 11) определяет условие завершения цикла; в циклах FOR и WHILE условие завершения цикла определяется по-другому |
| 7 | Вызов функции account_balance, определяющей баланс счета. Это пример вызова одной многократно используемой программы из другой. Вызов процедуры из другой продемонстрирован в строке 13 |
| 9–14 | Команда IF, которую можно интерпретировать так: «Если баланс счета окажется меньше 1000 долларов, прекратить оплату счетов. В противном случае оплатить следующий счет» |
Обработка ошибок PL/SQL
Язык PL/SQL предоставляет разработчикам мощный механизм оповещения о возникающих ошибках и их обработки. Следующая процедура получает имя и баланс счета по идентификатору, после чего проверяет баланс. При слишком низком значении процедура явно инициирует исключение, которое прекращает выполнение программы:
Рассмотрим подробнее ту часть кода, которая связана с обработкой ошибок.
| Строки | Описание |
| 5 | Объявление пользовательского исключения с именем l_balance_below_minimum. В Oracle имеется набор заранее определенных исключений, таких как DUP_VAL_ON_INDEX, но для данного приложения я хочу создать нечто более конкретное, поэтому определяю собственный тип исключения |
| 8–11 | Запрос для получения имени счета. Если счет с указанным идентификатором не существует, Oracle инициирует стандартное исключение NO_DATA_FOUND, что ведет к завершению программы |
| 19–22 | Если баланс слишком низок, процедура явно инициирует пользовательское исключение, поскольку это свидетельствует о наличии серьезных проблем со счетом |
| 24 | Ключевое слово EXCEPTION отмечает конец исполняемого раздела и начало раздела исключений, в котором обрабатываются ошибки |
| 25–28 | Блок обработки ошибок для ситуации, когда счет не найден. Если было инициировано исключение NO_DATA_FOUND, здесь оно перехватывается, а ошибка регистрируется в журнале процедурой log_error. Затем я заново инициирую то же самое исключение, чтобы внешний блок был в курсе того, что для идентификатора счета отсутствует совпадение |
| 30–33 | Блок обработки ошибок для ситуации, когда баланс счета оказался слишком низким (пользовательское исключение для данного приложения). Если было инициировано исключение l_balance_below_minimum, оно перехватывается и ошибка регистрируется в журнале. Затем я инициирую системное исключение VALUE_ERROR, чтобы оповестить внешний блок о проблеме |
Конечно, о PL/SQL еще можно сказать очень много — собственно, именно поэтому материал этой книги занимает не одну сотню страниц! Но эти примеры дают некоторое представление о коде PL/SQL, его важнейших синтаксических элементах и о той простоте, с которой пишется (и читается) код PL/SQL.
Версии PL/SQL
Каждая версия базы данных Oracle выходит с собственной версией PL/SQL, расширяющей функциональные возможности языка. Поэтому каждый программист должен прикладывать немало усилий, чтобы просто не отставать от эволюции PL/SQL. Необходимо постоянно осваивать новые возможности каждой версии, чтобы знать, как пользоваться ими в приложениях, и решать, оправдает ли их полезность модификацию уже существующих приложений.
В таблице 1 представлены основные средства всех версий PL/SQL — как старых, так и современных. (Учтите, что в ранних версиях Oracle номера версий PL/SQL отличались от версий базы данных, но начиная с Oracle8, версии совпадают.) В таблице приведен очень краткий обзор новых возможностей каждой версии. После таблицы следуют более подробные описания новых возможностей PL/SQL новейшей версии Oracle — Oracle Database 12c.
Каждый пакет Oracle Developer содержит собственную версию PL/SQL, которая обычно отстает от версии, доступной в самой СУБД. В этой статье основное внимание уделяется программированию PL/SQL на стороне сервера.
| Версия Oracle | Характеристики |
| 6.0 | Исходная версия PL/SQL (1.0), использовавшаяся главным образом как сценарный язык в SQL*Plus (она еще не позволяла создавать именованные программы с возможностью многократного вызова) и как язык программирования в SQL*Forms3 |
| 7.0 | Обновление первой версии (2.0). Добавлена поддержка хранимых процедур, функций, пакетов, определяемых программистом записей, таблиц PL/SQL и многочисленных расширений |
| 7.1 | Версия PL/SQL (2.1) поддерживает определяемые программистом подтипы данных, возможность использования хранимых функций в SQL-инструкциях, динамический SQL (посредством пакета DBMS_SQL). С появлением версии PL/SQL 2.1 стало возможным выполнять инструкции DDL из программ PL/SQL |
| 7.3 | В этой версии PL/SQL (2.3) были расширены возможности коллекций, усовершенствовано удаленное управление связями между таблицами, добавлены средства файлового ввода/вывода (пакет UTF_FILE) и завершена реализация курсорных переменных |
| 8.0 | Новый номер версии (8.0) отражает стремление Oracle синхронизировать номера версий PL/SQL с соответствующими номерами версий СУБД. PL/SQL 8.0 поддерживает многие усовершенствования Oracle8, включая большие объекты (LOB), объектно-ориентированную структуру и принципы разработки, коллекции (VARRAY и вложенные таблицы), а также средство организации очередей Oracle/AQ (Oracle/Advanced Queuing) |
| 8.1 | Это первая из i-серий Oracle (базы данных для Интернета). В соответствующую версию PL/SQL включен впечатляющий набор новых средств и возможностей, в том числе новая версия динамического SQL, поддержка Java для доступа к базе данных, модель процедур с правами вызывающего, разрешения на выполнение, автономные транзакции, высокопроизводительный «пакетный» язык DML и запросы |
| 9.1 | Версия Oracle 9i Database Release 1 появилась вскоре после Oracle 8i. В ее первом выпуске была реализована поддержка наследования объектных типов, табличные функции и выражения с курсорами (позволяющие организовать параллельное выполнение функций PL/SQL), многоуровневые коллекции, конструкция CASE и выражения CASE |
| 9.2 | В Oracle 9i Database Release 2 главный акцент сделан на языке XML, но есть и другие усовершенствования для разработчиков PL/SQL: ассоциативные массивы (индексируемые не только целыми числами, но и строками VARCHAR2), язык DML на базе записей (позволяющий, например, выполнять вставку с использованием записи), а также многочисленные улучшения пакета UTL_FILE (который теперь позволяет выполнять чтение/запись файлов из программы PL/SQL) |
| 10.1 | Версия Oracle Database 10g Release 1 была выпущена в 2004 году. Основное внимание в ней уделялось решетчатой обработке данных, с улучшенным/автоматизированным управлением базой данных. С точки зрения PL/SQL, самые важные новые функции, оптимизированный компилятор и предупреждения на стадии компиляции, были введены абсолютно прозрачно для разработчиков |
| 10.2 | Версия Oracle Database 10g Release 2, выпущенная в 2005 году, содержала небольшое количество новых функций для разработчиков PL/SQL — прежде всего, препроцессор с возможностью условной компиляции фрагментов программного кода в зависимости от определяемых разработчиком логических условий |
| 11.1 | Версия Oracle Database 11g Release 1 появилась в 2007 году. Важнейшей новой функцией для разработчиков PL/SQL был кэш результатов функций, но появились и другие удобства: составные триггеры, команда CONTINUE и низкоуровневая компиляция, генерирующая машинный код |
| 11.2 | Версия Oracle Database 11g Release 2 вышла осенью 2009 года. Самым важным новшеством стала возможность оперативного переопределения, позволяющая администраторам изменять приложения на ходу, во время их выполнения пользователями |
| 12.1 | Версия Oracle Database 12c Release 1 вышла в июне 2013 года. Она предлагает ряд усовершенствований в области управления доступом и привилегиями программных модулей и представлений; обеспечивает дополнительную синхронизацию языков SQL и P, особенно в отношении максимальной длины VARCHAR2 и динамического связывания SQL; поддерживает определение простых функций в конструкциях SQL и добавляет пакет UTL_CALL_STACK для детализированного управления доступом к стеку вызовов, стеку ошибок и обратной трассировке |
Новые возможности PL/SQL в Oracle Database 12c
Oracle Database 12c предоставляет ряд новых возможностей, которые повышают производительность кода PL/SQL и делают его более удобным. Также были устранены некоторые недоработки в языке. Ниже приводится сводка важнейших изменений языка для разработчиков PL/SQL.
Проникновение новых типов данных PL/SQL через границу PL/SQL/SQL
До выхода версии 12.1 привязка типов данных, специфических для PL/SQL (например, ассоциативных массивов), в динамических командах SQL была невозможна. Теперь значения типов данных, поддерживаемых только в PL/SQL, могут привязываться в анонимных блоках, вызовах функций PL/SQL в запросах SQL, командах CALL и в операторе TABLE в запросах SQL.
Условие ACCESSIBLE_BY
Неявные результаты команд PL/SQL
Представления BEQUEATH CURRENT_USER
Предоставление ролей программным модулям
До выхода Oracle Database 12c модули с правами вызывающего всегда выполнялись с привилегиями вызывающей стороны. Если вызывающая сторона имела более высокие привилегии, чем владелец, то модуль с правами вызывающего мог выполнять операции, непредусмотренные владельцем (или запрещенные для него).
В версии 12.1 вы можете назначать роли отдельным пакетам PL/SQL и автономным подпрограммам. Вместо модуля с правами определяющего можно создать модуль с правами вызывающего и назначить ему нужные роли. В этом случае модуль с правами вызывающего выполняется с привилегиями вызывающего и ролями, но без дополнительных привилегий, присущих схеме определяющего.
Привилегия INHERIT PRIVILEGES предоставляется всем схемам при установке/обновлении.
Новые директивы условной компиляции
Оптимизация выполнения функций в SQL
Использование %ROWTYPE с невидимыми столбцами
Oracle Database 12c позволяет определять невидимые столбцы. В PL/SQL атрибут %ROWTYPE поддерживает такие столбцы и работу с ними.
FETCH FIRST и BULK COLLECT
Пакет UTL_CALL_STACK
До выхода версии 12.1 пакет DBMS_UTILIT Y предоставлял три функции ( FORMAT_CALL_STACK, FORMAT_ERROR_STACK и FORMAT_ERROR_BACKTRACE ) для получения информации о стеке вызовов, стеке ошибок и обратной трассировке соответственно. В версии 12.1 пакет UTL_CALL_STACK предоставляет ту же информацию, а также более точные средства управления доступом к содержимому этих отформатированных строк.
Ресурсы для разработчиков PL/SQL
Первое издание данной книги вышло в 1995 году. Тогда это событие произвело настоящую сенсацию — это была первая независимая (то есть не связанная с компанией Oracle) книга о PL/SQL, которую давно и с нетерпением ожидали разработчики во всем мире. С тех пор появилось множество PL/SQL-ресурсов, среди которых различного рода книги, среды разработки, утилиты и веб-сайты (но разумеется, эта книга остается самым важным и ценным из них!).
Многие из этих ресурсов кратко описаны в следующих разделах. Пользуясь этими ресурсами, многие из которых относительно недороги, а часть вообще предоставляется бесплатно, вы сможете значительно повысить свою квалификацию (и качество вашего кода).
Книги о PL/SQL от O’Reilly
За прошедшие годы серия книг о PL/SQL издательства O’Reilly & Associates представлена уже довольно внушительным списком. Более подробная информация об этих изданиях представлена на сайте издательства.
Oracle PL/SQL Programming (авторы Steven Feuerstein, Bill Pribyl). Книга в 1300 страниц, которую вы сейчас читаете. Это настольная книга для многих профессиональных программистов PL/SQL, в которой рассматриваются все возможности базового языка. В этой версии описана версия Oracle11g Release 2.
Learning Oracle PL/SQL (авторы Bill Pribyl, Steven Feuerstein). Упрощенное введение в язык PL/SQL для новичков в программировании и тех, кто переходит к PL/SQL с других языков.
Oracle PL/SQL Best Practices (автор Steven Feuerstein). Относительно небольшая книга с десятками полезных советов, рекомендаций и приемов, которые помогут читателю писать качественный код PL/SQL. Эта книга является чем-то вроде краткого конспекта по PL/SQL. Второе издание содержит полностью переработанный материал, который строится на истории группы разработчиков из вымышленной компании My Flimsy Excuse.
Oracle PL/SQL Developer’s Workbook (авторы Steven Feuerstein, Andrew Odewahn). Содержит серию вопросов и ответов, помогающих разработчику проверить и дополнить свои знания о языке. В книге рассматриваются возможности языка вплоть до версии Oracle8i, но, конечно, большинство примеров работает и в последующих версиях базы данных.
Oracle Built-in Packages (авторы Steven Feuerstein, Charles Dye, John Beresniewicz). Справочное руководство по всем стандартным пакетам, входящим в комплект поставки Oracle. Эти пакеты позволяют упростить трудную работу, а иногда даже сделать невозможное. Рассматриваются версии до Oracle8 включительно, но подробные объяснения и примеры будут чрезвычайно полезны и в последующих версиях базы данных.
Oracle PL/SQL for DBAs (авторы Arup Nanda, Steven Feuerstein). С выходом каждой новой версии Oracle язык PL/SQL играет все более важную роль в работе администраторов баз данных (БД). Это объясняется двумя основными причинами. Во-первых, многие административные функции доступны через интерфейс пакетов PL/SQL. Чтобы пользоваться ими, необходимо также писать и запускать программы PL/SQL. Во-вторых, практический опыт PL/SQL необходим администратору БД и для того, чтобы выявлять проблемы в чужом коде. Материал, представленный в книге, поможет администратору БД быстро освоить PL/SQL для повседневной работы.
Oracle PL/SQL Language Pocket Reference (Steven Feuerstein, Bill Pribyl, Chip Dawes). Маленький, но очень полезный справочник с описанием базовых элементов языка PL/ SQL вплоть до Oracle11g.
Oracle PL/SQL Built-ins Pocket Reference (авторы Steven Feuerstein, John Beresniewicz, Chip Dawes). Еще один лаконичный справочник с краткими описаниями всех функций и пакетов вплоть до Oracle8.
PL/SQL в Интернете
Программисты PL/SQL найдут в Сети много полезных ресурсов. В списке в основном представлены те ресурсы, для которых соавторы предоставляли свои материалы или помогали управлять контентом.
PL/SQL Obsession. Интернет-портал Стивена Фейерштейна содержит ссылки на различные ресурсы PL/SQL: презентации, примеры кода, бесплатные программы (некоторые из них упоминаются в следующем разделе), видеоролики и т. д.
PL/SQL Challenge. Сайт, основанный на концепции «активного изучения», — вместо того, чтобы читать книгу или веб-страницу, вы отвечаете на вопросы по PL/SQL, SQL, логике, проектированию баз данных и Oracle Application Express, проверяя свои познания в этих областях.
PL/SQL Channel. Библиотека видеоуроков (на 27 с лишним часов) по языку Oracle PL/ SQL, записанных Стивеном Фейерштейном.
Oracle Technology Network. Сайт OTN (Oracle Technology Network) «предоставляет сервисы и ресурсы, необходимые разработчикам для создания, тестирования и распространения приложений» на основе технологии Oracle. Он знаком миллионам разработчиков: здесь можно загрузить программное обеспечение, документацию и множество примеров кода. PL/SQL также имеет собственную страницу на сайте OTN.
Quest Error Manager. Инфраструктура для стандартизации обработки ошибок в приложениях на базе PL/SQL. При помощи QEM вы сможете организовать регистрацию и оповещение об ошибках через универсальный API с минимальными усилиями. Информация об ошибках сохраняется в таблицах экземпляров (общая информация об ошибке) и контекста (пары «имя-значение», смысл которых определяется конкретным приложением).
oracle-developer.net. Сайт поддерживается Эдрианом Биллингтоном. Он предоставляет разработчикам баз данных Oracle замечательную подборку статей, учебников и вспомогательных инструментов. Эдриан углубленно рассматривает новые возможности каждой версии Oracle Database, приводя многочисленные примеры, сценарии анализа производительности и т. д.
ORACLE-BASE. ORACLE-BASE — еще один превосходный ресурс для специалистов по технологиям Oracle, созданный и сопровождаемый экспертом по Oracle: Тимом Холлом. Тим является обладателем звания Oracle ACE Director, участником сети OakTable Network, а также обладателем премии Oracle Magazine Editor’s Choice Awards в номинации «ACE of the Year». Занимается администрированием, проектированием и разработкой баз данных Oracle с 1994 года. См. http://oracle-base.com.
Несколько советов
С 1995 года, когда вышло в свет первое издание настоящей книги, мне представилась возможность обучать десятки тысяч разработчиков PL/SQL, помогать им и сотрудничать с ними. За это время мы многому научились и у наших читателей, составили более полное представление об оптимальных методах работы с PL/SQL. Надеюсь, вы позволите нам поделиться с вами нашими представлениями о том, как эффективнее работать с таким мощным языком программирования.
Не торопитесь!
Мы почти всегда работаем в очень жестких временных рамках. Времени вечно не хватает, ведь нам за короткое время нужно написать огромное количество кода. Итак, нужно поскорее приступать к работе — не так ли?
Нет, не так. Если сразу же углубиться в написание программного кода, бездумно преобразуя постановку задачи в сотни, тысячи и даже десятки тысяч строк, получится просто «каша», которую не удастся ни отладить, ни снабдить достойным сопровождением. Но и в жесткий график вполне можно уложиться, если не поддаваться панике и тщательно все спланировать.
Мы настоятельно рекомендуем не поддаваться давлению времени. Тщательно подготовьтесь к написанию нового приложения или программы.
Это лишь некоторые важные аспекты, на которые следует обратить внимание, приступая к написанию программного кода. Помните, что спешка при разработке приводит к многочисленным ошибкам и огромным потерям времени.
Не бойтесь обращаться за помощью
Вы, профессиональный программист, наверняка очень умны, много учились, повышали свою квалификацию и теперь неплохо зарабатываете. Вам под силу решить практически любую задачу и вы этим гордитесь. Но к сожалению, успехи нередко делают нас самоуверенными, и мы не любим обращаться за помощью даже при возникновении серьезных затруднений. Такое отношение к делу опасно и деструктивно.
Программное обеспечение пишется людьми. И поэтому очень важно понимать, что огромную роль в его разработке играет психологический фактор. Приведу простой пример.
У Джо, руководителя группы из шести разработчиков, возникает проблема с его кодом. Он бьется над ней часами, все больше выходя из себя, но так и не может найти источник ошибки. Ему и в голову не приходит обратиться за помощью к коллегам, потому что он является самым опытным во всей группе. Наконец, Джо доходит «до точки» и сдается. Он со вздохом снимает телефонную трубку и набирает добавочный: «Сандра, ты можешь зайти и взглянуть на мою программу? У меня какая-то проблема, с которой я никак не могу разобраться». Сандра заходит и с первого взгляда на программу Джо указывает на то, что должно было быть очевидно несколько часов назад. Ура! Программа исправлена, Джо благодарит, но на самом деле он тайно переживает.
У него в голове проносятся мысли «Почему же я этого не заметил?» и «А если бы я потратил еще пять минут на отладку, то нашел бы сам». Все это понятно, но довольно глупо. Короче говоря, часто мы не можем найти свои проблемы, потому что находимся слишком близко к собственному коду. Иногда нужен просто свежий взгляд со стороны, и это не имеет ничего общего ни с опытом, ни с особыми знаниями.
Именно поэтому мы рекомендуем придерживаться следующих простых правил.
Поощряйте творческий (и даже радикальный) подход к разработке приложений PL/SQL
Мы склонны превращать в рутину практически все составляющие нашей жизни. Мы привыкаем писать код определенным образом, делаем определенные допущения о продукте, отвергаем возможные решения без серьезного анализа, потому что заранее знаем, что это сделать нельзя. Разработчики крайне необъективны в оценке своих программ и часто закрывают глаза на явные недостатки. Иногда можно услышать: «Этот код не будет работать быстрее», «Я не могу сделать то, что хочет пользователь; придется подождать следующей версии», «С продуктом X, Y или Z все было бы легко и быстро, а с такими средствами приходится буквально сражаться за каждую мелочь». Но на самом деле выполнение практически любого кода можно немного ускорить. И программа может работать именно так, как хочет пользователь. И хотя у каждого продукта имеются свои ограничения, сильные и слабые стороны, не нужно дожидаться выхода следующей версии. Лучше встретить проблему лицом к лицу и, не позволяя себе никаких отговорок, найти ее решение.
Как это сделать? Следует отказаться от некоторых представлений и посмотреть на мир свежим взглядом. Пересмотрите выработанные годами профессиональные привычки. Относитесь к задаче творчески — постарайтесь отступить от традиционных методов, от зачастую ограниченных и механистических подходов.
Не бойтесь экспериментировать, даже если ваши идеи покажутся радикальным отступлением от нормы. Вы будете удивлены тем, как многому можно научиться таким образом; вырастете как программист, способный к решению нестандартных задач. Многое становится возможным, когда вы перестаете говорить: «Это невозможно!», а, наоборот, спокойно киваете и бормочете: «А если попробовать так…»