oracle游标的使用.ppt

第十二章 光标的使用,光标的概念,Oracle 光标是一种用于轻松的处理多行数据的机制。 当 PL/SQL 光标查询返回多行数据时，这些记录组被称为结果集。 Oracle 将这种活动集存储在您创建的显示定义的已命名的光标中。 没有光标， Oracle 开发人员必须单独地、显式地取回并管理光标查询选择的每一条记录。,12.1 光标的定义与使用,显式光标处理需四个 PL/SQL步骤: cursor 光标名称 is 查询语句; open 光标名称; Fetch 光标名称 into 变量列表; Close 光标名称;,12.1 光标的定义与使用,例1. declare cursor c1 is select ename, sal from emp where rownum11; v_ename varchar2(10); v_sal number(7,2); begin open c1; fetch c1 into v_ename, v_sal; while c1%found loop dbms_output.put_line(v_ename|to_char(v_sal) ); fetch c1 into v_ename, v_sal; end loop; close c1; end;,12.1 光标的定义与使用,2.光标属性 %found 布尔型属性,当最近一次读记录时成功返回,则值为true %nofound 布尔型属性,与%found相反. %isopen 布尔型属性, 当光标已打开时返回 true. %rowcount 数字型属性, 返回已从光标中读取得记录数.,12.1 光标的定义与使用,3.参数化光标 在声明光标时，将未确定的参数说明成变量，在使用光标时给出光标变量的具体值（相当于实参）使得语句可以按照给出的条件进行查询。,12.1 光标的定义与使用,例2： DECLARE Cursor c1 (view_pattern varchar2) IS Select view_name from all_views Where view_name like view_pattern|% AND rownum = 10 Order by view_name ; Vname varchar2(40); BEGIN For I1 in c1 ( DBA) loop DBMS_OUTPUT.PUT_LINE( I1.view_name ) ; END loop; END;,12.1 光标的定义与使用,处理隐式光标 所有的SQL 语句在上下文区内部都是可执行的，因此都有一个光标指向上下文区，此光标就是所谓的SQL光标(SQL cursor)，与显式光标不同，SQL 光标不被程序打开和关闭。,12.1 光标的定义与使用,例3: -节自在线代码 nomat1.sql BEGIN UPDATE rooms SET number_seats = 100 WHERE room_id = 99980; - 如果更新没有匹配则插入一新行 IF SQL%NOTFOUND THEN INSERT INTO rooms ( room_id, number_seats ) VALUES ( 99980, 100 ) ; END IF; END;,12.1 光标的定义与使用,例4: -节自在线代码 nomat2.sql BEGIN UPDATE rooms SET number_seats = 100 WHERE room_id = 99980; - 如果更新没有匹配则插入一新行 IF SQL%ROWCOUNT = 0 THEN INSERT INTO rooms ( room_id, number_seats ) VALUES ( 99980, 100 ) ; END IF; END;,12.1 光标的定义与使用,例5: -节自在线代码 nodata.sql DECLARE V_roomdata rooms%ROWTYPE; BEGIN Select * into v_roomdata from rooms Where room_id = -1 ; IF SQL%NOFOUND THEN INSERT INTO temp_table ( char_col ) VALUES ( Not Found ! ) ; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN INSERT INTO temp_table ( char_col ) VALUES ( Not Found ! exception handle. ) ; END;,12.2 光标循环,在高级语言编程和 PL/SQL 编程中，经常使用“提取循环(fetch loop)” 来实现逐行提取所需数据的方法。,12.2 光标循环,LOOP 和END LOOP来实现简单循环。 DECLARE V_studentID students.id%TYPE; V_firstName students.first_name%TYPE; V_LastName students.last_name%TYPE; CURSOR c_HistoryStudents IS SELECT id, first_name, last_name from students WHERE major = History;,BEGIN Open c_HistoryStudents ; LOOP FETCH c_HistoryStudents INTO v_studentID, v_FirstName, V_LastName ; EXIT WHEN c_HistoryStudents%NOFOUND ; INSERT INTO registered_students(student_id, department,course ) VALUES ( v_StudentsID,HIS,301 ); INSERT INTO temp_table ( num_col, char_col ) VALUES ( v_StudentID, v_FirstName| | v_lastName ) ; END LOOP; CLOSE c_HistoryStudents; COMMIT; END;,12.2 光标循环,WHILE循环 与简单循环不一样的是，while 循环是先判断后执行。只有满足条件才能执行其循环体内的语句；而简单循环至少执行一次。 declare v_ename emp.ename%type; v_empno emp.empno%type; v_sal emp.sal%type; v_rownum integer; cursor c_sal is select empno,ename,sal from emp where sal=3000;,begin open c_sal; v_rownum:=c_sal%rowcount; dbms_output.put_line(to_char(v_rownum); fetch c_sal INTO v_empno,v_ename,v_sal; while c_sal%found loop dbms_output.put_line(员工信息为：|to_char(v_empno)| |v_ename|的工资为：|to_char(v_sal); fetch c_sal INTO v_empno,v_ename,v_sal; /*exit when c_sal%notfound;*/ end loop;,12.2 光标循环,v_rownum:=c_sal%rowcount; dbms_output.put_line(to_char(v_rownum); close c_sal; end; 最主要的作用： 在前台的开发环境下，利用游标变量的值进行数据处理。,12.2 光标循环,12.2 光标循环,12.2 光标循环,12.2.3 光标 FOR 循环 DECLARE CURSOR c_HistoryStudents IS SELECT id, first_name,last_name FROM students WHERE major = History; BEGIN,FOR v_StudentData IN c_HistoryStudents LOOP INSERT INTO registered_students(student_id, department,course ) VALUES ( v_StudentsID,HIS,301 ); INSERT INTO temp_table ( num_col, char_col ) VALUES ( v_StudentID, v_FirstName| | v_lastName ) ; - 循环前隐含检查 %NOFOUND END LOOP; - 自动关闭光标 COMMIT; END;,12.2 光标循环,关于NO_DATA_FOUND和%NOTFOUND SELECT . . . INTO 语句触发 NO_DATA_FOUND； 当一个显式光标的 where 子句未找到时 触发 %NOFOUND； 当UPDATE或DELETE 语句的where 子句未找到时 触发 SQL%NOTFOUND； 在光标的提取(Fetch)循环中要用 %NOTFOUND 或%FOUND 来确定循环的退出条件，不要用 NO_DATA_FOUND。,12.3 光标变量,前面所给出光标都是显式的光标，而且是静态的光标。 Oracle从PL/SQL Release2.2版以后，声明的光标可以不是静态的，光标变量就是允许开发人员编写的程序在运行时与不同的语句相关联。 光标变量在运行可以取不同的值。以达到灵活的目的。,12.3 光标变量,声明光标变量 TYPE type_name IS REF CURSOR RETURN return_type; 为光标变量分配存储空间 EXEC SQL ALLCATE :variable 打开光标变量 OPEN cursor_variable FOR select_statements; 关闭光标变量 CLOSE cursor_variable;,实例见教材p118,学生训练案例,编写完成如下功能的pl/sql程序（要求用游标完成）。 1、按部门编号从小到大的顺序输出雇员名字、工资以及工资与平均工资的差。 2、已知每个部门有一个经理，编写程序，统计输出部门名称、部门总人数、总工资和部门经理。 3、为所有雇员增加工资，工资在1000以内的增加30%，工资在10002000之间的增加20%，2000以上的增加10%。,学生训练案例,4、用游标for循环完成如下功能的pl/sql程序： CLERK和manager是emp表中job列的2个属性值，求出职员为CLERK和manager的平均工资，并输出每个职员的工资及与其所属job的平均工资的差值。 注意： 游标for循环示显示游标的一种快捷使用方式，它使用for循环依次读取结果集中的行数据，当form循环开始时，游标自动打开（不需要open），每循环一次系统自动读取游标当前行的数据（不需要fetch)，当退出for循环时，游标被自动关闭（不需要使用close）。使用游标for循环的时候不能使用open语句，fetch语句和close语句，否则会产生错误。,学生训练案例,例如： DECALRE CURSOR c_dept IS SELECT deptno,dname FROM dept ORDER BY deptno; CURSOR c_emp (p_dept VARACHAR2) IS SELECT ename,salary FROM emp WHERE deptno=p_dept ORDER BY ename v_tot_salary EMP.SALARY%TYPE;,学生训练案例,BEGIN FOR r_dept IN c_dept LOOP DBMS_OUTPUT.PUT_LINE(Department:| r_d