Oracle游标使用详解

栏目: 数据库 · Oracle · 发布时间: 5年前

内容简介:本节对Oracle中的游标进行详细讲解。本节所举实例来源Oracle中scott用户下的emp表dept表:一、游标:

本节对Oracle中的游标进行详细讲解。本节所举实例来源Oracle中scott用户下的emp表dept表:

Oracle游标使用详解 Oracle游标使用详解

一、游标:

1、概念:

游标的本质是一个结果集resultset,主要用来临时存储从数据库中提取出来的数据块。

二、游标的分类:

1、显式游标:由用户定义,需要的操作:定义游标、打开游标、提取数据、关闭游标,主要用于对查询语句的处理。

属性:%FOUND        %NOTFOUND        %ISOPEN          %ROWCOUNT

Example:打印emp表的员工信息

DECLARE

CURSOR emp_cursor IS SELECT empno,ename,job FROM emp;

v_empno emp.empno%TYPE;

v_name emp.ename%TYPE;

v_job emp.job%TYPE;

BEGIN

OPEN emp_cursor;

LOOP

FETCH emp_cursor INTO v_empno,v_name,v_job;

DBMS_OUTPUT.PUT_LINE('员工号为:'||v_empno||'姓名是'||v_name||'职位:'||v_job);

EXIT WHEN emp_cursor%NOTFOUND;

END LOOP;

CLOSE emp_cursor;

END;

这里严格按照显示游标的书写规则:DECLARE emp_cursor定义游标OPEN emp_cursor打开游标FETCH emp_cursor INTO...提取数据CLOSE emp_cursor关闭游标,因为提取出来的数据属于多行,所以通过loop循环打印即可。

Example2:检验游标是否打开,如果打开显示提取行数

DECLARE

CURSOR emp_cursor IS SELECT empno,ename,job FROM emp;

v_empno emp.empno%TYPE;

v_name emp.ename%TYPE;

v_job emp.job%TYPE;

BEGIN

OPEN emp_cursor;

LOOP

FETCH emp_cursor INTO v_empno,v_name,v_job;

EXIT WHEN emp_cursor%NOTFOUND;

END LOOP;

IF emp_cursor%ISOPEN THEN

DBMS_OUTPUT.PUT_LINE('游标已打开');

DBMS_OUTPUT.PUT_LINE('读取了'||emp_cursor%ROWCOUNT||'行');

ELSE

DBMS_OUTPUT.PUT_LINE('游标没有打开');

END IF; 

CLOSE emp_cursor;

END;

通过%ISOPEN属性判断游标是否打开,%ROWCOUNT判断获取行数。

2、隐式游标:由系统定义并为它创建工作区域,并且隐式的定义打开提取关闭,隐式游标的游标名就是'SQL',属性和显示游标相同,主要用于对单行select语句或dml操作进行处理。

Example:又用户输入员工号修改员工工资如成功则打印输出成功标志。

为了尽量不改变原表,创建新表emp_new和原表数据相同:

CREATE TABLE emp_new

AS

SELECT * FROM emp;

BEGIN

UPDATE emp_new SET sal = sal+500 WHERE empno=&empno;

IF SQL%FOUND THEN

DBMS_OUTPUT.PUT_LINE('成功修改');

COMMIT;

ELSE

DBMS_OUTPUT.PUT_LINE('修改失败');

ROLLBACK;

END IF;

END;

Oracle游标使用详解

Oracle游标使用详解

这里注意增删改以后要对做的操作进行commit提交,如果操作失败则rollback回滚刚才的操作。

3、参数游标:

在定义游标时加入参数的游标,可以配合游标for循环快速找到需要的数据。这里先讲一下游标for循环

A、游标FOR循环:

隐含的执行了打开提取关闭数据,代码精简很多。Expression:

FOR table_record IN table_cursor LOOP

STATEMENT;

END LOOP;

Example:使用游标For循环打印输出员工信息:

DECLARE

CURSOR emp_cursor IS SELECT empno,ename,job FROM emp;

BEGIN

FOR emp_record IN emp_cursor LOOP

DBMS_OUTPUT.PUT_LINE('员工号:'||emp_record.empno||'员工姓名'||emp_record.ename||'员工职位'||emp_record.job);

