Java 匿名存储过程_oracle存储过程、匿名块、函数、包

最新推荐文章于 2023-09-14 01:23:45 发布

美滋滋呀

最新推荐文章于 2023-09-14 01:23:45 发布

阅读量444

点赞数

文章标签： Java 匿名存储过程

本文链接：https://blog.csdn.net/weixin_31243809/article/details/114228660

版权

使用过程与函数的原则：

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

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 --必须有返回

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

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;

美滋滋呀

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Java 匿名存储过程_oracle存储过程、匿名块、函数、包

使用过程与函数的原则：1、如果需要返回多个值和不返回值，就使用过程；如果只需要返回一个值，就使用函数。2、过程一般用于执行一个指定的动作，函数一般用于计算和返回一个值。3、可以SQL语句内部(如表达式)调用函数来完成复杂的计算问题，但不能调用过程。所以这是函数的特色。一、存储过程1、存储过程初步--存储过程:实现搬历史表createorreplaceproceduremovetohisto...
复制链接

扫一扫

Java 匿名存储过程_oracle存储过程、匿名块、函数、包

“相关推荐”对你有帮助么？