ORACLE中的游标

游标概念
内存单元
Oracle 服务器
执行 PL/SQL 程序
检索行
保存到游标中 提取行 提取行 提取行 提取行 一次处理一行
游标优点
游标允许应用程序对查询语句select 返回的行结果集 游标允许应用程序对查询语句 中每一行进行相同或不同的操作 它还提供对基于游标位置而对表中数据进行删除或更 新的能力 游标把作为面向集合的数据库管理系统和面向行的程 序设计两者联系起来使两个数据处理方式能够进行沟 通
隐式游标
隐式光标处理 : 隐式光标是指在处理SQL SQL时 不需定义光标，所使用的SQL语句包括： SQL语句包括 隐式光标是指在处理SQL时,不需定义光标，所使用的SQL语句包括： ,UPDATE,DELETE子句 子句。 INSERT ,UPDATE,DELETE子句。 隐式光标属性: 隐式光标属性: SQL%FOUND SQL%NOTFOUND 由执行上述语句结果引起 SQL%ROWCOUNT 隐式光标的使用: 隐式光标的使用: Begin update emp set sal=1200 where empno=1234; if SQL%NOTFOUND then insert into Errors(empno,sal) values(1234,1200); end if; End;
游标的分类
隐式游标是oracle为所有数据操纵语句 包括只返回单行数 为所有数据操纵语句(包括只返回单行数 隐式游标是 为所有数据操纵语句 据的查询语句)自动声明和操作的一种游标 自动声明和操作的一种游标。 据的查询语句 自动声明和操作的一种游标。 显示游标是由用户声明和操作的一种游标。 显示游标是由用户声明和操作的一种游标。 在每个用户会话中，可以同时打开多个游标， 在每个用户会话中，可以同时打开多个游标，其数量由数据 库初始化参数文件中的OPEN_CURSORS参数定义。 参数定义。 库初始化参数文件中的 参数定义
声明游标
提取行
带参数的显式游标
SQL> SET SERVEROUTPUT ON 声明显式游标时可以带参数以提高灵活性 SQL> DECLARE desig VARCHAR2(20); 声明带参数的显式游标的语法如下： 声明带参数的显式游标的语法如下： emp_code VARCHAR2(5); CURSOR <cursor_name>(<param_name> <param_type>) empnm VARCHAR2(20); CURSOR emp_cur(desigparam VARCHAR2) IS IS select_statement; SELECT empno, ename FROM employee WHERE designation=desigparam; BEGIN desig:= '&desig'; OPEN emp_cur(desig); LOOP FETCH emp_cur INTO emp_code,empnm; EXIT WHEN emp_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE(emp_code||' '||empnm); END LOOP; CLOSE emp_cur; END;
Logo
oracle
游标管理
1
课程目标
游标概念和用法 游标属性 常见游标的应用
游标概念
块执行SELECT 在PL/SQL块执行 块执行 ,INSERT,UPDATE,DELETE语句时，ORACLE会在 语句时， 语句时 会在 内存中为其分配上下文区(Context Area)。游标是指 内存中为其分配上下文区 。 向该区的指针，或是命名一个工作区(Work Area)， 向该区的指针，或是命名一个工作区 ， 或是一种结构化数据类型。 或是一种结构化数据类型。 它为应用程序提供了一种对具有多行数据的查询结 果集中每行数据进行单独处理的方法， 果集中每行数据进行单独处理的方法，是设计交互 式应用程序的常用编程接口。 式应用程序的常用编程接口。
更新的语法 删除的语法
使用显式游标更新行
SQL> SET SERVEROUTPUT ON SQL> DECLARE new_price NUMBER; CURSOR cur_toy IS SELECT toyprice FROM toys WHERE toyprice<100 FOR UPDATE OF toyprice; BEGIN OPEN cur_toy; LOOP FETCH cur_toy INTO new_price; EXIT WHEN cur_toy%NOTFOUND; UPDATE toys SET toyprice = 1.1*new_price WHERE CURRENT OF cur_toy; END LOOP; CLOSE 源自文库ur_toy; COMMIT; END;
隐式游标
SQL> SET SERVEROUTPUT ON SQL> DECLARE v_TOYID TOYS.ID%type := '&TOYID'; v_TOYNAME TOYS.NAME%Type := '&TOYNAME'; BEGIN UPDATE TOYS SET NAME = v_TOYNAME WHERE toyid=v_TOYID; IF SQL%NOTFOUND THEN DBMS_OUTPUT.PUT_LINE('编号未找到。'); 编号未找到。 编号未找到 ELSE DBMS_OUTPUT.PUT_LINE(‘表已更新 表已更新'); 表已更新 END IF; 语句不影响任何行， 如果 DML 语句不影响任何行，则返回 END; True /
使用显式游标更新行
允许使用游标删除或更新活动集中的行 声明游标时必须使用 SELECT … FOR UPDATE语句 语句
CURSOR <cursor_name> IS SELECT statement FOR UPDATE; UPDATE <table_name> DELETE FROM <table_name> SET <set_clause> WHERE CURRENT OF <cursor_name> WHERE CURRENT OF <cursor_name>
隐式游标
SQL> SET SERVEROUTPUT ON SQL> BEGIN UPDATE vendor_master SET venname= 'Rob Mathew' WHERE vencode='V004'; DBMS_OUTPUT.PUT_LINE (SQL%ROWCOUNT); END; 返回 DML 语句影响的行数 /
游标类型
游标的类型： 游标的类型：
游标类型
隐式游标
显式游标
REF 游标
显式游标用于处理返回多行的查询。 REF 游标用于处理运行时才能确定的动态 SQL 查询的结果 显式游标用于处理返回多行的查询。 程序中执行DML 语句时自动创建隐式游标。 在 PL/SQL 程序中执行DML SQL 语句时自动创建隐式游标。
隐式游标
SQL> SET SERVEROUTPUT ON SQL> BEGIN UPDATE toys SET toyprice=270 WHERE toyid= 'P005'; IF SQL%FOUND THEN DBMS_OUTPUT.PUT_LINE(‘表已更新 表已更新'); 表已更新 END IF; END; 只有在 DML 语句影响一行 / 或多行时， 或多行时，才返回 True
显式游标
SQL>SET SERVER OUTPUT ON SQL>DECLARE my_toy_price toys.toyprice%TYPE; CURSOR toy_cur IS SELECT toyprice FROM toys WHERE toyprice<250; BEGIN 打开游标 OPEN toy_cur; LOOP FETCH toy_cur INTO my_toy_price; EXIT WHEN toy_cur%NOTFOUND; DBMS_OUTPUT.PUT_LINE ('TOYPRICE=:玩具单价 ：'||my_toy_price); 玩具单价=： 玩具单价 END LOOP; 关闭游标 CLOSE toy_cur; END;
显示游标属性
%ROWCOUNT – SQL 语句影响的行数 即返回游标所找到的数据行数 返回一个 语句影响的行数,即返回游标所找到的数据行数 即返回游标所找到的数据行数(返回一个 整数)。示例如下： 整数 。示例如下： LOOP FETCH c1 INTO my_ename, my_depton; IF c1%ROWCOUNT > 10 THEN ... END IF; ... END LOOP; %ISOPEN - 游标是否打开，即游标打开为 游标是否打开，即游标打开为TRUE,反之则为 反之则为FALSE。示例如下： 反之则为 。示例如下： IF c1%ISOPEN THEN ——如果游标打开，则执行相应操作 如果游标打开， 如果游标打开 ... ELSE ——如果游标未打开，则打开游标 如果游标未打开， 如果游标未打开 OPEN c1; END IF;
显式游标工作进程
语句的结果集相关联， 把游标与 SQL 语句的结果集相关联，并且定义游 标的特征，如是否能够更新游标中的行。 标的特征，如是否能够更新游标中的行。 语句以填充游标。 执行 SQL 语句以填充游标。 检索想要查看的游标中的行。 检索想要查看的游标中的行。 根据需要， 根据需要，对游标中当前位置的行执行修改操作 更新或删除）。 （更新或删除）。 关闭游标。 关闭游标。
显式游标使用顺序
1、定义游标 2、打开游标 3、使用游标 4、关闭游标
定义显式游标
• DECLARE语句用于创建游标。 语句用于创建游标。 语句用于创建游标 • 将光标与一查询语句联系起来，用 将光标与一查询语句联系起来， 来引入表中的记录。 来引入表中的记录。 语法 游标名(参数) 子句； CURSOR 游标名(参数) IS SELECT 子句； 例如： 例如： EMP; CURESOR v_cur IS SELECT * FROM EMP;
使用显式游标
• 打开游标 OPEN 游标名 游标名; • 检索记录 FETCH语句检索记录集中的行，每次一行， 每执行一次FETCH，游标前进到记录集的下 一行。 FETCH 游标名 INTO 变量名； 变量名； • 关闭游标 CLOSE 游标名;（ 游标名 （注：必须关闭游标，以释 放分配给游标的所有资源。）
显式游标使用顺序
stud_no Stud_name James Roger George Stud_mrks 45 44 30
声明游标
打开游标
1 2
数据库
3
提取行 提取行 提取行 提取行
关闭游标
变量
显示游标属性
%FOUND – SQL 语句影响了一行或多行时为 TRUE,即游标找到数据就 即游标找到数据就 返回TRUE,反之则返回 反之则返回FALSE。示例如下： 返回 反之则返回 。示例如下： LOOP FETCH c1 INTO var1,var2; ——提取数据到变量中 提取数据到变量中 IF c1%FOUND THEN ——如果提取到数据，则进行处 如果提取到数据， 如果提取到数据 理 ... ELSE ——如果未提取到数据，则退出循 如果未提取到数据， 如果未提取到数据 环 EXIT; END IF; END LOOP; %NOTFOUND – SQL 语句没有影响任何行时为TRUE,即游标没有找到 语句没有影响任何行时为 即游标没有找到 数据就返回TRUE,反之则返回 反之则返回FALSE 数据就返回 反之则返回 LOOP FETCH c1 INTO var1,var2; ——提取数据到变量中 提取数据到变量中 EXIT WHEN c1%NOTFOUND; ——未找到时退出 未找到时退出 ... END LOOP;
循环游标的应用
为了简化游标操作， 语句提供了游标FOR循环语句。 循环语句。 为了简化游标操作，PL/SQL语句提供了游标 语句提供了游标 循环语句 工作原理： 工作原理： 一个游标FOR循环可以隐含的实现 循环可以隐含的实现OPEN,FETCH,CLOSE 一个游标 循环可以隐含的实现 游标以及循环处理结果集的功能。 游标以及循环处理结果集的功能。 其步骤是： 其步骤是： 当进入循环时，自动打开一个已经声明的游标， 当进入循环时，自动打开一个已经声明的游标，并提取第一 行游标数据； 行游标数据；当处理完当前所提取的数据而进入下一次循环 自动提取下一行游标数据； 时，自动提取下一行游标数据；当提取完结果集中的所有数 据行后结束循环，并自动关闭游标。除此之外， 据行后结束循环，并自动关闭游标。除此之外，当在游标 FOR循环语句中调用 循环语句中调用EXIT或GOTO语句，或者由于发生异 语句， 循环语句中调用 或 语句 常错误等原因而导致程序跳出循环时， 常错误等原因而导致程序跳出循环时，PL/SQL均能自动关 均能自动关 闭游标。 闭游标。