Oracle初学者笔记(十三)--PL/SQL2_异常处理和游标
如果想要dbms_output.put_line()等方法的输出能够在客户端看见的话，请
set serverouput on;
例子:
declare
empname emp.ename%type;
sno emp.empno%type;
iCount int:=0;
begin
sno:='7369';
empname:='jade';
dbms_output.put_line('employee no is '||sno||' employee name is '||empname);
select empno,ename into sno,empname from emp where empno='7369';
dbms_output.put_line('employee no is '||sno||' employee name is '||empname);
select count(*) into iCount from emp where sal>2000;
if iCount>0 then
dbms_output.put_line('有工资大于2000的人的信息'||iCount);
else
dbms_output.put_line('没有');
end if;
end;
/
异常
如同JVM一样，Oralce是最终异常处理人员；但是他们的处理结果对用户而言是不好理解的，因为他们的语言是如此地不友好，如此的ugly;
所以我们最好是自己捕获处理异常；
步骤：
1．声明异常
2．引发异常
3．处理异常
类型：
1， 预定义的异常：不需要显式声明；dbms_standard程序包里有定义；
例子： dbms_output.put_line('employee job is '||empjob);
exception
when no_data_found then
dbms_output.put_line('no data return');
如果不处理异常，程序就会报告Error而不是异常，不同于Java;
但是程序流程是一样的，出错后，后面的代码都不再执行，直接退出；
用户处理的话，出错后，处理后，其他的可以继续执行错误处理后面的代码，这和Java一样；
When others then
处理动作;
这里的others表示未知的异常发生时；这是个很保险的方法，类似用Exception的效果；其他的异常都是others的一个子类型；
2， 非预定义的异常：数据库本身不知道不能控制的，比如操作系统崩溃；
Oralce服务器错误
网络或者机器I/O错误
3， 用户定义的异常：有个类型叫exception类型
例子：
1 declare
2 dup_value exception;
3 icount int:=0;
4 begin
5 select count(*) into icount from place
6 where placeid='001';
7 if icount>0 then
8 raise dup_value;
9 end if;
10 exception
11 when dup_value then
12 dbms_output.put_line('already have the note');
13* end;
already have the note
但是我们也看到了，我们在这里定义了这样一个异常，但是当我们insert into一个’001’时，系统还是会发生系统的那个异常而不是我们定义的，解决的方法就是预条件编译：
1 declare
2 dup_value exception;
3 pragma exception_init(dup_value,-1); ---这句的意思就是异常时用我已
定义的dup_value来处理；
4 begin
5 insert into place values('001','jfka');
6 exception
7 when dup_value then
8 dbms_output.put_line('already have the note');
9* end;
already have the note
put_line只能返回给sql_plus;
raise_application_error函数的作用：
实际上，前面我们的做法只是在发生异常的时候打印了异常信息，并没有引发系统级的异常所以客户端也看不到我们打印的异常信息，但是raise_appliction_error就是直接引发系统异常而不是简单地打印错误消息：
6 exception
7 when dup_value then
8 raise_application_error(-20001,'already have the note');
9 --只能在sql/plus 中看:dbms_output.put_line('already have the note');
10* end;
declare
*
ERROR λÓÚµÚ 1 ÐÐ:
ORA-20001: already have the note
ORA-06512: ÔÚline 8
Raise_appliction_erro(-20000-> -20999,’消息’)
游标
服务器端的一个区域的一个指针；
因为在服务器端存储，所以开启游标的时候，我们在使用服务器端的资源；
好处：
1， 减少了网络流量：可以在服务器端做数据处理工作；
2， 可以实现遍历，可以对一个结果集进行逐行检索；
3， 允许直接更新表；
类型：
1，静态游标：由用户定义，任务明确，代码被编译后固定存储；
隐式游标是用户执行DML语句时，系统在服务器端自动打开的，将用户操作的数据存储在游标区域，用户执行完DML语句时，游标被自动关闭；
我们可以利用的四个属性：%notfound %found %rowcount %isopen，用来表示DML语句的执行情况；
隐式游标的名字都叫做sql,例子：
1 begin
2 insert into place values('006','beijing');
3 dbms_output.put_line('Rowcount:'||sql%rowcount);
4 if sql%notfound then
5 dbms_output.put_line('notfound is true');
6 else
7 dbms_output.put_line('notfound is false');
8 end if;
9 if sql%found then
10 dbms_output.put_line('found is true');
11 else
12 dbms_output.put_line('found is false');
13 end if;
14 if sql%isopen then
15 dbms_output.put_line('isopen is true');
16 else
17 dbms_output.put_line('isopen is false');
18 end if;
19* end;
Rowcount:1 ---统计所影响的行数
notfound is false -
found is true --和notfound都表示是否操作成功了，可以认为rocount>=1时就表示成功
isopen is false --隐式游标是否打开了？这里因为SQL执行结束后，自动关闭了；
显式游标是用户用代码定义，维护的；
注意，这样的游标是要声明的，但它是一个存储区域而不是变量；
游标标识符只不能赋值与传值，就象指针一样；
游标区域实际是个结果集，所以可以用查询语句来声明游标；
Cursor 游标名 is select * from emp;
Open,fetch,close方法用来操作；
declare
cursor empCur is select * from emp;
emprow emp%rowtype;
begin
open empCur;
fetch empCur into emprow; --fetch一次提走一行,remove的效果；
dbms_output.put_line('no'||emprow.empno||'ename'|| emprow.ename);
close empCur;
end;
/
如果有多个行的数据的话，就可以用循环，这非常类似Java中的Result类或者说是带有迭代器的集合类；
比如：
loop
fetch empCur into emprow;
dbms_output.put_line('no'||emprow.empno||'ename'|| emprow.ename);
exit when empCur%notfound; --显式游标也有类似的四个属性
end loop;
发现要使用显式游标的属性的话，必须在游标close方法被调用之前；
但是这里是有问题的：%notfound与%found属性是在 每次fetch执行后并且返回结果的情况下才会被分别置为false与true,他们的初始值分别是true和false,所以如果你尝试while empcur%found之后进入循环调用fetch的话，将会失败，因为根本进不了循环；所以改造的while循环使用方法为：
open empcur;
fetch empcur into emprow;
while empcur%found loop
dbms_output.put_line(' employee no : '||emprow.empno||' employee
name '||emprow.ename);
fetch empcur into emprow;
end loop;
dbms_output.put_line('fetch '||empcur%rowcount||' rows');
close empcur;
for循环是全自动的，自动打开游标，自动提取，自动关闭，自动声明行级变量：
declare
cursor empcur is select * from emp;
begin
for emprow in empcur loop
dbms_output.put_line(' employee no : '||emprow.empno||' employee
name '||emprow.ename);
end loop;
end;
/
绑定变量的使用： 我们发现cursor empcur is select * from emp where sal>1000;这个语句使得我们得到的游标是个固定的，结果集的查询也没有交互性，所以我们可以使用绑定变量来实现交互：
1 declare
2 cursor empcur is select * from emp where sal>&inputsalary;
3 begin
4 for emprow in empcur loop
5 dbms_output.put_line(' employee no : '||emprow.empno||' employee
6 name '||emprow.ename);
7 end loop;
8* end;
请输入inputsalary 的值: 4000
原值 2: cursor empcur is select * from emp where sal>&inputsalary;
新值 2: cursor empcur is select * from emp where sal>4000;
employee no : 7839 employee
name KING
绑定变量是无处不在的；比如：declare
Cache int;
Begin
Cache:=&inputcache;
这样一来，就会在程序编译的时候提示你输入绑定变量inputcache的值然后编译整个程序；
注意，是在编译的时候而不是运行的时候，所以这种交互性还是不够准确完美，因为仅限于SQL-Plus环境，出了这个环境，是无法运行的；
游标的好处就是：因为是逐行检索，所以在出现重复行的时候仍然可以完全检索数据，而selece语句在重复行的情况下并不能完全检索数据；
游标的不好处就是：当查询结果集很大的时候，服务器的内存将会被占用，服务器性能严重下降；
//个人觉得虽然PL/SQL是个过程语言，但是非常的ugly,你看看fetch empCur就有多清楚了；
2，动态(REF)游标:
静态游标打开的区域是固定的，打开的结果集也是固定的；
动态游标开打的区域是不固定的，打开的结果集也是不固定的；
静态游标被定义的时候使用查询语句，而动态游标在打开的时候使用查询语句；
也就是说定义一个静态游标只能对应一个结果集，而动态游标可以被多次使用以对应
到不同的结果集；
动态游标是一个类型，应该用这个类型声明游标变量然后使用；
游标被打开后就不能再被打开，所以动态游标被打开后，请不要再使用for循环；
declare
type refEmpCur is ref cursor;
empCur refEmpCur;
empRow emp%rowtype;
flag int:=0;
begin
flag:=&flag;
if flag=0 then
open empCur for select * from emp where sal>500 and sal<1000;
elsif flag=1 then
open empCur for select * from emp where sal>1000;
else
open empCur for select * from emp;
end if;
/*for empRow in empCur loop
dbms_output.put_line(' empno '||empRow.empno||' ename '||empRow.ename);
end loop;*/
loop
fetch empCur into empRow;
dbms_output.put_line(' empno '||emprow.empno||' ename '||empRow.ename);
exit when empCur%notfound;
end loop;
close empCur;
end;
/
上面所用的游标是弱型游标，也就是说对返回什么结果集没有限制；
动态游标可以分为：
具有返回类型的游标与没有返回类型的游标；
强型游标的返回类型是固定的：
声明为：type refEmpCur is ref cursor return emp%rowtype;代表这个动态游标必须返
回行级类型emp%rowtype的结果集；
游标的限制：
不能在程序包中声明游标变量；
远程子程序不能接受游标变量的值；
不能使用比较操作符对游标变量进行等或者不相等测试；
不能将空值赋予游标变量；
表不能存储游标变量的值；
试验
使用while和loop循环：
1 declare
2 cursor mycur is select * from category_header;
3 code category_header.cat_code%type;
4 description category_header.cat_desc%type;
5 begin
6 open mycur;
7 fetch mycur into code,description;
8 while mycur%found loop
9 dbms_output.put_line('code: '||code||’ ‘||'description: '||description);
10 fetch mycur into code,description;
11 end loop;
/* Loop
Fetch mycur into code,description;
…..
End loop;*
12 close mycur;
13* end;
code: 01 description: Ì©¹úÒ»ÈÕÓÎ
code: 02 description: Å·ÖÞÆßÈÕÓÎ
code: 03 description: º£ÄÏÈýÈÕÓÎ
使用for循环的效果：
declare
cursor mycur is select * from category_header;
begin
for cate in mycur loop
dbms_output.put_line(' code: '||cate.cat_code||' '||'desc : '||cate.cat_desc);
end loop;
end;
/
