ORA-00913错误

描述：PL/SQL: ORA-00913: too many values

目标：编写一个可以循环插入数据的脚本

操作过程：

SQL> desc tcustmer

Name               Null?    Type

----------------- -------- ----------------------------

CUST_CODE         NOT NULL VARCHAR2(10)

NAME                       VARCHAR2(30)

CITY                       VARCHAR2(20)

STATE                      CHAR(2)

SQL>CREATE SEQUENCE tcustmer_cust

INCREMENT BY 1

START WITH 1

MAXVALUE 100000000

CACHE 10000

NOCYCLE;

SQL> begin

2       for i in 1..10 loop

3         insert into tcustmer

4         values (tcustmer_cust.nextval,‘T‘,‘test‘||i,‘BEIJING‘,‘CN‘);

5         if mod(i,10)=0 then

6         commit;

7         end if;

8      end loop;

9      commit;

10  end;

11  /

insert into tcustmer

*

ERROR at line 3:

ORA-06550: line 3, column 20:

PL/SQL: ORA-00913: too many values

ORA-06550: line 3, column 8:

PL/SQL: SQL Statement ignored

检查发现插入的values值，列数超过了tcustmer表的列数

调整如下：

SQL>begin

for i in 1..10 loop

insert into tcustmer

values (‘T‘||tcustmer_cust.nextval,‘test‘||i,‘BEIJING‘,‘CN‘);

if mod(i,10)=0 then

commit;

end if;

end loop;

commit;

end;

/

PL/SQL procedure successfully completed.

总结：

对于tcustmer_cust.nextval理解错误，创建序列的目的正是消除主键的干扰，所以在使用的时候需要将其放到列值中。

原文：http://blog.csdn.net/huangyanlong/article/details/32732773