你不需要FOR LOOP,只需要一个UPDATE即可完成工作:
UPDATE emp
SET comm = extra
WHERE comm IS NULL AND extra IS NOT NULL;
—编辑—-
我没有注意到,在预期的输出deptno 10更新为20,更新deptno需要另一个查询:
UPDATE emp
SET deptno = 20
WHERE deptno = 10;
—-编辑—–
如果要将更改的值插入另一个表,请尝试使用RETURNING..BULK COLLECT和FORALL的过程:
CREATE OR REPLACE PROCEDURE pro_cedure( p_dept_id number )
IS
TYPE changed_table_type IS TABLE OF changed%ROWTYPE;
changed_buff changed_table_type;
BEGIN
SELECT deptno, comm, extra BULK COLLECT INTO changed_buff
FROM emp
WHERE comm IS NULL AND extra IS NOT NULL AND deptno = p_dept_id
FOR UPDATE;
UPDATE emp
SET comm = extra
WHERE comm IS NULL AND extra IS NOT NULL AND deptno = p_dept_id;
FORALL i IN 1 .. changed_buff.count
INSERT INTO changed VALUES changed_buff( i );
END;
/
如果您不打算在一次调用中处理大量记录(超过1000 …或最多几千),则该过程应该有效.如果一个dept_id可以包含一万或更多行,那么这个过程可能会很慢,因为它会消耗大量的PGA内存.在这种情况下,需要另一种以块的形式进行批量收集的方法.
– 编辑—如何存储序列值——-
我假设更改的表有4列,如下所示:
CREATE TABLE "TEST"."CHANGED"
( "DEPTNO" NUMBER,
"OLDVAL" NUMBER,
"NEWVAL" NUMBER,
"SEQ_NEXTVAL" NUMBER
) ;
我们将序列值存储在seq_nextval列中.
在这种情况下,过程可能如下所示:
create or replace
PROCEDURE pro_cedure( p_dept_id number )
IS
TYPE changed_table_type IS TABLE OF changed%ROWTYPE;
changed_buff changed_table_type;
BEGIN
SELECT deptno, comm, extra, sequence_name.nextval
BULK COLLECT INTO changed_buff
FROM emp
WHERE comm IS NULL AND extra IS NOT NULL AND deptno = p_dept_id
FOR UPDATE;
UPDATE emp
SET comm = extra
WHERE comm IS NULL AND extra IS NOT NULL AND deptno = p_dept_id;
FORALL i IN 1 .. changed_buff.count
INSERT INTO changed VALUES changed_buff( i );
END;
—编辑—带光标的版本用于小数据集—–
是的,对于小型数据集批量收集不会显着提高速度,并且在这种情况下使用for..loop的普通光标就足够了.
下面是如何将游标与更新一起使用的示例,注意FOR UPDATE子句,当我们计划使用WHERE CURRENT OF子句更新从游标获取的记录时,它是必需的.
这次在INSERT语句中计算序列值.
create or replace
PROCEDURE pro_cedure( p_dept_id number )
IS
CURSOR mycursor IS
SELECT deptno, comm, extra
FROM emp
WHERE comm IS NULL AND extra IS NOT NULL
AND deptno = p_dept_id
FOR UPDATE;
BEGIN
FOR emp_rec IN mycursor
LOOP
UPDATE emp
SET comm = extra
WHERE CURRENT OF mycursor;
INSERT INTO changed( deptno, oldval, newval, seq_nextval)
VALUES( emp_rec.deptno, emp_rec.comm,
emp_rec.extra, sequence_name.nextval );
END LOOP;
END;