Единое окно доступа к образовательным ресурсам

Язык PL/SQL

Голосов: 0

В учебно-методическом пособии рассматриваются основы языка программирования PL/SQL, реализованного в системе управления базами данных Oracle Database Server. Приводятся сведения о поддерживаемых типах данных, структуре программ PL/SQL и выполнении SQL-предложений в них. Отдельно рассмотрено создание хранимых в базах данных Oracle программ PL/SQL — процедур, функций, пакетов и триггеров.

Приведенный ниже текст получен путем автоматического извлечения из оригинального PDF-документа и предназначен для предварительного просмотра.
Изображения (картинки, формулы, графики) отсутствуют.
    И.С. Задворьев
  Язык PL/SQL
  учебно
-методическое пособие   УК №
 3800  Москва 201
7  

2 
Задворьев  И.С.  Язык  PL/SQL.  Учебно -
методическое пособие. —  М. , 2017 . – 188  с.  Автор:
 
Задворьев И.С., кандидат технических наук.  В  учебно
-методическом  пособии  рассматриваю т-
ся  основы  языка  программирования  PL/SQL ,  ре а-
лизованного  в  системе  управления  базами  данных 
Oracle  Database  Server .  Приводятся  сведения о по д-
держиваемых  типах  данных,  структуре  программ 
PL/SQL  и  выпо лнении  SQL-предложений  в  них. 
Отдельно  рассмотрено  создание  хранимых  в  базах 
данных  Oracle программ    PL/
SQL  —  процедур, 
функций, пакетов и триггеров.   © 
Задворьев  И ван  Сергеевич , 2017 г.  
konaz@mail.ru  

3 
Введение  в 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.  Задачи, решаемые PL/SQL
 
PL
/ SQL ,  в  отличие  от  Java, Python или  C++,  не  используется  для 
разработки  математических  приложений,  игр  и  т.  п.  Это  специфи ч-
ны й  язык  программ ирования  третьего  поколения,  предназначенный 
для  работы  с  базами  данных  Oracle прямо  в  ядре  сервера  Oracle.  Функция на языке  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 F 1 () RETURNS int   AS '  
DECLARE   
   r RECORD;  
BEGIN  
   FOR r IN SE LECT * FROM tab1 LOOP  
     UPDATE tab2 SET at3=r.at2; 
   END LOOP;  
   RETURN   1;  
END;  
' LANGUAGE plpgsql;   

4 
 Фактически  программы  на 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 будут  сохранены  и  результаты  вычис-лений.  

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

6 
 Для  второго  способа база  данных  используется только  для  хранения данных.  Все  необходимые  данные  по  каждому клиенту извлекаются приложением из  базы,  обсчитываются приложением и полученные сведения  о  задолженности сохраняются  обратно  в  базу. Обсчитыва-ющее  данные  приложение  часто  размещают  на  том  же  сервере,  где находится база  данных — чтобы  сеть  не  стала  узким  местом систе-мы. 
Выбор  используемого  способа  решения  задачи  является  обязанно-стью  архитектора  системы,  при  этом  следует  учитывать  много  фак-торов,  формируемых  в  каждом  конкретном  случае  на  основе  извест-ных достоинств и недостатков использования хранимых программ.  
Достоинства хранимых программ:  
 переносимость хранимых программ вместе с базой данных;  
 повышенная  производительность  обработки  за  счет  отсутствия передачи данных вне сервера баз данных; 
 тесная интеграция с подсистемой выполнения SQL (предложения SQL  в  хранимых  программах  выполняются  без  использования дополнительных интерфейсов и драйверов); 
 управление  доступом  к  данным  на  основе  хранимых  программ (доступ предоставляется не к таблицам базы данных на чтение и запись данных  в  них,  а  на  выполнение  хранимых  программ — тем  самым  выполняется  изоляция  данных  от  прикладных  про-грамм); 
 реализация  динамических  ограничений  целостности  и  концеп-ции активных баз данных с помощью механизма триггеров. 
Недостатки хранимых программ:  
 «размазывание» логики  работы  системы  по  нескольким про-граммах, написанных на разных языках; 
 необходимость  наряду  c программистами на  Java,  Python,  C++ иметь в команде программиста баз данных; 
 скудность выразительных возможностей  языков  хранимых  про-цедур на фоне современных языков Java, Python, C++; 
 непереносимость хранимых программ между различными СУБД; 
 возможные проблемы с масштабированием.  

