Oracle数据库编程

2017-01-11 15:24:15来源:oschina作者:BeGit人点击

1.functions


--语法结构 create or replace function function_name( arg1 [] datatype1, arg2 [] datatype2 )return datattype is beginend; --调用语法var v1 varchar2(100)exec :v1:=function_name --一:不带参数的定义 create or replace function get_time return varchar2 is time_result varchar2(50); beginselect sysdate into time_result from dual;return time_result;end get_time;--命令窗口中执行


SQL> var tinfo varchar2(50); SQL> exec :tinfo:=get_time; --执行结果 PL/SQL procedure successfully completed tinfo --------- 08-7月 -16

--二:有入参 create or replace function get_time_in( in_info in varchar2 )return varchar2 is time_result varchar2(50); beginselect sysdate into time_result from dual;return time_result||in_info;end get_time_in;


--命令窗口中执行


SQL> var v1 varchar2(50); SQL> exec :v1:=get_time_in('aaaaaaaaaaaaaaaaaaaaa'); --执行结果 PL/SQL procedure successfully completed v1 --------- 08-7月 -16aaaaaaaaaaaaaaaaaaaaa


--三:带out参数的create or replace function get_time_in2( in_info in varchar2, job out varchar2 )return varchar2 is time_result varchar2(50); beginselect sysdate into time_result from dual;return time_result||in_info||job;end get_time_in2; --命令窗口中执行 SQL> var job varchar2(20) SQL> var e_name varchar2(20) SQL> exec :e_name:=get_time_in2('in args',:job);


--执行结果


PL/SQL procedure successfully completed e_name --------- 09-7月 -16in args job ---------


参考:http://blog.csdn.net/jumtre/article/details/38092067


2.procudures


3.package


4.triggers


参见:http://www.oschina.net/code/snippet_1052786_57772


5.view


6.schedules


------------------------------------------------------------------------------------------------------------


一个国家电网物资项目的一个脚本参考:


drop type htjs_business_row ; drop type htjs_business_state_row_type; drop PACKAGE business_state_pkg;


--定义自定类型 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;


--声明包 create or replace package business_state_pkg is--预算状态函数function fun_htjs_business_state return htjs_business_row;--续保状态函数function fun_htjs_bs_state_continue return htjs_business_row;--供应商状态函数function fun_htjs_bs_state_supp return htjs_business_row; end business_state_pkg;--包体 create or replace package body business_state_pkg is --预算状态函数start 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开始SELECTbiss. 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.issubmitFROMbosp_htys_businessreserve bissLEFT JOIN bosp_htys_receptioninfo rec ON biss. ID = rec.business_idLEFT JOIN bosp_htjs_contractinfo con ON con. ID = biss.contractinfo_idLEFT JOIN bosp_htjs_subengineeringinfo sub ON sub. ID = con.subengineeringinfo_idLEFT JOIN bosp_htjs_engineeringinfo eng ON eng. ID = sub.engineering_idLEFT JOIN bosp_global_supplier sup ON con.supplier_id = sup. IDLEFT JOIN bosp_htys_backstageinfo back ON back.qt_business_id = rec. IDLEFT JOIN bosp_htys_budget budget ON budget.business_id = biss. IDLEFT JOIN bosp_htys_monthlygatherinfo bhmgather ON bhmgather.business_id = biss. IDLEFT JOIN bosp_htjs_contract_report bhcreport ON bhmgather.report_id = bhcreport. IDLEFT JOIN bosp_htys_ysinfo ys ON ys.business_id = biss. IDwhere biss.business_type IN (1,2,3,4,5,6)AND ( (bhcreport.id is not null and bhcreport.report_type = 2 AND bhcreport.is_del = 0) or bhcreport.id is null)--状态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 THENif BUS_STATE=0 thenbusinessState := 'dsl';elsif (BUS_STATE=1 or BUS_STATE=3) thenif formalcensor is null thenbusinessState := 'dxssc';elsif formalcensor=0 thenbusinessState := 'xsscbtg';elsif formalcensor=1 thenbusinessState := '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 thenif substanceresult=0 then businessState := 'szscbtg';elsif substanceresult=1 then businessState := 'szsctg'; if isquit=1 thenif back_app_state is not null thenbusinessState := 'isquit_money_app_state' || back_app_state;end if; elsebusinessState := 'dbzys';if iscomplete=1 thenbusinessState := '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 thenbusinessState := 'thgys';if formalcensor=0 thenbusinessState := '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; --预算状态函数end


--续保状态函数start function fun_htjs_bs_state_continue return htjs_business_row as rs htjs_business_row:= htjs_business_row(); businessState VARCHAR2(50);


