--Oracle10g
--首先建立一个Package
PACKAGE P
AS
TYPE recordset IS REF CURSOR;
FUNCTION get(var_in1 in VARCHAR, var_in2 in VARCHAR) return recordset;
END;PACKAGE BODY P
AS
FUNCTION get(var_in1 VARCHAR, var_in2 VARCHAR) return recordset IS
rc recordset;
BEGIN
if(var_in1='1' and var_in2='1') then
open rc for select 'hello' as var_out1, 'world' as var_out2 from dual;
end if;
return rc;
END;
END;--然后进行查询
create or replace type myType as object
(
var_out1 varchar(64),
var_out2 varchar(64)
) ;
create or replace type myTable as table of myType;--下面两句会出错
select * from THE ( select cast( P.GET(NULL,NULL) as myType) from dual) a ;
select * from table(cast(P.GET(NULL,NULL) as myTABLE));--错误为
ORA-00932: 数据类型不一致: 应为 -, 但却获得 CURSER刚接触Oracle第二天,已经快吐血了,不知道错在哪里,麻烦各位高手帮忙改正一下,谢谢!
--首先建立一个Package
PACKAGE P
AS
TYPE recordset IS REF CURSOR;
FUNCTION get(var_in1 in VARCHAR, var_in2 in VARCHAR) return recordset;
END;PACKAGE BODY P
AS
FUNCTION get(var_in1 VARCHAR, var_in2 VARCHAR) return recordset IS
rc recordset;
BEGIN
if(var_in1='1' and var_in2='1') then
open rc for select 'hello' as var_out1, 'world' as var_out2 from dual;
end if;
return rc;
END;
END;--然后进行查询
create or replace type myType as object
(
var_out1 varchar(64),
var_out2 varchar(64)
) ;
create or replace type myTable as table of myType;--下面两句会出错
select * from THE ( select cast( P.GET(NULL,NULL) as myType) from dual) a ;
select * from table(cast(P.GET(NULL,NULL) as myTABLE));--错误为
ORA-00932: 数据类型不一致: 应为 -, 但却获得 CURSER刚接触Oracle第二天,已经快吐血了,不知道错在哪里,麻烦各位高手帮忙改正一下,谢谢!
create or replace package p
is
type p_rec_typ is record(v1 varchar2(64), v2 varchar2(64));
type p_tab_typ is table of p_rec_typ;
function getv (v1 varchar2, v2 varchar2) return p_tab_typ pipelined;
end p;
/create or replace package body p
is
function getv (v1 varchar2, v2 varchar2)
return p_tab_typ pipelined
is
v p_rec_typ;
begin
if (v1='1' and v2='1') then
v.v1:='hello'; v.v2:='world';
pipe row(v);
end if;
return;
end getv;
end p;
/--select * from user_errors;
select * from table(p.getv(1,1));drop package p;-- 要么让函数返回 nested table 类型
create or replace type obj_typ is object(v1 varchar2(64), v2 varchar2(64));
/create or replace type obj_tab_typ is table of obj_typ;
/create or replace function getv(v1 varchar2, v2 varchar2)
return obj_tab_typ
is
tb obj_tab_typ:=obj_tab_typ(obj_typ('',''));
begin
if (v1='1' and v2='1') then
tb(1).v1:='hello'; tb(1).v2:='world';
end if;
return tb;
end;
/--select * from user_errors;
select * from table(getv('1','1'));drop type obj_tab_typ;
drop type obj_typ;
drop function getv;
第一种方法我试试看:)