*****************************************
? PLSQL游标和goto语句
*****************************************
备注：下面提到的游标为静态cursor，包括显示和隐式。
游标，从declare、open、fetch、close是一个完整的生命旅程。当然了一个这样的游标是可以被多次open进行使用的，显式cursor是静态cursor，她的作用域是全局的，但也必须明白，静态cursor也只有pl/sql代码才可以使用它。
静态游标变量是在定义时就必须指定SQL语句。
cursor 游标(结果集)用于提取多行数据，定义后不会有数据，使用后才有。一旦游标被打开，就无法再次打开(可以先关闭，再打开)。
?第一种游标的定义方式
?? declare
????????? cursor c_student is? select * from book;
??? begin
????????? open c_student;
????????? close c_student;
??? end;
第二种游标的定义方式，用变量控制结果集的数量。
??? declare
????????? v_id binary_integer;
????????? cursor c_student is select * from book where id>v_id;
??? begin
????????? v_id:=10;
????????? open c_student;
????????? close c_student;
??? end;
第三种游标的定义方式，带参数的游标，用的最多。
??? declare
????????? cursor c_student(v_id binary_integer) is select * from book where id>v_id;
??? begin
????????? open c_student(10);
????????? close c_student;
??? end;
?
游标的使用，一定别忘了关游标。
??? declare
????????? v_student emp%rowtype;
????????? cursor c_student(v_id binary_integer) is select * from?emp where rownum<v_id;
??? begin
????????? open c_student(10);
????????? fetch c_student into v_student;
????????? close c_student;
????????? dbms_output.put_line(v_student.name);
??? end;
如何遍历游标fetch
? 游标的属性 %found，%notfound,%isopen,%rowcount。
? %found:若前面的fetch语句返回一行数据，则%found返回true，如果对未打开的游标使用则报ORA-1001异常。
? %notfound，与%found行为相反。
? %isopen，判断游标是否打开。
? %rowcount:当前游标的指针位移量，到目前位置游标所检索的数据行的个数，若未打开就引用，返回ORA-1001。
注：
no_data_found和%notfound的用法是有区别的，小结如下
1）SELECT . . . INTO 语句触发 no_data_found；
2）当一个显式光标(静态和动态)的 where 子句未找到时触发 %notfound；
3）当UPDATE或DELETE 语句的where 子句未找到时触发 sql%notfound；
4）在光标的提取(Fetch)循环中要用 %notfound 或%found 来确定循环的退出条件，不要用no_data_found。
?
下面是几个实例：
create table BOOK
(
? ID?????? VARCHAR2(10) not null,
? BOOKNAME VARCHAR2(10) not null,
? PRICE??? VARCHAR2(10) not null,
? CID????? VARCHAR2(10) not null
);
?
--insert
create or replace procedure say_hello(
i_name in varchar2,
o_result_msg out varchar2
)
???? as
???? v_price varchar2(100);
???? e_myException exception;
???? begin
??????? insert into book(id,bookname,price) values (1,2,3);
??????? o_result_msg := 'success';
???? exception
??????? when others then
???????????? rollback;
???????????? o_result_msg := substr(sqlerrm, 1, 200);
???? end;
?
--update or delete
create or replace procedure say_hello(
i_name in varchar2,
o_result_msg out varchar2
)
???? as
???? v_price varchar2(100);
???? e_myException exception;
???? begin
??????? update book set price = '55' where bookname = i_name;
??????? delete from book where bookname = i_name;
??????? if sql%notfound then
?????????? raise e_myException;
??????? end if;
??????? /*
?if sql%rowcount = 0 then--写法2
?????????? raise e_myException;
??????? end if;
?*/
??????? o_result_msg := 'success';
???? exception
??????? when e_myException then
???????????? rollback;
???????????? o_result_msg := 'update or delete dail';
???? end;
?
--select
create or replace procedure say_hello(
i_name in varchar2,
o_result_msg out varchar2
)
???? as
???? v_price varchar2(100);
???? e_myException exception;
???? begin
??????? select price into v_price from book where bookname = i_name;
??????? o_result_msg := 'success';
???? exception
??????? when no_data_found then
???????????? rollback;
???????????? o_result_msg := 'select into dail';
???? end;
loop方式遍历游标
??? declare
????????? v_bookname? varchar2(100);
????????? cursor c_book(i_id number) is select bookname from book where id = i_id;
??? begin
??????? Open? c_book(i_id);
??????? Loop
??????????? Fetch c_book into v_bookname;
??????????? exit when c_student%notfound;
????????????? update book set price = '33' where bookname = v_bookname;
??????? End Loop;
??????? Close c_book;
??? end;
??? 或
??? declare
????????? v_bookname? varchar2(100);
????????? cursor c_book(i_id number) is select bookname from book where id = i_id;
??? begin
??????? Open? c_book(i_id);
????????? Fetch c_book into v_bookname;
????????? While c_book%Found
????????? Loop
????????????? update book set price = '33' where bookname = v_bookname;
????????? Fetch? c_book into v_bookname;
????????? End Loop;
??????? Close c_book;
??? end;
?? ????
while循环遍历游标，注意，第一次游标刚打开就fetch，%found为null，进不去循环
解决方法：while nvl(c_student%found,true) loop
??? declare
???????? v_bookname? varchar2(100);
???????? cursor c_book(i_id number) is select bookname from book where id = i_id;
??? begin
???????? Open? c_book(i_id);
???????? while nvl(c_book%found,true) --或这种写法：while c_book%found is null or c_book%found loop???
???????????? Fetch c_book into v_bookname;
???????????? update book set price = '33' where bookname = v_bookname;
???????? End Loop;
???????? Close c_book;
??? end;
?
for循环遍历，最简单，用的最多，不需要 声明v_student,Open和Close游标和fetch操作(不用打开游标和关闭游标，实现遍历游标最高效方式)
??? declare
???????? cursor c_book(i_id number) is select bookname from book where id = i_id;
??? begin
???????? for cur in c_book(i_id) --直接将入参i_id传入cursor即可
???????? loop
???????????? update book set price = '53' where bookname = cur.bookname;
???????? end loop;
??? end;
goto例子，一般不推荐使用goto，会使程序结构变乱
??? declare
???????? i number:=0;
??? begin
???????? if i=0 then
???????????? goto hello;
???????? end if;
???????? <<hello>>
???????? begin
???????????? dbms_output.put_line('hello');
???????????? goto over;
???????? end;
???????? <<world>>
???????? begin
???????????? dbms_output.put_line('world');
???????????? goto over;
???????? end;
???????? <<over>>
???????????? dbms_output.put_line('over');
???? end;
附：
declare
????????? cursor c_student(v_id binary_integer) is select * from emp where rownum < v_id;
??? begin
????????? for cur in c_student(10)
????????? loop??
????????? dbms_output.put_line(cur.ename);
??????????? end loop;
??? end;