conn dayu/dayu
create table test as select * from dba_objects;
alter table test add constraint test_p primary key (object_id);
create index test_name on test(object_name);
**************
常规写法
**************
DECLARE
i number := 0;
BEGIN
for i in 90000 .. 200000 loop
if mod(i,2)=0 then
insert into test
(object_id,object_name, owner)
values
(i+2,'dayu','dayu');
commit;
end if;
end loop;
END;
DECLARE
i number := 0;
BEGIN
for i in 90000 .. 200000 loop
if mod(i,2)=1 then
insert into test
(object_id,object_name, owner)
values
(i+2,'dayu', 'dayu');
commit;
end if;
end loop;
END;
****************
创建trigger
****************
create sequence testseq
increment by 1
start with 900000
maxvalue 999999999
cycle
cache 200;
create or replace trigger test_trigger
before insert on test
for each row
declare
tempkey test.object_id%type;
begin
select testseq.NEXTVAL into tempkey from dual;
if :new.object_id is null then
:new.object_id := tempkey+1;
end if;
end;
DECLARE
i number := 0;
BEGIN
for i in 90000 .. 200000 loop
if mod(i,2)=0 then
insert into test
(object_name, owner)
values
('dayu','dayu');
commit;
end if;
end loop;
END;
DECLARE
i number := 0;
BEGIN
for i in 90000 .. 200000 loop
if mod(i,2)=1 then
insert into test
(object_name, owner)
values
('dayu', 'dayu');
commit;
end if;
end loop;
END;
****************
直接调用sequence
****************
DECLARE
i number := 0;
BEGIN
for i in 90000 .. 200000 loop
if mod(i,2)=0 then
insert into test
(object_id,object_name, owner)
values
(testseq.NEXTVAL,'dayu','dayu');
commit;
end if;
end loop;
END;
DECLARE
i number := 0;
BEGIN
for i in 90000 .. 200000 loop
if mod(i,2)=1 then
insert into test
(object_id,object_name, owner)
values
(testseq.NEXTVAL,'dayu', 'dayu');
commit;
end if;
end loop;
END;