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

Теперь легко использовать эту конструкцию в подзапросе IN:

tkyte@TKYTE816> select *

2 from all users

3 where user id in

4 (select *

5 from TLE (cast(str2tbl(:bind variable) as myTableType))

USERNAME USER ID CREATED

SYSTEM 5 04-NOV-00

Этот прием можно использовать во многих случаях. Можно применить к значению PL/SQL-переменной типа набора операцию ORDER BY, можно возвращать клиенту наборы данных, сгенерированные PL/SQL-функцией, можно задавать конструкции WHERE для выбора определенных значений PL/SQL-переменных и т.д.

Если пойти чуть дальше, таким способом можно возвращать полные результирую-множества с несколькими столбцами. Например:

tkyte@TKrE816> create type myRecordType as object

2 (seq int,

3 a int,

4 b varchar2(10),

5 c date

Type created.

tkyte@TKYTE816> create table t (x int, y varchar2(10), z date); Table created.

tkyte@TKYTE816> create or replace type myTableType

2 as table of myRecordType

Type created.

tkyte@TKYTE816> create or replace function my function return myTableType

2 is

3 l data myTableType;

4 begin

5 l data := myTableType();

7 for i in 1..5

8 loop

9 l data.extend;

10 l data(i) :=myRecordType(i, i, row i, sysdate+i);

11 end loop;

12 return l data;

13 end;

14 / Function created.

1418

Глава 20

tkyte@TKYTE816> select *

2 from TABLE (cast(my function() as mytableType))

3 where c > sysdate+1

4 order by seq desc

5 row 5

4 row 4

3 row 3

2 row 2

29-MAR-01 28-MAR-01 27-MAR-01 26-MAR-01

Множественная выборка данных в записи

Итак, мы рассмотрели, как использовать наборы для выборки данных из PL/SQL-функции. Теперь разберемся, как с их помощью обеспечить множественную в]борку данных в аналог PL/SQL-записей. Выполнить множественную выборку в массив реал ных PL/SQL-записей нельзя, но можно выбрать данные во вложенную таблицу SQL. Для этого потребуется два объектных типа: скалярный тип, представляющий запись, и вложенная таблица с записями этого типа. Например:

tkyte@TKYTE816> create type myScalarType

2 as object

3 (username varchar2(3 0) ,

4 user id number,

5 created date

Type created.

tkyte@TKYTE816> create type myTableType as table of myScalarType

2 / Type created.

Теперь все готово для выборки данных в переменную типа MYTABLETYPE едую-щим образом:

tkyte@TKYTE816> declare

10 11 12 13

l users myTableType; begin

select cast(multiset(select username, user id,

from all users order by username) as myTableType) into l users from dual;

created

dbms output.put line(Retrieved l users.count

rows);

end;

Retrieved 25 rows

PL/SQL procedure successfully completed.

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

Запрос к представлению ALLUSERS можно заменить любым запросом, выбирающим строку типа VARCHAR2(30), число и дату. Запрос может быть сколь угодно слож-н1м, включать соединения и т.п. Фокус в том, что результаты этого подзапроса преобразуются в объектный тип. Затем можно выбрать все результирующее множество в локальную переменную с помощью стандартного оператора SELECT ... INTO.

Вставка записей

Зная, что можно выполнять операторы SELECT * FROM НАБОР, где НАБОР -

это либо локальная переменная, либо PL/SQL-функция, возвращающая вложенную таблицу, нетрудно придумать, как выполнить аналогичным образом оператор INSERT. Необходимо задать переменную типа вложенной таблицы и заполнить ее записями, которые требуется вставить. Следующий пример демонстрирует, как будет выглядеть вставка одной строки:

tkyte@TKYTE816> create table t as select * from all users where 1 = 0; Table created. tkyte@TKYTE816> declare

2 l users myTableType :=

3 myTableType(myScalarType(tom, 1, sysdate));

4 begin

5 insert into t

6 select * from TABLE (cast(l users as myTableType));

7 end;

tkyte@TKYTE816> select * from t;

USERNAME USER ID CREATED

tom 1 24-MAR-01

При работе с много-столбцовой таблицей этот прием может пригодиться.

Итак, в этом разделе мы рассмотрели использование объектных типов Oracle для расширения возможностей языка PL/SQL аналогично тому, как для этих целей используются классы в языках Java или С + + .

Описаны также интересные варианты использования вложеннгх таблиц. Возможность в1полнять оператор SELECT * из PL/SQL-функции открывает заманчивые перспективы. Списки значений произвольной длины для конструкции IN - только начало. Возможности поистине безграничны. Можно написать небольшую функцию, использующую средства пакета UTL FILE для чтения файла ОС, разбиения прочитанных строк на поля по запятым и возвращения результирующего множества, построенного по содержимому обычного файла, для вставки в другую таблицу или соединения с существующей таблицей.

Такое использование объектных типов дает новую жизнь хорошо зарекомендовавшему себя языку программирования. Создав самостоятельно один-два типа, вы найдете применение этой методике во множестве приложений. Это логический способ объединения данных и функций для работы с ними, что является одной из основных целей объектно-ориентированного программирования. Предвидя протесты, я не называю это