*****************************************
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;