1: 以管道的方式
--定义行对象
create type row_type as object(a varchar2(10), v varchar2(10));
--定义表对象
create type table_type as table of row_type;
--定义方法
create or replace function test_fun(a varchar2,b varchar2)
return table_type pipelined
is
-- 定义变量
v row_type;--定义v为行对象类型
ss varchar2(10);
begin
--给变量动态赋值
select '123456789' into ss from dual;
--输出参数
dbms_output.put_line(a || ' : ' || b || ' : ' || ss);
for thisrow in (
select 0 a , 1 b from dual
union all
select 2 a , 3 b from dual
) loop
v := row_type(thisrow.a, thisrow.b);
pipe row (v);
end loop;
return;
end;
--调用
select * from table(test_fun('dgd','456tt'));
2:以游标的方式
CREATE OR REPLACE FUNCTION A_Test(
a varchar2
)RETURN SYS_REFCURSOR
is
type_cur SYS_REFCURSOR;
BEGIN
OPEN type_cur FOR
select * from t_test_user where yhmc like a||'%';
RETURN type_cur;
END;
select A_Test('唐') from dual