oracle too many values,ORA-00913错误:PL/SQL: ORA-00913: too many values

ORA-00913错误:PL/SQL: ORA-00913: too many values

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理解错误，创建序列的目的正是消除主键的干扰，所以在使用的时候需要将其放到列值中。

Linux-6-64下安装Oracle 12C笔记

在CentOS 6.4下安装Oracle 11gR2(x64)

Oracle 11gR2 在VMWare虚拟机中安装步骤

Debian 下 安装 Oracle 11g XE R2

本文永久更新链接地址：

本文原创发布php中文网，转载请注明出处，感谢您的尊重！