oracle返回表id,Oracle 函数返回表实例2种写法实例

--注：用ps/sql工具执行语句

--一合同预算状态函数

--1.

drop type htjs_business_row

drop type htjs_business_state_row_type

CREATE OR REPLACE type htjs_business_state_row_type as object(bissid VARCHAR2(20),businessState varchar2(50));

CREATE OR REPLACE type htjs_business_row as table of htjs_business_state_row_type;

--2.

CREATE OR REPLACE

function fun_htjs_business_state return htjs_business_row pipelined as

v htjs_business_state_row_type;

businessState VARCHAR2(50);

bissid bosp_htys_businessreserve.id%type;

isquit bosp_htys_businessreserve.isquit%type;

BUS_STATE bosp_htys_businessreserve.BUS_STATE%type;

formalcensor bosp_htys_receptioninfo.formalcensor%type;

accept_state bosp_htys_receptioninfo.accept_state%type;

substanceresult bosp_htys_backstageinfo.substanceresult%type;

iscomplete bosp_htys_backstageinfo.iscomplete%type;

gather_state bosp_htys_backstageinfo.gather_state%type;

app_state bosp_htjs_contract_report.app_state%type;

back_app_state bosp_htys_backstageinfo.app_state%type;

mortem_state bosp_htys_ysinfo.mortem_state%type;

returnman bosp_htys_businessreserve.returnman%type;

issubmit bosp_htys_backstageinfo.issubmit%type;

begin

for myrow in (

--状态sql开始

SELECT

biss. ID,

biss.isquit,

biss.BUS_STATE,

rec.formalcensor,

rec.accept_state,

back.substanceresult,

back.iscomplete,

back.gather_state,

back.app_state back_app_state,

bhcreport.app_state,

ys.mortem_state,

biss.returnman,

back.issubmit

FROM

bosp_htys_businessreserve biss

LEFT JOIN bosp_htys_receptioninfo rec ON biss. ID = rec.business_id

LEFT JOIN bosp_htjs_contractinfo con ON con. ID = biss.contractinfo_id

LEFT JOIN bosp_htjs_subengineeringinfo sub ON sub. ID = con.subengineeringinfo_id

LEFT JOIN bosp_htjs_engineeringinfo eng ON eng. ID = sub.engineering_id

LEFT JOIN bosp_global_supplier sup ON con.supplier_id = sup. ID

LEFT JOIN bosp_htys_backstageinfo back ON back.qt_business_id = rec. ID

LEFT JOIN bosp_htys_budget budget ON budget.business_id = biss. ID

LEFT JOIN bosp_htys_monthlygatherinfo bhmgather ON bhmgather.business_id = biss. ID

LEFT JOIN bosp_htjs_contract_report bhcreport ON (

bhmgather.report_id = bhcreport. ID

AND bhcreport.report_type = 2

AND bhcreport.is_del = 0

)

LEFT JOIN bosp_htys_ysinfo ys ON ys.business_id = biss. ID

where biss.business_type IN (1,2,3,4,5,6)

--状态sql结束

) loop

--业务逻辑判断开始

bissid := myrow.ID;

isquit := myrow.isquit;

BUS_STATE := myrow.BUS_STATE;

formalcensor := myrow.formalcensor;

accept_state := myrow.accept_state;

substanceresult := myrow.substanceresult;

iscomplete := myrow.iscomplete;

gather_state := myrow.gather_state;

app_state := myrow.app_state;

back_app_state := myrow.back_app_state;

mortem_state := myrow.mortem_state;

returnman := myrow.returnman;

issubmit := myrow.issubmit;

dbms_output.put_line(BUS_STATE);

if BUS_STATE is not null THEN

if BUS_STATE=0 then

businessState := 'dsl';

elsif (BUS_STATE=1 or BUS_STATE=3) then

if formalcensor is null then

businessState := 'dxssc';

elsif formalcensor=0 then

businessState := 'xsscbtg';

elsif formalcensor=1 then

businessState := 'xssctg';

if accept_state=1 then

businessState := 'dszsc';

if mortem_state=4 then

businessState := 'ysth';

end if;

--实审开始

if substanceresult is not null then

if issubmit is not null then

if issubmit=1 then

if substanceresult=0 then

businessState := 'szscbtg';

elsif substanceresult=1 then

businessState := 'szsctg';

if isquit=1 then

if back_app_state is not null then

businessState := 'isquit_money_app_state' || back_app_state;

end if;

else

businessState := 'dbzys';

if iscomplete=1 then

businessState := 'ybzys';

if gather_state=1 then

if app_state is not null then

businessState := 'declare_state' || app_state;

end if;

end if;

end if;

end if;

elsif substanceresult=2 then

businessState := 'qxzz';

end if;

end if;

end if;

end if;

--实审结束

end if;

end if;

elsif BUS_STATE=2 then

businessState := 'thgys';

if formalcensor=0 then

businessState := 'xsscbtg';

end if;

end if;

end if;

--业务逻辑判断结束

v := htjs_business_state_row_type(bissid,businessState);

pipe row (v);

end loop;

return;

end;

--二：测试函数结果集表

