Oracle 第6章 游标

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章 子程序、程序包