PL/SQL Step By Step（二）

游标是PL/SQL非常重要的一部分，也是很多人为什么使用PL/SQL的原因。游标能够让开发者对数据库查询进行记录级别的控制。也就是说，可以对查询返回的数据一次一行进行步进，并且在两行之间停顿一段时间来进行一些特别的处理操作。

游标分为两种：隐式游标和显式游标。下面我们先介绍隐式游标，一会在介绍更加强大的显示游标。

1.隐式游标

隐式游标是不需要定义的，说白了，隐式游标就是数据操作语言DML，例如SELECT语句。也就是我们上一讲中提到的PL/SQL处理部分中的语句。先看一个例子：

DECLARE
  v_port_name varchar2(80);
BEGIN;
  SELECT PORT_NAME 
  INTO v_port_name
  FROM PORTS
  WHERE PORT_ID = 100;
  DBMS_OUTPUT.PUT_LINE(v_port_name);
END;

上面的这个SELECT语句就是一个隐式游标的例子。需要注意的是INTO关键字，基于SELECT语句的隐式游标必须使用INTO关键字用一系列变量来取得查询出来的各列的值。

隐式游标从本质上来说就是嵌在PL/SQL中的SQL语句，只要是合法的SQL语句就行。这不局限于SELECT语句，还可以使DELETE，INSERT，UPDATE语句，甚至包括DECODE语句。我们前面提到，DECODE是唯一一个不能作为PL/SQL表达式的SQL函数，但是它依然是合法的SQL语句，因此嵌入PL/SQL也可以构成隐式游标，这一点需要注意。

需要特别提一下：在隐式游标中定义的SELECT语句必须且只能返回一条记录，否则的话，Oracle会报错。这一点也非常重要。有人对这一点很困惑，但其实道理还是比较简单的：前面我们说了，基于SELECT语句的隐式游标必须使用INTO将查询到的结果的每一列以此存进定义的变量。大家都知道，变量一次只能处理一个值，所以隐式游标SELECT只允许返回一条记录。

如果SELECT返回的记录超过一条，那么换成我们下面要讲的显式游标吧。

2.显式游标

显式游标是在PL/SQL生命部分定义，并且在处理部分使用的游标。他之所以叫显式游标是因为他在定义的的时候需要命名。在使用显式游标的时候，主要需要和四个关键词打交道，我们逐一讲解：

CURSOR
这个关键字用在PL/SQL声明部分，用来定义显示游标，其格式如下：
CURSOR cursor_name IS a_valid_select_statement

OPEN
这个关键词用在PL/SQL处理部分，用来解析显式游标中定义的SQL语句，并且为执行做准备

FETCH...INTO
这个关键词用来一次获取显式游标查询结果的一行记录，并且将每一行记录的各个字段
分别赋值给一系列变量。其格式为：
FETCH cursor_name INTO val1,...valn

CLOSE
关闭游标，这样PL/SQL解析程序可以释放内存。但是即使不写CLOSE语句，PL/SQL解析程序
最终还是会发现并释放显式游标占用的内存。

说了这么多，我们看一个综合使用了隐式游标和显示有标的例子：

DECLARE
  v_today DATE;
  --定义显式游标开始
  CURSOR cur_cruises IS
    SELECT C.CAPTAIN_ID,
           E.LAST_NAME || ',' || E.FIRST_NAME FULL_NAME
    FROM CRUISES C,
         EMPLOYEES E
    WHERE C.CAPTAIN_ID = E.EMPLOYEE_ID;
      AND E.START_DATE >= v_today
      AND E.END_DATE <= v_today;
    
  --定义显式游标结束
  
  v_captain_id NUMBER(3);
  v_full_name VARCHAR2(40);
