欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识！
您现在的位置是： 首页

Oracle总结（二）：Oracle游标CURSOR

程序员文章站 2024-02-10 14:21:52
...

游标 CURSOR

1游标概述

  • 游标（cursor）是数据库系统在内存中开设的一个数据缓冲区，存放SQL语句的执行结果。
  • 每个游标都有一个名字，用户可以用SQL语句逐一从游标中获取记录，并赋给变量做进一步处理。
  • 作用：用于定位结果集的行 和 遍历结果集。
  • 游标产生时间：当执行DML SQL语句时；
  • 游标用于存放：结果集
  • 游标有名字吗：有，SQL或用户给他取名
  • 游标如何操作：用FETCH语句逐一从游标中提取记录，并赋给变量进一步处理
  • 同时可打开几个游标：多个，具体数量由数据库初始化参数OPEN_CURSOR决定。

2游标分类

  • 游标分为：静态游标（隐式和显式）和REF游标（+游标变量）

2.1游标的属性

  • %FOUND SQL语句影响了一行或多行时为true；
  • %NOTFOUND SQL语句没有影响任何行时为true(常用，没找到为T，就退出)
  • %ROWCOUNT SQL语句影响的行数；
  • %ISOPEN 游标是否打开，始终为false。

2.2静态游标

  • 静态游标是指结果集已经确实（静态定义）的游标。
  • 静态游标分：隐式游标和显示游标。

2.2.1隐式游标

在PL/SQL中隐式游标在执行DML SQL(Insert/Delete/Update/Select)语句时自动创建、自动声明、打开和关闭，其名SQL（注：所有的隐式游标名都叫“SQL”）；
示例：
使用游标的属性。

BEGIN
UPDATE emp SET sal=5000 WHERE empno=7369;
IF SQL%FOUND THEN
DBMS_OUTPUT.PUT_LINE( ‘表已更新’ );
END IF;
END;

提示：
在Java中，如对表进行了增删改操作，结果会返回n，表示在数据库中多少行受影响。
我们通过 n>0 或 n=0 来判断SQL代码是否执行成功。
这个n 即SQL%ROWCOUNT属性。

2.2.1显示游标

显示游标用于处理SELECT时返回多行的查询；
增删改时不会用显示游标；
显示游标需要手动的去做声明、打开、提取、关闭操作。
示例：

显示游标操作

DECLARE
--声明游标：划分存储区域，注意此时并没有执行Select语句。
CURSOR c_name IS
SELECT sal FROM emp WHERE empno=7369;
my_sal emp.sal%TYPE;
BEGIN
--打开游标：执行select语句，获得结果集存到游标中，此时游标指向结果集头，而不是第一条记录。
OPEN c_name;
--提取记录：移动游标取一条记录
FETCH c_name INTO my_sal;
DBMS_OUTPUT.PUT_LINE(my_sal);
--关闭游标：将游标放入缓冲池中，没有完全释放资源，可重新打开。
CLOSE c_name;
END;

带参数的显示游标

DECLARE
CURSOR c_name( dno NUMBER ) IS
SELECT * FROM emp WHERE deptno=dno;
my_a emp%ROWTYPE;
BEGIN
OPEN c_name(10); --打开游标，并传值
LOOP
FETCH c_name INTO my_a;
EXIT LOOP c_name%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( '名字：'||my_a.ename );
END LOOP;
CLOSE c_name;
END;

For循环游标

作用：简化游标处理代码（简化打开、提取、关闭）。
基本语法：
FOR r_index IN cursor_name LOOP
……
END LOOP;
示例：For循环游标操作

DECLARE
CURSOR c_name IS
SELECT * FROM emp;
BEGIN
FOR i IN c_name LOOP
DBMS_OUTPUT.PUT_LINE( i.ename||' '||i.job );
END LOOP;
END;

注释：如同foreach语句一样，i默认为行类型(%rowtype)，自动打开、提取、关闭。

带参数的For循环游标

DECLARE
CURSOR c_name( dno number ) IS
SELECT * FROM emp WHERE deptno=dno;
BEGIN
FOR i IN c_name(30) LOOP
DBMS_OUTPUT.PUT_LINE( i.ename||' '||i.job );
END LOOP;
END;

游标的嵌套问题

固名思义，就是在游标中嵌套一个游标。

示例：
列出所有部门的人员信息。
要求结果如下：
部门号：10 部门名称：****
编号：** 姓名：**
编号：** 姓名：**
……
部门号：20 部门名称：****
编号：** 姓名：**
编号：** 姓名：**
……
分析：
1.创建部门游标，先将部门信息列出来；
2.在循环取部门信息时，再创建打开员工表游标，进行读取。

