1414

Глава 20

строка 2 : (должна быть пустой) строка 3 : (должна быть пустой) строка 4: (должна быть пустой) строка 5: (должна быть пустой) строка 6: (должна быть пустой) строка 8: (должна быть строка N)строка 1 строка 9: (должна быть строка N)строка 2 строка 10 : (должна быть строка N)строка 3 строка 11: (должна быть строка N)строка 4 строка 12: (должна быть строка N)строка 5 строка 12 : (должна быть Hello World)Hello World поучили no data found, как и ожидалось

PL/SQL procedure successfully completed.

Мы инкапсулировали пакет UTL FILE в объектный тип Oracle. Получился замечательный интерфейс к стандартному пакету, работающий именно так, как нам хотелось. В терминах объектного программирования, мы создали класс UTL FILE, включив в него методы, которые работают так, как нужно нам, а не так, как придумали разработчики Oracle. Мы не переписывали пакет UTL FILE, а только создали для него другой интерфейс. Это хороший прием программирования, позволяющий легко решать проблемы, связанные с изменением реализации пакета UTL FILE или появлением ошибки в новой версии. Все эти проблемы можно решить, изменив тело типа, а не сотни или тысячи непосредственно зависящих от пакета приложений. Например, в одной из версий пакета UTL FILE нельзя было открыть несуществующий файл в режиме А (на добавление); при этом файл не создавался, хотя и должен был. Для решения проблемы достаточно было написать следующий код:

begin

file stat := utl file.fopen(file dir,filename,a); exception

- если файл не существует, fopen не сработает

- из-за ошибки режима a: 371510 when utl file.invalid operation then

- все остальные исключительные ситуации распространяются

- во внешний блок, как обычно

file stat := utl file.fopen(file dir,file name,w);

end;

Если файлы открываются на добавление в 100 подпрограммах, придется делать много исправлений. Если же использован дополнительный уровень интерфейса, достаточно внести изменение только в одном месте.

Уникальные приемы использования наборов

Еще один вариант применения объектных типов в PL/SQL связан с использованием наборов и их возможностей взаимодействия с языками SQL и PL/SQL. Наборы позволяют сделать три вещи в SQL и PL/SQL, о реализации которых часто спрашивают разработчики.

Использование объектно-реляционных средств 1415

Как выбрать данные (SELECT *) из PL/SQL-функции?* Можно написать PL/SQL-функцию и обращаться с запросами к ней, а не к таблице базы данных.

Как выбрать данные в массив записей? PL/SQL изначально позволяет выполнять выборку данных BULK COLLECT (выбирать по нескольку строк за раз) в PL/SQL-таблицу. К сожалению, это можно делать только в PL/SQL-таблицы с элементами скалярн1х типов. Я не могу сделать следующее:

select cl, c2 BULK COLLECT INTO record type from T поэтому приходится писать:

select cl, c2 BULK COLLECT INTO tablel, table2 from T

С помощью наборов можно обеспечить выборку данных в записи.

Как вставить запись? Вместо вставки по столбцам, я могу вставить в таблицу одну запись.

SELECT * из PL/SQL-функции

Чтобы продемонстрировать эту возможность, давайте вернемся к проблеме использования связываемых переменных (это моя любимая тема). Часто мне приходится слышать утверждения, что необходимо выполнить запрос вида:

select * from t where c in (:bind variable)

BIND VARIABLE представляет собой список значений. Другими словами, переменная BIND VARIABLE получает, допустим, значение 1, 2, 3 и необходимо, чтобы представленный выше запрос выполнялся как:

select * from t where с in (1, 2, 3)

и возвращал строки, в которых с = 1, 2 или 3; но при таком значении связываемой переменной фактически выполняется запрос:

select * from t where с in (1,2,3)

Он возвращает строки, в которых с = 1,2,3 - значение столбца с равно одной такой строке. Такие запросы часто порождаются пользовательскими интерфейсами, в которых пользователь может отметить флажками одно или несколько (любое количество) возможных значений из списка. Чтобы не создавать уникальные запросы для каждого сочетания флажков (мы знаем, насколько это плохо), необходим метод связывания произвольного количества элементов в списке. Ну, поскольку можно выполнять SELECT * из PL/SQL-функиии, решение есть. Сейчас я его продемонстрирую:

tkyte@TKYTE816> create or replace type myTableType

2 as table of number;

Type created.

Созданный таким образом тип данных и будет возвращать PL/SQL-функция. Этот тип обязательно должен быть задан на уровне SQL с помощью оператора CREATE TYPE.

* Другая формулировка той же проблемы: как возвращать результирующие множества из хранимых функций? Прим. научн. ред.

1416

Глава 20

Нельзя использовать тип, заданный в PL/SQL-пакете; конечная цель - выбирать ные с помощью SQL-операторов, поэтому необходим тип данных SQL. Этот пример также показывает, что я не являюсь принципиальным противником использования вложенных таблиц. Именно этот тип набора оптимален при программировании на PL/SQL, если приходится решать подобного рода задачи. При использовании массива VARay придется искусственно ограничить количество элементов. Размер же вложенной таблицы ограничен только объемом памяти, доступной в системе.

tkyte@TKYTE816>create or replace

2 function str2tbl(p str in varchar2) return myTableType

3 as

4 l str long default p str

5 l n number;

6 l data myTableType := myTabletype();

7 begin

8 loop

9 l n := instr(l str, ,) ;

10 exit when (nvl(l n,0) = 0);

11 l data.extend;

12 l data(l data.count) :=

13 ltrim(rtrim(substr(l str,l,l n-l)));

14 l str := substr(l str, l n+l);

15 end loop;

16 return l data;

17 end;

18 /

Function created.

Итак, создана PL/SQL-функция, принимающая строку со списком значений через запятую и преобразующая ее в SQL-тип MYTABLETYPE. Осталось только найти способ выбрать эти данные с помощью SQL-оператора. Это легко сделать с помощью оператора TABLE и преобразования типа, CAST:

tkyte@TKYTE816>variablebind variablevarchar2(30) tkyte@TKYTE816>exec :bind variable := 1,3,5,7,991

PL/SQL procedure successfully completed.

BIND VARIABLE

1,3,5,7,99

tkyte@TKYTE816> select *

2 from TABLE (cast(str2tbl(:bind variable) asmyTableType))

COLUMN VALUE