第9章 数据类型、流程控制和游标.ppt

* END LOOP; CLOSE v_emp; OPEN my_cursor FOR SELECT * FROM dept; LOOP FETCH my_cursor INTO v_deptinfo; EXIT WHEN my_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_deptinfo.deptno|| || v_deptinfo.dname); END LOOP; CLOSE my_cursor; --OPEN v_general FOR SELECT empno,ename,sal,deptno FROM emp; END; * 要求根据输入的不同表名进行不同处理，若表名为emp，则显示高于10号部门平均工资的员工信息；若表名为dept，则显示各个部门的人数。 * DECLARE v_table CHAR(20); TYPE type_cursor IS REF CURSOR; v_cursor type_cursor; v_emp emp%ROWTYPE; v_deptno emp.deptno%TYPE; v_num NUMBER; BEGIN v_table:=table_name; IF v_table = emp THEN OPEN v_cursor FOR SELECT * FROM emp WHERE sal( SELECT AVG(sal) FROM emp WHERE deptno=10); ELSIF v_table = dept THEN OPEN v_cursor FOR SELECT deptno,count(*) num FROM emp GROUP BY deptno; ELSE RAISE_APPLICATION_ERROR(-20000,Input must be emp‘ or dept); END IF; * LOOP IF v_table = emp THEN FETCH v_cursor INTO v_emp; EXIT WHEN v_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_emp.empno|| || v_emp.ename|| || v_emp.sal|| || v_emp.deptno); ELSE FETCH v_cursor INTO v_deptno,v_num; EXIT WHEN v_cursor%NOTFOUND; DBMS_OUTPUT.PUT_LINE(v_deptno|| ||v_num); END IF; END LOOP; CLOSE v_cursor; END; * 2007-9 东北大学东软信息学院计算机系 * Fetch 游标名 bulk collect into 集合1，集合2，...[limit 行数] 2007-9 东北大学东软信息学院计算机系 * DECLARE increment number(4); CURSOR c_emp(p_deptno emp.deptno%type)IS SELECT * FROM EMP WHERE deptno = p_deptno; BEGIN for I in 1..3 loop for emp_record in c_emp(I*10) loop increment:=100*I; update emp set sal = sal +increment where EMPNO= emp_record.empno; end loop; end loop; END; * 检索游标 语法格式 FETCH cursor_name INT