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

使用过程与函数的原则：

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

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

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

一、存储过程

1、存储过程初步

--存储过程:实现搬历史表
createorreplaceproceduremovetohistory_1(

o_countoutnumber,erroroutVARCHAR2)IS
V_COU-NTnumber;
V_SUBSCRIBE_IDTI_C_IOM_BUSIFORM.SUBSCRIBE_ID%TYPE;
v_timedate:=to_date('2015/3/2016:24:23','yyyy-mm-ddhh24:mi:ss');
CURSORC_MOVE2HISIS
SELECTSUBSCRIBE_IDFROMTI_C_IOM_BUSIFORMwhereaccept_date<=v_time;

BEGIN
o_count:=0;
OPENC_MOVE2HIS;
LOOP
FETCHC_MOVE2HISINTOV_SUBSCRIBE_ID;
EXITWHENC_MOVE2HIS%NOTFOUND;
BEGIN
SELECTCOUNT(*)INTOV_COUNTfromTI_C_IOM_BUSIFORMwhereaccept_date<=v_time;
IF(V_COUNT>0)THEN
INSERTINTOTI_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)
(selectBUSIFORM_ID,REMARK
fromTI_C_IOM_BUSIFORM
whereSUBSCRIBE_ID=V_SUBSCRIBE_ID
andaccept_date<=v_time);
deleteFROMTI_C_IOM_BUSIFORM
whereSUBSCRIBE_ID=V_SUBSCRIBE_ID
andaccept_date<=v_time;
o_count:=o_count+1;
ENDIF;
commit;
EXCEPTION
WHENOTHERSTHEN
rollback;
deletefromTI_CH_IOM_BUSIFORM
whereSUBSCRIBE_ID=V_SUBSCRIBE_ID;
commit;
END;
ENDLOOP;
closeC_MOVE2HIS;
endmovetohistory_1;

2、存储过程，加自定义exception，并改进，由外部传参数

--存储过程
createorreplace
proceduremovetohistory(o_timeindate,o_countoutnumber)IS
V_COUNTnumber;
V_SUBSCRIBE_IDTI_C_IOM_BUSIFORM.SUBSCRIBE_ID%TYPE;
v_timedate:=o_time;
v_errorexception;--自定义异常
CURSORC_MOVE2HISIS
SELECTSUBSCRIBE_IDFROMTI_C_IOM_BUSIFORMwhereaccept_date<=v_time;
BEGIN
o_count:=0;
SELECTCOUNT(*)INTOV_COUNTfromTI_C_IOM_BUSIFORMwhereaccept_date<=v_time;
if(V_COUNT<=0)then
raisev_error;
endif;
OPENC_MOVE2HIS;
LOOP
FETCHC_MOVE2HISINTOV_SUBSCRIBE_ID;
EXITWHENC_MOVE2HIS%NOTFOUND;
BEGIN
SELECTCOUNT(*)INTOV_COUNTfromTI_C_IOM_BUSIFORMwhereaccept_date<=v_time;
IF(V_COUNT>0)THEN
INSERTINTOTI_CH_IOM_BUSIFORM
(BUSIFORM_ID,REMARK
fromTI_C_IOM_BUSIFORM
whereSUBSCRIBE_ID=V_SUBSCRIBE_ID
andaccept_date<=v_time);
deleteFROMTI_C_IOM_BUSIFORM
whereSUBSCRIBE_ID=V_SUBSCRIBE_ID
andaccept_date<=v_time;
o_count:=o_count+1;
endif;
commit;
EXCEPTION
WHENOTHERSTHEN
rollback;
deletefromTI_CH_IOM_BUSIFORM
whereSUBSCRIBE_ID=V_SUBSCRIBE_ID;
commit;
END;
ENDLOOP;
closeC_MOVE2HIS;
exception
whenv_errorthen
RAISE_APPLICATION_ERROR(-20010,'dataisnotexists!');
endmovetohistory;

--存储过程调用
setserveroutputon;
declare
v_datedate:=to_date('2015/3/2419:19:21','yyyy-mm-ddhh24:mi:ss');
o_countnumber;
begin
o_count:=0;
movetohistory(v_date,o_count);
dbms_output.put_line('o_count:'||o_count);
end;

exec 存储过程名;

--存储过程赋权限
grantcreateanytabletousername;
grantcreateanyproceduretousername;
grantexecuteanyproceduretousername;

二、匿名块

--匿名块：在控制台实现简单输出（输入暂时没实现）
SETSERVEROUTPUTON;
declare
v_flow_templet_idTD_M_NODE_TEMPLET.flow_templet_id%type;
v_node_templet_idTD_M_NODE_TEMPLET.node_templet_id%type;
begin
--v_node_templet_id:=&请输入节点名;--这块还没有实现，总是报没有声明的错
v_node_templet_id:='BIZOPPORDER';
SELECTflow_templet_idintov_flow_templet_idFROMTD_M_NODE_TEMPLETaWHEREnode_templet_id=v_node_templet_id;
dbms_output.put_line(v_flow_templet_id);
EXCEPTION
--WHENNO_DATA_FOUNDTHEN
--dbms_output.put_line('未找到数据');
WHENOTHERSTHEN
dbms_output.put_line('@SQLCODEIS'||SQLCODE||'AND@SQLERRMis'||SQLERRM);
end;

三、函数

1、函数简单示例

createorreplacefunctiontomorrow
returndate--必须有返回
is
todaydate;--返回值在声明部分
nextdatedate;
begin
today:=sysdate;
nextdate:=today+1;
returnnextdate;--return
exception
whenothersthen
return'-1';--异常部分有return
end;

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

--函数：有入参
createorreplacefunctionfind_flow_name(node_temidinvarchar2)
returnVARCHAR2
is
v_flow_templet_idTD_M_NODE_TEMPLET.flow_templet_id%type;
begin
SELECTflow_templet_idintov_flow_templet_idFROMTD_M_NODE_TEMPLETaWHEREnode_templet_id=node_temid;
dbms_output.put_line(v_flow_templet_id);
returnv_flow_templet_id;
EXCEPTION
--WHENNO_DATA_FOUNDTHEN
--dbms_output.put_line('未找到数据');
WHENOTHERSTHEN
dbms_output.put_line('@SQLCODEIS'||SQLCODE||'AND@SQLERRMis'||SQLERRM);
return'-1';
endfind_flow_name;

--调用函数
setserveroutputon;
declare
v_node_namevarchar2(20):='ToOrder_PreOrderFZX1';
v_flow_nameVARCHAR2(20);
begin
--v_node_name:='ToOrder_PreOrderFZX1';
v_flow_name:=find_flow_name(v_node_name);
dbms_output.put_line('流程名：'||v_flow_name);
exception
whenothersTHEN
dbms_output.put_line(SQLCODE||'AND'||SQLERRM);
end;

四、SQLCODE和SQLERRM使用

setSERVEROUTPUTon;
DECLARE
v_errorVARCHAR2(500);
BEGIN
v_error:=SQLERRM;
dbms_output.put_line('@SQLCODEIS'||SQLCODE||'AND@SQLERRMis'||SQLERRM);
END;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!