1.functions

--语法结构

create or replace function function_name( arg1 [] datatype1, arg2 [] datatype2 )return datattype is begin   end;  --调用语法   var v1 varchar2(100)   exec :v1:=function_name      --一：不带参数的定义  create or replace function get_time return varchar2 is time_result varchar2(50); begin   select 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); begin   select 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); begin   select 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开始                     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                     LEFT JOIN bosp_htys_ysinfo ys ON ys.business_id = biss. ID                     where 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 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; --预算状态函数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.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                    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 then                           businessState := 'dxssc';                       elsif formalcensor=0  then                           businessState := 'xsscbtg';                       elsif formalcensor=1  then                           businessState := 'xssctg';                           if accept_state=1  then                               businessState := 'dszsc';                               if isinvalid=0  then                                   businessState := 'zf';                               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';                                                       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_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开始                     SELECT                     biss. ID,                     biss.BUS_STATE,                     rec.formalcensor,                     rec.accept_state,                     back.substanceresult,                     biss.returnman,                     back.isinvalid,                     rec.q_receptionstate,                     suppbg.app_state,                     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_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 then                           businessState := 'dxssc';                       elsif formalcensor=0  then                           businessState := 'xsscbtg';                       elsif formalcensor=1  then                           businessState := 'xssctg';                           if accept_state=1  then                               businessState := 'dszsc';                               if isinvalid=0  then                                   businessState := 'zf';                               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 app_state is not null then                                                                       businessState := 'suppbg_app_state' || app_state;                                                                       if app_state=2  then                                                                          if isinvalid=0  then                                                                              businessState := 'zf';                                                                          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_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')";