oracle create function的兩個例項

tolilong發表於2013-02-09
OracleFunction
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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章