想计算month_value 的 val3 ,需要用当天的val2与前一天的val1:
the_date val1 val2 val3
2010-5-1 100 118 null
2010-5-2 108 124 124/100
2010-5-3 138 109 109/108
CURSOR c_month_value IS
SELECT the_date,
val1,
val2,
val3
from month_value order by the_date asc;
v_detail_yesterday c_month_value%rowtype;
v_detail_today c_month_value%rowtype;
……
OPEN c_month_value;
LOOP
FETCH c_month_value
INTO v_detail_today;
EXIT WHEN c_month_value%NOTFOUND;
--计算过程需要用到前一天的数据,所以我想保存上一个游标记录
v_detail_yesterday := v_detail_today;
END LOOP;
the_date val1 val2 val3
2010-5-1 100 118 null
2010-5-2 108 124 124/100
2010-5-3 138 109 109/108
CURSOR c_month_value IS
SELECT the_date,
val1,
val2,
val3
from month_value order by the_date asc;
v_detail_yesterday c_month_value%rowtype;
v_detail_today c_month_value%rowtype;
……
OPEN c_month_value;
LOOP
FETCH c_month_value
INTO v_detail_today;
EXIT WHEN c_month_value%NOTFOUND;
--计算过程需要用到前一天的数据,所以我想保存上一个游标记录
v_detail_yesterday := v_detail_today;
END LOOP;
update month_values a set a.val3=(select deocde(b.val1,null,null,a.val2||'/'||b.val1 from month_values b
where b.the_date=a.the_date-1)
Table dropped
SQL>
SQL> CREATE TABLE testtest
2 ( the_date DATE,
3 val1 VARCHAR2(10),
4 val2 VARCHAR2(10),
5 val3 VARCHAR2(21)
6 );
Table created
SQL>
SQL> INSERT INTO testtest(The_Date,val1,val2)
2 VALUES(to_date('2010-5-1','yyyy-mm-dd'),100,118);
1 row inserted
SQL> INSERT INTO testtest(The_Date,val1,val2)
2 VALUES(to_date('2010-5-2','yyyy-mm-dd'),108,124);
1 row inserted
SQL> INSERT INTO testtest(The_Date,val1,val2)
2 VALUES(to_date('2010-5-3','yyyy-mm-dd'),109,118);
1 row inserted
SQL> commit;
Commit complete
SQL> select * from testtest;
THE_DATE VAL1 VAL2 VAL3
----------- ---------- ---------- ---------------------
2010-5-1 100 118
2010-5-2 108 124
2010-5-3 109 118
SQL>
SQL> update testtest a set a.val3=(select decode(b.val1,null,null,a.val2||'/'||b.val1) from testtest b
2 where b.the_date=a.the_date-1);
3 rows updated
SQL> select * from testtest;
THE_DATE VAL1 VAL2 VAL3
----------- ---------- ---------- ---------------------
2010-5-1 100 118
2010-5-2 108 124 124/100
2010-5-3 109 118 118/108
SQL>
就是想取cursor的记录时,保存一下,留到下一条记录计算使用,
但是这个方法好像行不通: --计算过程需要用到前一天的数据,所以我想保存上一个游标记录
v_detail_yesterday := v_detail_today;--保存失败!!!!!
4 rows updated
SQL> select * from testtest;
THE_DATE VAL1 VAL2 VAL3
----------- ---------- ---------- ---------------------
2010-5-1 100 118 118/100
2010-5-2 108 124 124/100
2010-5-3 109 118 118/108
2010-3-1 100 118
SQL>
第一个:如果前一天不存在则val3为null
第二个:取与本时间最接近的一天的数据来合成val3
但是我还是想用cursor 实现:不知是否可行?
--计算过程需要用到前一天的数据,所以我想保存上一个游标记录
v_detail_yesterday := v_detail_today;--保存失败!!!!!
下面是以前回别人的一个过程,你也可以参考下
--建测试表
CREATE TABLE test
(a NUMBER(1));
INSERT INTO test VALUES(1);
CREATE TABLE test1
(a NUMBER(1));
INSERT INTO test VALUES(1);
--初始化表内数据
DELETE FROM test1;
INSERT INTO test
SELECT ROWNUM FROM dual CONNECT BY ROWNUM<800;
--存储过程
CREATE OR REPLACE PROCEDURE test_cursor
AS
v_count NUMBER(9);
zzz ROWID;
v_a NUMBER(9);
zzz_old ROWID;
zzz_start ROWID;
zzz_end ROWID;
CURSOR cv_test IS
SELECT a,ROWID FROM test ORDER BY ROWID;
BEGIN
v_count:=0 ;--计数器初始化
zzz_old:='0';--防止第一次比较时zzz_old为空
OPEN cv_test;
LOOP
FETCH cv_test INTO v_a,zzz;
IF zzz_old<>zzz THEN
v_count:=v_count+1;
zzz_end:=zzz;
ELSE--已是表最后一条
INSERT INTO test1(a)
SELECT a FROM test WHERE ROWID BETWEEN zzz_start AND zzz_end;
END IF;
--取批次开始及结束rowid
IF v_count=1 THEN --500循环开始
zzz_start:=zzz;
zzz_end:=zzz;
ELSIF v_count=500 THEN--500循环结束
zzz_end:=zzz;
INSERT INTO test1(a)
SELECT a FROM test WHERE ROWID BETWEEN zzz_start AND zzz_end;
v_count:=0;--计数器归零
END IF;
--保存本次rowid,以便下次比较
zzz_old:=zzz;
EXIT WHEN cv_test%NOTFOUND;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END ;