【oracle学习】7.plsql光标和例外

前言
我们以下的所有操作均在PL/Sql Developer工具上完成:

我们以下的表操作可能会基于以下两张表:
我们创建一个员工表和部门表:

员工信息表

[sql] view plain copy

  1. create table EMP(
  2.     EMPNO NUMBER,
  3.     ENAME VARCHAR2(10),
  4.     JOB VARCHAR2(9),
  5.     MGR NUMBER,
  6.     HIREDATE DATE,
  7.     SAL BINARY_DOUBLE,
  8.     COMM BINARY_DOUBLE,
  9.     DEPTNO NUMBER
  10. );

其中job是职位,mgr是该员工的上司的id,sal是工资,comm是提成,deptno是所属部门。

SQL> select * from emp;
EMPNO ENAME                JOB                            MGR HIREDATE         SAL  COMM          DEPTNO
————— ——————– —————— ————— ————– —– —– —————
1110 张三                 主管                          1110 12-3月 -14      5200     0          20
1111 李四                 销售                          1116 03-11月-15      3400   500          30
1112 王五                 销售                          1116 25-4月 -12      4400   800          30
1113 赵二                 后勤                          1110 30-5月 -11      3450     0          40
1114 李磊磊               会计                          1110 22-12月-15      2500     0          50
1115 张少丽               销售                          1110 11-3月 -16      2400  1400          30
1116 林建国               主管                          1116 22-1月 -16      5700     0          20
1117 马富邦               后勤                          1116 22-7月 -13      2800     0          40
1118 沈倩                 会计                          1116 06-5月 -10      2100     0          50

部门表

[sql] view plain copy

  1. create table dept(
  2.     DEPTNO NUMBER,
  3.     DNAME VARCHAR2(50)
  4. );

SQL> select * from dept t;

DEPTNO    DNAME
——–  ——–
20        管理部门
30        销售部门
40        后勤部门
50        金融部门

(1)光标
①光标基础
光标(Cursor)其实就等同于Java中的ResultSet,是一个结果集。
它用来存储一个查询返回的多行数据。
语法:
cursor 光标名 [(参数名 数据类型[,参数名 数据类型]…)]
is select 语句;

光标的属性:
%isopen 是否被打开
%rowcount 行数
%notfound 是否有值

例如:
cursor cl is select ename from emp;

cl中就有emp中的所有ename字段的数据。

打开光标: open c1;(打开光标执行查询)
取一行光标的值:fetch c1 into pjob;(取一行到变量中)
关闭光标:close c1;(关闭光标释放资源)
注意:
上面的pjob必须与emp表中的job列类型一致:
定义: pjob emp.empjob%type;

演示:
使用光标查询员工姓名和工资,并打印

[sql] view plain copy

  1. set serveroutput on
  2. declare
  3.   –光标
  4.   cursor cemp is select ename,sal from emp;
  5.   pename emp.ename%type;
  6.   psal emp.sal%type;
  7. begin
  8.   open cemp;
  9.   loop
  10.     –从集合中取值
  11.     fetch cemp into pename,psal;
  12.     exit when cemp%notfound;
  13.     dbms_output.put_line(pename||‘的工资为’||psal);
  14.   end loop;
  15.   close cemp;
  16. end;
  17. /

结果:

不使用光标的话的方法为:

[sql] view plain copy

  1. declare
  2.   employee emp%rowtype;
  3. begin
  4.   for employee in (select * from emp)
  5.   loop
  6.     dbms_output.put_line(employee.ename||‘的工资为’||employee.sal);
  7.   end loop;
  8. end;
  9. /

结果:

②示例操作
接下来我们来看一个例子:

我们这里要求给员工涨工资,主管1000元,会计800元,其它400元。
也就是根据员工的职位,执行相应的update语句来给其涨工资。

先不用PLSQL来写,我们用大家熟悉的Java来解决这个问题。
这里肯定是使用JDBC连接数据数,然后拿到Connection对象,请求数据库,
得到一个结果集,给结果集按照职位设置涨工资参数,示例伪代码如下:

[java] view plain copy

  1. ResultSet rs = “select empno,job from emp”;
  2. while(rs.next()){
  3.     int eno = rs.getInt(“empno”);
  4.     String job =  rs.getString(“job”);
  5.     if(job.equels(“主管”)){
  6.         update emp set sal=sal+1000 where empno=eno;
  7.     } else if(job.equels(“销售”)){
  8.         update emp set sal=sal+800 where empno=eno;
  9.     }else{
  10.         update emp set sal=sal+400 where empno=eno;
  11.     }
  12. }

