普通的Insert into 语句一次只能插入一条记录 楼主如果要想一段sql 插多条记录,可以用For循环,在循环中,变量定义为lp insert into t_fms_dictionary(id,dic_key,dic_lang,DIC_UPDATABLE,DIC_VALUE) value (seq_dictionary_id.nextval, lp ,lp ,lp ,lp) --不需要select 如果是想一次插入多条记录,只执行一次插入操作,可以用Forall Type a_type is record (id number,dic_key varchar2,dic_lang varchar2 ,DIC_UPDATABLE varchar2,DIC_VALUE varchar2); Type a_table is table of a_type index by BINARY_INTEGER; declare a a_table; begin For i in 1..n loop --循环赋值 a[i].id := seq_dictionary_id.nextval; a[i].dic_key:= ''; ..... end loop; FORALL i IN a.first .. a.last 一次性插入 INSERT INTO bs_contract_lotitem VALUES a(i); end ;
oracle不让这么用。说明如下: 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 constrain
因为在序列中不能用union,所以不用序列可以。
被我惊奇的发现这样竟然可以create or replace function get_seq (p_in_sqname in varchar2) return number is l_res number ; begin execute immediate 'select '|| p_in_sqname|| '.nextval from dual' into l_res ; return l_res ; end ; insert into t_fms_dictionary(id,dic_key,dic_lang,DIC_UPDATABLE,DIC_VALUE) select get_seq('seq_dictionary_id'), '1','1' ,'1' ,'1' from dual union all select get_seq('seq_dictionary_id') , '2','2','2','2' from dual union all select get_seq('seq_dictionary_id') , '3','3','3','3' from dual
可以试一试,insert into t_fms_dictionary(id,dic_key,dic_lang,DIC_UPDATABLE,DIC_VALUE) select get_seq('seq_dictionary_id'),A.* from ( select '1','1' ,'1' ,'1' from dual union all select '2','2','2','2' from dual union all select '3','3','3','3' from dual) A
楼主如果要想一段sql 插多条记录,可以用For循环,在循环中,变量定义为lp
insert into t_fms_dictionary(id,dic_key,dic_lang,DIC_UPDATABLE,DIC_VALUE)
value (seq_dictionary_id.nextval, lp ,lp ,lp ,lp) --不需要select
如果是想一次插入多条记录,只执行一次插入操作,可以用Forall
Type a_type is record
(id number,dic_key varchar2,dic_lang varchar2 ,DIC_UPDATABLE varchar2,DIC_VALUE varchar2);
Type a_table is table of a_type index by BINARY_INTEGER;
declare
a a_table;
begin
For i in 1..n loop --循环赋值
a[i].id := seq_dictionary_id.nextval;
a[i].dic_key:= '';
.....
end loop;
FORALL i IN a.first .. a.last 一次性插入
INSERT INTO bs_contract_lotitem VALUES a(i);
end ;
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 constrain
因为在序列中不能用union,所以不用序列可以。
is
l_res number ;
begin
execute immediate 'select '|| p_in_sqname|| '.nextval from dual' into l_res ;
return l_res ;
end ;
insert into t_fms_dictionary(id,dic_key,dic_lang,DIC_UPDATABLE,DIC_VALUE)
select get_seq('seq_dictionary_id'), '1','1' ,'1' ,'1' from dual
union all
select get_seq('seq_dictionary_id') , '2','2','2','2' from dual
union all
select get_seq('seq_dictionary_id') , '3','3','3','3' from dual
select get_seq('seq_dictionary_id'),A.* from (
select '1','1' ,'1' ,'1' from dual
union all
select '2','2','2','2' from dual
union all
select '3','3','3','3' from dual) A