为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】:DM8
【操作系统】:麒麟V10
【CPU】:鲲鹏
【问题描述】*:
—环境
create table ret_test (id int identity(1,1),seqno int,col1 varchar(100),col2 varchar(100) );
CREATE SEQUENCE “Seq_test”
INCREMENT BY 1
START WITH 2
MAXVALUE 200000
MINVALUE 1;
create or replace trigger “trig_ret_test”
before INSERT
on “ret_test”
referencing OLD ROW AS “OLD” NEW ROW AS “NEW”
for each row
begin
select Seq_test.nextval into:new.seqno from dual;
end;
declare type cardid is RECORD(id int,seqno varchar(100),col1 varchar(100));
type cardid_table is table of cardid;
v_cardids cardid_table;
begin
insert into ret_test(col1,col2)
select '1234','afad' from dual
union all
select '12341','afad2' from dual
union all
select '12342','afad2' from dual
return id,seqno,col1 bulk collect
into v_cardids;
select * from table(v_cardids);
select * from ret_test;
end;
只能获取到第一行的值。
能用自增列,就尽量不用序列+触发器,如果库中存在几千个表,岂不是几千个触发器维护。