Материал: Язык PL SQL
Взаимодействие с базами данных
Предыдущие примеры представляют собой программы PL/SQL, генерирующие простой вывод для иллюстраций положений языка PL/SQL. Но основной целью использования языка PL/SQL является создание программ доступа к базам данных. Программа PL/SQL может взаимодействовать с базой данных Oracle только посредством языка SQL.
Работа с данными таблицы посредством команд языка DML. Программы PL/SQL могут включать любые синтаксически правильные команды INSERT, UPDATE и DELETE для изменения строк в таблице базы данных. Переменная или константа блока PL/SQL может присутствовать в качестве операнда выражения в команде DML. В качестве примера рассмотрим следующий анонимный блок PL/SQL, добавляющий новую запись в таблицу Catalogs:
После выполнения этой программы выберите в навигаторе узел Tables, таблицу Catalogs и откройте закладку Data. Нажмите кнопку Refresh (Ctrl+R)для того, чтобы увидеть новую запись.
Изменения, сделанные командами INSERT, UPDATE и DELETE внутри блока, являются частью текущей транзакции данного сеанса. Хотя во многих типах блоков пользователь может включить команды COMMIT и ROLLBACK, управление транзакциями обычно осуществляется за границами блока, что делает наглядными границы транзакции в программе.
Присвоение переменной значения в запросе. Программы PL/SQL часто используют блок INTO SQL-команды SELECT для присвоения переменной значения, взятого в базе данных или вычисленного на основе ее данных. Oracle поддерживает использование команды SELECT ... INTO только в программах PL/SQL. Выполните этот тип присваивания путем ввода анонимного блока, использующего команду SELECT ...INTO для присвоения значения переменной программы:
Команда SELECT…INTO должна иметь в качестве результирующей таблицы только одну строку. Если результирующая таблица не содержит строк, содержит более одной строки или возвращает значение неприемлемого типа, то Oracle генерирует исключение. Для обработки запроса, возвращающего более одной строки, программа PL/SQL должна использовать курсор (подробнее о курсорах далее).
Объявление и использование подпрограмм: процедуры и функции
Раздел объявлений блока может содержать описание именованных процедур, называемых подпрограммами. В этом случае операторы в теле программы могут вызывать (исполнять) подпрограммы каждый раз, когда это необходимо. Язык PL/SQL поддерживает два типа подпрограмм: процедуры в функции.
Процедурой называется подпрограмма, выполняющая некоторое действие. Функцией называется подпрограмма, которая вычисляет некоторое значение и возвращает его в программу, вызвавшую данную функцию.
Если подпрограмма была объявлена как процедура, то в нее можно передать некоторые значения и получить от нее значения с помощью параметров процедуры. Обычно вызывающая программа передает подпрограмме одну или более переменных в качестве параметров. Чтобы отличать параметры от переменных, констант и т.д., рекомендуется при объявлении снабжать их префиксом р_.
Для каждого параметра необходимо указать его тип без ограничивающих условий. Например, следует описывать параметр как VARCHAR2, а не VARCHAR2(100). Кроме того, для каждого параметра должен быть задан один из режимов его использования: IN, OUT или IN OUT:
параметр IN передает в подпрограмму значение, но подпрограмма не может изменить значение внешней переменной, соответствующей этому параметру;
параметр OUT инициализируется значением Null, после чего подпрограмма может манипулировать этим параметром для изменения значения соответствующей переменной во внешней вызывающей среде (подпрограмма может установить значение параметра OUT, но не может его прочитать);
параметр типа IN OUT соединяет в себе свойства параметров типа IN и OUT.
Введите приведенный ниже анонимный блок PL/SQL, который объявляет процедуру, печатающую горизонтальные линии заданной ширины:
В теле программы три раза вызывается процедура printLine:
Первый вызов задает значения обоих параметров процедуры, используя позицию параметра в строке. Значения параметров, указанные в вызове процедуры, неявно соответствуют параметрам процедуры, объявленным в тех же позициях.
Второй вызов задает значения параметров процедуры, используя их имена: значению параметра предшествуют имя параметра и оператор соответствия (=>). При использовании имен параметры могут указываться в произвольном порядке.
Третий вызов показывает, что для параметров процедуры без значений по умолчанию присвоение значений является обязательным, тогда как для параметров, имеющих значение по умолчанию, значения могут быть опущены.
Функция отличается от процедуры тем, что возвращает значение в среду, из которой была вызвана. Спецификация функции объявляет тип значения, возвращаемого функцией. Тело функции должно включать в себя один или более операторов RETURN для возвращения значения функции в вызывающую среду. Рассмотрим объявление и использование функции, вычисляющей сумму заказа:
Программа может вызвать функцию везде, где выражение является действительным, например, в качестве параметра при вызове процедуры или в правой части оператора присваивания. Команда SQL может также ссылаться на определенную пользователем функцию в условии блока WHERE.
Работа с переменными типа запись
До сих пор в объявлениях описывались простые скалярные переменные и константы, основанные на типах данных Oracle (NUMBER), и подтипы базовых типов (INTEGER). В блоке программы PL/SQL могут быть объявлены определенные пользователем типы, после чего они могут использоваться в программе.
Рассмотрим объявление и использование простого определенного пользователем типа, которым является запись. Тип запись включает в себя одно или несколько связанных полей, каждое из которых имеет свое имя и тип.
Программы PL/SQL используют тип запись для создания переменных, отвечающих структуре записи в таблице. Пользователь может объявить тип запись с именем bookRecord и использовать его для создания переменной типа запись, в которой содержатся поля book_ID, b_name, b_author, b_year, b_price, b_count и b_cat_ID. После объявления переменной типа запись можно работать с отдельными полями записи или передать всю запись в подпрограмму как одно целое.
Рассмотрим анонимный блок PL/SQL, иллюстрирующий объявление и использование типа запись. В разделе объявлений блока объявляется определяемый пользователем тип, соответствующий атрибутам таблицы Catalogs, а затем объявляются две переменные типа запись, использующие этот новый тип. Выполняются следующие действия:
делается ссылка на индивидуальные поля переменной типа запись, с использованием точечной нотации;
присваиваются значения полям переменной типа запись;
переменная типа запись передается в качестве параметра при вызове процедуры;
копируются значения полей одной записи в другую запись того же типа;
поля переменной типа запись используются в качестве выражений в команде INSERT.
Атрибуты %type и %rowtype
Эти атрибуты используются в программе для объявления переменных, констант, отдельных полей в записях и в переменных типа запись, соответствующих по свойствам полям базы данных и таблиц или других программных конструкций. Использование этих атрибутов упрощает объявление программных конструкций и делает программы более гибкими по отношению к изменениям в базах данных. Например, при изменении администратором таблицы Catalogs и добавления в нее нового поля переменная типа запись, объявленная с использованием атрибута типа %ROWTYPE, автоматически подстраивается в процессе выполнения программы к новому полю без изменения программы.
Атрибут %TYPE используется при объявлении переменной, константы или поля в переменной типа запись для перехвата во время выполнения программы типа данных другой программной конструкции или поля в таблице базы данных. Рассмотрим анонимный блок, использующий атрибут %TYPE для ссылки на поля таблицы Catalogs при объявлении типа catalogRecord.
В программе PL/SQL может использоваться атрибут %ROWTYPE, позволяющий объявлять переменные типа запись и другие объекты динамически, во время выполнения программы. Рассмотрим анонимный блок, иллюстрирующий использование атрибута %ROWTYPE для упрощения объявления переменной типа запись, соответствующей полям таблицы Catalogs.
Переменная типа запись, которая объявляется с помощью атрибута %ROWTYPE, автоматически получает для своих полей имена, соответствующие полям таблицы, на которую выполняется ссылка.
Преимущество атрибутов %TYPE и %ROWTYPE в том, что они делают объявления программы гибкими в отношении неизбежных изменений структуры данных. Программы в предыдущих примерах корректно работают при объявлении поля cat_name таблицы Catalogs как принадлежащегок любому из типов VARCHAR2(1), VARCHAR2(50), VARCHAR2(250) и т. д.
Работа с курсорами
Каждый раз, когда приложение передает SQL-команду СУБД Oracle, сервер открывает для обработки этой команды, по крайней мере, один курсор (рабочую область для команды SQL). Когда программа PL/SQL (или любое другое приложение) представляет команду INSERT, UPDATE или DELETE, Oracle автоматически открывает курсор для ее обработки. Oracle может автоматически обрабатывать команду SELECT, которая возвращает только одну строку.
Для обработки запроса, возвращающего результирующую таблицу из нескольких строк, программа PL/SQL должна явным образом объявить курсор, открыть его, извлекать из него строки по одной, а затем закрыть курсор. Oracle может применять к обработке курсора различные подходы.
Объявление именованного курсора (команды OPEN, FETCH, CLOSE)
Пользователь может явным образом объявить курсор с именем в разделе объявлений блока используя команду CURSOR. После этого можно открыть курсор в теле блока или в разделе обработки исключений с помощью команды OPEN, извлекать из него строки с помощью команды FETCH (обычно в цикле) и в заключение закрыть курсор командой CLOSE. Это наиболее трудоемкий подход, он чреват ошибками и является наихудшим в отношении производительности.
Явно задаваемый цикл FOR для курсора
Пользователь может объявить курсор командой CURSOR, а затем использовать цикл FOR для курсора для автоматического объявления переменной или записи, которая используется для получения строк из курсора. После открытия курсора из него извлекаются строки, и после извлечения последней курсор закрывается. Такой подход проще, менее подвержен ошибкам, обеспечивает более высокую производительность, но все же требует явного объявления курсора.
Неявный цикл FOR для курсора
Кроме объявления переменной или записи для приема строк из курсора пользователь может объявить курсор в самом цикле FOR и обрабатывать соответствующие строки по одной. Такой подход намного проще и обеспечивает такую же производительность, как и использование явного цикла FOR для курсора.
При использовании курсора для обработки большого количества строк циклы FOR курсора обеспечивают значительно большую производительность, чем использование команд OPEN/FETCH/CLOSE. Циклы FOR курсора извлекают за один раз из соответствующего курсора массив в 100 строк, тогда как при использовании для обработки курсора команд OPEN/FETCH/CLOSE Oracle извлекает за один раз лишь одну строку.
Рассмотрим анонимный блок, в котором объявляется и используется очень простой курсор для распечатки избранных полей из всех строк таблицы Users.
При обработке курсора могут использоваться другие, боле сложные подходы, описанные выше. Например, предыдущий блок может быть реализован следующим эквивалентным блоком:
Вэтой версии блока после извлечения из курсора первой записи последующие строки извлекаются из открытого курсора по одной с помощью цикла WHILE. В условии цикла WHILE используется атрибут курсора %FOUND для определения момента, когда из курсора извлекается последняя строка.
Атрибуты явным образом объявленных курсоров, которые можно использовать в языке PL/SQL для базовых курсоров.
cursor%FOUND – атрибут %FOUND получает значение TRUE, если предшествующей команде FETCH соответствует по крайней мере одна строка в базе данных; иначе атрибут получает значение FALSE;