第六章 游标

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;  

  


  
    
    分享到：
      
      
    
  

  
    
      win7 右键我的电脑，“管理”打不开处理方 ...
      |
      第五章 使用PL/SQL
    
  
  
    
      2011-05-24 15:26
      浏览 1189
      评论(0)
      
      
      分类:数据库      
      查看更多