Oracle游标 CURSOR实例详解
游标 CURSOR
一、游标概述
游标(cursor)是数据库系统在内存中开设的一个数据缓冲区,存放SQL语句的执行结果。
每个游标都有一个名字,用户可以用SQL语句逐一从游标中获取记录,并赋给变量做进一步处理。
作用:用于定位结果集的行 和 遍历结果集。
游标产生时间:当执行DML SQL语句时;
游标用于存放:结果集
游标有名字吗:有,SQL或用户给他取名
游标如何操作:用FETCH语句逐一从游标中提取记录,并赋给变量进一步处理
同时可打开几个游标:多个,具体数量由数据库初始化参数OPEN_CURSOR决定。
二、游标分类
游标分为:静态游标(隐式和显式)和REF游标(+游标变量)
1. 游标的属性
%FOUND SQL语句影响了一行或多行时为true;
%NOTFOUND SQL语句没有影响任何行时为true(常用,没找到为T,就退出)
%ROWCOUNT SQL语句影响的行数;
%ISOPEN 游标是否打开,始终为false。
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 显示游标
显示游标用于处理SELECT时返回多行的查询;
增删改时不会用显示游标;
显示游标需要手动的去做声明、打开、提取、关闭操作。
示例:
l 显示游标操作
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;
l 带参数的显示游标
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;
l 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),自动打开、提取、关闭。
l 带参数的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;
l 游标的嵌套问题
固名思义,就是在游标中嵌套一个游标。
示例:
列出所有部门的人员信息。
要求结果如下:
部门号: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;
3. REF游标
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. 标变量的优点和限制
4.1 游标变量功能强大,可以简化数据处理;
4.2 游标变量优点:
a.可从不同的select语句中提取结果集;
b.可以作为过程的参数进行传递;
c.可以引用游标的所有属性;
d.可以进行赋值运算;
4.3 游标变量的限制:
a.不能在程序包中声明游标变量;
b.for update子句不能与游标变量一起使用;
c.不能使用比较运算符。
三、动态语句拼接 :n
设定一个游标变量来执行动态的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;