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;