Язык 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, объем которого может составлять сотни тысяч строк.

Что же касается проблем масштабирования, то обработка данных непосредственно в базе данных средствами самой СУБД является достоинством хранимых программ до тех пор, пока обеспечивается требуемый уровень производительности. В противном случае это обстоятельство помешает масштабированию, так как установка дополнительных серверов потребует большого объема работ. Придется на каждом новом сервере устанавливать СУБД, создавать свою базу данных с хранимыми программами и решать задачу распределения данных по нескольким базам. Из достоинства хранимых программ интеграция хранения и обработки данных, таким образом, может стать недостатком. С отдельными приложениями, реализующими серверную бизнес-логику без хранимых программ, проблем масштабирования обычно нетдобавить новые сервера только для обсчитывающих приложений обычно довольно легко.