DECLARE
CURSOR mydc IS
SELECT * FROM dept;
mydr dept%ROWTYPE;--声明dept部门表对象
CURSOR myec(dno NUMBER) IS
SELECT * FROM emp WHERE empno=dno;
myer emp%ROWTYPE;--声明emp员工表对象
BEGIN
--先打开部门结果集游标
OPEN mydc;
LOOP
FETCH mydc INTO mydr;
EXIT LOOP mydc%NOTFOUND;--提取完退出LOOP
DBMS_OUTPUT.PUT_LINE( '部门号：'||mydr.deptno||' 部门名称：'||mydr.dname );
--再打开员工结果集游标，入部门编号
OPEN myec(mydr.deptno);
LOOP
FETCH myec INTO myer;
EXIT LOOP myec%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(' 编号：'||myer.empno||' 姓名：'||myer.ename);
END LOOP;
CLOSE myec;
END LOOP;
CLOSE mydc;
END;
--使用For循环简化
DECLARE
CURSOR mydc IS
SELECT * FROM dept;
CURSOR myec(dno number) IS
SELECT * FROM emp WHERE empno=dno;
BEGIN
--遍历部门结果集游标
FOR i INT mydc LOOP
DBMS_OUTPUT.PUT_LINE( '部门号'||i.deptno||' 部门名称：'||i.dname );
--遍历员工结果集游标，传下部门编号
FOR j INT myec(i.detpno) LOOP
DBMS_OUTPUT.PUT_LINE( ' 编号'||j.empno||' 姓名：'||j.ename );
END LOOP;
END LOOP;
END;

3REF游标

REF游标也叫动态游标，动态SQL执行时产生。
REF游标更应该被称之为游标类型，而游标变量则为该类型的游标。

3.1创建REF游标+游标变量

基本语法：
–创建REF游标
TYPE ref_cursor_name IS REF CURSOR
[RETURN 返回类型];
–声明REF游标变量
游标变量 ref_cursor_name;
示例：
创建个REF游标类型
TYPE ref_name IS REF CURSOR; --定义弱游标类型，名字叫ref_name
c_name ref_name;

3.2 REF游标分类

强类型：带RETURN返回类型
弱类型：不带RETURN返回类型
相对来说，弱类型更为灵活。
示例：
声明强类型REF游标+游标变量
DECLARE
TYPE c_type IS REF CURSOR
RETURN emp%ROWTYPE;
c_name c_type;

3.3 打开游标变量

OPEN cursor_name FOR 查询结果集;
示例：用REF游标+游标变量显示数据

DECLARE
TYPE my_t IS REF CURSOR
RETURN emp%ROWTYPE; --声明一个游标类型
myc my_t; --定义一个REF类型的游标变量
myr emp%ROWTYPE;
BEGIN
OPEN myc FOR
SELECT * FROM emp;
LOOP
FETCH myc INTO myr;
EXIT WHEN myc%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(myr.ename);
END LOOP;
CLOSE myc;
END;

4标变量的优点和限制

  • 游标变量功能强大，可以简化数据处理；
  • 游标变量优点：
    a.可从不同的select语句中提取结果集；
    b.可以作为过程的参数进行传递；
    c.可以引用游标的所有属性；
    d.可以进行赋值运算；
  • 游标变量的限制：
    a.不能在程序包中声明游标变量；
    b.for update子句不能与游标变量一起使用；
    c.不能使用比较运算符。
    三、动态语句拼接
    设定一个游标变量来执行动态的sql语句。
    数据库管理员、设计师常用的知识点。
    基本语法：
    OPEN 游标名 FOR ‘SELECT … >:1 …’;
    USEING 变量名;
    示例1：
    OPEN cursor_name FOR
    ‘SELECT * FROM emp WHERE sal>:1 ORDER BY sal DESC’
    USEING p_sal;
    解释：
    –设置占位符：sal>:1动态拼接（相当于JDBC中的?占位符）
    –填充占位符：将变量p_sal的值代入SELECT中“:1”位置处
    –如有多个动态拼接处，请设置:2，:3，……，同时using 后面变量用逗号分隔。
    示例2：
DECLARE
r_emp emp%ROWTYPE;
--定义REF游标及游标变量
TYPE c_type IS REF CURSOR;
cur c_type;
p_salary NUMBER;
BEGIN
p_salary := 2500;
--打开游标
OPEN cur FOR
'select * from emp where sal>:1 order by sal desc'
--填充占位符
USING p_salary;
DBMS_OUTPUT.PUT_LINE('薪水大于'|| p_salary ||'的员工有：');
LOOP
--提取游标中的数据到变量r_emp
FETCH cur INTO r_emp;
EXIT WHEN cur%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('编号：'|| r_emp.empno||'姓名：'||r_emp.ename||'薪水：'||r_emp.sal);
END LOOP;
--关闭游标
CLOSE cur;
END;

上一篇： 获取相册照片和获取拍照照片

下一篇： android保存图片到本地并可以在相册中显示出来

推荐阅读