Язык 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;