存储过程
使用过程与函数的原则:
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 hh24: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 hh24: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;
oracle存储过程、匿名块、函数、包
存储过程
原文:http://blog.51cto.com/1385903/2045575
来源: http://www.bubuko.com/infodetail-2410985.html