oracle 游标

本篇主要内容如下：

4.1 游标概念

4.1.1 处理显式游标

4.1.2 处理隐式游标

4.1.3 关于 NO_DATA_FOUND 和 %NOTFOUND的区别

4.1.4  使用游标更新和删除数据

4.2 游标变量

4.2.1  声明游标变量

4.2.2  游标变量操作

---

 

 

 

游标的使用

    在 PL/SQL 程序中，对于处理多行记录的事务经常使用游标来实现。

4.1 游标概念

  在PL/SQL块中执行SELECT、INSERT、DELETE和UPDATE语句时，ORACLE会在内存中为其分配上下文区（Context Area），即缓冲区。游标是指向该区的一个指针，或是命名一个工作区（Work Area），或是一种结构化数据类型。它为应用等量齐观提供了一种对具有多行数据查询结果集中的每一行数据分别进行单独处理的方法，是设计嵌入式SQL语句的应用程序的常用编程方式。

 在每个用户会话中，可以同时打开多个游标，其数量由数据库初始化参数文件中的OPEN_CURSORS参数定义。

对于不同的SQL语句，游标的使用情况不同：

SQL语句	游标
非查询语句	隐式的
结果是单行的查询语句	隐式的或显示的
结果是多行的查询语句	显示的

 

4.1.1 处理显式游标

 

1. 显式游标处理

显式游标处理需四个 PL/SQL步骤:

l 定义/声明游标：就是定义一个游标名，以及与其相对应的SELECT 语句。

格式：

 

     CURSOR  cursor_name [ (parameter[, parameter ] …)] 
            [ RETURN datatype ]
     IS  
        select_statement;

 

游标参数只能为输入参数，其格式为： 

 

parameter_name  [ IN ]  datatype  [ {:= | DEFAULT} expression ]

 

在指定数据类型时，不能使用长度约束。如NUMBER(4),CHAR(10) 等都是错误的。

[RETURN datatype]是可选的，表示游标返回数据的数据。如果选择，则应该严格与select_statement中的选择列表在次序和数据类型上匹配。一般是记录数据类型或带“%ROWTYPE”的数据。

 

l 打开游标：就是执行游标所对应的SELECT 语句，将其查询结果放入工作区，并且指针指向工作区的首部，标识游标结果集合。如果游标查询语句中带有FOR UPDATE选项，OPEN 语句还将锁定数据库表中游标结果集合对应的数据行。

格式：

 

OPEN  cursor_name [ ([parameter => ]  value [ , [parameter => ]  value]…)];

 

在向游标传递参数时，可以使用与函数参数相同的传值方法，即位置表示法和名称表示法。PL/SQL 程序不能用OPEN 语句重复打开一个游标。

 

l 提取游标数据：就是检索结果集合中的数据行，放入指定的输出变量中。 

格式：

 

FETCH  cursor_name  INTO  {variable_list  |  record_variable };

 

 

执行FETCH语句时，每次返回一个数据行，然后自动将游标移动指向下一个数据行。当检索到最后一行数据时，如果再次执行FETCH语句，将操作失败，并将游标属性%NOTFOUND置为TRUE。所以每次执行完FETCH语句后，检查游标属性%NOTFOUND就可以判断FETCH语句是否执行成功并返回一个数据行，以便确定是否给对应的变量赋了值。

l 对该记录进行处理；

l 继续处理，直到活动集合中没有记录；

l 关闭游标：当提取和处理完游标结果集合数据后，应及时关闭游标，以释放该游标所占用的系统资源，并使该游标的工作区变成无效，不能再使用FETCH 语句取其中数据。关闭后的游标可以使用OPEN 语句重新打开。

格式：

 

CLOSE  cursor_name;

 

     注：定义的游标不能有INTO 子句。

 

例1. 查询前10名员工的信息。

 

DECLARE
    CURSOR  c_cursor 
    IS   SELECT  first_name  ||  last_name, Salary 
    FROM  EMPLOYEES 
    WHERE  rownum < 11 ;   
   v_ename  EMPLOYEES.first_name % TYPE;
   v_sal    EMPLOYEES.Salary % TYPE;   
BEGIN
   OPEN  c_cursor;
   FETCH  c_cursor  INTO  v_ename, v_sal;
   WHILE  c_cursor % FOUND LOOP
     DBMS_OUTPUT.PUT_LINE(v_ename || ' --- ' || to_char(v_sal) );
      FETCH  c_cursor  INTO  v_ename, v_sal;
   END  LOOP;
   CLOSE  c_cursor;
END ;

 

例2. 游标参数的传递方法。

 

DECLARE
  DeptRec    DEPARTMENTS % ROWTYPE;
  Dept_name  DEPARTMENTS.DEPARTMENT_NAME % TYPE;
  Dept_loc   DEPARTMENTS.LOCATION_ID % TYPE;
   CURSOR  c1  IS  
   SELECT  DEPARTMENT_NAME, LOCATION_ID  FROM  DEPARTMENTS 
   WHERE  DEPARTMENT_ID  <=   30 ;
  
   CURSOR  c2(dept_no  NUMBER   DEFAULT   10 )  IS
     SELECT  DEPARTMENT_NAME, LOCATION_ID  FROM  DEPARTMENTS 
     WHERE  DEPARTMENT_ID  <=  dept_no;
   CURSOR  c3(dept_no  NUMBER   DEFAULT   10 )  IS  
     SELECT   *   FROM  DEPARTMENTS 
     WHERE  DEPARTMENTS.DEPARTMENT_ID  <= dept_no;
BEGIN
   OPEN  c1;
  LOOP
     FETCH  c1  INTO  dept_name, dept_loc;
     EXIT   WHEN  c1 % NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(dept_name || ' --- ' || dept_loc);
     END  LOOP;
     CLOSE  c1;

     OPEN  c2;
    LOOP
         FETCH  c2  INTO  dept_name, dept_loc;
         EXIT   WHEN  c2 % NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(dept_name || ' --- ' || dept_loc);
     END  LOOP;
     CLOSE  c2;

     OPEN  c3(dept_no  => 20 );
    LOOP
         FETCH  c3  INTO  deptrec;
         EXIT   WHEN  c3 % NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(deptrec.DEPARTMENT_ID || ' --- ' || deptrec.DEPARTMENT_NAME || ' --- ' || deptrec.LOCATION_ID);
     END  LOOP;
     CLOSE  c3;
END ;

 

2.游标属性

 Cursor_name%FOUND     布尔型属性，当最近一次提取游标操作FETCH成功则为 TRUE,否则为FALSE；

 Cursor_name%NOTFOUND   布尔型属性，与%FOUND相反；

 Cursor_name%ISOPEN     布尔型属性，当游标已打开时返回 TRUE；

 Cursor_name%ROWCOUNT   数字型属性，返回已从游标中读取的记录数。

 

例3：给工资低于1200 的员工增加工资50。

 

DECLARE
   v_empno  EMPLOYEES.EMPLOYEE_ID % TYPE;
   v_sal      EMPLOYEES.Salary % TYPE;
    CURSOR  c_cursor  IS   SELECT  EMPLOYEE_ID, Salary  FROM  EMPLOYEES; 
BEGIN
    OPEN  c_cursor;
   LOOP
       FETCH  c_cursor  INTO  v_empno, v_sal;
       EXIT   WHEN  c_cursor % NOTFOUND; 
       IF  v_sal <= 1200   THEN
             UPDATE