знаменитую рассылку:
Выпуск 11
Про "SELECT из хранимой процедуры"
Уважаемые подписчики! Ведущий все таки вернулся из отпуска и продолжает работу над рассылкой. Этот выпуск посвящен теме "возврата результирующего множества из хранимой процедуры" или, другими словами, тому, как формировать данные для оператора SELECT программно. Думаю, тема эта интересна многим разработчикам. Оказывается, начиная с версии 8i, это вполне возможно сделать и в Oracle. Именно об этом писал Том Кайт. Ответ в очередной раз промелькнул на сайте 27 августа 2002 года. Вашему вниманию предлагается сокращенный перевод соответствующего обсуждения.


--------------------------------------------------------------------------------

Вопросы:
Поддерживает ли Oracle вызов процедуры в качестве производной таблицы, например:

select * from (call myprocedure) ...

Далее, поддерживает ли он создание представления на базе процедуры:
Create view as (call myprocedure)

Ответ Тома Кайта:
Для этого надо использовать Oracle9i (но я покажу и способ для 8i):

ops$tkyte@ORA9I.WORLD>; create or replace function virtual_table(p_num_rows in number)
2 return virtual_table_type
3 PIPELINED -- обратите внимание на ключевое слово pipelined
4 is
5 begin
6 for i in 1 .. p_num_rows
7 loop
8 pipe row(i);
9 end loop;
10
11 return; -- обратите внимание - return без возвращаемого значения!
12 end;
13 /

Function created.

ops$tkyte@ORA9I.WORLD>;

Теперь мы увидим эту функцию в действии:

ops$tkyte@ORA9I.WORLD>; select * from TABLE(virtual_table(10));

COLUMN_VALUE
------------
1
2
3
4
5
6
7
8
9
10

10 rows selected.

Мы использовали ключевое слово pipe вместо suspend. До версии 9i, когда pipe не было, решение выглядело так (Я сразу исправил ошибку, которую Том затем исправил в ответ на комментарий читателя. То, что пришлось добавить, выделено полужирным. - Комментарий В.К.):

ops$tkyte@ORA9I.WORLD>; create or replace type virtual_table_type as table of number
2 /

Type created.

ops$tkyte@ORA9I.WORLD>; create or replace function virtual_table(p_num_rows in number)
2 return virtual_table_type
3 is
4 l_data virtual_table_type := virtual_table_type();
5 begin
6 for i in 1 .. p_num_rows
7 loop
8 l_data.extend;
9 l_data(l_data.count) := i;
10 end loop;
11
12 return l_data;
13 end;
14 /

Function created.

ops$tkyte@ORA9I.WORLD>;select * from TABLE(cast(virtual_table(10) as virtual_table_type));

COLUMN_VALUE
------------
1
2
3
4
5
6
7
8
9
10

10 rows selected.

Необходимо заполнить набор данными и вернуть его (ни pipe, ни suspend не поддерживается).

Комментарий читателя от 13 марта 2002 года об использовании этого решения в пакете
Привет,

Да, это действительно интересная возможность. Однако...

Я попытался поместить все в пакет. Не работает!! Буду благодарен за разъяснения...

SQL> @connect yd/yd@mydb
Connected.
yd@MYDB.CMT.MU>; create or replace type virtual_table_type as table of number;
2 /

Type created.

yd@MYDB.CMT.MU>; create or replace function virtual_table( p_num_rows in number )
return virtual_table_type
is
l_data virtual_table_type := virtual_table_type();
begin
for i in 1 .. p_num_rows
loop
l_data.extend;
l_data(l_data.count) := i;
end loop;
return l_data;
end;
/
Function created.

yd@MYDB.CMT.MU>; select * from TABLE(cast(virtual_table(5) as virtual_table_type));

COLUMN_VALUE
____________
1
2
3
4
5

5 rows selected.

yd@MYDB.CMT.MU>;

Теперь я помещаю все в пакет...

yd@MYDB.CMT.MU>; CREATE OR REPLACE PACKAGE test_pkg IS
type virtual_table_type is table of number;
function virtual_table( p_num_rows in number )
return virtual_table_type;
END;
/

