1、第10章 游标,黑龙江大学计算机科学技术学院,2,游 标,游标的概念及类型 显式游标 隐式游标,3,游标的概念,游标的概念使用游标的原因 对表进行操作的T_SQL语句通常都可产生或处理一组记录,但是许多应用程序(如C,VB,PB等)通常不能把结果集作为一个单元来处理,因此这些环境要求保证每次处理结果集中的一行或几行.游标（CURSOR）是Oracle系统在内存中开辟的一个工作区，在其中存放SELECT语句返回的查询结果。使用游标时，SELECT语句查询的结果可以是单条记录，多条记录，也可以是零条记录。游标提供了对结果集进行逐行处理的能力.游标工作区中，存在着一个指针（POINTER）,在初始状
2、态它指向查询结果的首记录,可以指向结果集的任意位置,4,游标的类型,显式游标由用户定义、操作，用于处理返回多行数据的SELECT查询。隐式游标由系统自动进行操作，用于处理DML语句和返回单行数据的SELECT查询。,5,显式游标,显式游标的操作过程显式游标的属性参数化显式游标显式游标的检索利用游标更新或删除数据,显示游标的使用过程,7,定义游标,语法格式CURSOR cursor_name IS select_statement ;说明游标必须在PL/SQL块的声明部分进行定义；游标定义时可以引用PL/SQL变量，但变量必须在游标定义之前定义；定义游标时并没有生成数据，只是将定义信息保存到数据
3、字典中；游标定义后，可以使用cursor_name%ROWTYPE定义游标类型变量。,例如：DECLARE CURSOR XS_CUR1 IS SELECT XH,XM,CJ FROM XS WHERE ZYM=计算机; v_xs xs%rowtype；,8,提取游标,语法格式FETCH cursor_name INTO variable_list | record_variable; 说明在使用FETCH语句之前必须先打开游标对游标第一次使用FETCH语句时，游标指针指向第一条记录，因此操作的对象是第一条记录，使用后，游标指针指向下一条记录。游标指针只能向下移动，不能回退INTO子句中的变量
4、个数、顺序、数据类型必须与工作区中每行记录的字段数、顺序以及数据类型一一对应。,DECLAREv_xh char(6); v_zxf number(2);CURSOR XS_CUR3ISSELECT XH,ZXF FROM XSWHERE ZYM=计算机;BEGINOPEN XS_CUR3;FETCH XS_CUR3 INTO v_xh,v_zxf;WHILE XS_CUR3%FOUNDLOOPdbms_output.put_line(v_xh|to_char(v_zxf);FETCH XS_CUR3 INTO v_xh,v_zxf;END LOOP;CLOSE XS_CUR3;END;,9,
5、关闭游标,语法格式 CLOSE cursor_name; 说明游标所对应的内存工作区变为无效，释放与游标相关的系统资源。,10,显式游标的属性,CURSOR_NAME%ISOPEN布尔型。如果游标已经打开，返回TRUE,否则为FALSE。CURSOR_NAME%FOUND布尔型，如果最近一次使用FETCH语句，有返回结果则为TRUE,否则为FALSE;CURSOR_NAME%NOTFOUND布尔型，如果最近一次使用FETCH语句,没有返回结果则为TRUE,否则为FALSE;CURSOR_NAME%ROWCOUNT数值型，返回到目前为止从游标缓冲区检索的元组数。,显式游标的属性,说明：1. 如果
6、试图打开一个已打开的游标或关闭一个已关闭的游标，将会出现错误。2. 因此用户在打开或关闭游标前，若不清楚其状态，应该用%ISOPEN进行检查。根据返回值为TRUE或FALSE，采取相应的动作。例如：IF mycur%ISOPEN THENFETCH mycur INTO v_kch,v_cj; /*游标已打开，可以操作*/ELSEOPEN mycur; /*游标没有打开，先打开游标*/ END IF;,12,根据输入的部门号查询某个部门的员工信息，部门号在程序运行时指定。,无参数游标举例,DECLARE v_deptno emp.deptno%TYPE; CURSOR c_emp IS SEL
7、ECT * FROM emp WHERE deptno=v_deptno; v_emp c_emp%ROWTYPE;BEGIN v_deptno:=,13,参数化显式游标,参数化游标定义语法格式：CURSOR cursor_name(parameter1 datatype,parameter2 datatype) IS select_statement 打开参数化游标的方法OPEN cursor_name(parameter1,parameter2),14,参数化显式游标,注意：定义游标时，只能指定参数的类型，而不能指定参数的长度、精度、刻度；打开带参数的游标时，实参的个数和数据类型等必须与游
8、标定义时形参个数和数据类型等相匹配。,15,显式游标的检索,利用简单循环检索游标利用WHILE循环检索游标利用FOR循环检索游标,16,利用简单循环检索游标,DECLARE CURSOR cursor_name IS SELECT;BEGIN OPEN cursor_name; LOOP FETCHINTO; EXIT WHEN cursor_name%NOTFOUND; END LOOP; CLOSE cursor_name;END;,17,DECLARE CURSOR c_emp(p_deptno emp.deptno%TYPE) IS SELECT * FROM emp WHERE de
9、ptno=p_deptno; v_emp c_emp%ROWTYPE;BEGIN OPEN c_emp(10); LOOP FETCH c_emp INTO v_emp; EXIT WHEN c_emp%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_emp.empno| |v_emp.ename); END LOOP; CLOSE c_emp; OPEN c_emp(20); LOOP FETCH c_emp INTO v_emp; EXIT WHEN c_emp%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_emp.empno| |v_emp.enam
10、e); END LOOP; CLOSE c_emp; END;,EMP表, DEPT 表,19,DECLARE CURSOR c_dept_stat IS SELECT deptno,avg(sal) avgsal FROM emp GROUP BY deptno; v_dept c_dept_stat%ROWTYPE;BEGIN OPEN c_dept_stat; LOOP FETCH c_dept_stat INTO v_dept; EXIT WHEN c_dept_stat%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_dept.deptno| |v_dept.avg
11、sal); END LOOP; CLOSE c_dept_stat;END;,利用简单循环统计并输出各个部门的平均工资。,20,利用WHILE循环检索游标,DECLARE CURSOR cursor_name IS SELECT;BEGIN OPEN cursor_name; FETCHINTO; WHILE cursor_name%FOUND LOOP FETCHINTO; END LOOP; CLOSE cursor;END;,21,DECLARE CURSOR c_dept_stat IS SELECT deptno,avg(sal) avgsal FROM emp GROUP BY d
12、eptno; v_dept c_dept_stat%ROWTYPE;BEGIN OPEN c_dept_stat; FETCH c_dept_stat INTO v_dept; WHILE c_dept_stat%FOUND LOOP DBMS_OUTPUT.PUT_LINE(v_dept.deptno| |v_dept.avgsal); FETCH c_dept_stat INTO v_dept; END LOOP; CLOSE c_dept_stat;END;,利用WHILE循环统计并输出各个部门的平均工资。,22,利用FOR循环检索游标,DECLARE CURSOR cursor_nam
13、e IS SELECT;BEGIN FOR loop_variable IN cursor_name LOOP END LOOP;END;,23,FOR循环说明,系统隐含地定义了一个数据类型为%ROWTYPE的变量，并以此作为循环的计算器。系统自动打开游标，不用显式地使用OPEN语句打开；系统重复地自动从游标工作区中提取数据并放入计数器变量中。系统自动进行%FOUND属性检查以确定是否有数据当游标工作区中所有的记录都被提取完毕或循环中断时，系统自动地关闭游标。,24,利用FOR循环统计并输出各个部门的平均工资。,DECLARE CURSOR c_dept_stat IS SELECT dept
14、no,avg(sal) avgsal FROM emp GROUP BY deptno; v_dept c_dept_stat%ROWTYPE;BEGIN FOR v_dept IN c_dept_stat LOOP DBMS_OUTPUT.PUT_LINE(v_dept.deptno| |v_dept.avgsal); END LOOP; END;,25,BEGINFOR v_emp IN (select * from emp where deptno=10) LOOP DBMS_OUTPUT.PUT_LINE(v_emp.empno| |v_emp.ename);END LOOP;END;
15、,隐式FOR游标,26,利用游标更新或删除数据,游标定义语法 CURSOR cursor_name IS SELECT select_list_item FROM table FOR UPDATE 更新或修改数据的语法为： UPDATE|DELETE WHERE CURRENT OF cursor_name 注意 由于COMMIT语句会释放会话拥有的任何锁，因此如果在检索游标的循环内使用COMMIT语句会释放定义游标时对数据加的锁，从而导致利用游标修改或删除数据的操作失败。,27,DECLARE CURSOR c_emp IS SELECT * FROM emp FOR UPDATE; v_i
16、ncrement NUMBER;BEGIN FOR v_emp IN c_emp LOOP CASE v_emp.deptno WHEN 10 THEN v_increment:=100; WHEN 20 THEN v_increment:=150; WHEN 30 THEN v_increment:=200; ELSE v_increment:=250; END CASE; UPDATE emp SET sal=sal+v_increment WHERE CURRENT OF c_emp; END LOOP;END;,修改员工工资，如果员工的部门号为10，工资提高100；部门号为20，工资提
17、高150；部门号为30，工资提高200；否则工资提高250。,处理隐式游标,说明：而对于非查询语句，如修改、删除操作，则由ORACLE 系统自动地为这些操作设置游标并创建其工作区，这些由系统隐含创建的游标称为隐式游标，隐式游标的名字为SQL，这是由ORACLE 系统定义的。对于隐式游标的操作，如定义、打开、取值及关闭操作，都由ORACLE 系统自动地完成，无需用户进行处理。用户只能通过隐式游标的相关属性，来完成相应的操作。在隐式游标的工作区中，所存放的数据是与用户自定义的显示游标无关的、最新处理的一条SQL 语句所包含的数据。INSERT, UPDATE, DELETE, SELECT INT
18、O语句中不必明确定义游标,29,隐式游标,用于处理INSERT、UPDATE、DELETE和SELECTINTO语句没有OPEN、FETCH、CLOSE命令属性 SQL%ISOPENSQL%FOUNDSQL%NOTFOUND SQL%ROWCOUNT,30,修改员工号为1000的员工工资，将其工资增加100。如果该员工不存在，则向emp表中插入一个员工号为1000，工资为1500的员工。BEGINUPDATE emp SET sal=sal+100 WHERE empno=1000;IF SQL%NOTFOUND THEN INSERT INTO emp(empno,sal) VALUES(1
19、000,1500);END IF;END;或BEGINUPDATE emp SET sal=sal+100 WHERE empno=1000;IF SQL%ROWCOUNT=0 THEN INSERT INTO emp(empno,sal) VALUES(1000,1500);END IF;END;,处理隐式游标,例：删除EMP 表中20号部门的所有员工，如果该部门中已没有员工可删除，则在DEPT 表中删除该部门 DECLAREV_deptno emp.deptno%TYPE :=20;BEGIN DELETE FROM emp WHERE deptno=v_deptno; IF SQL%NOTFOUND THENDELETE FROM dept WHERE deptno= 20; END IF;END;,
