oracle存储过程、匿名块、函数、包

使用过程与函数的原则：

1、如果需要返回多个值和不返回值，就使用过程；如果只需要返回一个值，就使用函数。

2、过程一般用于执行一个指定的动作，函数一般用于计算和返回一个值。

3、可以SQL语句内部（如表达式）调用函数来完成复杂的计算问题，但不能调用过程。所以这是函数的特色。

一、存储过程

1、存储过程初步

--存储过程:实现搬历史表
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、存储过程，加自定义exception，并改进，由外部传参数

--存储过程
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;

--存储过程调用
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 存储过程名;

--存储过程赋权限
grant create any table to username;
grant create any procedure to username;
grant execute any procedure to username;

二、匿名块

--匿名块：在控制台实现简单输出（输入暂时没实现）
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 := &请输入节点名;   -- 这块还没有实现，总是报没有声明的错
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('未找到数据');
WHEN OTHERS THEN
dbms_output.put_line( '@SQLCODE IS '||SQLCODE ||' AND @SQLERRM is '||SQLERRM);
end;

三、函数

1、函数简单示例

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、通过给函数传参数调用函数

--函数：有入参
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('未找到数据');
WHEN OTHERS THEN
dbms_output.put_line( '@SQLCODE IS '||SQLCODE ||' AND @SQLERRM is '||SQLERRM);
return '-1' ;
end find_flow_name;

--调用函数
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;