CREATE OR REPLACE PACKAGE PKG_TEST
AS
TYPE R_CABLERES IS RECORD(DEVBM VARCHAR2(60),LINENUM NUMBER(6),LENGTH NUMBER(9,2),ERRINFO VARCHAR2(200));
TYPE T_CABLERES IS TABLE OF R_CABLERES INDEX BY BINARY_INTEGER;
--定义动态游标
TYPE T_CURSOR IS REF CURSOR;
END;
/CREATE OR REPLACE PROCEDURE TMP_P_TESTS(R_RECORD OUT PKG_TEST.T_CURSOR)
AS
R_ROW PKG_TEST.R_CABLERES; --记录
T_DATA PKG_TEST.T_CABLERES; --表
BEGIN
SELECT '第一行',1,2.3,'测试1' INTO R_ROW FROM DUAL;
T_DATA(T_DATA.COUNT + 1) := R_ROW;
SELECT '第二行',4,5.6,'测试2' INTO R_ROW FROM DUAL;
T_DATA(T_DATA.COUNT + 1) := R_ROW; --************************************************************
--在这里,如何将T_DATA里的数据赋值给游标R_RECORD从存储过程返回
--************************************************************
END;
/
AS
TYPE R_CABLERES IS RECORD(DEVBM VARCHAR2(60),LINENUM NUMBER(6),LENGTH NUMBER(9,2),ERRINFO VARCHAR2(200));
TYPE T_CABLERES IS TABLE OF R_CABLERES INDEX BY BINARY_INTEGER;
--定义动态游标
TYPE T_CURSOR IS REF CURSOR;
END;
/CREATE OR REPLACE PROCEDURE TMP_P_TESTS(R_RECORD OUT PKG_TEST.T_CURSOR)
AS
R_ROW PKG_TEST.R_CABLERES; --记录
T_DATA PKG_TEST.T_CABLERES; --表
BEGIN
SELECT '第一行',1,2.3,'测试1' INTO R_ROW FROM DUAL;
T_DATA(T_DATA.COUNT + 1) := R_ROW;
SELECT '第二行',4,5.6,'测试2' INTO R_ROW FROM DUAL;
T_DATA(T_DATA.COUNT + 1) := R_ROW; --************************************************************
--在这里,如何将T_DATA里的数据赋值给游标R_RECORD从存储过程返回
--************************************************************
END;
/
open R_RECORD for select * from THE (select cast(T_DATA as T_CABLERES) from dual);
2 /类型已创建。SQL>
SQL> create or replace function f_order(i_name in varchar2)
2 return sys_refcursor as
3 var_t_table t_order_table1;
4 var_cur sys_refcursor;
5 var_id number;
6 var_name varchar2(200);
7 var_rn number;
8 var_current_id number;
9 v_sql varchar2(4000);
10 var_cur_ret sys_refcursor;
11 begin
12 v_sql := 'select tt.id,tt.'||i_name||',row_number() over(partition by id order by id) rn from table1 tt';
13
14 dbms_output.put_line(v_sql);
15 var_t_table := t_order_table1();
16
17 open var_cur for v_sql;
18
19 var_current_id :=-1;
20
21
22 loop
23 fetch var_cur into var_id,var_name,var_rn;
24 exit when var_cur%NOTFOUND;
25 if(var_current_id<>var_id) then
26 var_current_id := var_id;
27 var_t_table.extend();
28 var_t_table(var_t_table.count) := to_char(var_id)||'|'||var_name;
29 else
30 var_t_table(var_t_table.count) := var_t_table(var_t_table.count)||','||var_name;
31 end if;
32
33 end loop;
34 close var_cur;
35 --return var_t_table;
36 open var_cur_ret for select * from THE (select cast(var_t_table as t_order_table1) from dual);
37 return var_cur_ret;
38 exception when others then
39 raise;
40
41 end;
42 /函数已创建。SQL>
SQL> select f_order('name') from dual;F_ORDER('NAME')
--------------------
CURSOR STATEMENT : 1CURSOR STATEMENT : 1COLUMN_VALUE
---------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------------------------------------------------------------------------------------1|a1,b1,c1
2|a2
3|a3,b3
我把open R_RECORD for select * from THE (select cast(T_DATA as T_CABLERES) from dual);
复制到我的代码里后编译报错‘无效的数据类型’,把包名称前缀加在T_CABLERES前面后还是报同样的错误