【数据库学习笔记】Oracle_03_PL/SQL深入:游标编程,自定义异常

PL/SQL(本篇文章，使用sqldeveloper工具)

A.游标编程

1.游标

oracle服务器处理客户端的SQL语句

或者批处理存储过程，触发器中的数据处理请求时

会在内存中开辟一个工作区，在其中存储处理的结果

游标，相当于指向该内存区的指针，可以逐一从内存区中获取记录

并赋给变量，交由程序进一步处理

游标由系统或用户以变量的形式定义

2.分类

a.隐式游标

在 PL/SQL 程序中执行DML SQL 语句时自动创建隐式游标，名字固定叫sql

b.显式游标

显式游标用于处理返回多行的查询

c.REF游标

REF 游标用于处理运行时才能确定的动态 SQL 查询的结果

3.隐式游标

PL/SQL中使用DML(CRUD)语句时自动创建

名为sql，通过检查隐式游标的属性可以获得最近执行的DML语句信息

存放的是最新处理的一条SQL 语句所包含的数据

属性：

sql%FOUND – SQL 语句影响了一行或多行时为 TRUE   

sql%NOTFOUND – SQL 语句没有影响任何行时为TRUE

sql%ROWCOUNT – SQL 语句影响的行数

练习

先执行这句话

-- 别忘了先执行这句，否则打印不出信息
set serveroutput on;

a.scott模式下把emp表中销售人员的工资上调20%，然后使用隐式游标输出上调工资的员工数量(权限不够，切换sys，给scott最高权限)

给最高权限

conn sys/123456 as sysdba;

grant dba to scott;

-- 别忘了先执行这句，否则打印不出信息
set serveroutput on;

begin
    -- 销售人员工资上调20%
    update emp set sal=sal*1.2 where job ='SALESMAN';
    -- 判断是否有影响的行数
    if sql%found then
        dbms_output.put_line(sql%rowcount||'人上调工资');
    else
        dbms_output.put_line('没有人上调工资');
    end if;
end;

b.查询scott模式下编号为7369的员工信息，打印出来

declare
    vname emp.ename%type;   -- 定义vname，类型为ename的类型
begin
    -- 查询结果放入vname
    select ename into vname from emp where empno=7369;
    -- 判断是否有结果
    if sql%found then
        dbms_output.put_line(sql%rowcount);
    else
        dbms_output.put_line('没有找到数据');
    end if;
    -- 异常
    exception
        when too_many_rows then
            dbms_output.put_line('查找的行记录多余1行');
        when no_data_found then
            dbms_output.put_line('未找到匹配的行');
end;

4.显式游标

用户声明和操作的一种游标，常操作select查询结果集

步骤：

a.声明游标，在declare部分

就是再定义一个游标名，以及与其相对应的SELECT 语句

语法

CURSOR cursor_name[(parameter[, parameter]…)]

     [RETURN datatype]

IS

     select_statement;

游标参数只能为输入参数，其格式为：

parameter_name [IN] datatype [{:= | DEFAULT} expression]，在指定数据类型时，不能使用长度约束。如NUMBER(4),CHAR(10) 等都是错误的

[RETURN  datatype]是可选的，表示游标返回数据的数据。如果选择，则应该严格与select_statement中的选择列表在次序和数据类型上匹配。一般是RECORD记录数据类型或带“%ROWTYPE”的数据

b.打开游标

游标声明完毕，必须打开才能使用

就是执行游标所对应的SELECT语句

将其查询结果放入工作区，并且指针指向工作区的首部

标识出游标结果集合

语法：

OPEN cursor_name[([parameter =>] value[, [parameter =>] value]…)];

c.读取游标

就是检索结果集合中的数据行，放入指定的输出变量中

打开一个游标后，就可以读取游标中的数据了

语法：

FETCH cursor_name INTO {variable_list | record_variable };

执行FETCH语句时，每次返回一个数据行

然后自动将游标移动指向下一个数据行

当检索到最后一行数据时，如果再次执行FETCH语句

将操作失败，并将游标属性%NOTFOUND置为TRUE

所以每次执行完FETCH语句后，检查游标属性%NOTFOUND

就可以判断FETCH语句是否执行成功并返回一个数据行

以便确定是否给对应的变量赋了值

d.关闭游标

当提取和处理完游标结果集合数据后，应及时关闭游标

以释放该游标所占用的系统资源，并使该游标的工作区变成无效

不能再使用FETCH 语句取其中数据

关闭后的游标可以使用OPEN 语句重新打开

语法：

CLOSE cursor_name;

备注：显式游标和隐式游标有相同的属性

练习

a.使用游标检索scott模式中emp表，查找编号为7369的雇员名称和职务

DECLARE
    -- 定义变量，类型为字段类型
    vname EMP.ENAME%TYPE;
    vjob EMP.JOB%TYPE;
    -- 定义游标，存放查询的数据
    CURSOR cur_emp is
    select ename,job from emp where empno=7369;
