使用過程與函數(shù)的原則：
創(chuàng)新互聯(lián)公司-專業(yè)網(wǎng)站定制、快速模板網(wǎng)站建設(shè)、高性價比桐梓網(wǎng)站開發(fā)、企業(yè)建站全套包干低至880元,成熟完善的模板庫,直接使用。一站式桐梓網(wǎng)站制作公司更省心,省錢,快速模板網(wǎng)站建設(shè)找我們，業(yè)務(wù)覆蓋桐梓地區(qū)。費用合理售后完善，十年實體公司更值得信賴。
1、如果需要返回多個值和不返回值，就使用過程；如果只需要返回一個值，就使用函數(shù)。
2、過程一般用于執(zhí)行一個指定的動作，函數(shù)一般用于計算和返回一個值。
3、可以SQL語句內(nèi)部（如表達式）調(diào)用函數(shù)來完成復(fù)雜的計算問題，但不能調(diào)用過程。所以這是函數(shù)的特色。
一、存儲過程
1、存儲過程初步
--存儲過程:實現(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、存儲過程，加自定義exception，并改進，由外部傳參數(shù)
--存儲過程 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;
--存儲過程調(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 存儲過程名;
--存儲過程賦權(quán)限 grant create any table to username; grant create any procedure to username; grant execute any procedure to username;
二、匿名塊
重慶分公司
重慶分公司
--匿名塊：在控制臺實現(xiàn)簡單輸出（輸入暫時沒實現(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 := &請輸入節(jié)點名; -- 這塊還沒有實現(xiàn)，總是報沒有聲明的錯
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ù)簡單示例
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;
