Of course you can return resultsets from functions (see example
below). However, what I think the poster is requesting is the return
of an array structure (also example below).
FIRST - Returning resultset from function...
--
- Global types package...
--
CREATE OR REPLACE PACKAGE pkg_my_types
AS
TYPE typ_ref_cursor IS REF CURSOR;
END;
/
--
- Function returning a resultset, referencing user-defined ref cursor
type...
--
CREATE OR REPLACE FUNCTION return_rc (
query_in IN VARCHAR2
) RETURN pkg_my_types.typ_ref_cursor
AS
cur_results pkg_my_types.typ_ref_cursor;
BEGIN
OPEN cur_results FOR query_in;
RETURN cur_results;
END;
/
--
- Execute the function to get the resultset...
--
col object_name format a30
set autoprint on
variable resultset refcursor
exec :resultset := return_rc('select * from emp');
SECOND - return an array...
--
- Create a nested table SQL type...
--
create or replace type myTableType
as table of varchar2(30);
/
--
- Create a function to return loaded array of nested table type...
--
create or replace function demo_fnc return myTableType as
t_return_array myTableType :=
myTableType('DBMS_SQL','DBMS_OUTPUT','DBMS_UTILITY');
begin
return t_return_array;
end;
/
--
- SELECT from it...
--
select column_value
from TABLE(CAST(demo_fnc() AS myTableType)));
--
- Test it with an IN predicate...
--
select owner, object_name, object_type
from all_objects
where object_name in (select column_value
from TABLE(CAST(demo_fnc() AS myTableType)));
--
- Test it with an equality predicate...
--
select a.owner, a.object_name, a.object_type
from all_objects a
, table(cast(demo_fnc() as myTableType)) b
where a.object_name = b.column_value;
Hope this helps. If you want a composite collection (several
attributes), then you must create an object type to base your nested
table on...
--
- Object type (unfortunately no %rowtype support)...
--
create type myObjectType as object
( id number
, str varchar2(128)
);
/
--
- Nested table type...
--
create type myArrayType
as table of myObjectType;
/
--
- Function to load composite varray and return it...
--
CREATE FUNCTION myFunction RETURN myArrayType AS
myArray myArrayType := myArrayType(myObjectType(1,'First record'),
myObjectType(2,'Second record'),
myObjectType(3,'Third record')
);
BEGIN
RETURN myArray;
END;
/
--
- Select from it...
--
SELECT id, str
FROM TABLE(CAST(myFunction() AS myArrayType));
Hope this helps. When we hit 9i, we will be extending the use of TABLE
functions, so these have become pretty optimized...
Regards
Adrian
Received on Mon Aug 12 2002 - 06:44:26 CDT
