ORA-00913错误:PL/SQL: ORA-00913: too many values
浏览数：27 /
时间：2015年06月12日
ORA-00913错误
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.
描述：PL/SQL: ORA-00913: too many values
目标：编写一个可以循环插入数据的脚本
操作过程：
SQL> desc tcustmerName 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理解错误，创建序列的目的正是消除主键的干扰，所以在使用的时候需要将其放到列值中。
ORA-00913错误:PL/SQL: ORA-00913: too many values,古老的榕树,5-wow.com
郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。