select htjs_business_state.* from table(fun_htjs_business_state) htjs_business_state;

--三：创建对应物化视图

drop materialized view mv_htjs_business_state;

create materialized view mv_htjs_business_state refresh force on demand as select htjs_business_state.* from table(fun_htjs_business_state) htjs_business_state;

--1.查询语句：

select * from mv_htjs_business_state;

--四：调用前手动刷新物化视图

--1.pl/sql命令刷新：exec dbms_mview.refresh('mv_htjs_business_state');

--2.java中调用：call dbms_mview.refresh('mv_htjs_business_state')";

--业务状态写法二：

create or replace function fun_htjs_business_state

return htjs_business_row

as

rs htjs_business_row:= htjs_business_row();

businessState VARCHAR2(50);

bissid bosp_htys_businessreserve.id%type; isquit bosp_htys_businessreserve.isquit%type; BUS_STATE bosp_htys_businessreserve.BUS_STATE%type; formalcensor bosp_htys_receptioninfo.formalcensor%type; accept_state bosp_htys_receptioninfo.accept_state%type; substanceresult bosp_htys_backstageinfo.substanceresult%type; iscomplete bosp_htys_backstageinfo.iscomplete%type; gather_state bosp_htys_backstageinfo.gather_state%type; app_state bosp_htjs_contract_report.app_state%type; back_app_state bosp_htys_backstageinfo.app_state%type; mortem_state bosp_htys_ysinfo.mortem_state%type; returnman bosp_htys_businessreserve.returnman%type; issubmit bosp_htys_backstageinfo.issubmit%type; begin for myrow in( --状态sql开始 SELECT biss. ID,biss.isquit,biss.BUS_STATE,rec.formalcensor,rec.accept_state,back.substanceresult,back.iscomplete,back.gather_state,back.app_state back_app_state,bhcreport.app_state,ys.mortem_state,biss.returnman,back.issubmit FROM bosp_htys_businessreserve biss LEFT JOIN bosp_htys_receptioninfo rec ON biss. ID = rec.business_id LEFT JOIN bosp_htjs_contractinfo con ON con. ID = biss.contractinfo_id LEFT JOIN bosp_htjs_subengineeringinfo sub ON sub. ID = con.subengineeringinfo_id LEFT JOIN bosp_htjs_engineeringinfo eng ON eng. ID = sub.engineering_id LEFT JOIN bosp_global_supplier sup ON con.supplier_id = sup. ID LEFT JOIN bosp_htys_backstageinfo back ON back.qt_business_id = rec. ID LEFT JOIN bosp_htys_budget budget ON budget.business_id = biss. ID LEFT JOIN bosp_htys_monthlygatherinfo bhmgather ON bhmgather.business_id = biss. ID LEFT JOIN bosp_htjs_contract_report bhcreport ON ( bhmgather.report_id = bhcreport. ID AND bhcreport.report_type = 2 AND bhcreport.is_del = 0 ) LEFT JOIN bosp_htys_ysinfo ys ON ys.business_id = biss. ID where biss.business_type IN (1,6) --状态sql结束 ) loop --业务逻辑判断开始 bissid := myrow.ID; isquit := myrow.isquit; BUS_STATE := myrow.BUS_STATE; formalcensor := myrow.formalcensor; accept_state := myrow.accept_state; substanceresult := myrow.substanceresult; iscomplete := myrow.iscomplete; gather_state := myrow.gather_state; app_state := myrow.app_state; back_app_state := myrow.back_app_state; mortem_state := myrow.mortem_state; returnman := myrow.returnman; issubmit := myrow.issubmit; dbms_output.put_line(BUS_STATE); if BUS_STATE is not null THEN if BUS_STATE=0 then businessState := 'dsl'; elsif (BUS_STATE=1 or BUS_STATE=3) then if formalcensor is null then businessState := 'dxssc'; elsif formalcensor=0 then businessState := 'xsscbtg'; elsif formalcensor=1 then businessState := 'xssctg'; if accept_state=1 then businessState := 'dszsc'; if mortem_state=4 then businessState := 'ysth'; end if; --实审开始 if substanceresult is not null then if issubmit is not null then if issubmit=1 then if substanceresult=0 then businessState := 'szscbtg'; elsif substanceresult=1 then businessState := 'szsctg'; if isquit=1 then if back_app_state is not null then businessState := 'isquit_money_app_state' || back_app_state; end if; else businessState := 'dbzys'; if iscomplete=1 then businessState := 'ybzys'; if gather_state=1 then if app_state is not null then businessState := 'declare_state' || app_state; end if; end if; end if; end if; elsif substanceresult=2 then businessState := 'qxzz'; end if; end if; end if; end if; --实审结束 end if; end if; elsif BUS_STATE=2 then businessState := 'thgys'; if formalcensor=0 then businessState := 'xsscbtg'; end if; end if; end if; --增加记录 rs.extend; DBMS_OUTPUT.put_line (rs.count); rs(rs.count) := htjs_business_state_row_type(bissid,businessState); end loop; return rs; end fun_htjs_business_state;

总结

如果觉得编程之家网站内容还不错，欢迎将编程之家网站推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。