Oracle游标的使用实例详解
什么是游标？
①从表中检索出结果集，从中每次指向一条记录进行交互的机制。
②关系数据库中的操作是在完整的行集合上执行的。
由 SELECT 语句返回的行集合包括满足该语句的 WHERE 子句所列条件的所有行。由该语句返回完整的行集合叫做结果集。
应用程序，尤其是互动和在线应用程序，把完整的结果集作为一个单元处理并不总是有效的。
这些应用程序需要一种机制来一次处理一行或连续的几行。而游标是对提供这一机制的结果集的扩展。
游标是通过游标库来实现的。游标库是常常作为数据库系统或数据访问 API 的一部分而得以实现的软件，用来管理从数据源返回的数据的属性（结果集）。这些属性包括并发管理、在结果集中的位置、返回的行数，以及是否能够在结果集中向前和/或向后移动（可滚动性）。
游标跟踪结果集中的位置，并允许对结果集逐行执行多个操作，在这个过程中可能返回至原始表，也可能不返回至原始表。
换句话说，游标从概念上讲基于数据库的表返回结果集。
由于它指示结果集中的当前位置 ，就像计算机屏幕上的光标指示当前位置一样，“游标”由此得名。
游标有什么作用？
①指定结果集中特定行的位置。
②基于当前的结果集位置检索一行或连续的几行。
③在结果集的当前位置修改行中的数据。
④对其他用户所做的数据更改定义不同的敏感性级别。
⑤可以以编程的方式访问数据库。
引言
本节对Oracle中的游标进行详细讲解。本节所举实例来源Oracle中scott用户下的emp表dept表：
一、游标：
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;
这里注意增删改以后要对做的操作进行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;
这里既然有参数，那么必然会有对游标的声明，在结合游标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;
可以看到这里工资有了相应的变化。至此，Oracle游标解析完毕，总而言之，游标只是作为我们从数据库中提取出来的一部分数据，我们针对这个结果集做一系列的操作。
总结
以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对我们的支持。
相关推荐
-
Oracle出现超出打开游标最大数的解决方法
本文实例讲述了Oracle出现超出打开游标最大数的解决方法.分享给大家供大家参考,具体如下: Java代码在执行conn.createStatement()和conn.prepareStatement()的时候,实际上都是相当与在数据库中打开了一个cursor.尤其是,假如你的createStatement和prepareStatement是在一个循环里面的话,就会非常轻易出现这个问题.因为游标一直在不停的打开,而且没有关闭. 一般来说,我们在写Java代码的时候,createStatement
-
Oracle 游标使用总结
Oracle游标分为显示游标和隐式游标. 显示游标(Explicit Cursor):在PL/SQL程序中定义的.用于查询的游标称作显示游标. 隐式游标(Implicit Cursor):是指非PL/SQL程序中定义的.而且是在PL/SQL中使用UPDATE/DELETE语句时,Oracle系统自动分配的游标. 一.显示游标 1.使用步骤 (1)定义 (2)打开 (3)使用 (4)关闭 2.使用演示 首先创建测试用表STUDENT,脚本如下: 复制代码 代码如下: CREATE TABLE "S
-
Oracle中游标Cursor基本用法详解
查询 SELECT语句用于从数据库中查询数据,当在PL/SQL中使用SELECT语句时,要与INTO子句一起使用,查询的 返回值被赋予INTO子句中的变量,变量的声明是在DELCARE中.SELECT INTO语法如下: SELECT [DISTICT|ALL]{*|column[,column,...]} INTO (variable[,variable,...] |record) FROM {table|(sub-query)}[alias] WHERE............ PL/SQL
-
oracle 在一个存储过程中调用另一个返回游标的存储过程
第一种情况是返回的游标是某个具体的表或视图的数据,如: SQL-Code: 复制代码 代码如下: CREATE OR REPLACE PROCEDURE P_TESTA ( PRESULT OUT SYS_REFCURSOR ) AS BEGIN OPEN PRESULT FOR SELECT * FROM USERS; END P_TESTA; 其中USERS就是数据库中一个表.在调用的时候只要声明一个该表的ROWTYPE类型就可以了: SQL-Code: 复制代码 代码如下: CREATE
-
Oracle显示游标的使用及游标for循环
下面给大家介绍在什么情况下用隐式游标,什么情况下用显示游标: 1.查询返回单行记录时→隐式游标: 2.查询返回多行记录并逐行进行处理时→显式游标 --显示游标属性 declare CURSOR cur_emp IS SELECT * FROM emp; row_emp cur_emp%ROWTYPE; BEGIN OPEN cur_emp; FETCH cur_emp INTO row_emp; WHILE cur_emp%FOUND LOOP dbms_output.put_line(row_
-
详解Oracle游标的简易用法
下面看下Oracle游标的简易用法,具体代码如下所示: create or replace procedure NW_DelYW(iOPERATION_ID number, sUserID varchar2) is sCurDJBH yw_operation_link.djbh%type; cursor table_yw(ywid yw_operation.id%type) is select * from yw_operation_link t1 where t1.operation_id =
-
Oracle存储过程游标用法分析
本文实例讲述了Oracle存储过程游标用法.分享给大家供大家参考,具体如下: 使用游标的5个步骤 1.声明一些变量用于保存select语句返回的指 2.声明游标,并指定select 语句 3.打开游标 4.从游标中获取记录 5.关闭游标 从游标中获取每一条记录可使用fetch语句.fetch语句将列的指读取到指定的变量中: 语法: fetch cursor_name into variable[, variable ...]; 例子: create or replace procedure se
-
Oracle存储过程返回游标实例详解
有俩种方法: 一种是声明系统游标,一种是声明自定义游标,然后后面操作一样,参数类型为 in out 或out (1)声明个人系统游标.(推荐) 复制代码 代码如下: create or replace p_temp_procedure ( cur_arg out sys_refcursor; --方法1 ) begin open cur_arg for select * from tablename; end 调用 复制代码 代码如下: declare cur_calling sys_refcu
-
Oracle游标的使用实例详解
什么是游标? ①从表中检索出结果集,从中每次指向一条记录进行交互的机制. ②关系数据库中的操作是在完整的行集合上执行的. 由 SELECT 语句返回的行集合包括满足该语句的 WHERE 子句所列条件的所有行.由该语句返回完整的行集合叫做结果集. 应用程序,尤其是互动和在线应用程序,把完整的结果集作为一个单元处理并不总是有效的. 这些应用程序需要一种机制来一次处理一行或连续的几行.而游标是对提供这一机制的结果集的扩展. 游标是通过游标库来实现的.游标库是常常作为数据库系统或数据访问 API 的一部
-
Oracle分页查询的实例详解
Oracle分页查询的实例详解 1.Oracle分页查询: SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (SELECT * FROM tab) A WHERE ROWNUM <= 40 ) WHERE RN >= 21; 这个分页比下面的执行时间少,效率高. 2. select * from (select c.*,rownum rn from tab c) where rn between 21 and 40 对比这两种写法,绝大多数的情况下,第一个
-
oracle 的表空间实例详解
oracle 的表空间实例详解 查询表空间 SELECT UPPER(F.TABLESPACE_NAME) "表空间名", D.TOT_GROOTTE_MB "表空间大小(M)", D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)", TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100, 2), '990.99')
-
Oracle的out参数实例详解
Oracle的out参数实例详解 一 概念 1.一般来讲,存储过程和存储函数的区别在于存储函数可以有一个返回值:而存储过程没有返回值. 2.过程和函数都可以通过out指定一个或多个输出行.我们可以利用out参数,在过程和函数中实现返回多个值. 3.存储过程和存储函数都可以有out参数. 4.存储过程和存储函数都可以有多个out参数. 5.存储过程可以通过out参数来实现返回值. 6.如果只有一个返回值,用存储函数:否则,就用存储过程. 二 实例 --out参数:查询某个员工姓名月薪和职位 /*
-
mysql的存储过程、游标 、事务实例详解
mysql的存储过程.游标 .事务实例详解 下面是自己曾经编写过的mysql数据库存储过程,留作存档,以后用到的时候拿来参考. 其中,涉及到了存储过程.游标(双层循环).事务. [说明]:代码中的注释只针对当时业务而言,无须理会. 代码如下: DELIMITER $$ DROP PROCEDURE IF EXISTS `transferEmailTempData`$$ CREATE PROCEDURE transferEmailTempData(IN jobId VARCHAR(24)) BEG
-
C# Oracle数据库操作类实例详解
本文所述为C#实现的Oracle数据库操作类,可执行超多常用的Oracle数据库操作,包含了基础数据库连接.关闭连接.输出记录集.执行Sql语句,返回带分页功能的dataset .取表里字段的类型和长度等,同时还有哈稀表自动插入数据库等高级任务.需要特别指出的是:在执行SQL语句,返回 DataReader之前一定要先用.read()打开,然后才能读到数据,再用hashTable对数据库进行insert,update,del操作,注意此时只能用默认的数据库连接"connstr". 完整
-
Oracle 自定义split 函数实例详解
Oracle 自定义split 函数 Oracle没有提供split函数,但可以自己建立一个函数实现此功能.比如"abc defg hijkl nmopqr stuvw xyz",分隔符是空格,但空格个数不定. 源代码: CREATE OR REPLACE TYPE ty_str_split IS TABLE OF VARCHAR2 (4000); CREATE OR REPLACE FUNCTION fn_var_split ( p_str IN VARCHAR2,
-
Oracle 大小写转换函数实例详解
Oracle 大小写转换函数 小写转大写UPPer 大写转小写LOWER 例: select lower(ename) from emp; select upper(ename) from emp; --是否包含字母(大写和小写) select case when regexp_like('123','.([a-z]+|[A-Z])') then '包含字母' else '不包含字母' end from dual; --是否包含小写字母 select case when regexp_like(
-
Oracle排名函数(Rank)实例详解
--已知:两种排名方式(分区和不分区):使用和不使用partition --两种计算方式(连续,不连续),对应函数:dense_rank,rank ·查询原始数据:学号,姓名,科目名,成绩 select * from t_score S_ID S_NAME SUB_NAME SCORE 1 张三 语文 80.00 2 李四 数学 80.00 1 张三 数学 0.00 2 李四 语文 50.00 3 张三丰 语文 10.00 3 张三丰 数学 3 张三丰 体育 120.00 4 杨过 JAVA 9
-
Oracle查询优化日期运算实例详解
加减日.月.年 在Oracle中,date类型可以直接加减天数,而加减月份要用add_months函数. select a.hiredate 雇用日期, a.hiredate + 5 加5天, a.hiredate - 5 减5天, add_months(hiredate, 5) 加5个月, add_months(hiredate, -5) 减5个月, add_months(hiredate, 5 * 12) 加5年, add_months(hiredate, -5 * 12) 减5年 fro