您的位置: 首页  >  文章  >  Oracle12C--显式游标(三十六)

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

分类: 文章 • 2024-07-04 16:55:58

知识点的梳理：

  • 显式游标取出数据，使用的是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个基本属性：
    • Oracle12C--显式游标(三十六)
  • 补充：在定义游标时声明游标返回类型
    • 定义游标时，可以使用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

流程图：

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

分析：
本程序在声明部分定义了一个显式游标，在进行游标操作之前，首先判断游标是否已经打开，如果没有打开，则使用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循环

程序运行图：

Oracle12C--显式游标(三十六)
  • 示例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控制循环语句，通过此操作可以让某些数据不被现实。

相关推荐