begin
    -- 打开游标
    open cur_emp;
    -- 读取游标，将游标所在的数据，放入定义的变量中
    FETCH cur_emp INTO vname,vjob;
    -- 判断
    if cur_emp%found then
        dbms_output.put_line(vname||'---'||vjob);
    else
        dbms_output.put_line('没有查到数据');
    end if;
    -- 关闭游标
    close cur_emp;
end;

b.显式游标常和while循环语句结合使用，简化遍历操作

使用游标检索员工编号大于7900的员工信息

DECLARE
    -- 定义变量存储一行数据
    row_emp emp%ROWTYPE;
    -- 定义游标
    CURSOR cur_emp is
    select * from emp where empno>7900;
BEGIN
    -- 打开游标
    OPEN cur_emp;
    -- 读取游标
    FETCH cur_emp into row_emp;
    -- 循环遍历
    WHILE cur_emp%found loop
        dbms_output.put_line(row_emp.empno||'---'||row_emp.ename);
        -- 再次读取
        FETCH cur_emp into row_emp;
    end loop;
    --关闭游标
    close cur_emp;
END;

c.使用for循环游标，再次简化操作

不再需要open fetch和close语句

不用%FOUND属性检测是否到最后一条记录

一切Oracle隐式的帮我们完成了

也不需要在declare部分声明临时变量

其中for后面的类型是一个自动的record类型

declare
    -- 定义游标
    cursor cur_emp is
    select * from emp where empno>7900;
begin
    -- for循环
    for emp_row in cur_emp loop
        dbms_output.put_line(emp_row.ename);
    end loop;
end;

5.REF 游标：又称游标变量

与游标不同的是，游标变量是动态的，而游标是静态的

游标只能与指定的查询相连，即固定指向一个查询的内存处理区域

而游标变量则可与不同的查询语句相连

它可以指向不同查询语句的内存处理区域

但不能同时指向多个内存处理区域，在某一时刻只能与一个查询语句相连

步骤：

a.定义一个REF CURSOU数据类型

b.声明一个该数据类型的游标变量

c.在打开的时候和一个SQL语句关联

d.获取记录，操作记录

e.关闭游标，完全释放资源

练习：

a.使用游标检索scott模式中emp表，查找编号为7369的雇员名称和职务

declare
    -- 定义一个数据类型
    type cur_ref is ref cursor;
    -- 定义一个中间变量
    temp varchar2(20);
    -- 定义一个游标变量
    vcur cur_ref;
begin
    -- 打开游标关联sql语句
    open vcur for
    select ename from emp where empno>7900;
    -- 获取操作记录
    fetch vcur into temp;
    -- do_while循环
    loop
        dbms_output.put_line(temp);
        -- 再次获取
        fetch vcur into temp;
        exit when vcur%notfound;
    end loop;
    -- 关闭游标
    close vcur;
end;

B.自定义异常

1.异常处理

pl/sql的异常处理在exception代码块中，按照产生方式分为：

预定义异常：系统提供的异常，直接使用

自定义异常：程序设计人员根据业务需要自定义的异常

2.预定义异常

3.自定义异常

可以分为错误编号异常和业务逻辑异常

a.错误编号异常

错误发生时候系统显示错误号和相关描述信息的异常

oralce系统发生错误时，会提供错误号和相关描述信息

但错误编号较为抽象，不易理解

可以在declare中声明exception类型的异常变量名

使用PRAGMA EXCEPTION_INIT给错误编号关联该异常变量

然后就像系统预定义异常一样处理了

编号异常是对系统错误号的包装，使用异常处理机制后

防止异常引发程序崩溃

练习

在emp表中插入编号为7369的员工后

引发ORA-00001错误号，同时程序崩溃

insert into emp(empno) values(7369);

错误编号后面的解释有些抽象，并且程序崩溃，我们自定义一下：

declare
    -- 定义异常变量
    pk_excep exception;
    -- 关联异常变量名和错误号
    pragma exception_init(pk_excep,-00001);
begin
    -- 执行一个错误语句
    insert into emp(empno) values(7369);
    exception when pk_excep then
    dbms_output.put_line('主键empno已存在');
end;

可以看出，程序正常执行完成

b.业务逻辑异常

违反业务逻辑时候需要显示触发的异常

系统自定义异常或错误编号异常，均由oracle系统判断

但是业务逻辑异常oracle系统无法知晓，所以需要使用raise语句触发

首先在decalre部分声明异常变量

然后在begin部分根据业务逻辑执行raise语句

然后在exception部分进行异常处理

练习

实际应用中dept中dname不能为空

insert into dept(deptno) values(11);

select * from dept;

desc dept;

可以看出，表中并未规定dname不能为空

所以从语法上来讲，并没有异常

但是从业务逻辑来讲，dname不能为空，所以要自定义一个异常

declare  
    null_excep exception;  
    dept_row dept%rowtype;  
begin  
    dept_row.deptno:=9;  
    insert into dept values(dept_row.deptno,dept_row.dname,dept_row.loc);  
    -- 如果dname为空  
    if dept_row.dname is null then  
        raise null_excep;  
    end if;  
    exception when null_excep then  
        dbms_output.put_line('dname不能为空');  
        -- 回滚，否则依然会执行语句  
        rollback;  
end;  

查看表中数据