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

使用过程与函数的原则：

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

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

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

一、存储过程

1、存储过程初步

--存储过程:实现搬历史表create or replace procedure movetohistory_1 (                                                                                     o_count out number ,                                           error out VARCHAR2) ISV_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 replaceprocedure movetohistory ( o_time in date,                          o_count out number ) ISV_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;declarev_date date  := to_date( '2015/3/24 19:19:21','yyyy-mm-dd hh34:mi:ss' );o_count number;begino_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;declarev_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 THENdbms_output.put_line( '@SQLCODE IS '||SQLCODE ||' AND @SQLERRM is '||SQLERRM);end;

三、函数

1、函数简单示例

create or replace function tomorrowreturn date --必须有返回is  today date; --返回值在声明部分  nextdate date;begin  today := sysdate;  nextdate := today + 1;return nextdate; --returnexceptionwhen others then  return '-1';  --异常部分有returnend;

2、通过给函数传参数调用函数

--函数：有入参create or replace function find_flow_name(node_temid in varchar2)return VARCHAR2isv_flow_templet_id TD_M_NODE_TEMPLET.flow_templet_id% type;beginSELECT 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 THENdbms_output.put_line( '@SQLCODE IS '||SQLCODE ||' AND @SQLERRM is '||SQLERRM);return '-1' ;end find_flow_name;

--调用函数set serveroutput on;declarev_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);exceptionwhen others THENdbms_output.put_line( SQLCODE||' AND ' ||SQLERRM);end;

四、SQLCODE和SQLERRM使用

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