使用PLSQL可以解决JDBC能解决的问题。
我们使用PLSQL来解决这个问题,结构和这个差不多,但是语法不一样。
但是我们把SQL语言的数据操纵能力与过程语言的数据处理能力结合起来,使
得PLSQL面向过程但又比过程语言简单、高效、灵活和实用。

我们使用PLSQL来实现这个例子:

首先注意,oracle默认一次性只能开启300个光标,我们可以通过
“show parameters cursor”指令来查看光标目前的设置信息:
使用Command窗口:

光标的开启数是可以调整的,但是现在我们的数据不到300,所以调整方法
等下一个总结我们再介绍。

编写PLSQL程序:

[sql] view plain copy

  1. declare
  2.   –光标代表员工
  3.   cursor cemp is select empno,job from emp;
  4.   pempno emp.empno%type;
  5.   pjob emp.empjob%type;
  6. begin
  7.   open cemp;
  8.   loop
  9.     fetch cemp into pempno,pjob;
  10.     exit when cemp%notfound;
  11.     –判断
  12.     if pjob=‘主管’ then update emp set sal=sal+1000 where empno=pempno;
  13.       elsif pjob=‘销售’ then update emp set sal=sal+800 where empno=pempno;
  14.       else update emp set sal=sal+400 where empno=pempno;
  15.     end if;
  16.   end loop;
  17.   close cemp;
  18.   dbms_output.put_line(‘完成’);
  19. end;
  20. /

执行程序之前:

执行程序并commit之后,结果:

③带参数的光标
我们来查询某个部门的员工姓名

[sql] view plain copy

  1. declare
  2.   cursor cemp(pdno number) is select ename from emp where deptno=pdno;
  3.   pename emp.ename%type;
  4. begin
  5.   open cemp(20);
  6.   loop
  7.     fetch cemp into pename;
  8.     exit when cemp%notfound;
  9.     dbms_output.put_line(pename);
  10.   end loop;
  11. end;
  12. /

查询的是部门为20的所有员工的姓名,结果:

(2)例外
例外是程序设计语言提供的一种功能,用来增强程序的健壮性和容错性。
其实类似于Java的Exception。

Oracle的异常处理
A.系统定义的例外
–No_data_found(没有找到数据)
–Too_many_rows(select…into语句匹配多个行)
–Zero_Divide(被零除)
–Value_error(算术或转换错误)
–Timeout_on_resource(在等待资源时发生超时)

B.用户定义的例外
用户自己去定义的自定义例外。

例子:测试“被零除”的例外

[sql] view plain copy

  1. declare
  2.   pnum number;
  3. begin
  4.   pnum:=1/0;
  5. exception
  6.   when Zero_Divide then dbms_output.put_line(‘1:0不能做被除数!’);
  7.   when Value_error then dbms_output.put_line(‘2:算术或转换错误!’);
  8.   when others then dbms_output.put_line(‘3:其它错误!’);
  9. end;
  10. /

结果:

用户自定义的例外,其实就是用户定义的一个变量

查询100号部门的员工姓名(100号部门是不存在的)

[sql] view plain copy

  1. declare
  2.   pename emp.ename%type;
  3.   No_emp_found exception;  –自定义例外
  4.   cursor cemp is select ename from emp where deptno=100;
  5. begin
  6.   open cemp;
  7.   fetch cemp into pename;
  8.   if cemp%notfound then
  9.      raise No_emp_found;  –抛出自定义异常
  10.   end if;
  11.   close cemp;
  12. exception
  13.     when No_emp_found then dbms_output.put_line(‘1:没有找到员工!’);
  14.     when others then dbms_output.put_line(‘2:其它错误!’);
  15. end;
  16. /

结果:

由于我们在close cemp;之前就raise了一个异常,oracle在抛出异常之后,就会
找没有关闭的光标,然后自动关闭掉。但有时候在复杂环境下,自动关闭往往是
不保险的,所以这里我们要自己手动关闭光标。

我们在exception的判断中,可以判断一下光标是否关闭,如果没有关闭就关闭它。

[sql] view plain copy

  1. if cemp%isopen then
  2.   close cemp;
  3. endif;

未经允许不得转载:JX BLOG » 【oracle学习】7.plsql光标和例外

赞 (0)

评论 0

评论前必须登录!

登陆 注册