ocrale一个insert插入多条数据的问题

原来的表结构是这样:
studentId studentName
2232 张三
2233 李四

我用这样的方法插入:
insert into table_student(studentId,studentName)
select '2234','aaa'from dual
union
select '2235','bbb' from dual
这样是可以插入的

但是我建表的时候忘了加主键,现在加入后
表结构变成:
id studentId studentName
1 2232 张三
2 2233 李四
我再用上面的方法插入:
insert into table_student(studentId,studentName)
select SEQ_SEQUENCE.nextval, '2234','aaa'from dual
union
select SEQ_SEQUENCE.nextval,'2235','bbb' from dual
就会报错:ORA-02287:sequence number not allowed here
请问该在这种情况下该如何实现一次插入多条数据?

0

4个回答

select deptsque.nextval,deptsque.nextval from dual;

所得查询结果为两列相同的数字,一条语句中,调用多次nextval的话,产生的值是一样的,所以插第二个主键就出错了。

0
wing3501
wing3501 看来也只能一条一条插入了....
6 年多之前 回复

[quote]insert into table_student(studentId,studentName)
select SEQ_SEQUENCE.nextval, '2234','aaa'from dual [/quote]
字段数不对???
insert into table_student(id,studentId,studentName)

0
wing3501
wing3501 不是字段的问题,字段是我不小心忘加了,不好意思
6 年多之前 回复

分多条SQL执行
insert into table_student(studentId,studentName)
values (SEQ_SEQUENCE.nextval, '2234','aaa');
insert into table_student(studentId,studentName)
values (SEQ_SEQUENCE.nextval ,'2235','bbb');
如果插入的数据多就用PreparedStatement的批量插入

0
chunfengxixi
chunfengxixi SEQ_SEQUENCE.nextval不能和Union一起用
6 年多之前 回复
wing3501
wing3501 只能分开一条一条的插入了吗
6 年多之前 回复

[quote]oracle使用序列nextval, currval限制

同一层的SQL,使用序列,不允许有排序操作,不能有order by

Restrictions on Sequence Values You cannot use CURRVAL and NEXTVAL in the
following constructs:
■ A subquery in a DELETE, SELECT, or UPDATE statement
■ A query of a view or of a materialized view
■ A SELECT statement with the DISTINCT operator
■ A SELECT statement with a GROUP BY clause or ORDER BY clause
■ A SELECT statement that is combined with another SELECT statement with the
UNION, INTERSECT, or MINUS set operator
■ The WHERE clause of a SELECT statement
■ The DEFAULT value of a column in a CREATE TABLE or ALTER TABLE statement
■ The condition of a CHECK constraint
Within a single SQL statement that uses CURRVAL or NEXTVAL, all referenced LONG
columns, updated tables, and locked tables must be located on the same database. [/quote]

0
Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!