Oracle 数据库管理与应用：第7章 PLSQL编程基础3

1、7.7 游标的创建与应用 当SELECT语句在PL/SQL程序块中使用时，要求查询结果集中只能包含一条记录，若查询出来的数据多于一行，则执行出错SQL提供了游标机制，使用游标可以解决这个问题。游标是指向查询结果集的一个指针（内存的数据结构），通过游标可以将查询结果集中的记录逐一取出，并在PL/SQL程序块中进行处理。 在Oracle中，包含两种类型的游标：显式游标和隐式游标。显式游标是用户自己创建并操作的游标隐式游标是由系统自动创建并管理的游标，用户可以访问隐式游标的属性。系统为所有的DML语句和SELECT语句自动创建“隐式游标”。 7.7.1 显式游标 1. 显式游标的使用过程 定义游标打

2、开游标提取数据处理数据关闭游标 （1）定义游标在使用显式游标之前，必须先在程序块的定义部分对其进行定义。 语法如下：CURSOR cursor_name IS select_statement;例如：CURSOR c1 IS SELECT * FROM scott.emp; （2）打开游标当游标被打开时，Oracle会执行游标所对应的SELECT语句，并将游标作为指针，指向SELECT语句结果集的第一行。语法如下：OPEN cursor_name;例如：OPEN c1;（3）使用游标获取数据 游标被打开后，可以使用FETCH语句获取游标正在指向的查询结果集中的记录该语句执行后游标的指针自动下移

3、，指向下一条记录。因此，每执行一次FETCH语句，游标只获取到一行记录，如果要处理查询结果集中的所有数据，那么需要多次执行FETCH语句，通常使用循环实现。FETCH语句的语法如下：FETCH cursor_name INTO variable1,variable2,.;其中，variable表示一组用于接收游标获取到的当前记录的变量。这些变量的个数、数据类型、宽度应和游标指向的查询结果集的结构保持一致。例如：FETCH c1 INTO emp_rec; 注意：本例中使用的emp_rec变量是一个记录变量(例如：使用%ROWTYPE定义类型)，如果使用简单变量需要定义多个，实现起来较麻烦。 （

4、4）处理从游标中获取到的数据可以进一步处理存入到变量中的数据了，具体执行何种处理操作，与具体的业务有关。例如，将上面emp_rec记录变量中接收到的数据进行输出。dbms_output.put_line(姓名是：|emp_rec.ename|工资是|emp_rec.sal);（5）关闭游标 在提取并处理了结果集的所有数据之后，就可以关闭游标并释放其结果集了。语法如下：CLOSE cursor_name;例如：CLOSE c1; 例7.32 从scott.emp表中查询所有记录，利用游标获取前两行记录并输出ename、job、sal中的值。DECLARE CURSOR c1 IS SELECT

5、* FROM scott.emp; emp_rec scott.emp%ROWTYPE; -定义一个和表结构完全一致的记录变量BEGIN OPEN c1; FETCH c1 INTO emp_rec; dbms_output.put_line(姓名是:|emp_rec.ename| 工作是:|emp_rec.job| 工资是:|emp_rec.sal); FETCH c1 INTO emp_rec; dbms_output.put_line(工作是:|emp_rec.ename|工作是:|emp_rec.job| 工资是:|emp_rec.sal); CLOSE c1;END; 将上面的程序改

6、进：程序实际需要处理的数据只有ename、job和sal三个字段的值 所以，可以将游标的定义和记录变量的定义改为如下形式：CURSOR c1 IS SELECT ename,job,sal FROM scott.emp;emp_rec c1%ROWTYPE; -将记录变量定义为和游标的结构一致 2. 游标的常用属性 在游标的使用过程中，经常需要用到游标的四个属性，以确定游标当前和总体状态。%ISOPEN属性：游标是否打开%FOUND属性： 是否取到数据%NOTFOUND属性：与上面相反%ROWCOUNT属性：已获取的记录总数游标属性的引用格式：游标名%游标属性名 如：c1%ISOPEN参见教材

7、p158159结果为逻辑型数据结果为整型数据3. 显式游标的循环用户在使用FETCH语句获取游标指向的查询结果时，每次只能获取一条记录。如果获取结果集中所有的记录，那么需要使用循环重复执行FETCH语句。 （1）游标的LOOP循环例7.33 使用游标实现逐行输出scott.emp表中部门编号为10的员工姓名和工资。DECLARE CURSOR emp_cursor IS SELECT ename,sal FROM scott.emp WHERE deptno=10; emp_record scott.emp%ROWTYPE; -也可以使用游标的结构直接定义该记录变量BEGIN OPEN emp

8、_cursor ; LOOP FETCH emp_cursor INTO emp_record.ename,emp_record.sal; EXIT WHEN emp_cursor%NOTFOUND; dbms_output.put_line(ename: |emp_record.ename| sal:|emp_record.sal); END LOOP; dbms_output.put_line(row count:|emp_cursor%rowcount); CLOSE emp_cursor;END;（2）游标的FOR循环与其他的循环语句相比FOR循环更能方便的控制游标的循环过程，这是由于

