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. Если вы написали на PL/SQL процедуру или функцию для базы данных Oracle, находящейся на портативном компьютере, то эту же процедуру можно будет перенести в базу данных на компьютере корпоративной сети и выполнить ее без каких-либо изменений (конечно, при условии совместимости версий Oracle). «Написать один раз и использовать везде» — этот основной принцип PL/SQL был известен задолго до появления языка Java. Впрочем, «везде» в данном случае означает «при работе с любой базой данных Oracle».
- Встроенный язык. PL/SQL не используется как самостоятельный язык программирования. Это встроенный язык, работающий только в конкретной управляющей среде. Таким образом, программы PL/SQL можно запускать из базы данных (скажем, через интерфейс SQL*Plus). Также возможно определение и выполнение программ PL/SQL из формы или отчета Oracle Developer (клиентский PL/SQL). Однако вы не сможете создать исполняемый файл программы на PL/SQL и запускать его автономно.
- Высокопроизводительный, высокоинтегрированный язык баз данных. В настоящее время существует много способов написания программ, работающих с базами данных Oracle. Например, можно использовать Java и JDBC или Visual Basic и ODBC, а можно воспользоваться, скажем, Delphi, C++ и т. д. Однако эффективный код для работы с базой данных Oracle проще написать на PL/SQL, чем на любом другом языке программирования. В частности, Oracle имеет несколько расширений, предназначенных специально для PL/SQL, таких как инструкция FORALL, позволяющая повысить производительность обработки запросов на порядок и более.
История 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 занимает особое место как один из пионеров в этой области и, конечно, продолжает применяться разработчиками для создания легко переносимого кода приложений баз данных.
Улучшенная защита приложений и защита целостности транзакций
Защита приложений была еще более важным аспектом, чем переносимость. База данных и язык SQL позволяют жестко контролировать доступ к любым таблицам базы данных (и внесение изменений в них). Например, с командой GRANT вы можете быть уверены в том, что выполнение команды UPDATE с конкретной таблицей будет разрешено только определенным ролям и пользователям. С другой стороны, команда GRANT не может гарантировать, что пользователь внесет правильную последовательность изменений в одну или несколько таблиц, необходимых для большинства бизнес-транзакций.
Язык PL/SQL обеспечивает строгий контроль за выполнением логических транзакций. Одним из средств такого контроля является система полномочий на выполнение. Вместо того чтобы выдавать разрешения на обновление таблиц ролям или пользователям, вы выдаете разрешение только на выполнение процедуры, которая управляет и предоставляет доступ к необходимым структурам данных. Владельцем процедуры является другая схема базы данных Oracle («определитель»), которой, в свою очередь, предоставляются разрешения на обновление таблиц, участвующих в транзакции. Таким образом, процедура становится «привратником» транзакции. Программный код (будь то приложение Oracle Forms или исполняемый файл Pro*C) может выполняться только посредством вызова процедуры, и это гарантирует целостность транзакций приложения.
Начиная с Oracle8i, в Oracle появилось ключевое слово AUTHID, значительно расширяющее гибкость модели защиты PL/SQL. Оно позволяет выполнять программы в соответствии с описанной ранее моделью прав определителя или же выбрать режим AUTHID CURRENT_USER, при котором программы выполняются с правами текущей учетной записи. Это всего лишь один из примеров развития и повышения гибкости PL/SQL.
Скромное начало, постоянное усовершенствование
Язык 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 прост в сравнении с другими языками программирования. Программа делится на «блоки» с разными разделами, четко идентифицируемыми с помощью понятных ключевых слов.
Рассмотрим несколько примеров, демонстрирующих применение ключевых элементов структуры и функциональности PL/SQL.
Интеграция с SQL
Одним из важнейших аспектов PL/SQL является его тесная интеграция с SQL. Для выполнения SQL-инструкций в программах на PL/SQL не требуется никакой промежуточной программной «прослойки» вроде ODBC (Open Database Connectivity) или JDBC ( Java Database Connectivity). Инструкция UPDATE илиSELECT просто вставляется в программный код, как в следующем примере.
DECLARE
l_book_count INTEGER;
BEGIN
SELECT COUNT(*)
INTO l_book_count
FROM books
WHERE author LIKE '%FEUERSTEIN, STEVEN%';
DBMS_OUTPUT.PUT_LINE (
'Стивен является автором (или соавтором) ' ||
l_book_count ||
' книг.');
-- Я решил изменить написание своего имени ...
UPDATE books
SET author = REPLACE (author, 'STEVEN', 'STEPHEN')
WHERE author LIKE '%FEUERSTEIN, STEVEN%';
END;Теперь посмотрим, что делает этот код. Его подробное описание дано в следующей таблице.
| Строки | Описание |
| 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 |
Управляющие конструкции и логические условия
PL/SQL содержит полный набор команд, предназначенных для управления последовательностью выполнения строк программы. В него входят следующие команды: IF и Реализация условной логики выполнения — например, «Если количество книг больше 1000, то...»CASE.
Полный набор команд циклов и итеративных вычислений. К этой группе относятся команды FOR, WHILE и LOOP.
GOTO. Да, в PL/SQL есть даже GOTO — команда безусловной передачи управления из одной точки программы в другую. Впрочем, это не означает, что ей следует пользоваться.
Следующая процедура (многократно используемый блок кода, который можно вызывать по имени) демонстрирует работу отдельных команд:
PROCEDURE pay_out_balance (
account_id_in IN accounts.id%TYPE)
IS
l_balance_remaining NUMBER;
BEGIN
LOOP
l_balance_remaining := account_balance (account_id_in);
IF l_balance_remaining < 1000
THEN
EXIT;
ELSE
apply_balance (account_id_in, l_balance_remaining);
END IF;
END LOOP;
END pay_out_balance;Структура программного кода описана в следующей таблице.
| Строки | Описание |
| 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 предоставляет разработчикам мощный механизм оповещения о возникающих ошибках и их обработки. Следующая процедура получает имя и баланс счета по идентификатору, после чего проверяет баланс. При слишком низком значении процедура явно инициирует исключение, которое прекращает выполнение программы:
PROCEDURE check_account (
account_id_in IN accounts.id%TYPE)
IS
l_balance_remaining NUMBER;
l_balance_below_minimum EXCEPTION;
l_account_name accounts.name%TYPE;
BEGIN
SELECT name
INTO l_account_name
FROM accounts
WHERE id = account_id_in;
l_balance_remaining := account_balance (account_id_in);
DBMS_OUTPUT.PUT_LINE (
'Баланс счета ' || l_account_name ||
' = ' || l_balance_remaining);
IF l_balance_remaining < 1000
THEN
RAISE l_balance_below_minimum;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
-- Ошибочный идентификатор счета
log_error (...);
RAISE;
WHEN l_balance_below_minimum
THEN
log_error (...);
RAISE VALUE_ERROR;
END;Рассмотрим подробнее ту часть кода, которая связана с обработкой ошибок.
| Строки | Описание |
| 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 и в операторе в запросах SQL.TABLE
Условие ACCESSIBLE_BY
Теперь в спецификацию пакета можно добавить секцию ACCESSIBLE_BY, указывающую, какие программные модули могут вызывать подпрограммы этого пакета. Данная возможность позволяет открыть доступ к подпрограммам во вспомогательных пакетах, предназначенных только для конкретных программных модулей. Фактически речь идет о своего рода «белых списках» для пакетов.
Неявные результаты команд PL/SQL
До выхода Oracle Database 12c хранимая подпрограмма PL/SQL явно возвращала итоговые наборы из запросов SQL, через параметр OUT REF CURSOR или условиеRETURN. Клиентская программа должна была осуществить явную привязку параметров для получения итогового набора. Теперь хранимая подпрограмма PL/SQL может неявно вернуть результаты запроса клиенту при помощи пакета PL/SQL DBMS_SQL (вместо параметров OUT REF CURSOR). Эта функциональность упрощает миграцию приложений, полагающихся на неявное возвращение результатов запросов из хранимых подпрограмм (возможность поддерживается такими языками, как Transact SQL) из сторонних баз данных в Oracle.
Представления BEQUEATH CURRENT_USER
До выхода Oracle Database 12c представление всегда вело себя так, как если бы оно обладало правами определяющего (AUTHID DEFINER), даже если оно использовалось в модуле, обладающем правами вызывающего (AUTHID CURRENT_USER). Теперь представление может определяться с ключевыми словами BEQUEATH DEFINER (используется по умолчанию ), при котором оно использует поведение с правами определяющего, или BEQUEATH CURRENT_USER, с которыми его поведение близко к поведению с правами вызывающего.
Предоставление ролей программным модулям
До выхода Oracle Database 12c модули с правами вызывающего всегда выполнялись с привилегиями вызывающей стороны. Если вызывающая сторона имела более высокие привилегии, чем владелец, то модуль с правами вызывающего мог выполнять операции, непредусмотренные владельцем (или запрещенные для него).
В версии 12.1 вы можете назначать роли отдельным пакетам PL/SQL и автономным подпрограммам. Вместо модуля с правами определяющего можно создать модуль с правами вызывающего и назначить ему нужные роли. В этом случае модуль с правами вызывающего выполняется с привилегиями вызывающего и ролями, но без дополнительных привилегий, присущих схеме определяющего.
Модуль с правами вызывающего теперь может выполняться с привилегиями вызывающего только в том случае, если его владелец обладает привилегией INHERIT PRIVILEGES для вызывающего или привилегией INHERIT ANY PRIVILEGES.
Привилегия INHERIT PRIVILEGES предоставляется всем схемам при установке/обновлении.
Новые директивы условной компиляции
В версии 12.1 в Oracle были добавлены две новые стандартные директивы $$PLSQL_UNIT_OWNER и , которые возвращают владельца и тип текущего программного модуля PL/SQL.$$PLSQL_UNIT_TYPE
Оптимизация выполнения функций в SQL
Oracle предоставляет два новых способа улучшения производительности функций PL/ SQL в командах SQL: разработчик может определить функцию внутри команды SQL при помощи условия WITH или добавить в программный модуль директиву UDF, которая сообщает компилятору, что функция будет использоваться в основном в командах SQL.
Использование %ROWTYPE с невидимыми столбцами
Oracle Database 12c позволяет определять невидимые столбцы. В PL/SQL атрибут %ROWTYPE поддерживает такие столбцы и работу с ними.
FETCH FIRST и BULK COLLECT
В версии 12.1 можно использовать необязательное условие FETCH FIRST для ограничения количества строк, возвращаемых запросом, что существенно снижает SQL-сложность стандартных запросов «верхних N результатов». Наибольшую пользу FETCH FIRST принесет при миграции сторонних баз данных в Oracle Database. Впрочем, эта конструкция также способна повысить быстродействие некоторых командSELECT BULK COLLECT INTO.
Пакет UTL_CALL_STACK
До выхода версии 12.1 пакет DBMS_UTILITY предоставлял три функции (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. Надеюсь, вы позволите нам поделиться с вами нашими представлениями о том, как эффективнее работать с таким мощным языком программирования.
Не торопитесь!
Мы почти всегда работаем в очень жестких временных рамках. Времени вечно не хватает, ведь нам за короткое время нужно написать огромное количество кода. Итак, нужно поскорее приступать к работе — не так ли?
Нет, не так. Если сразу же углубиться в написание программного кода, бездумно преобразуя постановку задачи в сотни, тысячи и даже десятки тысяч строк, получится просто «каша», которую не удастся ни отладить, ни снабдить достойным сопровождением. Но и в жесткий график вполне можно уложиться, если не поддаваться панике и тщательно все спланировать.
Мы настоятельно рекомендуем не поддаваться давлению времени. Тщательно подготовьтесь к написанию нового приложения или программы.
- Создайте сценарии тестирования. До того как будет написана первая строка кода, необходимо продумать, каким образом будет проверяться реализация задачи. Это позволит вам заранее продумать интерфейс программы и ее функциональность.
- Установите четкие правила написания SQL-команд в приложении. В общем случае мы рекомендуем разработчикам не включать в программы большой объем SQL- кода. Большинство инструкций, в том числе запросы на обновление и вставку отдельных записей, должны быть «скрыты» в заранее написанных и тщательно отлаженных процедурах и функциях (это называется инкапсуляцией данных). Такие программы оптимизируются, тестируются и сопровождаются более эффективно, чем SQL-инструкции, хаотично разбросанные по программному коду (многие из которых неоднократно повторяются в приложении).
- Установите четкие правила обработки исключений в приложении. Желательно создать единый пакет обработки ошибок, который скрывает все подробности ведения журнала ошибок, определяет механизм инициирования исключений и их распространения во вложенных блоках, а также позволяет избежать жесткого кодирования исключений, специфических для конкретного приложения, прямо в программном коде. Этим пакетом должны пользоваться все разработчики — в таком случае им не придется писать сложный, отнимающий много времени код обработки ошибок.
- Выполняйте «пошаговую проработку». Другими словами, придерживайтесь принципа нисходящего проектирования, чтобы уменьшить сложность кода, с которым вы имеете дело в каждый конкретный момент. Применяя данный подход, вы обнаружите, что исполняемые разделы ваших модулей становятся короче и проще для понимания. В будущем такой код будет проще сопровождать и модифицировать. Важнейшую роль в реализации этого принципа играет использование локальных, или вложенных, модулей.
Это лишь некоторые важные аспекты, на которые следует обратить внимание, приступая к написанию программного кода. Помните, что спешка при разработке приводит к многочисленным ошибкам и огромным потерям времени.
Не бойтесь обращаться за помощью
Вы, профессиональный программист, наверняка очень умны, много учились, повышали свою квалификацию и теперь неплохо зарабатываете. Вам под силу решить практически любую задачу и вы этим гордитесь. Но к сожалению, успехи нередко делают нас самоуверенными, и мы не любим обращаться за помощью даже при возникновении серьезных затруднений. Такое отношение к делу опасно и деструктивно.
Программное обеспечение пишется людьми. И поэтому очень важно понимать, что огромную роль в его разработке играет психологический фактор. Приведу простой пример.
У Джо, руководителя группы из шести разработчиков, возникает проблема с его кодом. Он бьется над ней часами, все больше выходя из себя, но так и не может найти источник ошибки. Ему и в голову не приходит обратиться за помощью к коллегам, потому что он является самым опытным во всей группе. Наконец, Джо доходит «до точки» и сдается. Он со вздохом снимает телефонную трубку и набирает добавочный: «Сандра, ты можешь зайти и взглянуть на мою программу? У меня какая-то проблема, с которой я никак не могу разобраться». Сандра заходит и с первого взгляда на программу Джо указывает на то, что должно было быть очевидно несколько часов назад. Ура! Программа исправлена, Джо благодарит, но на самом деле он тайно переживает.
У него в голове проносятся мысли «Почему же я этого не заметил?» и «А если бы я потратил еще пять минут на отладку, то нашел бы сам». Все это понятно, но довольно глупо. Короче говоря, часто мы не можем найти свои проблемы, потому что находимся слишком близко к собственному коду. Иногда нужен просто свежий взгляд со стороны, и это не имеет ничего общего ни с опытом, ни с особыми знаниями.
Именно поэтому мы рекомендуем придерживаться следующих простых правил.
- Не бойтесь признаться в том, что чего-то не знаете. Если вы будете скрывать, что вам не все известно о приложении или его программном коде, это кончится плохо. Выработайте в коллективе культуру взаимоотношений, при которой вполне естественными считаются вопросы друг к другу и обоюдные консультации.
- Обращайтесь за помощью. Если вы не можете найти причину ошибки в течение получаса, немедленно просите о помощи. Можно даже организовать систему взаимопомощи, при которой у каждого работника имеется напарник. Не допускайте, чтобы вы (или ваши коллеги) часами мучились с поисками выхода из затруднительного положения.
- Организуйте процесс рецензирования кода. Код должен передаваться в отдел контроля качества или использоваться в проекте только после его предварительного прочтения и конструктивной критики одним или несколькими разработчиками.
Поощряйте творческий (и даже радикальный) подход к разработке приложений PL/SQL
Мы склонны превращать в рутину практически все составляющие нашей жизни. Мы привыкаем писать код определенным образом, делаем определенные допущения о продукте, отвергаем возможные решения без серьезного анализа, потому что заранее знаем, что это сделать нельзя. Разработчики крайне необъективны в оценке своих программ и часто закрывают глаза на явные недостатки. Иногда можно услышать: «Этот код не будет работать быстрее», «Я не могу сделать то, что хочет пользователь; придется подождать следующей версии», «С продуктом X, Y или Z все было бы легко и быстро, а с такими средствами приходится буквально сражаться за каждую мелочь». Но на самом деле выполнение практически любого кода можно немного ускорить. И программа может работать именно так, как хочет пользователь. И хотя у каждого продукта имеются свои ограничения, сильные и слабые стороны, не нужно дожидаться выхода следующей версии. Лучше встретить проблему лицом к лицу и, не позволяя себе никаких отговорок, найти ее решение.
Как это сделать? Следует отказаться от некоторых представлений и посмотреть на мир свежим взглядом. Пересмотрите выработанные годами профессиональные привычки. Относитесь к задаче творчески — постарайтесь отступить от традиционных методов, от зачастую ограниченных и механистических подходов.
Не бойтесь экспериментировать, даже если ваши идеи покажутся радикальным отступлением от нормы. Вы будете удивлены тем, как многому можно научиться таким образом; вырастете как программист, способный к решению нестандартных задач. Многое становится возможным, когда вы перестаете говорить: «Это невозможно!», а, наоборот, спокойно киваете и бормочете: «А если попробовать так…»
