甲骨文:将光标转换为表以进行连接

Question

我用相等的数据类型树不同的表。

我必须假脱机文件，但首先我必须将此表的每个表与其他表联接并创建带有结果的文件。

例：

标签1

ID|CODE
-------
1 | A
2 | B
3 | A

标签2

ID|CODE
-------
1 | C
2 | C
3 | A

塔布3

ID|CODE
-------
1 | C
2 | B
3 | B

NOM_CODES

CODE|DESC
A   | desc1
B   | desc1
C   | desc1
D   | desc1

这是树(每个表一个)过程之一：

procedure proc_tab1 is
l_File_Handle Utl_File.File_Type;
v_File_Name   VARCHAR2(100);
begin
v_File_Name := 'TAB_1.TXT';
IF Utl_File.Is_Open(l_File_Handle) THEN
Utl_File.Fclose(l_File_Handle);
END IF;
l_File_Handle := Utl_File.Fopen(l_DIR, v_File_Name, 'W', l_Len);
for c in (select t.ID||','||t.code||','||c.desc
from tab1 t
join nom_codes c
on t.code = c.code) loop
Utl_File.Put_Line(l_File_Handle,
Convert(c.file_line, 'CL8MSWIN1251') || l_cr);
end loop;
Utl_File.Fclose(l_File_Handle);
end proc_tab1 ;   

我想在一个新过程中重复(打开文件、连接、put_line、关闭文件)的事情。

像这样：

procedure proc_tab1 is
v_File_Name   VARCHAR2(100);
begin
v_File_Name := 'TAB_1.TXT';
spool_file(v_File_Name, cursor(select t.id, t.code from t1));
end proc_tab1 ;  

而这个新程序：

procedure spool_file (p_file_name varchar2, p_curr sys_refcursor) is
l_File_Handle Utl_File.File_Type;
begin 
IF Utl_File.Is_Open(l_File_Handle) THEN
Utl_File.Fclose(l_File_Handle);
END IF;
l_File_Handle := Utl_File.Fopen(l_DIR, p_file_name , 'W', l_Len);
for c in (select t.ID||','||t.code||','||c.desc
from table(p_curr) t
join nom_codes c
on t.code = c.code) loop
Utl_File.Put_Line(l_File_Handle,
Convert(c.file_line, 'CL8MSWIN1251') || l_cr);
end loop;
Utl_File.Fclose(l_File_Handle);
end spool_file ;

我想要以某种方式将行集从选项卡1(以及选项卡 2 和选项卡 3)传递到spool_file。spool_file将此行集转换为表并进行连接和其他常见想法。

实际上我试过这个，但我无法SYS_REFCURSOR转换为 TABLE。我可以创建一个返回流水线结果的函数，但我认为这不是一个好主意，因为我将不得不对数据进行两次迭代(一次用于管道，一次用于循环)。

我对想法持开放态度。

Answer 1

你可以在 Oracle 中做一些疯狂的事情，但这通常意味着架构出了什么问题：

declare
l_cur1 SYS_REFCURSOR;
l_cur2 SYS_REFCURSOR;
begin
--test data
open l_cur1 for select 1 id, 'A' code from dual union all select 2 id, 'B' code from dual;
open l_cur2 for select 'A' code, 'A-VALUE' name from dual union all select 'B' code, 'B-VALUE' name from dual;
--convert cursors in xml and parse and joins
for c in ( 
select tab1.id, tab1.code, tab2.NAME
from ( 
select extractvalue( value( src1 ), 'ROW/ID' ) ID, extractvalue( value( src1 ), 'ROW/CODE' ) CODE 
from table( xmlsequence( xmltype( l_cur1 ).extract( 'ROWSET/ROW' ) ) ) src1 ) tab1
left join (
select extractvalue( value( src2 ), 'ROW/CODE' ) CODE, extractvalue( value( src2 ), 'ROW/NAME' ) NAME 
from table( xmlsequence( xmltype( l_cur2 ).extract( 'ROWSET/ROW' ) ) ) src2 ) tab2 on tab1.CODE = tab2.CODE
) 
loop
dbms_output.put_line( c.id );
dbms_output.put_line( c.code );
dbms_output.put_line( c.name );
dbms_output.put_line( '---------------' );
end loop;
end;