oracle create function 例項2

tolilong發表於2013-02-18
OracleFunction
上次使用的create function 查詢的結果在pl/sql developer上面顯示的cursor，並不能顯示需要查詢的結果。
今天在網上查詢到另外一種方法很好的替代了上次的方案，
[@more@]
具體步驟如下：
1.CREATE OR REPLACE TYPE "HISSAAS"."EMP_ROW_TYPE" as object
(tenant_id varchar2( 20 ),tenant_name varchar2 ( 50),mc varchar2 (50 ),mzl int,jzl int ,bll int ,sylint,qxs int ,qxl int );
2.CREATE OR REPLACE TYPE "HISSAAS"."EMP_TABLE_TYPE" as table of emp_row_type;
3.
create or replace function getempname_2(v_date1 in varchar2,v_date2 in varchar2 )
return emp_table_type pipelined
is
v emp_row_type;
begin
for thisrow in
( 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
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
) loop
v:=emp_row_type(thisrow.tenant_id,thisrow.tenant_name,thisrow.mc,thisrow.mzl,thisrow.jzl,thisrow.bll,thisrow.syl,thisrow.qxs,thisrow.qxl);
pipe row (v);
end loop ;
return;
end ;
4,在pl/sql中輸入
select * from table(getempname_2(20130101,20130201)) 即可查詢結果。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24237320/viewspace-1060213/，如需轉載，請註明出處，否則將追究法律責任。

相關文章