数据库讲义--2--游标、异常（课堂版）

1.游标

游标提供了一种从表中检索数据并进行操作的灵活手段。PL/SQL在执行SELECT、INSERT、DELETE和UPDATE语句时，ORACLE会在内存中为其分配上下文区（Context Area），即缓冲区。游标是指向该区的一个指针，通过游标PL/SQL程序可以一次处理查询结果集中的一行，并可以对该行数据执行特定操作，从而为用户在处理数据的过程中提供方便。 在oracle中，主要使用显式游标，隐式游标，动态游标（以后讲—存储过程实现分页的时候）。

2. 显式(示)游标

显式游标是由用户声明和操作的一种游标，通常用于操作查询结果集（即由select语句返回的查询结果），使用它处理数据的步骤为：声明游标、打开游标、读取游标和关闭游标4个步骤。其中，读取游标很可能是一个反复的步骤(迭代)，因为游标每次只能读取一行数据，对于多条记录，需要反复读取，直到游标读取不到数据为止。

2-1 游标使用的步骤 a. 声明游标

定义游标名字，以及对应的select语句。

CURSOR cursor_name*(input_parameter1*, input_parameter2+…)+ [RETURN ret_type] IS

select_statement; cursor_name: 所声明游标名称

ret_name： 执行游标操作后的返回值类型，这是一个可选项。

select_statement: 游标所使用的select语句，为游标的反复读取提供了结果集。

input_parameter1: 游标的“输入参数”，可以有多个，这是可选项。它指定用户在打开游标后，向游标中传递的值，该参数的定义和初始化格式如下： para_name [in] datatype [ {:= | default } para_value ]

其中，para_name表示参数名称，其后面的关键字\表示输入方向，可以省略；datatype表示参数的数据类型，但数据不可以指定长度； para_value表示该参数的初始值或默认值，也可以是表达式； para_name参数的初始值可以用常规方式赋值（:=）,也可以使用关键字default。

示例代码：

声明一个游标，用来读取emp表中职务是销售员(SALESMEN)的雇员信息。 declare

cursor cur_emp(var_job in varchar2:='SALESMEN')

is select empno, ename, sal from emp where job=var_job;

b. 打开游标

在游标声明完毕之后，必须打开才能使用，打开游标的语法： open cur_name[(para_values1[,para_values2]...)]; cur_name: 打开游标的名字

para_values1: 指定“输入参数”的值，根据声明游标时的实际情况，可以是多个或一个，

这是可选项。 示例代码：

open cur_emp('MANAGER');

c. 读取游标

fetch cur_name into {variable};

variable: 表明一个变量列表或者“记录”变量(RECORD类型)，oracle使用“记录”变量来存储游标中的数据。

