oracle存儲(chǔ)過程、匿名塊、函數(shù)、包

使用過程與函數(shù)的原則：

1、如果需要返回多個(gè)值和不返回值，就使用過程；如果只需要返回一個(gè)值，就使用函數(shù)。

2、過程一般用于執(zhí)行一個(gè)指定的動(dòng)作，函數(shù)一般用于計(jì)算和返回一個(gè)值。

3、可以SQL語句內(nèi)部（如表達(dá)式）調(diào)用函數(shù)來完成復(fù)雜的計(jì)算問題，但不能調(diào)用過程。所以這是函數(shù)的特色。

一、存儲(chǔ)過程

1、存儲(chǔ)過程初步

--存儲(chǔ)過程:實(shí)現(xiàn)搬歷史表
create or replace procedure movetohistory_1 (
                                          
                                           o_count out number ,
                                           error out VARCHAR2) IS
V_COU-NT number;
V_SUBSCRIBE_ID TI_C_IOM_BUSIFORM.SUBSCRIBE_ID% TYPE;
v_time date  := to_date( '2015/3/20 16:24:23','yyyy-mm-dd hh34:mi:ss' );
CURSOR C_MOVE2HIS IS
 SELECT SUBSCRIBE_ID FROM TI_C_IOM_BUSIFORM where accept_date <= v_time;
 
BEGIN
  o_count :=0;
  OPEN C_MOVE2HIS;
  LOOP
    FETCH C_MOVE2HIS INTO V_SUBSCRIBE_ID;
    EXIT WHEN C_MOVE2HIS%NOTFOUND;
    BEGIN
       SELECT COUNT (*) INTO V_COUNT from  TI_C_IOM_BUSIFORM where accept_date <= v_time;
       IF(V_COUNT > 0 ) THEN
            INSERT INTO TI_CH_IOM_BUSIFORM
            (BUSIFORM_ID,DAY,TRADE_ID,TRADEGROUP_ID,TRADEGROUP_NUM,TRADEGROUP_INDEX,BUSIFORM_TYPE,SUBSCRIBE_ID,BUSIFORM_OPER_TYPE,BUSINESS_TYPE,STATUS,NET_TYPE_CODE,ADDRESS_CODE_A,ADDRESS_CODE_Z,FLOW_TEMPLET_ID,IN_MODE_CODE,DBSRC,BPM_MAIN_ID,ORDER_ID,PRIORITY,SUGGEST_TIME,CONFIRM_RES_NO,PROD_SPEC_ID,RES_TEMPLET_ID,ACCEPT_DATE,EXEC_TIME,EXEC_MONTH,LATEST_FINISH_TIME,PLAN_FINISH_TIME,FINISH_TIME,SRC_TYPE,BUSIFORM_EXTEND,USER_ID,TRADE_STAFF_ID,TRADE_EPARCHY_CODE,TRADE_CITY_CODE,TRADE_DEPART_ID,AREA_CODE,SERIAL_NUMBER,BUSIFORM_CUST_TYPE,TERM_IP,CUST_CLASS_ID,CUST_ID,CUST_NAME,CUST_LINK_NAME,CUST_LINK_PHONE,PRODUCT_ID,PRODUCT_NAME,ERROR_TYPE, ERROR_CODE,ERROR_DESC,CLOSE_SATISFY_DEGREE,CLOSE_DESC,RSRV_STR1,RSRV_STR2,RSRV_STR3,RSRV_DATE1,RSRV_DATE2, REMARK)
            ( select BUSIFORM_ID,DAY,TRADE_ID,TRADEGROUP_ID,TRADEGROUP_NUM,TRADEGROUP_INDEX,BUSIFORM_TYPE,SUBSCRIBE_ID,BUSIFORM_OPER_TYPE,BUSINESS_TYPE,STATUS,NET_TYPE_CODE,ADDRESS_CODE_A,ADDRESS_CODE_Z,FLOW_TEMPLET_ID,IN_MODE_CODE,DBSRC,BPM_MAIN_ID,ORDER_ID,PRIORITY,SUGGEST_TIME,CONFIRM_RES_NO,PROD_SPEC_ID,RES_TEMPLET_ID,ACCEPT_DATE,EXEC_TIME,EXEC_MONTH,LATEST_FINISH_TIME,PLAN_FINISH_TIME,FINISH_TIME,SRC_TYPE,BUSIFORM_EXTEND,USER_ID,TRADE_STAFF_ID,TRADE_EPARCHY_CODE,TRADE_CITY_CODE,TRADE_DEPART_ID,AREA_CODE,SERIAL_NUMBER,BUSIFORM_CUST_TYPE,TERM_IP,CUST_CLASS_ID,CUST_ID,CUST_NAME,CUST_LINK_NAME,CUST_LINK_PHONE,PRODUCT_ID,PRODUCT_NAME,ERROR_TYPE, ERROR_CODE,ERROR_DESC,CLOSE_SATISFY_DEGREE,CLOSE_DESC,RSRV_STR1,RSRV_STR2,RSRV_STR3,RSRV_DATE1,RSRV_DATE2, REMARK
             from  TI_C_IOM_BUSIFORM
             where SUBSCRIBE_ID =  V_SUBSCRIBE_ID
               and accept_date <= v_time);
             delete FROM TI_C_IOM_BUSIFORM
             where SUBSCRIBE_ID =  V_SUBSCRIBE_ID
             and accept_date <= v_time;
            o_count := o_count + 1;
        END IF ;
      commit;  
    EXCEPTION
        WHEN OTHERS THEN
           rollback;
           delete from TI_CH_IOM_BUSIFORM
            where SUBSCRIBE_ID =  V_SUBSCRIBE_ID;
            commit;
    END;
  END LOOP;
  close C_MOVE2HIS;
