oracle – 在plsql中使用for循环更新

我有更新问题并插入下面的列.请告知此事.

这是输入

depnto   extra    comm
----------------------------
20       300      NULL
20       300      400
20       NULL     NULL
20       500      NULL

这是预期的产出

depnto  Extra    comm
---------------------
20      300      300
20      300      400
20      NULL     NULL           
20      500      500

我需要在以下条件下使用额外列更新comm列.

>如果comm为null,则将额外值更新为comm.
>如果comm不为null,则无需更新,
>如果两者都为null,则保留为null,
>如果comm列有一个值,则无需覆盖.

我的节目如下.即使我需要跟踪哪些行已更新以及哪个值在另一个表中.

PROCEDURE (dept_id )
AS
BEGIN
   FOR r IN (SELECT *
               FROM emp
              WHERE comm IS NULL AND extra IS NOT NULL AND deptno = dept_id)
   LOOP
      UPDATE emp
         SET comm = extra
       WHERE comm IS NULL AND extra IS NOT NULL AND deptno = dept_id;



      INSERT INTO changed_comm (deptno,oldval,newval)
           VALUES (dept_id,r.comm,r.extra);
   END LOOP;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      NULL;
END;

请在上面提供一些意见.它没有正确插入.

解决方法

你不需要FOR LOOP,只需要一个UPDATE即可完成工作：

UPDATE emp
  SET comm = extra
WHERE comm IS NULL AND extra IS NOT NULL;

这是一个演示：http://www.sqlfiddle.com/#!4/aacc3/1

—编辑—-
我没有注意到,在预期的输出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,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,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,newval,seq_nextval)
                VALUES( emp_rec.deptno,emp_rec.comm,emp_rec.extra,sequence_name.nextval );
      END LOOP;
END;

oracle – 在plsql中使用for循环更新

解决方法

猜你在找的Oracle相关文章