--再贴一次Arindom -- Thanks for the question regarding "Return values from a PL/SQL table into a ref cursor?", version 8.1.6 -------------------------------------------------------------------------------- You AskedHow do I return the values from a PL/SQL table (indexed by BINARY_INTEGER) into a ref cursor?The contents of the PL/SQL table are NOT returnable by a single SQL statement. AS it is a PL/SQL table ; I can't do a standard select (This doesn't work -open TunnelCrs for 'select gw1, gw2 from a') Currently, to create the ref. cursor am currently doing thefollowing: open TunnelCrs for 'select '||to_char(a(1).gw1)||','|| to_char(a(1).gw1)||' from dual';
If there are multiple rows ; I am using an 'union all' . The following is my type and PL/SQL table definitions: TYPE gw_ttn is record ( gw_id1 INTEGER, gw_id2 INTEGER );TYPE gw_tn is table of gw_ttn index by binary_integer; TYPE TunnelCursor IS REF CURSOR; a gw_tn;Is there a more elegant solution? -------------------------------------------------------------------------------- and we said...the proper way to do this is to NOT use a PLSQL table type but to use a SQL Object Type instead. It would look like this: [email protected]> create or replace type myScalarType as object 2 ( x int, 3 y date, 4 z varchar2(25) 5 ) 6 / Type [email protected]> create or replace type myTableType as table of myScalarType; 2 / Type [email protected]> create or replace 2 function demo_proc( p_start_row in number, 3 p_end_row in number ) 4 return myTableType 5 as 6 l_data myTableType := myTableType(); 7 l_cnt number default 0; 8 begin 9 for x in ( select * from emp order by sal desc ) 10 loop 11 l_cnt := l_cnt + 1; 12 if ( l_cnt >= p_start_row ) 13 then 14 l_data.extend; 15 l_data(l_data.count) := 16 myScalarType( x.empno, 17 x.hiredate, 18 x.ename ); 19 end if; 20 exit when l_cnt = p_end_row; 21 end loop; 22 23 return l_data; 24 end; 25 / Function [email protected]> select * 2 from the ( select cast( demo_proc(2,6) as mytableType ) 3 from dual ) a 4 / X Y Z ---------- --------- ------------------------- 7788 09-DEC-82 SCOTT 7902 03-DEC-81 FORD 7566 02-APR-81 JONES 7698 01-MAY-81 BLAKE 7782 09-JUN-81 [email protected]> So, I am recommending you use a SQL type -- not a plsql table type (they work very much the same with the notable exception that the SQL Nested table demands you use .EXTEND to allocate space whereas the plsql table type just "makes room" as needed.By using the SQL Type, you can select from the table easily. Your ref cursor example would be:[email protected]> create or replace package my_pkg 2 as 3 type rc is ref cursor; 4 5 procedure p( p_cursor in out rc ); 6 end; 7 /Package [email protected]> create or replace package body my_pkg 2 as 3 4 procedure p( P_cursor in out rc ) 5 is 6 l_data myTableType := myTableType(); 7 begin 8 for i in 1 .. 3 loop 9 l_data.extend; 10 l_data(i) := 11 myScalarType( i, sysdate+i, i || ' data'); 12 end loop; 13 14 open p_cursor for 15 select * 16 from TABLE ( cast ( l_data as myTableType) ); 17 end; 18 19 end; 20 /Package body [email protected]> set autoprint on [email protected]> variable x refcursor [email protected]> exec my_pkg.p(:x)PL/SQL procedure successfully completed. X Y Z ---------- --------- ------------------------- 1 27-MAY-00 1 data 2 28-MAY-00 2 data 3 29-MAY-00 3 [email protected]>
Was this response helpful to you? Let us know! Reviews
--------------------------------------------------------------------------------
You AskedHow do I return the values from a PL/SQL table
(indexed by BINARY_INTEGER) into a ref cursor?The contents of the PL/SQL table are NOT returnable by a single
SQL statement. AS it is a PL/SQL table ; I can't do a standard
select (This doesn't work -open TunnelCrs for
'select gw1, gw2 from a') Currently, to create the ref. cursor am currently doing thefollowing:
open TunnelCrs for
'select '||to_char(a(1).gw1)||','||
to_char(a(1).gw1)||' from dual';
If there are multiple rows ; I am using an 'union all' . The following is my type and PL/SQL table definitions:
TYPE gw_ttn is record (
gw_id1 INTEGER,
gw_id2 INTEGER
);TYPE gw_tn is table of gw_ttn index by binary_integer;
TYPE TunnelCursor IS REF CURSOR;
a gw_tn;Is there a more elegant solution? --------------------------------------------------------------------------------
and we said...the proper way to do this is to NOT use a PLSQL table type but to use a SQL
Object Type instead. It would look like this:
[email protected]> create or replace type myScalarType as object
2 ( x int,
3 y date,
4 z varchar2(25)
5 )
6 /
Type [email protected]> create or replace type myTableType as table of myScalarType;
2 /
Type [email protected]> create or replace
2 function demo_proc( p_start_row in number,
3 p_end_row in number )
4 return myTableType
5 as
6 l_data myTableType := myTableType();
7 l_cnt number default 0;
8 begin
9 for x in ( select * from emp order by sal desc )
10 loop
11 l_cnt := l_cnt + 1;
12 if ( l_cnt >= p_start_row )
13 then
14 l_data.extend;
15 l_data(l_data.count) :=
16 myScalarType( x.empno,
17 x.hiredate,
18 x.ename );
19 end if;
20 exit when l_cnt = p_end_row;
21 end loop;
22
23 return l_data;
24 end;
25 /
Function [email protected]> select *
2 from the ( select cast( demo_proc(2,6) as mytableType )
3 from dual ) a
4 / X Y Z
---------- --------- -------------------------
7788 09-DEC-82 SCOTT
7902 03-DEC-81 FORD
7566 02-APR-81 JONES
7698 01-MAY-81 BLAKE
7782 09-JUN-81 [email protected]>
So, I am recommending you use a SQL type -- not a plsql table type (they work
very much the same with the notable exception that the SQL Nested table demands
you use .EXTEND to allocate space whereas the plsql table type just "makes room"
as needed.By using the SQL Type, you can select from the table easily. Your ref cursor
example would be:[email protected]> create or replace package my_pkg
2 as
3 type rc is ref cursor;
4
5 procedure p( p_cursor in out rc );
6 end;
7 /Package [email protected]> create or replace package body my_pkg
2 as
3
4 procedure p( P_cursor in out rc )
5 is
6 l_data myTableType := myTableType();
7 begin
8 for i in 1 .. 3 loop
9 l_data.extend;
10 l_data(i) :=
11 myScalarType( i, sysdate+i, i || ' data');
12 end loop;
13
14 open p_cursor for
15 select *
16 from TABLE ( cast ( l_data as myTableType) );
17 end;
18
19 end;
20 /Package body [email protected]> set autoprint on
[email protected]> variable x refcursor
[email protected]> exec my_pkg.p(:x)PL/SQL procedure successfully completed.
X Y Z
---------- --------- -------------------------
1 27-MAY-00 1 data
2 28-MAY-00 2 data
3 29-MAY-00 3 [email protected]>
Was this response helpful to you? Let us know! Reviews