oracle create function的两个实例

oracle create function的两个实例:[@more@]

create function的两种例子:

CREATE OR REPLACE FUNCTION "HISSAAS"."GETDEPARTNAME" (vDepartCode in varchar2) return varchar2

as

DepartName varchar2( 1000);

begin

select tenant_name into DepartName from saas.SYY_TENANT where tenant_id = vDepartCode;

return DepartName;

end ;

/

=================================================================================

CREATE OR REPLACE FUNCTION "HISSAAS"."GETEMPNAME_1" (v_date1 in varchar2, v_date2 in varchar2 )

return sys_RefCursor

as

type cur_current is Ref Cursor;

cur_result cur_current;

v_cur varchar2( 1000);

begin

v_cur:=

'select p.tenant_id,

getdepartname(p.TENANT_ID) tenant_name,

getEmpName(p.diagdoct) mc,

count(distinct p.mzsn) mzl,

count(distinct p.mzsn) jzl,

count(distinct i.mzemrid) bll,

(case

when count(distinct p.mzsn) <> 0 then

count(distinct i.mzemrid) / count(distinct p.mzsn)

else

0.0000

end) syl,

count(distinct c.emrid) qxs,

(case

when count(i.mzemrid) <> 0 then

count(distinct c.emrid) / count(distinct i.mzemrid)

else

0

end) qxl

from SMZ_PATIDIAGREC p, SBL_MZEMR_INDEX i, SBL_QUA_CHK_CORRECT c

where 1 = 1

and p.mzsn = i.mzsn

and c.emrid = i.mzemrid

and p.diagtime >= to_date(''' ||v_date1||''',''yyyy/mm/dd'')

and p.diagtime <= to_date(''' ||v_date2||''',''yyyy/mm/dd'')

group by p.tenant_id, getdepartname(p.TENANT_ID), p.DIAGDOCT

order by p.tenant_id, p.tenant_id' ;

open cur_result for v_cur;

return cur_result;

end ;

/

但是这个不能使用:select * from table(getempname_1(x,y)) 查询

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24237320/viewspace-1060203/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/24237320/viewspace-1060203/