Package created.

yd@MYDB.CMT.MU>; CREATE OR REPLACE PACKAGE BODY test_pkg IS
function virtual_table( p_num_rows in number )
return virtual_table_type
is
l_data virtual_table_type := virtual_table_type();
begin
for i in 1 .. p_num_rows
loop
l_data.extend;
l_data(l_data.count) := i;
end loop;
return l_data;
end;
END;
/

Package body created.

yd@MYDB.CMT.MU>; select * from TABLE(cast(test_pkg.virtual_table(5) as test_pkg.virtual_table_type));

select * from TABLE(cast(test_pkg.virtual_table(5) as test_pkg.virtual_table_type))
*
ERROR at line 1:
ORA-22905: cannot access rows from a non-nested table item

yd@MYDB.CMT.MU>; select * from TABLE(cast(test_pkg.virtual_table(5) as virtual_table_type));
select * from TABLE(cast(test_pkg.virtual_table(5) as virtual_table_type))
*
ERROR at line 1:
ORA-00902: invalid datatype

yd@MYDB.CMT.MU>; select * from TABLE(cast(virtual_table(5) as test_pkg.virtual_table_type));
select * from TABLE( cast ( virtual_table(5) as test_pkg.virtual_table_type ) )
*
ERROR at line 1:
ORA-22905: cannot access rows from a non-nested table item

Ответ Тома Кайта:
Чтобы использовать SQL-операторы такого рода, необходимо указывать типы SQL, а не типы PLSQL. PLSQL - это надстройка над SQL, поэтому SQL и "не видит" типы PLSQL.

Необходимо создать типы SQL с помощью оператора create or replace type. Нет другого способа, если уж вы хотите использовать эту возможность. Этот тип можно рассматривать как аналог представления - это метаданные. Как нельзя поместить в пакет представление, так нельзя помещать туда и подобный тип.

Комментарий читателя от 26 августа 2002 о передаче параметра функции в представлении
Это очень полезно для задачи, которой я сейчас занимаюсь, но как передать параметр такой функции? Вот представление, которое я использую, а GlSales - PL/SQL-функция с одним параметром, runDate.

Create View GL_View AS
SELECT
transactiondate
,postingdate
,networknumber
,location
,transactioncode
,transactiontype
,SUM(quantity) quantity
,SUM(dollaramount) dollaramount
,isdropship
,debitorcredit
,itemnumber
,itemvariant1
,itemvariant2
,itemvariant3
FROM
TABLE (CAST(GlSales(runDate) AS GlSalesTableType))
GROUP BY
transactiondate
,postingdate
,networknumber
,location
,transactioncode
,transactiontype
,isdropship
,debitorcredit
,itemnumber
,itemvariant1
,itemvariant2
,itemvariant3;

Если использовать Func(parm), как показано выше, выдается сообщение об ошибке:

TABLE (CAST(GlSales(runDate) AS GlSalesTableType))
*
ERROR at line 64:
ORA-00904: invalid column name

Ответ Тома Кайта
Придется делать что-то типа:

...
FROM
TABLE (CAST(GlSales(to_date(userenv('client_info'), 'dd/mm/yyyy'))
AS GlSalesTableType))
GROUP BY
...

а затем:

exec dbms_application_info.set_client_info('01/01/2002');
select * from gl_view;

Оригинал обсуждения этого вопроса можно найти здесь.

Комментарий В.К.
Что тут можно сказать... Отлично. Добавлю только синтаксис выражения CAST:

<выражение CAST> ::=
CAST (<выражение для преобразования> AS <имя типа>)
<выражение для преобразования> ::=
<выражение>
| (<подзапрос>)
| MULTISET (<подзапрос>)

--------------------------------------------------------------------------------

В следующем выпуске
Поскольку я получил всего пару пожеланий по работе рассылки от уже более, чем 2500 подписчиков, буду продолжать метаться от одной темы к другой. В следующем выпуске будем разбираться, когда и почему сервер переходит с одного файла журнала повторного выполнения (redo log) на другой, и что при этом происходит.

С наилучшими пожеланиями,

В.К.