bissid bosp_htys_businessreserve.id%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;returnman bosp_htys_businessreserve.returnman%type;isinvalid bosp_htys_backstageinfo.isinvalid%type; q_receptionstate bosp_htys_receptioninfo.q_receptionstate%type;issubmit bosp_htys_backstageinfo.issubmit%type; begin for myrow in(--状态sql开始SELECT biss. ID,biss.BUS_STATE,rec.formalcensor,rec.accept_state,back.substanceresult,biss.returnman,back.isinvalid,rec.q_receptionstate,back.issubmitFROM bosp_htys_businessreserve bissLEFT JOIN bosp_htys_receptioninfo rec ON biss. ID = rec.business_idLEFT JOIN bosp_htjs_contractinfo con ON con. ID = biss.contractinfo_idLEFT JOIN bosp_htjs_subengineeringinfo sub ON sub. ID = con.subengineeringinfo_idLEFT JOIN bosp_htjs_engineeringinfo eng ON eng. ID = sub.engineering_idLEFT JOIN bosp_global_supplier sup ON con.supplier_id = sup. IDLEFT JOIN bosp_htys_backstageinfo back ON back.qt_business_id = rec. ID WHERE biss.business_type = 8--状态sql结束)loop --业务逻辑判断开始bissid := myrow.ID; BUS_STATE := myrow.BUS_STATE;formalcensor := myrow.formalcensor;accept_state := myrow.accept_state;substanceresult := myrow.substanceresult;returnman := myrow.returnman;isinvalid := myrow.isinvalid;q_receptionstate := myrow.q_receptionstate;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 thenbusinessState := 'dxssc'; elsif formalcensor=0 thenbusinessState := 'xsscbtg'; elsif formalcensor=1 thenbusinessState := 'xssctg';if accept_state=1 thenbusinessState := 'dszsc';if isinvalid=0 thenbusinessState := 'zf';end if;--实审开始if substanceresult is not null then--if issubmit is not null then --if issubmit=1 thenif substanceresult=0 thenbusinessState := 'szscbtg';elsif substanceresult=1 thenbusinessState := 'szsctg';elsif substanceresult=2 thenbusinessState := 'qxzz';end if; --end if;--end if;end if;--实审结束end if; end if; elsif BUS_STATE=2 then businessState := 'thgys'; if formalcensor=0 thenbusinessState := '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_bs_state_continue; --续保状态函数end


--供应商状态函数start function fun_htjs_bs_state_supp return htjs_business_row as rs htjs_business_row:= htjs_business_row(); businessState VARCHAR2(50);


bissid bosp_htys_businessreserve.id%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;returnman bosp_htys_businessreserve.returnman%type;isinvalid bosp_htys_backstageinfo.isinvalid%type; q_receptionstate bosp_htys_receptioninfo.q_receptionstate%type;app_state bosp_htys_ht_supplierinfobg.app_state%type;issubmit bosp_htys_backstageinfo.issubmit%type; begin for myrow in(--状态sql开始SELECTbiss. ID,biss.BUS_STATE,rec.formalcensor,rec.accept_state,back.substanceresult,biss.returnman,back.isinvalid,rec.q_receptionstate,suppbg.app_state,back.issubmit FROMbosp_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_ht_supplierinfobg suppbg ON biss. ID = suppbg.business_id WHERE BISS.BUSINESS_TYPE=7--状态sql结束)loop --业务逻辑判断开始bissid := myrow.ID; BUS_STATE := myrow.BUS_STATE;formalcensor := myrow.formalcensor;accept_state := myrow.accept_state;substanceresult := myrow.substanceresult;returnman := myrow.returnman;isinvalid := myrow.isinvalid;q_receptionstate := myrow.q_receptionstate;app_state := myrow.app_state;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 thenbusinessState := 'dxssc'; elsif formalcensor=0 thenbusinessState := 'xsscbtg'; elsif formalcensor=1 thenbusinessState := 'xssctg';if accept_state=1 thenbusinessState := 'dszsc';if isinvalid=0 thenbusinessState := 'zf';end if;--实审开始if substanceresult is not null then--if issubmit is not null then --if issubmit=1 thenif substanceresult=0 thenbusinessState := 'szscbtg';elsif substanceresult=1 thenbusinessState := 'szsctg';if app_state is not null then businessState := 'suppbg_app_state' || app_state; if app_state=2 thenif isinvalid=0 thenbusinessState := 'zf';end if; end if;end if; elsif substanceresult=2 thenbusinessState := 'qxzz';end if; --end if;--end if;end if;--实审结束end if; end if; elsif BUS_STATE=2 then businessState := 'thgys'; if formalcensor=0 thenbusinessState := '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_bs_state_supp; --供应商状态函数end


end business_state_pkg;


--创建视图 create materialized view mv_htjs_business_state refresh force on demand as select htjs_business_state.* from table(business_state_pkg.fun_htjs_business_state) htjs_business_state; create materialized view mv_htjs_bs_state_continue refresh force on demand as select htjs_bs_state_continue.* from table(business_state_pkg.fun_htjs_bs_state_continue) htjs_bs_state_continue; create materialized view mv_htjs_bs_state_supp refresh force on demand as select htjs_bs_state_supp.* from table(business_state_pkg.fun_htjs_bs_state_supp) htjs_bs_state_supp; --删除视图 drop materialized view mv_htjs_business_state; drop materialized view mv_htjs_bs_state_continue; drop materialized view mv_htjs_bs_state_supp; --查询 select htjs_business_state.* from table(business_state_pkg.fun_htjs_bs_state_supp) htjs_business_state; select * from mv_htjs_business_state; select htjs_bs_state_continue.* from table(business_state_pkg.fun_htjs_bs_state_continue) htjs_bs_state_continue; select * from mv_htjs_bs_state_continue; select htjs_bs_state_supp.* from table(business_state_pkg.fun_htjs_bs_state_supp) htjs_bs_state_supp; select * from mv_htjs_bs_state_supp; --刷新视图(命令) exec dbms_mview.refresh('mv_htjs_business_state'); exec dbms_mview.refresh('mv_htjs_bs_state_continue'); exec dbms_mview.refresh('mv_htjs_bs_state_supp');


--java调用:call dbms_mview.refresh('mv_htjs_business_state')";

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台