Oracle-循环

 

Oracle-循环

--插入0到1000,含1000

create table tbl_cycle_test

(

temp_num number

);

truncate table tbl_cycle_test;

--exit when

truncate table tbl_cycle_test;

declare

n number := 0;

begin

loop

insert into tbl_cycle_test (temp_num) values (n);

n := n + 1;

exit when n = 1001;

end loop;

commit;

end;

select min(t.temp_num),max(t.temp_num) from tbl_cycle_test t;

--loop

truncate table tbl_cycle_test;

declare

n number := 0;

begin

loop

insert into tbl_cycle_test (temp_num) values (n);

n := n + 1;

if n = 1001 then exit;

end if;

end loop;

commit;

end;

select min(t.temp_num),max(t.temp_num) from tbl_cycle_test t;

--while

truncate table tbl_cycle_test;

declare

n number := 0;

begin

while n <=1000 loop

insert into tbl_cycle_test (temp_num) values (n);

n := n + 1;

end loop;

commit;

end;

select min(t.temp_num),max(t.temp_num) from tbl_cycle_test t;

--for普通循环

truncate table tbl_cycle_test;

declare

n number := 0;

begin

for n in 0..1000 loop

insert into tbl_cycle_test (temp_num) values (n);

end loop;

commit;

end;

select min(t.temp_num),max(t.temp_num) from tbl_cycle_test t;

--游标

--搞清楚游标和游标变量

drop table tbl_cycle_test2;

create table tbl_cycle_test2 as

select * from tbl_cycle_test;

--For 循环游标

-- 1 定义游标

-- 2 定义游标变量

-- 3 使用for循环来使用这个游标

truncate table tbl_cycle_test;

declare

cursor test2 is select * from tbl_cycle_test2;

n test2%rowtype;

begin

for n in test2 loop

insert into tbl_cycle_test (temp_num) values (n.temp_num);

end loop;

commit;

end;

select min(t.temp_num),max(t.temp_num) from tbl_cycle_test t;

--Fetch游标

--使用的时候必须要明确的打开和关闭

truncate table tbl_cycle_test;

declare

cursor test2 is select * from tbl_cycle_test2;

n test2%rowtype;

begin

open test2;

loop

fetch test2 into n;

--判读是否提取到值，没取到值就退出

--取到值 n%notfound 是false

--取不到值 n%notfound 是true

exit when test2%notfound;

insert into tbl_cycle_test (temp_num) values (n.temp_num);

end loop;

--关闭游标

close test2;

commit;

end;

select min(t.temp_num),max(t.temp_num) from tbl_cycle_test t;