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

最新推荐文章于 2021-07-27 15:41:44 发布

weixin_34122548

最新推荐文章于 2021-07-27 15:41:44 发布

阅读量89

点赞数

文章标签：数据库 python java

原文链接：https://my.oschina.net/u/1052786/blog/740140

版权

2019独角兽企业重金招聘Python工程师标准>>>

--注：用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 := 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,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;

--增加记录
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;