35

Язык pl/sql

Язык PL/SQL – фирменный процедурно-ориентированный язык программирования СУБД Oracle, встроенный в Oracle XE. С его помощью можно писать программы обработки информации, включающие в себя:

• процедуры языка PL/SQL (для управления выполнением операторов);

• команды языка SQL для доступа к базам данных Oracle.

Язык SQL представляет собой лишь средство доступа к БД, позволяющее приложениям вводить данные в БД Oracle и извлекать их из нее. Он не является полноценным языком программирования приложений БД. Для разработки таких приложений необходимо использовать высокоуровневый язык, включающий в себя язык SQL. Для программирования сервера баз данных Oracle и связанных с ним приложений можно использовать процедурный язык PL/SQL.

Язык PL/SQL похож на язык программирования Ada, многие их процедурные конструкции совпадают, однако он оптимизирован для тес­ного взаимодействия с СУБД Oracle.

Язык PL/SQL имеет операторы, позволяющие объяв­лять переменные и константы, управлять ходом выполнения программы, вводить данные и работать с ними и многие другие команды.

Блоки программы pl/sql

Программа на языке PL/SQL имеет структуру из отдельных блоков, в которых сгруппированы логически свя­занные объявления и операторы. Каждый блок программы PL/SQL решает свою конкретную задачу. Это позволяет организовать программу PL/SQL так, чтобы она была понятна и легко чи­талась.

Весь код PL/SQL, выполняющий фактическую работу, состоит из блоков. Блок PL/SQL состоит из четырех разделов:

• раздел заголовка;

• раздел объявлений (деклараций);

• выполняемый раздел (основное тело программы);

• раздел исключений (блок обработки ошибок).

Анонимный блок – блок PL/SQL без секции заголовка. Анонимные блоки могут выполняться из SQL*Plus, использоваться в функциях, процедурах и триггерах PL/SQL. Один блок можно помещать в другой блок.

DECLARE

-- раздел объявлений (необязателен)

BEGIN

-- тело программы (обязательно)

EXCEPTION

-- обработчики ошибок (необязательны)

END;

Основы языка PL/SQL будем изучать путем выполнения анонимных блоков в Oracle SQL Developer. Анонимный блок не хранится постоянно в базе данных Oracle. Приложение просто отправля­ет блок на сервер базы данных для обработки его в режиме вы­полнения.

Заголовок блока верхнего уровня функции, процедуры или триггера содержит их спецификацию. Для анонимных блоков заголовок содержит ключевое слово DECLARE. Для помеченных блоков заголовок содержит имя метки в двойных угловых скобках, за которым следует ключевое слово DECLARE:

≪just_a_label≫

DECLARE

В процедуре с вложенными блоками, можно ссылаться на элемент блока, предваряя имя элемента именем блока (метка_блока . метка_элемента).

В разделе объявлений объявляются переменные, кон­станты, обработчики исключений и другие объекты, которые становятся доступны в других частях этого же блока. Когда выполнение блока завершается, объявленные элементы перестают существовать. Все, что находится в секции объявлений, может использоваться только внутри блока (существует на протяжении его времени жизни). Часть кода, в которой может использоваться переменная, называется областью видимости. Раз­дел объявлений начинается ключевым словом DECLARE и неявно заканчивается ключевым словом BEGIN тела программы. Если в программе не требуются объявления, то раздел объявлений может быть опущен.

Тело программы блока содержит исполняемые операторы блока (в теле программы блок выполняет свои кон­кретные функции). Тело блока начинается ключевым словом BEGIN и заканчивается ключевым словом EXCEPTION, которое открывает раздел обработчика исключений блока. Если блок не содержит обработчиков исключений, то тело программы заканчивается ключевым словом END, закрывающим весь блок.

Необязательный раздел обработки ошибок блока содержит обра­ботчики исключений (процедуры обработки ошибок) данного блока. Если при выполнении какого-либо оператора в теле программы возникает ошибка, то управление передается соответствующему обра­ботчику в разделе обработки исключений для выполнения предусмотрен­ных действий. Раздел обработки исключений блока начинается ключевым словом EXCEPTION и заканчивается клю­чевым словом END. Если программа не требует обработки исключе­ний, то раздел может быть опущен.

Комментарии не являются обязательными элементами программы, но их следует включать для описания объявленных объектов и действий операторов. Комментарии описывают цели выполнения от­дельных сегментов программы. В языке PL/SQL используются два типа комментариев.

-- Однострочный комментарий

/* Многострочный

комментарий*/

Основы программирования на языке pl/sql

Все процедурные языки имеют базовые элементы, ко­торые необходимо изучить до начала работы и написания программ на этом языке. Базовые элементы язы­ка PL/SQL включают:

• объявление переменных программы и присвоение им значений;

• управление порядком выполнения операторов программы с по­мощью циклов и условных операторов;

• включение в программу команд SQL и взаимодействие с БД Oracle;

• объявление и использование процедур и функций в блоках PL/SQL;

• объявление заданных пользователем типов (записи и вложенные таблицы);

• объявление и использование курсоров для обработки запросов, возвращающих более одной строки;

• использование обработчиков исключений, реагирующих на ошибки при выполнении программы.

