在plsql中使用for循环更新 [英] update using for loop in plsql

问题描述

我在更新时遇到问题，请插入下面的列中.请为此提供建议.

i'm having problem updating and insert into below column. Please advise on this.

这是输入

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

在以下情况下，我需要用extra列更新comm列.

I need to update comm column with extra column on below conditions.

• 如果comm为null，则多余的值将更新为comm.
• 如果comm不为null，则无需更新
• 如果两者都为空，则保留为空，
• 如果comm列中的值无需覆盖.

我的程序在下面.甚至我都需要跟踪哪些行被更新以及另一个表中的值.

My program is below. Even I need to keep track which are rows are updated and to which value in another table.

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;

请在上面提供一些意见.无法正确插入.

please provide some opinion on above. Its not inserting correctly.

推荐答案

您不需要FOR LOOP，只需执行一个UPDATE就可以了:

You do not need FOR LOOP, just a single UPDATE does the work:

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，则需要另一个查询:

--- EDIT ----

I didn't notice, that in the expected output deptno 10 was updated to 20,
to update deptno an another query is needed:

UPDATE emp
   SET deptno = 20
WHERE deptno = 10;

----编辑-----

如果要将更改的值插入到另一个表中，请尝试使用RETURNING..BULK COLLECT和FORALL进行以下过程:

---- EDIT -----

If you want to insert changed values to the other table, try a procedure with RETURNING..BULK COLLECT and 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内存.在这种情况下，需要另一种以块为单位进行批量收集的方法.

-编辑---如何存储序列值-------

我假设表changed具有4列，如下所示:

The procedure should work if you are not going to process huge number of records in a one call (more than 1000 ... or maximum a few thousands). If one dept_id can contain ten thousands and more rows, then this procedure might be slow, becasue it will consume a huge amount of PGA memory. In such a case, an another approach with bulk collectiong in chunks is required.

-- EDIT --- how to store sequence values -------

I assume that the table changed has 4 columns, like this:

  CREATE TABLE "TEST"."CHANGED" 
   (    "DEPTNO" NUMBER, 
        "OLDVAL" NUMBER, 
        "NEWVAL" NUMBER, 
        "SEQ_NEXTVAL" NUMBER 
   ) ;

，我们将序列值存储在seq_nextval列中.

在这种情况下，过程可能如下所示:

and we will store sequence values in the seq_nextval column.

In such a case the procedure might look like this:

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的普通游标就足够了.
以下是tu如何将游标与更新一起使用的示例，请注意FOR UPDATE子句，当我们计划使用WHERE CURRENT OF子句更新从游标中获取的记录时，这是必需的. 这次在INSERT语句中评估了序列值.

--- EDIT --- version with cursor for small sets of data -----

Yes, for small sets of data bulk collecting doesn't give significant increase of the speed, and plain cursor with for..loop is sufficient in such a case.
Below is an example how tu use the cursor together with update, notice the FOR UPDATE clause, it is required when we plan to update a record fetched from the cursor using WHERE CURRENT OF clause.
This time a sequence value is evaluated within the INSERT statement.

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;

这篇关于在plsql中使用for循环更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！