Oracle12C--显式游标(三十六)

知识点的梳理：

• 显式游标取出数据，使用的是FETCH...INTO

    

• 简介
  • 隐式游标是用户操作SQL时自动生成的，而显式游标是指在声明块中直接定义的游标；
  • 语法：显示语句必须明确定义出要使用的SQL查询语句，游标操作的也是查询语句返回的结果数据

cursor 游标名称([参数列表]) [return 返回值类型] is 子查询 [for update [of 数据列,数据列,] [NOWAIT] ];

• PL/SQL中显示游标的操作步骤：
  • 第一步：声明游标(cursor 游标名称 is 查询语句)。使用cursor定义；
  • 第二步：为查询打开游标（语法：OPEN游标名称）。使用OPEN操作，当游标打开时首先会检查绑定此游标的变量内容，之后再确定所使用的查询结果集，最后游标将指针指向结果集的第一行。如果用户定义的是一个带有参数的游标，则会在打开游标时为游标设置指定的参数值；
  • 第三步：取得结果放入PL/SQL变量中（语法：FETCH 游标名称 INTO ROWTYPE 变量）。使用循环和FETCH....INTO操作；
  • 第四步：关闭游标（语法： CLOSE 游标名称）。
• 显式游标有4个基本属性：
  • 
• 补充：在定义游标时声明游标返回类型
  • 定义游标时，可以使用RETURN来明确游标的操作类型；
  • 示例：
    • 
      

CURSOR cur_emp RETURN emp%ROWTYPE IS SELECT * FROM emp ;

• 如果不屑RETURN，表示其对应类型为查询语句返回的数据行类型。例如，如果查询的是emp表，则游标的RETURN类型为emp，如果查询的是dept表，则游标的RETURN类型为dept，而如果明确写上了RETURN emp%ROWTYPE，则表示后面跟的子查询的返回结构只能是emp行结构；
• 此语句很少使用；

• 举个栗子
  • 示例1：定义显式游标

DECLARE CURSOR cur_emp IS SELECT * FROM emp ; v_empRow emp%ROWTYPE ; BEGIN IF cur_emp%ISOPEN THEN -- 游标已经打开 NULL ; ELSE -- 游标未打开 OPEN cur_emp ; -- 打开游标 END IF ; FETCH cur_emp INTO v_empRow ; -- 取出游标当前行数据 WHILE cur_emp%FOUND LOOP -- 判断是否有数据 DBMS_OUTPUT.put_line(cur_emp%ROWCOUNT || '、雇员姓名：' || v_empRow.ename || '，职位：' || v_empRow.job || '，工资：' || v_empRow.sal) ; FETCH cur_emp INTO v_empRow ; -- 把游标指向下一行 END LOOP ; CLOSE cur_emp ; -- 关闭游标 END ; /

运行结果： 1、雇员姓名：SMITH，职位：CLERK，工资：10800 2、雇员姓名：ALLEN，职位：SALESMAN，工资：1920 3、雇员姓名：WARD，职位：SALESMAN，工资：1500 4、雇员姓名：JONES，职位：MANAGER，工资：10800 5、雇员姓名：MARTIN，职位：SALESMAN，工资：1500 6、雇员姓名：BLAKE，职位：MANAGER，工资：3420 7、雇员姓名：CLARK，职位：MANAGER，工资：2940 8、雇员姓名：SCOTT，职位：ANALYST，工资：10800 9、雇员姓名：KING，职位：PRESIDENT，工资：10800 10、雇员姓名：TURNER，职位：SALESMAN，工资：1800 11、雇员姓名：ADAMS，职位：CLERK，工资：1320 12、雇员姓名：JAMES，职位：CLERK，工资：1140 13、雇员姓名：FORD，职位：ANALYST，工资：10800 14、雇员姓名：MILLER，职位：CLERK，工资：1560

流程图：

分析： 本程序在声明部分定义了一个显式游标，在进行游标操作之前，首先判断游标是否已经打开，如果没有打开，则使用OPEN打开游标，之后使用FETCH取得游标中的一行数据，并将这行数据的内容设置在v_empRow变量中。如果此时数据存在，则使用WHILE循环继续取出游标中的下一条数据记录，并且使用FETCH改变v_empRow变量的内容 总结： 对于显式游标的操作属性，是需要用户定义其调用顺序的，而不像隐式游标一样，所有的操作属性内容都是固定的。

• 示例2：游标操作前必须打开，关闭后的游标也不可再用。
  • 没有打开游标直接进行操作

