oracle 自定义函数

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 (' '));