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