oracle 在 plsql 中使用 for 循环进行更新
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18670392/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
update using for loop in plsql
提问by user2686661
i'm having problem updating and insert into below column. Please advise on this.
我在更新和插入下面的列时遇到问题。请就此提出建议。
This is the input
这是输入
depnto extra comm
----------------------------
20 300 NULL
20 300 400
20 NULL NULL
20 500 NULL
This is the expected output
这是预期的输出
depnto Extra comm
---------------------
20 300 300
20 300 400
20 NULL NULL
20 500 500
I need to update commcolumn with extracolumn on below conditions.
我需要在以下条件下comm用extra列更新列。
- If comm Is null then extra value is updated to comm.
- If comm Is not null, no need to update,
- If both are null, leave as null,
- if comm column has a value no need to overwrite.
- 如果 comm 为空，则额外的值将更新为 comm。
- 如果 comm 不为空，则无需更新，
- 如果两者都为空，则保留为空，
- 如果 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.
请提供一些上述意见。它没有正确插入。
回答by krokodilko
You do not need FOR LOOP, just a single UPDATE does the work:
您不需要FOR LOOP，只需一个 UPDATE 即可完成工作：
UPDATE emp
SET comm = extra
WHERE comm IS NULL AND extra IS NOT NULL;
Here is a demo: http://www.sqlfiddle.com/#!4/aacc3/1
这是一个演示：http://www.sqlfiddle.com/#!4/ aacc3/1
--- EDIT ----
I didn't notice, that in the expected output deptno 10 was updated to 20,
to update deptnoan another query is needed:
--- 编辑 ----
我没有注意到，在预期的输出中，deptno 10 已更新为 20，
需要更新deptno另一个查询：
UPDATE emp
SET deptno = 20
WHERE deptno = 10;
---- EDIT -----
If you want to insert changed values to the other table, try a procedure with RETURNING..BULK COLLECT and FORALL:
---- 编辑 -----
如果要将更改的值插入另一个表，请尝试使用 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;
/
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_idcan 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 changedhas 4 columns, like this:
如果您不打算在一次调用中处理大量记录（超过 1000 ... 或最多几千条），该过程应该可以工作。如果一个dept_id可以包含一万行甚至更多行，那么这个过程可能会很慢，因为它会消耗大量的 PGA 内存。在这种情况下，需要另一种以块为单位进行批量收集的方法。
-- 编辑 --- 如何存储序列值 -------
我假设该表changed有 4 列，如下所示：
CREATE TABLE "TEST"."CHANGED"
( "DEPTNO" NUMBER,
"OLDVAL" NUMBER,
"NEWVAL" NUMBER,
"SEQ_NEXTVAL" NUMBER
) ;
and we will store sequence values in the seq_nextvalcolumn.
In such a case the procedure might look like this:
我们将在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;
--- 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 UPDATEclause, it is required when we plan to update a record fetched from the cursor using WHERE CURRENT OFclause.
This time a sequence value is evaluated within the INSERT statement.
--- 编辑 --- 带有用于小数据集的游标的版本 -----
是的，对于小数据集，批量收集不会显着提高速度，并且带有 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;
回答by Shashank Shekhar Singh
BEGIN
FOR person IN (SELECT A FROM EMP WHERE B IN (SELECT B FROM ustom.cfd_180518) )
LOOP
--dbms_output.put_line(person.A);
UPDATE custom.cfd_180518 SET c = person.a;
END LOOP;
END;