end movetohistory_1;

2、存儲(chǔ)過程，加自定義exception，并改進(jìn)，由外部傳參數(shù)

--存儲(chǔ)過程
create or replace
procedure movetohistory ( o_time in date,
                          o_count out number ) IS
V_COUNT number;
V_SUBSCRIBE_ID TI_C_IOM_BUSIFORM.SUBSCRIBE_ID% TYPE;
v_time date := o_time;
v_error exception; --自定義異常
CURSOR C_MOVE2HIS IS
 SELECT SUBSCRIBE_ID FROM TI_C_IOM_BUSIFORM where accept_date <= v_time;
BEGIN
  o_count :=0;
  SELECT COUNT (*) INTO V_COUNT from  TI_C_IOM_BUSIFORM where accept_date <= v_time;
  if(V_COUNT <= 0 ) then
   raise v_error;
  end if;
  OPEN C_MOVE2HIS;
  LOOP
    FETCH C_MOVE2HIS INTO V_SUBSCRIBE_ID;
    EXIT WHEN C_MOVE2HIS%NOTFOUND;
    BEGIN
    SELECT COUNT (*) INTO V_COUNT from  TI_C_IOM_BUSIFORM where accept_date <= v_time;
    IF(V_COUNT > 0 ) THEN
         INSERT INTO TI_CH_IOM_BUSIFORM
        (BUSIFORM_ID,DAY,TRADE_ID,TRADEGROUP_ID,TRADEGROUP_NUM,TRADEGROUP_INDEX,BUSIFORM_TYPE,SUBSCRIBE_ID,BUSIFORM_OPER_TYPE,BUSINESS_TYPE,STATUS,NET_TYPE_CODE,ADDRESS_CODE_A,ADDRESS_CODE_Z,FLOW_TEMPLET_ID,IN_MODE_CODE,DBSRC,BPM_MAIN_ID,ORDER_ID,PRIORITY,SUGGEST_TIME,CONFIRM_RES_NO,PROD_SPEC_ID,RES_TEMPLET_ID,ACCEPT_DATE,EXEC_TIME,EXEC_MONTH,LATEST_FINISH_TIME,PLAN_FINISH_TIME,FINISH_TIME,SRC_TYPE,BUSIFORM_EXTEND,USER_ID,TRADE_STAFF_ID,TRADE_EPARCHY_CODE,TRADE_CITY_CODE,TRADE_DEPART_ID,AREA_CODE,SERIAL_NUMBER,BUSIFORM_CUST_TYPE,TERM_IP,CUST_CLASS_ID,CUST_ID,CUST_NAME,CUST_LINK_NAME,CUST_LINK_PHONE,PRODUCT_ID,PRODUCT_NAME,ERROR_TYPE, ERROR_CODE,ERROR_DESC,CLOSE_SATISFY_DEGREE,CLOSE_DESC,RSRV_STR1,RSRV_STR2,RSRV_STR3,RSRV_DATE1,RSRV_DATE2, REMARK)
        ( select BUSIFORM_ID,DAY,TRADE_ID,TRADEGROUP_ID,TRADEGROUP_NUM,TRADEGROUP_INDEX,BUSIFORM_TYPE,SUBSCRIBE_ID,BUSIFORM_OPER_TYPE,BUSINESS_TYPE,STATUS,NET_TYPE_CODE,ADDRESS_CODE_A,ADDRESS_CODE_Z,FLOW_TEMPLET_ID,IN_MODE_CODE,DBSRC,BPM_MAIN_ID,ORDER_ID,PRIORITY,SUGGEST_TIME,CONFIRM_RES_NO,PROD_SPEC_ID,RES_TEMPLET_ID,ACCEPT_DATE,EXEC_TIME,EXEC_MONTH,LATEST_FINISH_TIME,PLAN_FINISH_TIME,FINISH_TIME,SRC_TYPE,BUSIFORM_EXTEND,USER_ID,TRADE_STAFF_ID,TRADE_EPARCHY_CODE,TRADE_CITY_CODE,TRADE_DEPART_ID,AREA_CODE,SERIAL_NUMBER,BUSIFORM_CUST_TYPE,TERM_IP,CUST_CLASS_ID,CUST_ID,CUST_NAME,CUST_LINK_NAME,CUST_LINK_PHONE,PRODUCT_ID,PRODUCT_NAME,ERROR_TYPE, ERROR_CODE,ERROR_DESC,CLOSE_SATISFY_DEGREE,CLOSE_DESC,RSRV_STR1,RSRV_STR2,RSRV_STR3,RSRV_DATE1,RSRV_DATE2, REMARK
         from  TI_C_IOM_BUSIFORM
         where SUBSCRIBE_ID =  V_SUBSCRIBE_ID
         and accept_date <= v_time);
         delete FROM TI_C_IOM_BUSIFORM
         where SUBSCRIBE_ID =  V_SUBSCRIBE_ID
         and accept_date <= v_time;
         o_count := o_count + 1;
    end if ;
    commit; 
    EXCEPTION
        WHEN OTHERS THEN
           rollback;
           delete from TI_CH_IOM_BUSIFORM
            where SUBSCRIBE_ID =  V_SUBSCRIBE_ID;
            commit;
    END;
  END LOOP;
  close C_MOVE2HIS;
  exception
   when v_error then
          RAISE_APPLICATION_ERROR(- 20010, 'data is not exists!' );
