oracle存储过程、匿名块、函数、包
使用过程与函数的原则:
创新互联公司-专业网站定制、快速模板网站建设、高性价比桐梓网站开发、企业建站全套包干低至880元,成熟完善的模板库,直接使用。一站式桐梓网站制作公司更省心,省钱,快速模板网站建设找我们,业务覆盖桐梓地区。费用合理售后完善,十年实体公司更值得信赖。
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;
文章题目:oracle存储过程、匿名块、函数、包
文章源于:http://pwwzsj.com/article/pjccpp.html