1.返回单行的函数
create or replace function query_store (vcode varchar2)
return varchar2
is
vname store.name%type;
begin
select name into vname from store where code=vcode;
return vname;
end;
select query_store ('0002') from dual;
2.返回游标格式
create or replace function query_store_sysrefcursor(vcode varchar2)
return sys_refcursor
is
my_cursor sys_refcursor;
begin
open my_cursor for
select code,name from store where code=vcode;
return my_cursor;
end;
select query_store_sysrefcursor('0002') from dual;
3.返回结果集
create or replace rype store_row_type as object ( code varchar2(10),name varchar2(50));
create or replace type store_table_type is table of store_row_type;
create or replace function query_store_tabletype (vcode varchar2)
return store_table_type pipelined
as
r store_row_type;
begin
for a in (select code ,name from store ) loop
r:=store_row_type(a.code,a.code);
pipe row(r);
end loop;
return;
end;
select * from table (query_store_tabletype (' '));