END LOOP;

END;

这里游标FOR循环省去了对于取到的数据的变量的命名和赋值,同时如果全部打印则不用写循环条件,代码精简了很多。

如果想让代码更加精简,则可以去掉对游标的声明引入子查询即可,操作如下。

BEGIN

FOR emp_record IN (SELECT empno,ename,job FROM emp) LOOP

DBMS_OUTPUT.PUT_LINE('员工号:'||emp_record.empno||'员工姓名'||emp_record.ename||'员工职位'||emp_record.job);

END LOOP;

END;

代码更加精简,得到的结果相同。和隐式游标是不是有点像,但隐式游标主要用于的是单行select和dml语句的操作,注意2者用法的区别。

下面继续参数游标的实例:

Example:输入部门号打印员工信息:

DECLARE

CURSOR emp_cursor(dno NUMBER)IS SELECT empno,ename,job FROM emp WHERE deptno=dno;

BEGIN

FOR emp_record IN emp_cursor(&dno) LOOP

DBMS_OUTPUT.PUT_LINE('员工号'||emp_record.empno||'姓名'||emp_record.ename||'职位'||emp_record.job);

END LOOP;

END;

Oracle游标使用详解

Oracle游标使用详解

这里既然有参数,那么必然会有对游标的声明,在结合游标FOR循环快速超找所需要的数据。

三、使用游标修改数据的注意事项

1、使用游标修改数据时,为防止他人在自己操作数据时对数据进行修改,oracle提供for update子句进行加锁。

同时在你使用update或delete时,必须使用where current of+name_cursor语句,以及在最后记得提交。如果

是级联操作则可以使用for update of 来进行相关表的加锁。

Example1:对职位是PRESIDENT的员工加1000工资,MANAGER的人加500工资

CREATE TABLE emp_new

AS

SELECT * FROM emp;

DECLARE

CURSOR empnew_cursor IS SELECT ename,job FROM emp_new FOR UPDATE;

BEGIN

FOR empnew_record IN empnew_cursor LOOP

DBMS_OUTPUT.PUT_LINE('姓名'||empnew_record.ename||'职位'||empnew_record.job);

IF empnew_record.job='PRESIDENT' THEN

UPDATE emp_new SET sal=sal+1000 WHERE CURRENT OF empnew_cursor;

ELSIF empnew_record.job='MANAGER' THEN

UPDATE emp_new SET sal=sal+500 WHERE CURRENT OF empnew_cursor;

END IF;

END LOOP;

COMMIT;

END;

SELECT * FROM EMP WHERE job in('PRESIDENT','MANAGER');

SELECT * FROM EMP_NEW WHERE job in('PRESIDENT','MANAGER');

Oracle游标使用详解

Oracle游标使用详解

可以看到这里工资有了相应的变化。

至此,Oracle游标解析完毕,总而言之,游标只是作为我们从数据库中提取出来的一部分数据,我们针对这个结果集做一系列的操作。

更多Oracle相关信息见 Oracle 专题页面 https://www.linuxidc.com/topicnews.aspx?tid=12

Linux公社的RSS地址: https://www.linuxidc.com/rssFeed.aspx

本文永久更新链接地址: https://www.linuxidc.com/Linux/2018-09/153942.htm


以上就是本文的全部内容,希望本文的内容对大家的学习或者工作能带来一定的帮助,也希望大家多多支持 码农网

查看所有标签

猜你喜欢:

本站部分资源来源于网络,本站转载出于传递更多信息之目的,版权归原作者或者来源机构所有,如转载稿涉及版权问题,请联系我们

Visual Thinking

Visual Thinking

Colin Ware / Morgan Kaufmann / 2008-4-18 / USD 49.95

Increasingly, designers need to present information in ways that aid their audiences thinking process. Fortunately, results from the relatively new science of human visual perception provide valuable ......一起来看看 《Visual Thinking》 这本书的介绍吧!

JS 压缩/解压工具
JS 压缩/解压工具

在线压缩/解压 JS 代码

RGB HSV 转换
RGB HSV 转换

RGB HSV 互转工具