Иван Сергеевич Задворьев
Язык PL/SQL
Введение в PL/SQL
Назначение PL/SQL
PL/SQL – «Procedural Language extensions to the Structured Query Language», что переводится как «Процедурные языковые расширения для языка SQL».
Практически в каждой СУБД корпоративного уровня есть язык программирования, предназначенный для расширения возможностей SQL:
PL/SQL – в Oracle Database Server;
Transact-SQL – в Microsoft SQL Server;
SQL PL – в IBM DB2;
PL/pgSQL – в PostgreSQL.
На этих языках создаются программы, которые хранятся непосредственно в базах данных и выполняются СУБД, поэтому их называют языками хранимых процедур (stored procedure languages). Языки хранимых процедур имеют схожие синтаксис и семантику, поэтому после освоения языка PL/SQL впоследствии можно будет довольно легко перейти, например, на Transact-SQL или PL/pgSQL.
Функция на языке Oracle PL/SQL
Функция на языке PL/pgSQL PostgreSQL
CREATE FUNCTION F1 RETURN INT AS
BEGIN
FOR r IN (SELECT * FROM tab1) LOOP
UPDATE tab2 SET at3=r.at2;
END LOOP;
RETURN 1;
END;
CREATE FUNCTION F1() RETURNS int AS '
DECLARE
r RECORD;
BEGIN
FOR r IN SELECT * FROM tab1 LOOP
UPDATE tab2 SET at3=r.at2;
END LOOP;
RETURN 1;
END;
' LANGUAGE plpgsql;
Задачи, решаемые PL/SQL
PL/SQL, в отличие от Java, Python или C++, не используется для разработки математических приложений, игр и т. п. Это специфичный язык программирования третьего поколения, предназначенный для работы с базами данных Oracle прямо в ядре сервера Oracle. Фактически программы на PL/SQL – это обертки вокруг предложений SQL.
Язык PL/SQL используется для решения следующих задач:
реализация серверной бизнес-логики в виде хранимых программ;
автоматизация задач администрирования баз данных Oracle;
разработка web-приложений;
разработка клиентских приложений в среде Oracle Developer.
Мы не будем останавливаться на автоматизации задач администрирования баз данных и разработке клиентских приложений, а сосредоточимся на главном направлении использования PL/SQL – реализации бизнес-логики на стороне сервера в виде хранимых программ.
Сценарий использования программ PL/SQL
Пусть в корпоративной сети на Linux-сервере находится база данных Oracle со сведениями о клиентах организации. Подключимся к серверу Oracle с ноутбука по сети с помощью утилиты SQL*Plus. Запуск на выполнение из SQL*Plus процедуры PL/SQL calc_clients_debt для расчета клиентской задолженности может выглядеть примерно так:
SQL> BEGIN
2 calc_clients_debt(p_account_from=>100001,p_account_to=>200000);
3 END;
4 /
PL/SQL procedure successfully completed.
Только четыре строки для запуска процедуры calc_clients_debt будут переданы с ноутбука на Linux-сервер, где сервер баз данных Oracle, получив эти строки, выполнит процедуру PL/SQL. На ноутбук обратно вернутся только сведения об успешности завершения работы процедуры – одна строка. Требуемые для расчетов гигабайты финансовых данных для заданного диапазона в 100 000 лицевых счетов на ноутбук по сети передаваться не будут – выборка всех данных клиентов c помощью выполнения SQL из PL/SQL и все расчеты по ним в PL/SQL будут осуществляться ядром СУБД Oracle на мощном Linux-сервере. На этом же сервере, в этой же базе данных Oracle процедурой calc_clients_debt будут сохранены и результаты вычислений.
Так расчет задолженности мог выглядеть, если бы его запускал технический специалист, знающий устройство базы данных и предпочитающий работать с ней в SQL*Plus. Понятно, что сотрудники бухгалтерии или клиентского отдела не работают с базой данных в SQL*Plus. Для них должна быть разработана и установлена клиентская программа на C#, Java или другом языке программирования с экранными формами и отчетами. В этой программе на экранной форме пользователь задает диапазон обрабатываемых лицевых счетов и нажимает кнопку «Рассчитать задолженность».
Клиентская программа через соответствующие программные интерфейсы, которые есть в большинстве современных языков программирования, запускает в Oracle на выполнение хранимую процедуру calc_clients_debt и начинает показывать пользователю наполняющиеся песочные часы или бегающую полоску (progress bar). Сама программа при этом не осуществляет обработку данных, которая в это время идет на удаленном Linux-сервере. Как только хранимая процедура успешно завершится и сервер Oracle сообщит об этом клиентской программе, та выдаст пользователю сообщение «Задолженность успешно рассчитана».
Это типичный сценарий использования PL/SQL: реализация бизнес-логики (в данном примере – расчета клиентской задолженности) в виде хранимой в базе данных процедуры на PL/SQL с ее запуском из клиентской программы, подключившейся к серверу Oracle по сети. Обычно программы на PL/SQL работают «под капотом» и их не видно снаружи.
Достоинства и недостатки хранимых программ
При реализации бизнес-логики вполне можно обойтись и без использования хранимых программ. Так, задачу расчета клиентской задолженности можно решить двумя способами:
разработать одно или несколько (frontend, backend) приложений на Java, JavaScript, C++, Python и т. п., реализующих только пользовательский интерфейс, а бизнес-логику собственно расчета задолженности реализовать в виде хранимой программы, которую вызывают приложения при запуске процесса расчета;
разработать одно или несколько (frontend, backend) приложений на Java, JavaScript, C++, Python и т. п., реализующих и пользовательский интерфейс, и бизнес-логику расчета задолженности.
Для второго способа база данных используется только для хранения данных. Все необходимые данные по каждому клиенту извлекаются приложением из базы, обсчитываются приложением и полученные сведения о задолженности сохраняются обратно в базу. Обсчитывающее данные приложение часто размещают на том же сервере, где находится база данных – чтобы сеть не стала узким местом системы.
Выбор используемого способа решения задачи является обязанностью архитектора системы, при этом следует учитывать много факторов, формируемых в каждом конкретном случае на основе известных достоинств и недостатков использования хранимых программ.
Достоинства хранимых программ:
переносимость хранимых программ вместе с базой данных;
повышенная производительность обработки за счет отсутствия передачи данных вне сервера баз данных;
тесная интеграция с подсистемой выполнения SQL (предложения SQL в хранимых программах выполняются без использования дополнительных интерфейсов и драйверов);
управление доступом к данным на основе хранимых программ (доступ предоставляется не к таблицам базы данных на чтение и запись данных в них, а на выполнение хранимых программ – тем самым выполняется изоляция данных от прикладных программ);
реализация динамических ограничений целостности и концепции активных баз данных с помощью механизма триггеров.
Недостатки хранимых программ:
«размазывание» логики работы системы по нескольким программах, написанных на разных языках;
необходимость наряду c программистами на Java, Python, C++ иметь в команде программиста баз данных;
скудность выразительных возможностей языков хранимых процедур на фоне современных языков Java, Python, C++;
непереносимость хранимых программ между различными СУБД;
возможные проблемы с масштабированием.
Наиболее существенным недостатком хранимых программ является их привязка к конкретной СУБД. Например, при переходе c Oracle на PostgreSQL в рамках актуальной темы импортозамещения, все хранимые программы придется переписывать с PL/SQL на PL/pgSQL, а это приведет к существенным затратам на реинжиниринг кода PL/SQL, объем которого может составлять сотни тысяч строк.
Что же касается проблем масштабирования, то обработка данных непосредственно в базе данных средствами самой СУБД является достоинством хранимых программ до тех пор, пока обеспечивается требуемый уровень производительности. В противном случае это обстоятельство помешает масштабированию, так как установка дополнительных серверов потребует большого объема работ. Придется на каждом новом сервере устанавливать СУБД, создавать свою базу данных с хранимыми программами и решать задачу распределения данных по нескольким базам. Из достоинства хранимых программ интеграция хранения и обработки данных, таким образом, может стать недостатком. С отдельными приложениями, реализующими серверную бизнес-логику без хранимых программ, проблем масштабирования обычно нет – добавить новые сервера только для обсчитывающих приложений обычно довольно легко.
Переносимость программ PL/SQL
Переносимость программ PL/SQL вместе с базой данных обеспечивается архитектурой языка PL/SQL, похожей на архитектуру языка Java.
При программировании на языках C/C++, Pascal в результате работы компилятора получается исполняемый (executable) файл. Этот файл содержит машинные команды конкретной аппаратной платформы и предназначен для работы в конкретной операционной системе. Поэтому если исполняемый файл для Windows скопировать на компьютер с операционной системой Linux, то он там не запустится. Если исполняемый файл для одной аппаратной платформы перенести на другую платформу (компьютер с другими машинными командами), то он там тоже не запустится. В итоге, если требуется обеспечить кроссплатформенность, то для одной и той же программы на C++, приходится иметь версию для Windows и версию для Linux, версию для x86 (32-х разрядную) и версию для x64 (64-х разрядную) и так далее.
Иначе дело обстоит с программированием на Java. В результате работы компилятора Java получается не исполняемый файл с машинными командами, а файл с байт-кодом (bytecode) – машинно-независимым кодом низкого уровня, исполняемым интерпретатором байт-кода. Этот интерпретатор байт-кода называется виртуальной машиной Java (Java Virtual Machine, JVM). При запуске программы Java файл с ее байт-кодом подается на вход виртуальной машине Java, которая преобразует инструкции байт-кода в машинные коды конкретной платформы. Таким образом, для того, чтобы запустить программу на Java в той или иной среде, достаточно иметь для этой среды JVM. Например, в ядре сервера Oracle есть виртуальная машина Aurora JVM, предназначенная для выполнения хранимых в базах данных Oracle программ Java.
В результате работы компилятора PL/SQL тоже получается не исполняемый файл, а байт-код, который называется p-code. Этот байт-код при запуске программ PL/SQL интерпретируется виртуальной машиной PL/SQL (PL/SQL Vitual Machine, PVM), находящейся в ядре СУБД Oracle. Виртуальная машина PL/SQL есть во всех версиях СУБД Oracle для любой операционной системы и для любой аппаратной платформы, поэтому программы PL/SQL остаются работоспособными при переносах баз данных Oracle с одних вычислительных систем на другие.
Оценка языка PL/SQL
Как язык программирования PL/SQL имеет следующие достоинства:
статическая типизация;
наличие средств обработки ошибок и пользовательских исключений;
наличие лаконичных и удобных языковых конструкций для выполнения предложений языка SQL.
Считается, что эффективный высокопроизводительный код для работы с базой данных Oracle проще написать на PL/SQL, чем на любом другом процедурном языке программирования. В частности, в PL/SQL имеются специальные средства массовой обработки данных (bulk processing), позволяющие повысить производительность на порядок и более.
Приведем достаточно большую цитату из книги «Oracle для профессионалов», написанной Томом Кайтом, вице-президентом Oracle Corporation [18, стр. 48]:
«При разработке программного обеспечения базы данных я придерживаюсь достаточно простой философии, которая оставалась неизменной на протяжении многих лет:
Все, что только возможно, должно делаться в одном предложении SQL. Верите или нет, но это возможно почти всегда. С течением времени это утверждение становится еще более справедливым. SQL – исключительно мощный язык.
Если что-то нельзя сделать в одном предложении SQL, то это необходимо реализовать на языке PL/SQL с помощью как можно более краткого кода. Следуйте принципу «больше кода = больше ошибок, меньше кода = меньше ошибок».
Если задачу нельзя решить средствами PL/SQL, попробуйте воспользоваться хранимой процедурой Java. Однако после выхода Oracle 9i и последующих версий потребность в этом возникает очень редко. PL/SQL является полноценным и популярным языком третьего поколения (third-generation programming language – 3GL).
Если задачу не удается решить на языке Java, попробуйте написать внешнюю процедуру C. Именно такой подход применяют наиболее часто, когда нужно обеспечить высокую скорость работы приложения, либо использовать API-интерфейс от независимых разработчиков, реализованный на языке C.
Если вы не можете решить задачу с помощью внешней процедуры C, всерьез задумайтесь над тем, есть ли в ней необходимость.
… Мы будем использовать PL/SQL и его объектные типы для решения задач, которые в SQL решить невозможно или неэффективно. Язык PL/SQL существует уже очень долгое время – на его отработку ушло более 27 лет (к 2015 году); в действительности, возвращаясь к версии Oracle 10g, был переписан сам компилятор PL/SQL, чтобы стать в первую очередь оптимизирующим компилятором.
Никакой другой язык не связан настолько тесно с SQL и не является до такой степени оптимизированным для взаимодействия с SQL. Работа с SQL в среде PL/SQL происходит совершенно естественным образом, в то время как в любом другом языке, от Visual Basic до Java, применение SQL может оказаться довольно-таки обременительным».
Опция New Procedural Option – PL/SQL появилась в версии Oracle 6.0 в 1988 году. С тех пор на PL/SQL написаны миллионы строк кода серверной бизнес-логики и разработаны тысячи клиентских форм и отчетов в среде Oracle Developer. Многие годы Oracle Corporation демонстрировала свою приверженность PL/SQL и с выходом каждой новой версии Oracle Database Server в PL/SQL вводятся новые усовершенствования. Язык PL/SQL является неотъемлемой частью технологий Oracle и в планах корпорации декларируется его развитие и поддержка в будущем.
Первая программа на PL/SQL
По давней традиции, восходящей к языку C, учебники по языкам программирования начинаются с программы «Hello, World!» и описания типов данных изучаемого языка. Не будем нарушать эту традицию, но внесем в нее одно изменение. Так как язык PL/SQL предназначен для работы с базами данных Oracle, то строку «Hello, World!» не будем задавать статически в исходном коде, а возьмем из таблицы базы данных.
CREATE TABLE hello_world_table(message VARCHAR2(30));
INSERT INTO hello_world_table VALUES('Hello, World!');
Выполним в SQL*Plus следующий код:
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 l_message VARCHAR2(30);
3 BEGIN
4 SELECT message INTO l_message FROM hello_world_table;
5 DBMS_OUTPUT.PUT_LINE(l_message);
6 END;
7 /
Hello, World!
PL/SQL procedure successfully completed.
В представленной выше программе PL/SQL с помощью команды SELECT INTO значение столбца message строки таблицы hello_world_table считывается из базы данных и присваивается локальной переменной l_message, значение которой затем выводится в окне SQL*Plus. Переменная l_message предварительно объявлена в разделе объявлений после ключевого слова DECLARE.
Экранный вывод в PL/SQL осуществляет процедура PUT_LINE встроенного пакета DBMS_OUTPUT, который есть во всех базах данных Oracle. Можно считать, что процедура DBMS_OUTPUT.PUT_LINE в языке PL/SQL – аналог процедуры printf в языке C.
Напомним о двух вещах, важных при работе с утилитой SQL*Plus:
для запуска в SQL*Plus программы PL/SQL на выполнение необходимо на новой строке напечатать символ / и нажать клавишу Enter на клавиатуре;
в SQL*Plus экранный вывод программ на PL/SQL включается командой SET SERVEROUTPUT ON (по умолчанию экранный вывод выключен).
Если не выполнить команду SET SERVEROUTPUT ON, то в консоли SQL*Plus ничего печататься не будет. В популярном GUI-клиенте Quest SQL Navigator экранный вывод PL/SQL тоже по умолчанию выключен и включается специальной кнопкой «Turn the server output», которая после нажатия должна остаться во «вдавленном» положении.