BEGIN
  --使用隐式游标开始
  SELECT TRUNC(SYSDATE)
  INTO v_today
  FROM DUAL;
  --使用隐式游标结束
  
  OPEN cur_cruises; --打开游标
  
  LOOP
    FETCH cur_cruises INTO v_captain_id,v_full_name;--获取数据
    
    EXIT WHEN cur_cruises%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE('Captain ID:' || v_captain_id);
    DBMS_OUTPUT.PUT_LINE('Name      :' || v_full_name);
  END LOOP;
  CLOSE cur_cruises;--关闭游标
  
END;

这段代码的意图是非常显而易见的，必要的注释在代码中体现了。显式游标可以通过任何可独立运行的SQL语句来构建，除此之外，在显式游标的定义中可以使用之前定义的变量，上面的代码就是一个很好的例子。

3.游标的属性

上面的代码中出现了一个陌生的东西：%NOTFOUND。这是一种游标的属性。接下来，我们分别介绍游标的集中属性：

NOTFOUND
是一个BOOLEAN类型。当最近一次FETCH操作没有返回记录时，该属性值为真，否则为假。
FOUND
是一个BOOLEAN类型。当最近一次FETCH操作返回记录时，该属性值为真，否则为假。
ISOPEN
是一个BOOLEAN类型。判断游标是否已经打开。
ROWCOUNT
游标目前为止已经处理过的行数。

上面的属性对于隐式游标和显示游标都是存在的。但是有一个问题，隐式游标是没有名字的，没办法向上面例子代码中显示游标一样使用后属性。可以通过下面这种方式：

BEGIN
  UPDATE EMPLOYEES
  SET SALARY = SALARY * 1.5;--加薪啦
  DBMS_OUTPUT.PUT_LINE('Rows Affacted:' || SQL%ROWCOUNT);
END;

通过使用SQL来引用最近的一个隐式游标。

4.循环语句回顾

上一篇博客中，我们介绍了PL/SQL循环语句的四种形式，并且提到了CURSOR循环。但是当时没介绍游标，所以没有详细讲解。现在有了游标的知识，我们来看看CURSOR循环到底是个什么东东。看一个例子：

BEGIN
  FOR rec_employee IN (SELECT EMPLOYEE_ID FROM EMPLOYEES)
  LOOP
    DBMS_OUTPUT.PUT_LINE('IN THE FOR LOOP' || rec_employee.EMPLOYEE_ID);
  END LOOP;
END;

这种循环其实基本上和之前的数值FOR循环一样，只不过数值FOR循环通过一个计数变量来迭代，而这里顺着游标记录一条一条往下从而达到迭代效果。实际上，CURSOR FOR循环自动做了四件事情：

将rec_employee定义为%ROWTYPE类型。
定义了一个匿名的显示游标
自动从头到尾遍历查询出的记录
离开循环，执行CLOSE操作，关闭游标。

5.补充

上面提到了%ROWTYPE类型定义，这里补充一下PL/SQL中的两种高级的变量类型定义方法：%TYPE和%ROWTYPE。

%TYPE：将变量定义为%TYPE左边的东西一样，例如：

DECLARE
  v_name SOMETABLE.USERNAME%TYPE;--将v_name变量定义为和SOMETABLE表的USERNAME字段一样的类型
BEGIN
  --DO STH
END;

%ROWTYPE：将变量定义为和左边东西一行一样的类型（也就是所有字段类型一一对应），上面的rec_employee 就是个例子。

这一讲就到这里，游标是PL/SQL中非常重要的语言特性和组成部分，需要重点掌握。

PS：

美好的周末又到了，可惜周六还是得八点半到实验室，祝博友们周末愉快！^_^

posted @ 2012-03-02 21:36 wawlian 阅读(1666) 评论(0) 编辑收藏举报

刷新页面返回顶部

wawlian

window.onload = function() { dp.SyntaxHighlighter.ClipboardSwf = 'https://files.cnblogs.com/wawlian/clipboard.swf'; dp.SyntaxHighlighter.HighlightAll('code'); };

PL/SQL Step By Step（二）

公告