Oracle数据库存储过程练习20181212
先创建一个测试的数据表
--测试表 CREATE TABLE TEST20181207 ( ID INTEGER PRIMARY KEY, FUND NUMBER,--上日资金 BALANCE NUMBER,--本日资金 CDATE VARCHAR2(10) );
添加测试数据:
--添加测试数据 INSERT INTO TEST20181207 VALUES(1,100,200,'2018-10-31'); INSERT INTO TEST20181207 VALUES(2,100,200,'2018-11-01'); INSERT INTO TEST20181207 VALUES(3,200,0,'2018-11-03'); INSERT INTO TEST20181207 VALUES(4,0,100,'2018-11-10'); INSERT INTO TEST20181207 VALUES(5,100,0,'2018-11-20'); INSERT INTO TEST20181207 VALUES(6,10,100,'2018-11-10'); INSERT INTO TEST20181207 VALUES(7,100,0,'2018-11-20'); COMMIT;
创建存储过程:
CREATE OR REPLACE PROCEDURE TESTSELECT20181207 (I_START_DATE VARCHAR2, I_END_DATE VARCHAR2) IS T_ID1 INTEGER; T_FUND1 NUMBER; T_BALANCE1 NUMBER; T_CDATE1 VARCHAR2(10); T_ID2 INTEGER; T_FUND2 NUMBER; T_BALANCE2 NUMBER; T_CDATE2 VARCHAR2(10); CURSOR CURSOR1 IS SELECT ID,FUND,BALANCE,CDATE FROM TEST20181207 WHERE CDATE BETWEEN I_START_DATE AND I_END_DATE; CURSOR CURSOR2(T_CDATE VARCHAR2) IS SELECT ID,FUND,BALANCE,CDATE FROM TEST20181207 WHERE CDATE = ( SELECT MIN(CDATE) FROM TEST20181207 WHERE CDATE > T_CDATE ) AND CDATE BETWEEN I_START_DATE AND I_END_DATE; BEGIN OPEN CURSOR1; LOOP FETCH CURSOR1 INTO T_ID1,T_FUND1,T_BALANCE1,T_CDATE1; EXIT WHEN CURSOR1%NOTFOUND; OPEN CURSOR2(T_CDATE1); LOOP FETCH CURSOR2 INTO T_ID2,T_FUND2,T_BALANCE2,T_CDATE2; EXIT WHEN CURSOR2%NOTFOUND; IF T_FUND2 <> T_BALANCE1 THEN DBMS_OUTPUT.PUT_LINE('编号1:'||T_ID1||',本日资金1:'||T_BALANCE1||',日期1:'||T_CDATE1); DBMS_OUTPUT.PUT_LINE('编号2:'||T_ID2||',上日资金2:'||T_FUND2||',日期2:'||T_CDATE2); END IF; END LOOP; CLOSE CURSOR2; END LOOP; CLOSE CURSOR1; END TESTSELECT20181207; /
最后一个/在同时执行创建多个存储过程是必须的,/代表一个存储过程代码的结尾(结束).
调用存储过程:
CALL TESTSELECT20181207('2018-10-31','2018-11-30');
运行结果:
好了,就这样了.
2015年10月-2023年6月 总计7年.
所有总计:7年.
我是唯物唯心二元论的,暂时主学法语.
学习打卡交流QQ群:946556683
所有总计:7年.
我是唯物唯心二元论的,暂时主学法语.
学习打卡交流QQ群:946556683