在游标中包含了一个数据行指针，其用来指向当前数据行。刚刚打开游标时，指针指向结果集中的第一行，当使用fetch--into语句读取数据完毕后，游标中的指针自动指向下一行数据，直到指针指向结果集中最后一条记录之后为止(当最后一条记录为空时，表示读取完毕），这是游标的%found属性值为false.

4. 关闭游标

游标使用完毕后，需要关闭，以释放资源。 close cur_name;

整个游标操作的示例代码：

declare

cursor cur_emp (var_job varchar2 := 'SALESMAN')

is select empno, ename, sal from emp where job = var_job; type record_emp is record

(var_empno emp.empno%type, var_ename emp.ename%type, var_sal emp.sal%type); emp_row record_emp; begin

open cur_emp('MANAGER'); fetch cur_emp into emp_row; while cur_emp%found loop

dbms_output.put_line(emp_row.var_ename||'的编号'||emp_row.var_empno||',的工资'||emp_row.var_sal);

fetch cur_emp into emp_row; end loop; close cur_emp; end; /

2-2 游标的属性 游标有4个属性：

%found: 布尔型属性，如果SQL语句至少影响到一行的数据，该属性为true, 否则为false

%notfound:布尔型属性，与%found相反。 %isopen: 布尔型属性，游标是否打开

%rowcount: 数字型属性，返回受SQL语句影响的行数

使用指南，声明一个游标，检索指定员工编号的雇员信息，然后使用游标的%found属性来判断是否检索到指定员工编号的雇员信息。 declare

var_name varchar2(50); var_job varchar2(50);

/*声明游标*/

cursor cur_emp

is select ename, job form emp where empno=7499; begin

open cur_emp;

fetch cur_emp into var_name, var_job; if cur_emp%found then

dbms_output.put_line('编号是7499'||var_name); else

dbms_output.put_line('查无此人'); endif; end; /

3. 隐式游标

在执行一个SQL语句时，oracle会自动创建一个隐式游标。这个游标是内存中处理该语句的工作区域。隐式游标主要处理数据操纵语句（如update, delete语句）的执行结果，当然，特殊情况下，也可以处理select语句的查询结果。隐式游标，也有属性。当使用隐式游标的属性时，需要在属性前面加上隐式游标的默认名称--SQL. 示例代码：

把emp表中销售员（SALESMAN）的工资上调20%,使用隐式游标的属性输出上调工资的员工数量。 begin

update emp set sal=sal*(1+0.2) where job='SALESMAN'; if sql%notfound then

dbms_output.put_line('没有雇员上调工资'); else

dbms_output.put_line('有'||sql%rowcount||'个雇员上调工资'); end if; end; /

4. 通过for语句循环遍历游标

使用for语句遍历游标中的数据时，可以把其计数器看做一个自动的RECORD类型变量。

4-1 遍历显式游标

for var_auto_record in cur_name loop plsqlsentence; end loop;

var_auto_record: 自动的RECORD类型的变量，可以是任意合法的变量名称。 cur_name: 指定游标的名称 plsqlsentence: PL/SQL语句

示例代码：

使用显示游标和for语句检索部门编号是30的雇员信息并输出： declare

cursor cur_emp

is select * from emp where deptno=30; begin

for emp_record in cur_emp loop

dbms_output.put(emp_record.empno); dbms_output.put(emp_record.ename); dbms_output.put(emp_record.job); end loop; end; \\

4-2 使用隐式游标和for语句 begin

for emp_record in (select empno, ename, sal from emp where job='SALESMAN') loop

dbms_output.put(emp_record.empno); dbms_output.put(emp_record.ename); dbms_output.put_line(emp_record.sal); end loop; end; /

5. 异常—理解成java try—catch--….

在PL/SQL程序中，有exception块的定义。 declare begin exception end; /

在编写PL/SQL程序时，避免不了发生一些错误，可能是程序员自己造成，也可能是操作系统或者硬件环境错误，比如除数为零，磁盘I/O错误等，oracle采用异常机制来处理，异常处理代码通常放在PL/SQL的exception代码块中。根据异常产生的机制和原理，oracle异常分为两大类：

预定义异常：oracle系统自身为用户提供大量的、可在PL/SQL中使用的预定义异常，以便检查用户代码失败的一般原因。这种异常，用户无序搭理，由oracle自动引发。

自定义异常：出现操作系统错误，机器硬件损坏等，以及业务实际需求，程序设计人员需要自定义一些错误的业务逻辑，而PL/SQL程序在运行过程中，可能触发这些错误的业务逻辑，对于上述异常，需要在程序中自定义异常，然后由oracle自动触发。

5-1 预定义异常

当PL/SQL程序返回了oracle系统内部规定的设计规范，会自动引发一个预定义异常，详见表格：

预定义说明的部分 ORACLE 异常错误 错误号

异常错误信息名称 说明

ORA-0001

Dup_val_on_index 违反了唯一性限制 ORA-0051

Timeout-on-resource 在等待资源时发生超时 ORA-0061

Transaction-backed-out 由于发生死锁事务被撤消 ORA-1001

Invalid-CURSOR

试图使用一个无效的游标 ORA-1012 Not-logged-on 没有连接到ORACLE ORA-1017 Login-denied

无效的用户名/口令 ORA-1403

No_data_found

SELECT INTO没有找到数据 ORA-1422

Too_many_rows

SELECT INTO 返回多行 ORA-1476 Zero-divide

搜索“diyifanwen.net”或“第一范文网”即可找到本站免费阅读全部范文。收藏本站方便下次阅读，第一范文网，提供最新资格考试认证数据库讲义--2--游标、异常（课堂版） 全文阅读和word下载服务。