如何在PL SQL中使用游标for循环将行从一个表插入到另一个表?

Question

我不知道这有多少是正确的

DECLARE    
CURSOR cur_depts IS
SELECT *
FROM dept;
BEGIN
FOR i IN cur_depts
LOOP
INSERT INTO dept_backup
VALUES(i);
END LOOP;
CLOSE  cur_depts;
END;

这是我得到的错误

Error report -
ORA-06550: line 8, column 20:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 8, column 8:
PL/SQL: SQL Statement ignored
06550. 00000 -  "line %s, column %s:n%s"
*Cause:    Usually a PL/SQL compilation error.
*Action:

我在这里使用Scott schema中的表。

Answer 1

您不需要游标(也不应该使用游标，因为循环中的单个插入会更慢，并产生更多的日志记录)。您可以简单地使用一条INSERT ... SELECT ...语句:

INSERT INTO dept_backup
SELECT * FROM dept;

Answer 2

正如你被告知的那样，你并不真的需要PL/SQL，游标和循环，但是-如果你正在学习它们，那么这里是如何。

SQL> create table dept_backup as select * From dept where 1 = 2;
Table created.
SQL> declare
2    cursor cur_depts is
3      select deptno, dname, loc
4      from dept;
5  begin
6    for i in cur_depts loop
7      insert into dept_backup (deptno, dname, loc)
8        values (i.deptno, i.dname, i.loc);
9    end loop;
10  end;
11  /
PL/SQL procedure successfully completed.
SQL> select * from dept_backup;
DEPTNO DNAME          LOC
---------- -------------- -------------
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON
SQL>

---

更短的选项是不显式声明游标-使用游标FOR循环:

SQL> truncate table dept_backup;
Table truncated.
SQL> begin
2    for i in (select deptno, dname, loc from dept) loop
3      insert into dept_backup (deptno, dname, loc)
4        values (i.deptno, i.dname, i.loc);
5    end loop;
6  end;
7  /
PL/SQL procedure successfully completed.
SQL> select * from dept_backup;
DEPTNO DNAME          LOC
---------- -------------- -------------
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON
SQL>

---

请注意，如果使用这种语法，则不必关闭游标;如果使用以下语法，必须声明一个游标变量，打开游标，从中获取，退出循环并关闭游标:

SQL> truncate table dept_backup;
Table truncated.
SQL> declare
2    cursor cur_depts is select deptno, dname, loc from dept;
3    cd_r   cur_depts%rowtype;
4  begin
5    open cur_depts;
6    loop
7      fetch cur_depts into cd_r;
8      exit when cur_depts%notfound;
9      insert into dept_backup (deptno, dname, loc)
10        values (cd_r.deptno, cd_r. dname, cd_r.loc);
11    end loop;
12    close cur_depts;
13  end;
14  /
PL/SQL procedure successfully completed.
SQL> select * from dept_backup;
DEPTNO DNAME          LOC
---------- -------------- -------------
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON
SQL>

显然，游标FOR循环更简单。

Answer 3

i是游标记录，不能直接插入到另一个表中，需要引用特定的列。例如,

INSERT INTO dept_backup (b_col1, b_col2, b_col3) values(i.c_col1,i.c_col2,i.c_col3);

Answer 4

两个问题(假设dept和dept_backup有匹配的列列表):

1. 使用这种语法时，record变量周围不应该有括号。
2. 不能在for循环完成并且已经隐式关闭了for循环之后显式关闭for循环游标。

试试这个:

declare
cursor cur_depts is
select * from dept;
begin
for r in cur_depts loop
insert into dept_backup values r;
end loop;
end;

(我更喜欢使用r记录循环变量，以避免与i数字循环变量混淆。)