DECLARE CURSOR cur_emp IS SELECT * FROM emp ; v_empRow emp%ROWTYPE ; BEGIN LOOP -- 没有打开游标 FETCH cur_emp INTO v_empRow ; -- 取出游标当前行数据 EXIT WHEN cur_emp%NOTFOUND ; -- 如果没有找到数据则退出循环 DBMS_OUTPUT.put_line(cur_emp%ROWCOUNT || '、雇员姓名：' || v_empRow.ename) ; END LOOP ; CLOSE cur_emp ; -- 关闭游标 EXCEPTION WHEN INVALID_CURSOR THEN DBMS_OUTPUT.put_line('程序出错。SQL CODE = ' || SQLCODE || '，SQLERRM = ' || SQLERRM) ; END ; /

运行结果： 程序出错。SQL CODE = -1001，SQLERRM = ORA-01001: 无效的游标

• 示例3：使用LOOP循环输出游标

DECLARE CURSOR cur_emp IS SELECT * FROM emp ; v_empRow emp%ROWTYPE ; BEGIN IF cur_emp%ISOPEN THEN -- 游标打开 NULL ; ELSE -- 游标未打开 OPEN cur_emp ; -- 打开游标 END IF ; LOOP FETCH cur_emp INTO v_empRow ; -- 取出游标当前行数据 EXIT WHEN cur_emp%NOTFOUND ; -- 如果没有找到数据则退出循环 DBMS_OUTPUT.put_line(cur_emp%ROWCOUNT || '、雇员姓名：' || v_empRow.ename || '，职位：' || v_empRow.job || '，工资：' || v_empRow.sal) ; END LOOP ; CLOSE cur_emp ; -- 关闭游标 END ;    运行结果： 1、雇员姓名：SMITH，职位：CLERK，工资：10800 2、雇员姓名：ALLEN，职位：SALESMAN，工资：1920 3、雇员姓名：WARD，职位：SALESMAN，工资：1500 4、雇员姓名：JONES，职位：MANAGER，工资：10800 5、雇员姓名：MARTIN，职位：SALESMAN，工资：1500 6、雇员姓名：BLAKE，职位：MANAGER，工资：3420 7、雇员姓名：CLARK，职位：MANAGER，工资：2940 8、雇员姓名：SCOTT，职位：ANALYST，工资：10800 9、雇员姓名：KING，职位：PRESIDENT，工资：10800 10、雇员姓名：TURNER，职位：SALESMAN，工资：1800 11、雇员姓名：ADAMS，职位：CLERK，工资：1320 12、雇员姓名：JAMES，职位：CLERK，工资：1140 13、雇员姓名：FORD，职位：ANALYST，工资：10800 14、雇员姓名：MILLER，职位：CLERK，工资：1560

分析：LOOP循环与WHILE循环最大的区别是，LOOP循环首先会执行一次循环体内容。所以本程序只需要在LOOP语句中使用FETCH取得当前游标数据，之后判断此数据是否存在，如果存在则输出，如果不存在则结束LOOP循环 程序运行图：

• 示例4：WHILE循环与LOOP循环，都需要人工打开或关闭游标。而FOR循环则由系统自动为用户打开和关闭游标

DECLARE CURSOR cur_emp IS SELECT * FROM emp ; BEGIN FOR emp_row IN cur_emp LOOP DBMS_OUTPUT.put_line(cur_emp%ROWCOUNT || '、雇员姓名：' || emp_row.ename || '，职位：' || emp_row.job || '，工资：' || emp_row.sal) ; END LOOP ; END ; / 运行结果： 1、雇员姓名：SMITH，职位：CLERK，工资：10800 2、雇员姓名：ALLEN，职位：SALESMAN，工资：1920 3、雇员姓名：WARD，职位：SALESMAN，工资：1500 4、雇员姓名：JONES，职位：MANAGER，工资：10800 5、雇员姓名：MARTIN，职位：SALESMAN，工资：1500 6、雇员姓名：BLAKE，职位：MANAGER，工资：3420 7、雇员姓名：CLARK，职位：MANAGER，工资：2940 8、雇员姓名：SCOTT，职位：ANALYST，工资：10800 9、雇员姓名：KING，职位：PRESIDENT，工资：10800 10、雇员姓名：TURNER，职位：SALESMAN，工资：1800 11、雇员姓名：ADAMS，职位：CLERK，工资：1320 12、雇员姓名：JAMES，职位：CLERK，工资：1140 13、雇员姓名：FORD，职位：ANALYST，工资：10800 14、雇员姓名：MILLER，职位：CLERK，工资：1560

综上所述，FOR循环因为将游标的开启关闭状态交由管理且语法简练，应是首选

• 示例5：利用FOR循环直接输出查询结果

BEGIN FOR v_dept IN (SELECT deptno,dname,loc FROM dept) LOOP DBMS_OUTPUT.put_line('部门编号：' || v_dept.deptno || '，名称：' || v_dept.dname || '，位置：' || v_dept.loc) ; END LOOP ; END ; /

