Oracle PL/SQL 关于游标的使用
环境说明
VirtualBox6.0、Vagrant2.2.3、CentOS7.6 、Dodkcer 18.09.7 、Oracle11G、
Docker Image:docker pull registry.cn-hangzhou.aliyuncs.com/helowin/oracle_11g
PS:数据库运行VirtualBox虚拟机中的CentOS系统上的Docker容器中,使用Scott用户
游标简介
游标是Oracle执行SQL与的内存区域。在数据库编程中,游标是内部数据结构,能够处理SQL查询结果。
游标是相对于上下文区域的句柄或者指针。借助于游标PL/SQL程序可以控制上下文区域,以及当语句执行时所发生的事情。
游标有两种类型,分别为隐式游标和显式游标:
- 隐式游标:每当SQL语句执行时,Oracle会自动声明一个隐式游标。用户不会感觉到这一点,并且不能控制或者处理隐式游标的信息。
- 显式游标:对于返回多行数据的任何查询,都可以为之定义显式游标,定义好的游标可以处理返回的每行数据。
游标属性
在处理游标时,可能需要确定游标的状态。 以下是可以使用的游标属性列表。
属性 | 返回值 | 释义 |
---|---|---|
%ISOPEN | Boolean | 游标处于打开状态返回TRUE |
%FOUND | Boolean | 执行成功,则返回TRUE ;、没有执行被返回,则返回FALSE |
%NOTFOUND | Boolean | 执行了成功的FETCH ,则返回FALSE 、没有执行被返回,则返回TRUE |
%ROWCOUNT | Number | 返回从游标中所检索的记录数 |
PS: 如果声明了游标,但不打开,则返回INVALID_CURSOR
,或者游标已关闭。
隐式游标
隐式游标会自动的与每个DML语句(UPDATE、DELETE、INSERT)建立关联,所有的UPDATE、DELETE语句都有标识被当前操作所影响数据行集合的游标。
隐式游标被用户处理INSERT、UPDATE、DELETE和SELETE INTO语句,在处理隐式游标的过程中,Oracle会自动执行OPEN、FETCH和CLOSE的操作。
INSERT 使用游标:
set serveroutput on; begin INSERT INTO SCOTT.EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES (7986, 'Nathan', 'SYSDBA', 7698, TO_DATE('2019-09-04 00:00:00', 'YYYY-MM-DD HH24:MI:SS'), 18621.00, null, 20); DBMS_OUTPUT.PUT_LINE('INSERT:EMP=>受影响的行数是'||SQL%ROWCOUNT||'行'); end; --执行结果: [2019-09-04 11:38:57] completed in 24 ms [2019-09-04 11:38:57] INSERT:EMP=>受影响的行数是1行
SELECT INTO使用游标:
set serveroutput on; declare ename varchar2(20); sal number; begin select emp.ename, emp.sal into ename , sal from emp where emp.empno = 7986; DBMS_OUTPUT.PUT_LINE('SELECT:EMP=>'||ename||'的薪水是'||sal); exception when NO_DATA_FOUND then DBMS_OUTPUT.PUT_LINE('SELECT:EMP=>没有找到数据!'); end; -- 执行结果: [2019-09-04 11:39:54] completed in 6 ms [2019-09-04 11:39:54] SELECT:EMP=>Nathan的薪水是18621
UPDATE使用游标:
set serveroutput on; begin update emp set emp.sal = 8000 where emp.empno = 7986; DBMS_OUTPUT.PUT_LINE('UPDATE:EMP=>受影响的行数是'||SQL%ROWCOUNT||'行'); end; -- 执行结果: [2019-09-04 11:40:28] completed in 168 ms [2019-09-04 11:40:28] UPDATE:EMP=>受影响的行数是1行
DELETE使用游标:
set serveroutput on; begin delete from emp where emp.empno = 7986; DBMS_OUTPUT.PUT_LINE('DELETE:EMP=>受影响的行数是'||SQL%ROWCOUNT||'行'); end; --执行结果: [2019-09-04 11:41:48] completed in 10 ms [2019-09-04 11:41:48] DELETE:EMP=>受影响的行数是1行
显式游标
相较于隐式游标,显式游标的好处是可以对程序进行更多的编程控制,隐式游标的效率没有显式游标的效率高,更难以捕获异常。显式游标的使用分为以下四步:
- 声明游标:在内存中建立游标的初始化环境
- 打开游标:打开已经声明好的游标,分配内存
- 检索游标:通过已经声明和打开的游标来检索数据
- 关闭游标:关闭,释放内存。
- 使用游标查询emp表中的员工名称以及薪水:
-- 开启输出
set serveroutput on;
-- 游标的
declare
-- 声明游标
cursor cur is select emp.ENAME,emp.SAL from emp;
ename emp.ename%type;
sal emp.sal%type;
begin
-- 打开游标
open cur;
loop
-- 检索数据
fetch cur into ename,sal;
-- 当游标中没有数据的时候退出
exit when cur%notfound;
DBMS_OUTPUT.PUT_LINE(ename|| '的薪水是'|| sal);
end loop;
-- 关闭游标
close cur;
end;
-- 执行结果:
[2019-09-04 09:01:55] completed in 5 ms
[2019-09-04 09:01:55] SMITH的薪水是800
[2019-09-04 09:01:55] ALLEN的薪水是1600
[2019-09-04 09:01:55] WARD的薪水是1250
[2019-09-04 09:01:55] JONES的薪水是2975
[2019-09-04 09:01:55] MARTIN的薪水是1250
[2019-09-04 09:01:55] BLAKE的薪水是2850
[2019-09-04 09:01:55] CLARK的薪水是2450
[2019-09-04 09:01:55] SCOTT的薪水是3000
[2019-09-04 09:01:55] KING的薪水是5000
[2019-09-04 09:01:55] TURNER的薪水是1500
[2019-09-04 09:01:55] ADAMS的薪水是1100
[2019-09-04 09:01:55] JAMES的薪水是950
[2019-09-04 09:01:55] FORD的薪水是3000
[2019-09-04 09:01:55] MILLER的薪水是1300
Done.
本博客所有文章除特别声明外,均采用 CC BY-NC-SA 4.0 许可协议。转载请注明来自 Yang!