9、：FOR循环中的循环控制变量不需要事先定义。在游标的FOR循环之前，系统能够自动打开游标；在FOR循环结束后，系统能够自动关闭游标，不需要人为操作。在游标的FOR循环过程中，系统能够自动执行FETCH语句；每一次循环，系统就自动执行一次FETCH语句，将游标指向的当前行记录存入循环控制变量中 例7.34 使用FOR循环语句完成例7.33中规定的操作DECLARE CURSOR emp_cursor IS SELECT ename,sal FROM scott.emp WHERE deptno=10;BEGIN FOR emp_record IN emp_cursor LOOP dbms_out

10、put.put_line(ename: |emp_record.ename| sal:|emp_record.sal); END LOOP;/* 下面这个语句无效，因为FOR循环结束后游标自动关闭dbms_output.put_line(row count:|emp_cursor%rowcount);*/END;例7.35 使用游标查询scott.emp表中的所有记录，并在程序块中输出工资最高的前五行记录。DECLARE CURSOR cur IS SELECT * FROM scott.emp ORDER BY sal DESC;BEGIN FOR rec IN cur LOOP IF cu

11、r%ROWCOUNT=5 THEN dbms_output.put_line(ename: |rec.ename| sal:|rec.sal); ELSE EXIT; -退出循环 END IF; END LOOP;END;7.7.2 带参数的游标 参数游标是指带有参数的游标，在定义了参数游标之后，当使用不同参数值打开游标时，可以产生不同的结果集。定义参数游标的语法如下： CURSOR cursor_name(parameter_name datatype) IS select_statment; 使用open命令打开带参数的游标时，需要给游标传递实参值。例7.36 定义参数游标，查询指定部门的

12、员工姓名。DECLARE-定义游标参数no，参数类型为number类型CURSOR emp_cursor(no NUMBER) IS SELECT ename FROM scott.emp WHERE deptno=no;emp_rec emp_cursor%ROWTYPE;BEGIN -打开参数游标时，指明一个替代变量作为游标参数的值 OPEN emp_cursor(&no); LOOP FETCH emp_cursor INTO emp_rec; EXIT WHEN emp_cursor%NOTFOUND; dbms_output.put_line(ename:|emp_rec.ename

13、); END LOOP; CLOSE emp_cursor;END;使用FOR循环完成上例的操作，代码如下所示：DECLARECURSOR emp_cursor(no NUMBER) IS SELECT * FROM scott.emp WHERE deptno=no; BEGIN FOR emp_record in emp_cursor(&no) LOOP dbms_output.put_line(ename:|emp_record.ename); END LOOP;END;7.7.3 隐式游标 在执行一个SQL语句时（select、Insert、update、delete），Oracle服

14、务器将自动创建一个隐式游标。通过游标可获得SQL语句的执行结果，以及游标的状态信息。隐式游标属性的访问方式：sql%属性名例7.37 以下是一个更新指定部门员工工资的示例，通过游标属性查看被更新记录的行数。BEGIN UPDATE scott.emp SET sal = 1500 WHERE deptno = &no; IF sql%NOTFOUND THEN dbms_output.put_line(no update); ELSE dbms_output.put_line(update row: | sql%ROWCOUNT); END IF;END;如果在循环中不需要引用游标的名称，那么

15、可以直接在游标FOR循环中使用子查询，由系统自动创建隐式游标。例7.38 使用隐式游标的FOR循环查询emp表中的数据。BEGIN FOR emp_record IN (SELECT ename,sal FROM emp) LOOP dbms_output.put_line(emp_record.ename| |emp_record.sal); END LOOP;END; 7.7.4 使用游标更新表中的数据 可以使用游标在表中修改、删除数据。但是需要注意，若想通过游标修改或删除数据，那么在定义游标时必须要带有FOR UPDATE子句。语法格式如下：CURSOR cursor_name(para

16、meter_name datatype)IS select_statementFOR UPDATE OF column_reference NOWAIT;FOR UPDATE子句用于在游标结果集数据上加行共享锁，以防止其他用户在相应行上执行DML操作。OF子句用来指定要锁定的列，忽略OF子句，那么表中选择的数据行都将被锁定。 NOWAIT子句，表示，如果游标要修改的行被另一个用户的操作锁定，则OPEN会立即返回错误提示，不再等待。 为了修改或删除游标当前行数据，必须在UPDATE、 DELETE语句中使用WHERE CURRENT OF 子句，表示修改或删除的记录是游标指向的当前记录。UPDA

17、TE table_name SET column= new_value WHERE CURRENT OF cursor_name;DELETE table_name WHERE CURRENT OF cursor_name;例7.39 使用显式游标查询scott.emp表中的记录，并将工资低于3000的工资增加为原来的百分之二十。DECLARE CURSOR emp_cursor IS SELECT ename, sal FROM scott.emp FOR UPDATE;BEGIN FOR rec IN emp_cursor LOOP IF rec.sal10000 THENRAISE ex_insert; -用户自定义异常的触发ELSEINSER