Переменные программы

Процедурно-ориентированные программы объявляют переменные и используют их для временного хранения информации. При объявлении переменной ееможно инициализировать сразу или сделать это позднее в теле программы. Если на переменную нало­жено ограничение состоящее в том, что ее значение не равно Null, то ее инициализация должна быть частью объявления. По умолчанию PL/SQL присваивает переменной значение Null, если ей не было при­своено значение явным образом. При объявлении переменной ей может быть задан любой тип Oracle и ANSI/ISO, тип, определенный пользователем, или один из под­типов (подтипом называется новый тип, являющийся подмножеством ба­зового типа).

Рассмотрим объявление перемен­ных и констант программы, их инициализацию и присвоение им значений в теле программы PL/SQL.

В примере есть несколько деталей, требующих уточнения:

• при объявлении константы в объявление включается ключевое слово CONSTANT; константа должна быть инициализирована, после чего изменить ее значение невозможно;

• если в программе объявляется переменная, принадлежащая к типу данных, имеющему ограничения, то это ограничение должно быть указано в объявлении, например VARCHAR2(20);

• переменная todaysDate инициализируется с помощью функции SYSDATE; эта функция возвращает текущую дату и время, определяемые операционной системой компьютера, на котором хранится база данных;

• в программе используется процедура DBMS_OUTPUT.PUT_LINE, осуществляющая вывод на стандартное устройство вывода;

• при попытке выполнить блок не будет виден результат вызова процедуры DBMS_OUTPUT.PUT_LINE, если не установлена переменная среды SERVEROUTPUT напри­мер, с помощью команды SET SERVEROUTPUT ON;

• для обозначения окончания программы на странице команд SQL используется строка, содержащая косую черту (/).

Для выполнения блока в программе SQL Developer необходимо нажать на кнопку Run Scriptили <F5>.

Рассмотрим присваивание значений переменным программы. Оператор присваивания в программе PL/SQL (:=) задает переменной значение, являющееся результатом вычисления выражения.

В примере используются кон­станты, операция сцепления символьных строк и операторы сложения (|| и + ), а также функции LAST_DAY и SYSDATE. Большинство функций SQL поддерживаются и в операторах языка PL/SQL.

Управление ходом выполнения программы

Процедурные программы имеют определенный порядок вы­полнения содержащихся в ней операторов. Программы на языке PL/SQL могут управлять ходом исполнения операторов, используя:

• итеративную логику (циклы);

• условную логику (if-then-else);

• последовательную логику (опера­торы перехода goto).

Для выполнения ряда операторов программы определенное количество раз в программе могут использоваться циклы. Рассмотрим анонимный блок, иллюстрирующий выполнение ти­пичного цикла. Такой цикл начинается ключевым словом LOOP и заканчивается клю­чевыми словами END LOOP.

Любой базовый цикл должен включать в себя команду EXIT WHEN или EXIT для окончания цикла, в противном случае он будет выполняться бесконечно.

В примере использована процедура DBMS_OUTPUT.PUT для вывода информации без перехода на следующую строку.

Рассмотрим анонимный блок, иллюстрирующий использование цикла WHILE. Оператор LOOP этого цикла начинается условием, находящимся после ключевого слова WHILE, и заканчивается ключевыми словами END LOOP.

Обратите внимание на то, что определение цикла WHILE требует обя­зательного задания условия, определяющего, когда цикл должен закончиться.

В заключение рассмотрим анонимный блок, описывающий использование третьего типа цикла FOR. Ключевое слово LOOP цикла FOR определяет начало цикла, а ключевые слова END LOOP – его окончание.

В цикле FOR может быть объявлена своя собственная целая переменная (счетчик), являющаяся частью оператора цикла FOR ... LOOP. Ключевое слово LOOP и цикл FOR автоматически увеличивают или уменьшают значение переменной счетчика, поэтому у пользователя нет необходимости делать это в теле цикла.

Оператор IF ... ELSIF ... ELSE позволяет реализовать в программе PL/SQL условную логику. Общий синтаксис оператора имеет вид:

IF condition THEN

statement1;

statements2;

…

ELSIF condition THEN

statement3;

statement4;

…

ELSE

statement5;

statement6;

…

END;

Оператор IF выполняется следующим образом:

• Вычисляется результат логического условия IF, и если он равен TRUE, то выполняются операторы блока THEN. Если результатом является значение FALSE или UNKNOWN (например, когда в условии выражение сравнивается с неинициализированной переменной), то операторы блока THEN не выполняются.

• Необязательный блок ELSIF задает альтернативное условие. Если его результатом является значение TRUE, то выполняются операторы следующего блока THEN. Если условие блока ELSIF дает значение FALSE или UNKNOWN, то операторы последующего блока THEN не выполняются. Оператор IF может содержать несколько блоков ELSIF.

• Необязательный блок ELSE задает последовательность операто­ров, которые выполняются, когда ни одно из условий предыдущих блоков IF и ELSIF не дает значение TRUE (не выполняется).

Рассмотрим следующий анонимный блок PL/SQL. В программе используется SQL-функция MOD и условия в операторе IF для определения того, является ли счетчик цикла FOR кратным 3 или 5 либо кратным и 3 и 5.