Oracle 第6章 游标

ttybbpvqhv

　　


　　Oracle 第5章 使用PL/SQL

　　Oracle 第7章 子程序、程序包

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 第5章 使用PL/SQL
　　Oracle 第7章 子程序、程序包