运行结果： 部门编号：10，名称：ACCOUNTING，位置：NEW YORK 部门编号：20，名称：RESEARCH，位置：DALLAS 部门编号：30，名称：SALES，位置：CHICAGO 部门编号：40，名称：OPERATIONS，位置：BOSTON

• 示例6：上面的例子，都是将游标取得的数据直接输出，下面将游标数据保存到索引表中，随后可利用索引下标进行指定数据的访问；

DECLARE CURSOR cur_emp IS SELECT * FROM emp ; -- 定义游标取得emp表数据 TYPE emp_index IS TABLE OF emp%ROWTYPE INDEX BY PLS_INTEGER ;-- 定义索引表数据类型为emp行结构 v_emp emp_index ; -- 定义索引表变量 BEGIN FOR emp_row IN cur_emp LOOP -- 利用循环取得每一行记录 v_emp(emp_row.empno) := emp_row ; -- 将雇员编号作为索引表下标 END LOOP ; DBMS_OUTPUT.put_line('雇员编号：' || v_emp(7369).empno || '，姓名：' || v_emp(7369).ename || '，职位：' || v_emp(7369).job) ; END ; /

运行结果： 雇员编号：7369，姓名：SMITH，职位：CLERK 分析： 本sql在声明部分定义了一个游标，此游标负责额取得emp表中的全部记录，同时也定义了一个与emp表行结构相同的索引表类型。在程序主体部分将每一行的记录保存在索引表中，这样就可以利用索引表的下标取得要操作的数据了；

• 示例7：例6以上的例子都是静态select语句下的游标操作，下面是在动态select中使用

DECLARE v_lowsal emp.sal%TYPE := &inputlowsal ; v_highsal emp.sal%TYPE := &inputhighsal ; CURSOR cur_emp IS SELECT * FROM emp WHERE sal BETWEEN v_lowsal AND v_highsal ; BEGIN FOR emp_row IN cur_emp LOOP DBMS_OUTPUT.put_line(cur_emp%ROWCOUNT || '、雇员姓名：' || emp_row.ename || '，职位：' || emp_row.job || '，工资：' || emp_row.sal) ; END LOOP ; END ; /

输入参数：2000，3000 运行结果： 1、雇员姓名：CLARK，职位：MANAGER，工资：2940

• 示例8：定义参数游标

DECLARE CURSOR cur_emp (p_dno emp.deptno%TYPE) IS SELECT * FROM emp WHERE deptno=p_dno; BEGIN FOR emp_row IN cur_emp(&inputDeptno) LOOP DBMS_OUTPUT.put_line(cur_emp%ROWCOUNT || '、雇员姓名：' || emp_row.ename || '，职位：' || emp_row.job || '，工资：' || emp_row.sal) ; END LOOP ; END ; / 输入参数：10 运行结果： 1、雇员姓名：CLARK，职位：MANAGER，工资：2940 2、雇员姓名：KING，职位：PRESIDENT，工资：10800 3、雇员姓名：MILLER，职位：CLERK，工资：1560

分析： 本程序定义的游标是按照部门查询出全部的雇员信息，但是部门编号需要由用户自己输入，所以游标定义时就定义了一个dno的参数，而在循环游标时，根据用户输入的结构查询数据

• 示例9：使用可变数组，来限定每次取得的游标数量，这种操作通过，"FETCH BULK COLLECT INTO LIMIT"语句完成

DECLARE TYPE dept_varray IS VARRAY(2) OF dept%ROWTYPE ; v_dept dept_varray ; v_rows NUMBER := 2 ; -- 每次提取的行数 v_count NUMBER := 1 ; -- 每次少显示1条记录 CURSOR cur_dept IS SELECT * FROM dept ; -- 定义游标 BEGIN IF cur_dept%ISOPEN THEN -- 游标已经打开 NULL ; ELSE -- 游标未打开 OPEN cur_dept ; -- 打开游标 END IF ; FETCH cur_dept BULK COLLECT INTO v_dept LIMIT v_rows ; -- 保存指定行数 CLOSE cur_dept ; -- 关闭游标 FOR x IN v_dept.FIRST .. (v_dept.LAST - v_count) LOOP DBMS_OUTPUT.put_line('部门编号：' || v_dept(x).deptno || '，部门名称：' || v_dept(x).dname || '，部门位置：' || v_dept(x).loc) ; END LOOP ; END ; /

运行结果： 部门编号：10，部门名称：ACCOUNTING，部门位置：NEW YORK 分析： 可变数组默认开辟的大小为2，即最多只能保存两行游标数据，所以在程序主体部分使用FETCH cur_dept BULK COLLECT INTO v_dept LIMIT v_rows语句操作时限定了只能取得两行记录。 此程序的主要功能是从取得的两个数据中取得第一行，所以在使用FOR循环输出时采用了一个v_dept.LAST-v_count控制循环语句，通过此操作可以让某些数据不被现实。