oracle循环游标

declare
 i int;
begin
 i:=1;
 loop
  dbms_output.put_line(i);
  exit when i=10;
  i:=i+1;
 end loop;
end;

/*
  序列
  create  sequence 序列名;
  序列名.Nextval  取下一个序列
  序列名.Currval  得到当前的序列
*/
create  sequence mySeq;
drop table student;
create table student(
 sid int,
 sname varchar(20)
)

insert into student
values(mySeq.Nextval,'李四');
commit;
select * from student;
select mySeq.Currval from dual;
--删除序列
drop  sequence test;
select test.Currval from dual;
--序列每次递增3,初始值为2
create sequence test start with 2
increment by 3;

declare
 i int:=1;
begin
 while i<10
  loop
   insert into student
values(mySeq.Nextval,'李四'||i);
   i:=i+1;
  end loop;
  commit;--批量插入
end;

create table star(
  sid int
)

begin
  for i in 2..8
   loop
    insert into star values(i);
   end loop;
end;
begin
  for i in reverse 2..8
   loop
    insert into star values(i);
   end loop;
end;
select * from star;

declare
  myException exception;
  pragma exception_init(myException,-2291);
begin
  update student set sname='ceshi' where sid=28789;
  exception when myException then
   dbms_output.put_line('该行不存在');
end;

declare
  myException exception;--定义一个异常变量
begin
  update student set sname='ceshi' where sid=28789;
  if sql%notfound then--判断修改、删除的数据是否被找到
   raise myException;--抛出异常
  end if;
  --捕获异常
  exception when myException then
   dbms_output.put_line('该行不存在');
end;

declare
  myException exception;--定义一个异常变量
begin
  update student set sname='ceshi' where sid=28789;
  if sql%notfound then--判断修改、删除的数据是否被找到
   raise_application_error(-20002,'该行不存在');--抛出异常
  end if;
  --捕获异常
  exception  when others then
   dbms_output.put_line(sqlcode||'该行不存在'||sqlerrm);
end;

--rowid 行id 该行所在的位置
select * from student
where rowid='AAAM7fAABAAAO3qAAA';

declare
 sname student.sname%type;
begin
 select sname into sname from student where sid=28787;
 if sql%found then
  dbms_output.put_line(sname);
 end if;
end;

begin
 update student set sname='ceshi' where sid=28787;
 if sql%notfound then
  dbms_output.put_line('数据未找到');
 end if;
end;

declare
 sn student.sname%type;
begin
 update student
  set sname='ce shi'
   where sid=38787
   returning sname into sn; --<= returning 列名 into 标量变量
 dbms_output.put_line(sn);
end;

declare
type myArr is varray(10000) of varchar2(20);
arr myArr;
begin
 update student
  set sname='ce shi'
  --returning 列名 bulk collect into 集合变量
  --将返回的多行的值放入到集合变量中
  returning sname bulk collect  into arr;
  -- 集合变量名.first  集合的第一个值
  --集合变量名.last  集合的最后一个值
  for i in arr.first..arr.last
  loop
   dbms_output.put_line(arr(i));
  end loop;
end;