end movetohistory;

--存儲(chǔ)過程調(diào)用
set serveroutput on;
declare
v_date date  := to_date( '2015/3/24 19:19:21','yyyy-mm-dd hh34:mi:ss' );
o_count number;
begin
o_count := 0;
movetohistory(v_date,o_count);
dbms_output.put_line( 'o_count:'||o_count);
end;

exec 存儲(chǔ)過程名;

--存儲(chǔ)過程賦權(quán)限
grant create any table to username;
grant create any procedure to username;
grant execute any procedure to username;

二、匿名塊

--匿名塊：在控制臺(tái)實(shí)現(xiàn)簡(jiǎn)單輸出（輸入暫時(shí)沒實(shí)現(xiàn)）
SET SERVEROUTPUT ON;
declare
v_flow_templet_id TD_M_NODE_TEMPLET.flow_templet_id% type;
v_node_templet_id TD_M_NODE_TEMPLET.node_templet_id% type;
begin
--v_node_templet_id := &請(qǐng)輸入節(jié)點(diǎn)名;   -- 這塊還沒有實(shí)現(xiàn)，總是報(bào)沒有聲明的錯(cuò)
v_node_templet_id := 'BIZOPPORDER';
SELECT flow_templet_id into v_flow_templet_id FROM TD_M_NODE_TEMPLET a WHERE node_templet_id = v_node_templet_id;
dbms_output.put_line(v_flow_templet_id);
EXCEPTION
--WHEN NO_DATA_FOUND THEN
--dbms_output.put_line('未找到數(shù)據(jù)');
WHEN OTHERS THEN
dbms_output.put_line( '@SQLCODE IS '||SQLCODE ||' AND @SQLERRM is '||SQLERRM);
end;

三、函數(shù)

1、函數(shù)簡(jiǎn)單示例

create or replace function tomorrow
return date --必須有返回
is
  today date; --返回值在聲明部分
  nextdate date;
begin
  today := sysdate;
  nextdate := today + 1;
return nextdate; --return
exception
when others then
  return '-1';  --異常部分有return
end;

2、通過給函數(shù)傳參數(shù)調(diào)用函數(shù)

--函數(shù)：有入?yún)?create or replace function find_flow_name(node_temid in varchar2)
return VARCHAR2
is
v_flow_templet_id TD_M_NODE_TEMPLET.flow_templet_id% type;
begin
SELECT flow_templet_id into v_flow_templet_id FROM TD_M_NODE_TEMPLET a WHERE node_templet_id = node_temid;
dbms_output.put_line(v_flow_templet_id);
return v_flow_templet_id;
EXCEPTION
--WHEN NO_DATA_FOUND THEN
--dbms_output.put_line('未找到數(shù)據(jù)');
WHEN OTHERS THEN
dbms_output.put_line( '@SQLCODE IS '||SQLCODE ||' AND @SQLERRM is '||SQLERRM);
return '-1' ;
end find_flow_name;

--調(diào)用函數(shù)
set serveroutput on;
declare
v_node_name varchar2( 20):= 'ToOrder_PreOrderFZX1' ;
v_flow_name VARCHAR2( 20);
begin
--v_node_name := 'ToOrder_PreOrderFZX1';
v_flow_name := find_flow_name(v_node_name);
dbms_output.put_line( '流程名：'|| v_flow_name);
exception
when others THEN
dbms_output.put_line( SQLCODE||' AND ' ||SQLERRM);
end;

四、SQLCODE和SQLERRM使用

set SERVEROUTPUT on;
DECLARE
v_error VARCHAR2( 500);
BEGIN
v_error:=SQLERRM;
dbms_output.put_line( '@SQLCODE IS '||SQLCODE ||' AND @SQLERRM is '||SQLERRM);
END;