Язык PL/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 и т. п., реализующих только пользовательский интерфейс, а бизнес-логику собственно расчета задолженности