Oracle 第6章 游标
1、技术目标
- 游标的使用
2、什么是游标?
游标是查询数据时指向结果集的指针,通过游标可一次访问结果集中
的一行,Oracle有两种游标:
-
静态游标 ,在编译时知道select语句的游标,静态游标有两种:
- 隐式游标
- 显示游标
-
REF游标 ,有时候为游标使用的查询直到代码运行时才确定,
这种情况使用REF游标(引用游标)和游标变量来实现,
有两种REF游标:
- 强类型REF游标
- 弱类型REF游标
3、隐式游标
PL/SQL为DML语句隐式声明游标,用户不能命名和控制这种游标,所以
称为隐式游标,隐式游标使用属性返回信息,属性 包括:
- %FOUND ,在DML语句影响一行或多行时返回true
- %NOTFOUND ,没有影响任何行时返回true
- %ROWCOUNT ,返回DML语句影响行数,如DML没有影响任何行返回0
- %ISOPEN ,返回游标是否已打开的值,在SQL语句执行完后,Oracle自动关闭SQL游标,隐式游标的%ISOPEN属性始终为false
注意:通过检查隐式游标的属性可以获取与最近执行时的SQL语句相关的信息
使用1: 用%FOUND属性判断是否获取查询结果,
set serveroutput on begin --查询 update toys set toyprice=270 where toyid='T005'; --判断是否有查询结果 if SQL%FOUND then DBMS_OUTPUT.PUT_LINE('表已更新'); else DBMS_OUTPUT.PUT_LINE('编号未找到'); end if; end; /
使用2: 用%ROWCOUNT获取SQL语句执行所影响的行数,
set serveroutput on begin update vendor_master set venname='Rob Mathew' where vencode='V004' DBMS_OUTPUT.PUT_LINE('更新了' || SQL%ROWCOUNT || '行'); end; /
使用3 :按编号查询员工,判断是否查到,
set serveroutput on declare empid varchar2(10); desig varchar2(20); begin empid := '&职员编号'; --根据员工编号查询出员工职位并给变量desig赋值 select designation into desig from employee where empno = empid; if SQL%ROWCOUNT > 0 then DBMS_OUTPUT.PUT_LINE('职员的头衔是:' + desig); end if; exception when NO_DATA_FOUND then DBMS_OUTPUT.PUT_LINE('职员未找到'); end; /
4、显示游标
显示游标是用户显示声明的游标,游标将指向活动集(查询结果)中的当前行,
使用显示游标的步骤:
1)声明
2)打开
3)从游标中获取记录
4)关闭游标
显示游标在PL/SQL程序的DECLARE部分声明,语法 为:
CURSOR cursor_name [(parameter [, parameter] ...)]
[return return_type]
IS
select_statement;
语法说明:
cursor_name:游标名称
parameter:指定输入参数
return type:定义游标提取的行的类型
select_statement:游标定义的查询语句
游标声明后,可使用如下语句控制游标 :
- open :打开游标,open cursor_name [ (parameters) ];
- fetch :从游标中提取行,fetch cursor_name into variables;
写在循环内,用于从结果集中一次检索一行,
每次提取后指针就向前移动一行 - close :关闭游标,close cursor_name,游标处理完后必须关闭
使用1: 声明游标toy_cur,打开并使用该游标提取所有行,
set serveroutput on declare --定义变量my_toy_price,其类型与toyprice字段一致 my_toy_price toys.toyprice%TYPE; CURSOR toy_cur IS select toyprice from toys where toyprice < 250; begin open toy_cur; --打开游标 LOOP --循环 --循环提取玩具的价格并给变量my_toy_price赋值 fetch toy_cur into my_toy_price; --当查询没有返回任何行时退出循环 exit when toy_cur%NOTFOUND; --输出玩具价格 DBMS_OUTPUT.PUT_LINE(toy_cur%ROWCOUNT || '. 玩具单价:' || my_toy_price); END LOOP; --关闭游标 close toy_cur; end; /
使用2: 用游标操作PRODUCTDETAILS表,
declare --定义变量pro_price,其类型与PRODPRICE字段一致 pro_price PRODUCTDETAILS.PRODPRICE%TYPE; --定义变量pro_name,其类型与PRODNAME字段一致 pro_name PRODUCTDETAILS.PRODNAME%TYPE; --定义游标pro_cur,查询所有价格小于5000的商品 CURSOR pro_cur IS select PRODNAME, PRODPRICE from PRODUCTDETAILS where PRODPRICE < 5000; begin open pro_cur; --打开游标 LOOP --循环 --循环提取商品的名称和价格并赋值给变量pro_name和pro_price fetch pro_cur into pro_name, pro_price; --当查询没有返回任何行时退出循环 exit when pro_cur%NOTFOUND; --输出商品名、价格 DBMS_OUTPUT.PUT_LINE(pro_cur%ROWCOUNT || ', 商品名:' || pro_name || ', 单价:' || pro_price); END LOOP; --关闭游标 close pro_cur; end; /
5、使用显示游标删除或更新
语法:cursor cursor_name is select语句 for update [of colums];
注意:select语句只能包括一个表
游标声明中使用FOR UPDATE子句后,可使用以下语法更新行:
update 表名 set column_name = column_value
where CURRENT OF cursor_name;
注意:update命令中使用的列也必须出现在for update of子句中,
update和delete语句只有在打开游标并提取到特定行后才能使用
使用: 用显示游标更新行,将所有价格低于100的玩具提价10%,
set serveroutput on declare new_price number; --定义游标 cursor cur_toy is select toyprice from toys where toyprice < 100 for update of toyprice; begin open cur_toy;--打开游标 LOOP --执行SQL查询语句,提取价格并赋值给变量new_price fetch cur_toy into new_price; --如果没有查询结果,退出循环 exit when cur_toy%NOTFOUND; --给价格低于100的商品提价10% update toys set toyprice = 1.1 * new_price where CURRENT OF cur_toy; end loop; close cur_toy; commit; end; /
6、带参的显示游标
显示游标可以接受输入的参数,声明带参游标的语法 如下:
CURSOR cursor_name (<param_name> data_type)
[return <return_type>]
IS
select_statement;
使用: 提示输入部门编号,根据输入的部门编号提取该部门
下的员工,显示员工的编号和姓名,
declare dept_code emp.deptno%TYPE; emp_code emp.empno%TYPE; emp_name emp.ename%TYPE; --定义带参游标,参数名为deptparam,查询指定部门的员工 cursor emp_cur (deptparam number) is select empno, ename from emp where deptno=deptparam;--查询条件使用参数匹配 begin --输入部门编号 dept_code := '&部门编号'; open emp_cur(dept_code);--打开游标的同时传参 LOOP --循环 --取出员工编号、员工名并赋值给变量emp_code、emp_name fetch emp_cur into emp_code, emp_name; --没有查询出结果退出循环 exit when emp_cur%NOTFOUND; --系统输出 DBMS_OUTPUT.PUT_LINE(emp_code || ' ' || emp_name); END LOOP; --关闭游标 close emp_cur; end; /
7、使用循环游标简化显示游标的代码
当用户需要从游标中提取所有记录时使用循环游标,可自动从结果集
中获取行,处理完所有行后关闭游标,循环游标还自动创建%ROWTYPE
类型的变量并将该变量作为记录索引,语法 如下:
FOR row_record IN cursor_name
LOOP
语句;
END LOOP ;
语法说明:
row_record,保存行记录的变量,为%ROWTYPE类型,FOR循环外不能访问
cursor_name,游标名称
循环游标的特点:
- 在从游标中提取了所有记录后自动终止
- 可提取和处理游标中的每一条记录
- 如果在提前记录后%NOTFOUND属性返回true,会终止循环,如果没有
返回任何行,不会进入循环
使用: 输出显示所有玩具的编号、名称、价格,
SET SERVER OUTPUT ON DECLARE CURSOR mytoyCur IS SELECT toyid, toyname, toyprice FROM toys; BEGIN FOR toyRec IN mytoyCur LOOP DBMS_OUTPUT.PUT_LINE( '玩具编号:' || ' ' || toyRec.toyid||' ' || ‘玩具名称:' || ' '|| toyRec.toyname||' ' || ‘玩具单价:' || ' '||toyRec.toyprice); END LOOP; END; /
处理带参的循环游标语法如下:
FOR row_record IN cursor_name (parameters)
LOOP
语句;
END LOOP ;
8、REF 游标和游标变量
隐式/显示游标在使用它们的时候查询语句已确定,如需在运行时动态
决定执行什么查询,可使用REF游标和游标变量
创建游标变量需要两个步骤:
- 声明REF游标类型
- 声明REF游标类型的变量
用于声明REF游标类型的语法 为:
TYPE ref_cursor_name
IS REF CURSOR
[RETURN return_type];
语法说明:
return可选子句用于指定游标提取结果集的返回类型,如果
包含return子句表示强类型REF游标,不包含return则为弱
类型REF游标
定义好游标、游标变量后,可在PL/SQL的执行部分打开REF游标,语法为:
OPEN cursor_name FOR select_statement;
注意:提取和关闭游标的语法与显示游标相似
使用: 接收用户输入,选择查看员工信息或者部门信息,
set serveroutput on --accept命令可接收用户输入并存入变量tab中 ACCEPT tab PROMPT '你想查看什么信息?员工信息(E),部门信息(D):'; DECLARE --声明REF游标(弱类型) TYPE refCurT IS REF CURSOR; --声明游标变量refCur refCur refCurT; pId number; pName varchar2(100); --定义变量selection保存用户输入的字符(E或D) selection varchar2(1) := UPPER(SUBSTR('&tab'), 1, 1); BEGIN --判断用户输入的是不是'E' IF selection = 'E' THEN --打开游标,同时指定select语句 OPEN refCur FOR --查询员工表 select empNo id, empName name from emp; --准备显示员工信息 DBMS_OUTPUT.PUT_LINE('===员工信息==='); ELSIF selection = 'D' THEN --打开游标,同时指定select语句 OPEN refCur FOR --查询部门表 select depNo id, depName name from dept; --准备显示部门信息 DBMS_OUTPUT.PUT_LINE('===部门信息==='); ELSE --提示输入 DBMS_OUTPUT.PUT_LINE('请输入员工信息(E) 或 部门信息(D)'); RETURN; END IF; --从游标中提取编号、名称,赋值给变量pId、pName FETCH refCur INTO pId, pName; --循环提取所有信息并显示 WHILE refCur%FOUND LOOP --显示信息 DBMS_OUTPUT.PUT_LINE('编号:' || pId || ' 名字:' || pName); --从游标中提取编号、名称,赋值给变量pId、pName FETCH refCur INTO pId, pName; END LOOP; --关闭游标 CLOSE refCur; END; /
9、使用REF游标执行动态SQL语句
EXECUTE IMMEDIATE语句只能处理返回单行或没有返回的SQL语句,
REF游标可处理返回结果集的动态SQL,语法如下:
OPEN cursor_name
FOR 动态SQL语句字符串
[USING 绑定的输入参数]
注意:这种游标,声明部分与普通REF游标相同,只是OPEN语法不一样
使用: 显示薪水高于2500的员工信息,
SET SERVEROUTPUT ON VARIABLE maxSal NUMBER EXECUTE :maxSal := 2500 DECLARE empRec emp%ROWTYPE; --定义REF游标 TYPE cType IS REF CURSOR; --定义游标变量 cur cType; --薪水 pSalary number; BEGIN pSalary := :maxSal; --打开游标,设置SQL字符串 OPEN cur FOR 'select * from Emp where sal > :inputSal order by sal desc'; USING pSalary --使用变量pSalary的值作为输入参数传递给:inputSal --循环显示出所有薪水大于指定值的员工 LOOP --使用游标提取行 FETCH cur INTO empRec; --判断所有行是否读取完毕,读取完毕需退出循环 EXIT WHEN cur%NOTFOUND; --显示信息 DBMS_OUTPUT.PUT_LINE('编号:' || empRec.empNo || ' 姓名:' || empRec.eName || ' 薪水:' || empRec.sal); END LOOP; --关闭游标 CLOSE cur; END; /
10、游标变量的优点和限制
游标变量的优点 有:
- 可从不同的SELECT语句中提取结果集
- 可以作为过程的参数进行传递
- 可以引用游标的所有属性
- 可以进行赋值运算
使用游标变量的限制:
- 不能在程序包中声明游标变量
- FOR UPDATE子句不能与游标变量一起使用
- 不能使用比较运算符
11、总结
- 游标用于处理查询结果集中的数据
- 游标类型有:隐式游标、显式游标 和 REF 游标
- 隐式游标由PL/SQL自动定义、打开和关闭
- 显式游标用于处理返回多行的查询
- 显式游标可以删除和更新活动集中的行
- 要处理结果集中所有记录时,可使用循环游标
- 在声明REF游标时,不需要将 SELECT 语句与其关联
相关推荐
Oracle数据库中的游标和异常管理是数据库编程的重要组成部分,尤其在处理复杂的PL/SQL块时。游标允许程序逐行处理查询结果,提供了一种灵活且高效的数据访问方式。 首先,我们来了解一下游标的基本概念。游标...
#### 六、游标属性 游标属性用于提供关于游标状态的信息。常用的属性包括: - **%FOUND**:如果上一次提取操作成功,则为TRUE;否则为FALSE。 - **%NOTFOUND**:如果上一次提取操作没有找到更多的数据,则为TRUE;...
### Oracle数据库应用——第六章 游标管理 #### 一、引言 在数据库操作中,游标是一种非常重要的机制,特别是在使用Oracle数据库时。游标允许开发人员以编程方式处理查询结果集,使得能够逐行地访问和操作数据。在...
### Oracle游标循环详解 #### 一、引言 在Oracle数据库编程中,游标是一种非常重要的机制,用于处理查询结果集。通过游标,我们可以逐行读取数据,并执行复杂的逻辑操作。本文将详细介绍Oracle中三种常见的游标...
第6章 约束.ppt 第7章 视图.ppt 第8章 函数与存储过程.ppt 第9章 游标.ppt 第10章 触发器.ppt 第11章 序列.ppt 第12章 用户角色与权限控制.ppt 第13章 Oracle数据类型.ppt 第14章 Oracle中的函数与表达式.ppt 第15章...
第6章Oracle中的字符型及ppt 第7章Oracle中的数值型ppt 第8章Oracle中的日期型ppt 第9章Oracle中的复杂数据处理ppt 第10章Oracle中的控制语句.ppt 第11章视图ppt 第12章约速ppt 第13章游标pp 第14章触发器ppt 第15章...
第六章 多表查询 第七章 子查询 第八章 高级查询 第九章 数据字典 第十章 Oracle数据类型 第十一章 Oracle体系结构(DBA) 第十二章 DDL(改变表结构) 第十三章 DML(改变数据结构) 第十四章 约束 第十五章 ...
- **打开游标**:使用`OPEN`语句打开已声明的游标,这会基于绑定变量的值确定活动集,并将指针指向第一行。 ```sql OPEN cursor_name; ``` - **提取结果**:使用`FETCH`语句将查询结果提取到PL/SQL变量中。有两...
Oracle存储过程中的游标是数据库操作中非常重要的一个概念,特别是在处理大量数据或者需要逐行处理结果集时。游标允许我们动态地控制查询结果的处理流程,而不是一次性获取所有结果。下面我们将深入探讨Oracle存储...
### Oracle数据库应用——游标管理知识点详解 #### 一、隐式游标的使用 隐式游标是由PL/SQL在内部自动创建和管理的一种特殊游标类型,它主要用于处理简单的SELECT INTO语句。 ##### 实现思路 - **声明变量**:首先...
第六章 多表查询 第七章 子查询 第八章 高级查询 第九章 数据字典 第十章 Oracle数据类型 第十一章 Oracle体系结构(DBA) 第十二章 DDL(改变表结构) 第十三章 DML(改变数据结构) 第十四章 约束 第十五章 视图 第十...
第六章 ORACLE 11g 的数据查询 第七章 ORACLE 数据的基本操作 第八章 索引 第九章 视图 第十章 PL/SQL基础 第十一章 存储过程与函数 第十二章 触发器 第十三章 游标 第十四章 安全管理 第十五章 数据库备份与恢复
第六章 多表查询 第七章 子查询 第八章 高级查询 第九章 数据字典 第十章 Oracle数据类型 第十一章 Oracle体系结构(DBA) 第十二章 DDL(改变表结构) 第十三章 DML(改变数据结构) 第十四章 约束 第十五章 ...
- 打开游标:使用 `OPEN` 语句打开游标,使游标指向查询结果的第一行。 - 读取数据:使用 `FETCH` 语句从游标中读取一行数据。 - 处理数据:对读取的数据进行处理。 - 关闭游标:使用 `CLOSE` 语句关闭游标。 示例...
在Oracle学习的第六章中,我们将重点探讨游标的管理和使用,这是数据库编程中的一个关键概念,尤其在处理复杂的数据操作时显得尤为重要。 游标(Cursor)在Oracle数据库中是一个重要的编程工具,它允许我们逐行处理...
1. **无参游标**:在第六个示例中,声明了一个名为`yb`的游标,它查询了`emp`表中`empno`小于7600的员工名字。通过循环提取游标,逐行打印名字,并在最后显示影响的行数。 2. **带参游标**:第七个示例展示了如何...
第六章 多表查询 第七章 子查询 第八章 高级查询 第九章 数据字典 第十章 Oracle 数据类型 第十一章 Oracle 体系结构(DBA) 第十二章 DDL(改变表结构) 第十三章 DML(改变数据结构 第十四章 约束 第十五章 视图 第十...
第6章 简单查询 第7章 sql单行函数 第8章 操纵数据 第9章 复杂查询 第10章 管理常用对象 第三部分 pl/sql 第11章 pl/sql基础 第12章 访问oracle 第13章 编写控制结构 第14章 使用复合数据类型 ...
第6章 简单查询 第7章 sql单行函数 第8章 操纵数据 第9章 复杂查询 第10章 管理常用对象 第三部分 pl/sql 第11章 pl/sql基础 第12章 访问oracle 第13章 编写控制结构 第14章 使用复合数据类型 ...
第6章-Oracle内置函数 数值型函数 字符型函数 日期型函数 转换函数 NULL函数 集合函数 其他函数 第7章-PLSQL基础 PL/SQL是什么 PL/SQL 变量的使用 表达式 PL/SQL结构控制 PL/SQL中使用DML和DDL语言 PL/...