先创建一个测试的数据表
-- 测试表
- 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');
运行结果:
好了, 就这样了.
来源: http://www.bubuko.com/infodetail-2881364.html