7 
Наиболее  существенным  недостатком  хранимых  программ  является 
их  привязка  к  конкретной  СУБД.  Например,  при  переходе  c  Oracle 
на  PostgreSQL  в  рамках  актуальной  темы  импортозамещения,  все 
хранимые  программы  придется  переписывать  с  PL/SQL  на 
PL/pgSQL,  а  это  приведет  к  существенным  затратам  на  реинжин и-
рин г  кода  PL/SQL ,  объем  которого  может  составлять  сотни  тысяч 
строк 1
. 
 
Что  же  касается  проблем  масштабирования,  то  обработка  данных 
непосредственно  в  базе  данных  средствами  самой  СУБД  является 
достоинством  хранимых  программ  до  тех  пор,  пока  обеспечивается 
требуемый  уровень  производительности.  В  противном  случае  это  
обстоятельство  по мешает  масштабированию,  так  как  установка  д о-
полнительных  серверов  потребует  большого  объема  работ.  Придется 
на  каждом  новом  сервере  устанавливать  СУБД,  создавать  свою  базу 
данных  с  хранимыми  программами  и  решать  задачу  распределения 
данных  по  нескольким  базам.  Из  достоинства  хранимых  программ 
интеграция  хранения  и  обработки  данных,  таким  образом,  может 
стать  недостатком.  С  отдельными  приложениями,  реализующими 
серверную  бизнес -логику  без  хранимых  программ,  проблем  масшт а-
бирования  обычно  нет  —  добавить  новые  сервера  только  для  обсч и-
тывающих приложений обычно довольно легко. 
Переносимость программ PL/SQL  
Переносимость программ PL/SQL вместе с базой  данных обеспечив а-
ется  архитектурой  языка  PL/SQL,  похожей  на  архитектуру  языка 
Ja va.  
При  программировании  на  языках  C /C++,  Pascal  в  результате  раб о-
ты  компилятора  получается  исполняемый  (executable) файл.  Этот 
файл  содержит  машинные  команды  конкретной  аппаратной  пла т-
формы  и  предназначен  для  работы  в  конкретной  операционной  с и-
стеме.  Поэтому  если  исполняемый  файл  для  Windows  скопировать 
на  компьютер  с  операционной  системой  Linux,  то  он  там  не  запу-
стится.  Если  исполняемый  файл  для  одной  аппаратной  платформы  1
 
На  PL
/SQL  возможна реализация больших п роектов. Например , при 
разработке системы  управления предприятием Oracle E -Business Suite  было 
написано  более 60 миллионов  строк кода на PL/SQL!  

8 
 перенести  на  другую платформу  (компьютер  с другими  машинными командами),  то  он  там тоже не  запустится. В  итоге,  если  требуется обеспечить  кроссплатформенность,  то  для  одной  и  той  же  програм-мы  на  C++,  приходится  иметь  версию для  Windows и версию для Linux, версию для x86 (32-х разрядную) и версию для x64 (64-х раз-рядную) и так далее.  
Иначе  дело  обстоит  с  программированием  на  Java.  В  результате  ра-боты  компилятора Java получается  не исполняемый  файл с  машин-ными  командами,  а  файл  с  байт-кодом (bytecode)1 — машинно-независимым  кодом  низкого  уровня,  исполняемым  интерпретатором байт-кода.  Этот  интерпретатор байт-кода называется  виртуальной машиной Java (Java Virtual Machine, JVM). При запуске программы Java файл  с ее байт-кодом  подается  на  вход  виртуальной  машине Java,  которая  преобразует  инструкции  байт-кода  в  машинные  коды конкретной  платформы.  Таким  образом,  для  того,  чтобы запустить программу на Java в той  или иной  среде, достаточно иметь для этой среды JVM2. Например,  в ядре сервера 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 с  одних  вычислитель-ных систем на другие3. 
                                           1 Байт-код называется так, потому что длина каждого кода операции традиционно составляет один байт. 2 Девиз языка Java так и звучит — Write Once, Run Anywhere. 3 Может понадобиться перекомпиляция программ PL/SQL при переносе на другую платформу, но изменять исходный код не придется.  

9 
 
Оценка языка PL/SQL 
Как язык программирования PL/SQL имеет следующие достоинства:  
 статическая типизация1; 
 наличие  средств  обработки  ошибок  и  пользовательских исклю-чений; 
 наличие лаконичных и удобных языковых конструкций  для вы-полнения предложений языка 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  является  полноценным  и  популярным 
                                           1 Статическая типизация рассматривается в параграфе «Типы данных PL/SQL».  

10 
 языком  третьего  поколения  (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,  


    
Яндекс цитирования Яндекс.Метрика