CSDN博客

img lizhenhuatop

用好游标

发表于2008/10/2 9:15:00  1554人阅读

 

    一个游标(cursor)可以被看作指向结果集(a set of rows)中一行的指针(pointer)。游标每个时间点只能指向一行,但是可以根据需要指向结果集中其他的行。
例如:SELECT * FROM employees WHERE sex='M'会返回所有性别为男的雇员,在初始的时候,游标被放置在结果集中第一行的前面。使游标指向第一行,要执行FETCH。当游标指向结果集中一行的时候,可以对这行数据进行加工处理,要想得到下一行数据,要继续执行FETCH。FETCH操作可以重复执行,直到完成结果集中的所有行
 
    在存储过程中使用游标,有如下几个步骤:
      声明游标、打开游标、根据需要一次一行,讲游标指向的数据取到本地变量(local variables)中、结束时关闭游标
   
   声明游标:
    >>-DECLARE--cursor-name--CURSOR----+------------+--------->
                                                          '-WITH HOLD--'
    >-----+--------------------------------+--------------------->
             |                       .-TO CALLER--.  |
              '-WITH RETURN--+------------+--'
                                      '-TO CLIENT--'
    >----FOR--+-select-statement-+----------------------------><
                    '-statement-name---'    
  WITH RETURN子句用于将游标所定义的结果集传递给另一个存储过程或者应用(an application)
  如果select语句中包含CURRENT DATE, CURRENT TIME和CURRENT TIMESTAMP,所有的FETCH语句都会返回相同的日期、时间、时间戳值,因为这些特定寄存器是在打开游标(OPEN CURSOR)的时候进行检查的
   
    FETCH语法:
    >>-FETCH--+-------+---cursor-name---------->
                     '-FROM--'
                        .-,----------------.
                        V                        |
    >------INTO-----host-variable---+----------><
    FETCH语句使游标指向结果集中的下一行,并且将游标现在的位置赋值给特定的过程变量
例如:一个公司,按照如下规则计算加薪金额:
 1.公司中除了总裁(president)外,所有人都会至少增加p_min的薪水
 2.任何奖金(bonus)高于$600的员工都会另增加4%
 3.员工的佣金(commission)越高,增加越少。佣金(commission)少于$2000的另增加3%,佣金(commission)在$2000到$3000的增加另2%
 4.佣金(commission)高于$3000的另增加1%
 5.无论每个员工增加多少,增加比例不能高于p_max
CREATE PROCEDURE total_raise ( IN  p_min DEC(4,2)
                                           , IN  p_max DEC(4,2)
                                           , OUT p_total DEC(9,2) )
    LANGUAGE SQL
    SPECIFIC total_raise     
tr: BEGIN
    -- Declare variables
    DECLARE v_salary DEC(9,2);
    DECLARE v_bonus  DEC(9,2);
    DECLARE v_comm   DEC(9,2);
    DECLARE v_raise  DEC(4,2);
    DECLARE v_job    VARCHAR(15) DEFAULT 'PRES';
    -- Declare returncode
    DECLARE SQLSTATE CHAR(5);

    -- Procedure logic
    DECLARE c_emp CURSOR FOR
        SELECT salary, bonus, comm
        FROM   employee
        WHERE  job != v_job;                              -- (1)这里的SELECT定义了结果集中的行和
                                                                          列
        OPEN c_emp;                                        -- (2)
 
        SET p_total = 0;
        FETCH FROM c_emp INTO v_salary, v_bonus, v_comm;  -- (3)得到一行数据,并将其
                                                                                           复制给本地变量
 
        WHILE ( SQLSTATE = '00000' ) DO                   --SQLSTATE 00000: 操作执行成功,
                                                                            并且未产生任何类型的警告或异常情
                                                                            况。通过这个可以检查是否到达最后一行
           SET v_raise = p_min;
           IF ( v_bonus >= 600 ) THEN
               SET v_raise = v_raise + 0.04;
           END IF;

           IF ( v_comm < 2000 ) THEN
               SET v_raise = v_raise + 0.03;
           ELSEIF ( v_comm < 3000 ) THEN
               SET v_raise = v_raise + 0.02;
           ELSE
               SET v_raise = v_raise + 0.01;
           END IF;

           IF ( v_raise > p_max ) THEN
               SET v_raise = p_max;
           END IF;

           SET p_total = p_total + v_salary * v_raise;
           FETCH FROM c_emp INTO v_salary, v_bonus, v_comm;  -- (4)在WHILE逻辑中得到
                                                                                              更多的行数据
       END WHILE;

       CLOSE c_emp;                                          -- (5)
   END tr
    如果只是想把结果集中的第一个值复制给本地变量,而声明一个游标是不恰当的,因为打开游标会耗费很多资源。所以如下这段代码:
DECLARE c_tmp CURSOR FOR
        SELECT c1
        FROM t1;
OPEN c_emp;
FETCH FROM c_emp INTO v_c1;
CLOSE c_emp;
应当用有FETCH FIRST 1 ROW ONLY的子句的SQL语句:SELECT c1 INTO v_c1 FROM t1 FETCH FIRST 1 ROW ONLY;
  
  positioned delete:利用游标删除当前行 
  一个用于删除的游标(a deletable cursor)应该符合以下的要求:
     1.每个outer fullselect中的FROM子句只跟一个表有关
     2.outer fullselect不包含VALUES, GROUP BY, 或者HAVING子句,并且不包括列函数
     3.outer fullselect的select列表中不包含DISTINCT
     4.select语句不包含ORDER BY或FOR READ ONLY子句
     5.游标是静态定义的,或者明确了FOR UPDATE子句
>>-DELETE FROM-|----table-name---------|--------------->
                        +-----view-name---------+
>----WHERE CURRENT OF--cursor-name--------------------><
例如:在emp_act表中,如果记录的时间比输入参数p_date早的话,就将该记录删除,并返回删除记录总数
CREATE PROCEDURE cleanup_act ( IN  p_date    DATE
                                             , OUT p_deleted INT )
    LANGUAGE SQL
    SPECIFIC cleanup_act                         
ca: BEGIN
    -- Declare variable
    DECLARE v_date DATE;
    -- Declare returncode
    DECLARE SQLSTATE CHAR(5);

    -- Procedure logic

    DECLARE c_emp CURSOR FOR                       -- (1)和上面那种read-only cursor语法
                                                                      类似,只是多了FOR UPDATE
        SELECT emendate
        FROM emp_act
    FOR UPDATE;

    OPEN c_emp;

    FETCH FROM c_emp INTO v_date;                 --注意此处,不要落了
    SET p_deleted = 0;

    WHILE ( SQLSTATE = '00000' ) DO
        IF ( v_date < p_date ) THEN
            DELETE FROM emp_act
            WHERE CURRENT OF c_emp;                -- (2)
            SET p_deleted = p_deleted + 1;
        END IF;
        FETCH FROM c_emp INTO v_date;
    END WHILE;
    CLOSE c_emp;
END ca
直接用DELETE语句删除而不用游标被称作searched delete。像上例这种情况,采用searched delete会比使用positioned delete效率更高。但用positioned delete可以处理更复杂的逻辑
 
   Positioned Update
  一个用于更新的游标(A cursor is updatable)应该The cursor is deletable
>>-UPDATE----+-table-name-------------------+-------------->
                     +-view-name--------------------+
>-----SET--| assignment-clause |--------------------------->
>-----WHERE CURRENT OF--cursor-name-----------------------><
CREATE PROCEDURE upd_raise ( IN p_min DEC(4,2)
                                         , IN p_max DEC(4,2) )
LANGUAGE SQL
    SPECIFIC upd_raise                        
ur: BEGIN
    -- Declare variables
    DECLARE v_salary DEC(9,2);
    DECLARE v_bonus  DEC(9,2);
    DECLARE v_comm   DEC(9,2);
    DECLARE v_raise  DEC(4,2);
    -- Declare returncode
    DECLARE SQLSTATE CHAR(5);
    -- Procedure logic
    DECLARE c_emp CURSOR FOR
        SELECT salary, bonus, comm
        FROM employee
        WHERE job!='PRES'
    FOR UPDATE OF salary;                      -- (1)如果只是更新表中的一部分字段,可以利用
                                                                  FOR UPDATE OF <column list>提高效
                                                           率,让DB2引擎知道只有这些特定列要UPDATE
    OPEN c_emp;
    FETCH FROM c_emp INTO v_salary, v_bonus, v_comm;
    WHILE ( SQLSTATE = '00000' ) DO
        SET v_raise = p_min;
        IF ( v_bonus >= 600 ) THEN
            SET v_raise = v_raise + 0.04;
        END IF;
        IF ( v_comm < 2000 ) THEN
            SET v_raise = v_raise + 0.03;
        ELSEIF ( v_comm < 3000 ) THEN
            SET v_raise = v_raise + 0.02;
        ELSE
            SET v_raise = v_raise + 0.01;
        END IF;
        IF ( v_raise > p_max ) THEN
            SET v_raise = p_max;
        END IF;
        UPDATE employee                                  
           SET salary = v_salary * (1 + v_raise)
         WHERE CURRENT OF c_emp;
        FETCH FROM c_emp INTO v_salary, v_bonus, v_comm;
    END WHILE;
    CLOSE c_emp;
END ur
 
    使用游标时候的COMMIT和ROLLBACK:
      数据库程序中很重要的一点就是事务处理(transaction或者the unit of work(UOW))。事务当中的任何一部分失败,整个事物就会失败。利用COMMIT和ROLLBACK进行适当的事务控制对于保证数据完整性来说是至关重要的。
      当在使用游标的时候使用COMMIT或者ROLLBACK语句时,游标的行动取决于是否在生命的时候加了WITH HOLD子句。如果一个游标在声明的时候没有指定WITH HOLD,那么它的所有资源(游标,锁,大对象数据类型或者LOB locators)都将在COMMIT或者ROLLBACK之后被释放。因此,如果需要在完成一个事务之后使用游标,就必须重新打开游标,并从第一行开始执行。如果定义了一个游标WITH HOLD
,游标就会在事务之间保存它的位置和锁(lock)。需要明白的是,只有保证游标位置的锁被held了。
  锁(lock)是个数据库对象(a database object),我们用它来控制多个应用访问同一个资源的方式。而一个LOB locator使存储在本地变量中的4字节的值,程序可以用它来查到数据库系统中的LOB对象的值
      定义了WITH HOLD的游标在COMMIT之后
       1.仍然保证是打开(open)的
       2.游标指向下一个满足条件的行之前
       3.在COMMIT语句之后只允许FETCH和CLOSE
       4.Positioned delete和positioned update只在同一事务中fetch的行上可用
       5.所有的LOB locators会被释放
       6.除了保存声明为WITH HOLD的游标位置的锁,其他锁都会释放
       7.当执行了数据修改语句或者含有WITH HOLD游标的修改语句被commit的时候
      所有定义为WITH HOLD的游标在ROLLBACK之后:
       1.所有游标会被关闭
       2.所有在该事务中的锁会被释放
       3.所有的LOB locators会被freed
例如:
CREATE PROCEDURE update_department ( )
    LANGUAGE SQL
    SPECIFIC upd_dept                            
ud: BEGIN
    -- Declare variable
    DECLARE v_deptno CHAR(3);
    -- Declare returncode
    DECLARE SQLSTATE CHAR(5);
    DECLARE c_dept CURSOR WITH HOLD FOR
        SELECT deptno
        FROM department
    FOR UPDATE OF location;
    -- Declare condition handler
    DECLARE CONTINUE HANDLER FOR SQLSTATE '24504', SQLSTATE '24501'
      L1: LOOP                                                          -- (1)
         LEAVE L1;
      END LOOP;
    -- Procedure logic
    OPEN c_dept;                                                       --打开游标,指向第一行前面的位置
    FETCH FROM c_dept INTO v_deptno;                                    -- (2)
    UPDATE department SET location='FLOOR1' WHERE CURRENT OF c_dept;    -- (3)
    COMMIT;                                                             -- (4)因为该游标声明为WITH
                                                                            HOLD,此时游标依旧打开,并且
                                                                            指向第二行前面的位置。此时表
                                                                            中第一行的锁释放了,且第一行
                                                                            的值更新为FLOOR1
    FETCH FROM c_dept INTO v_deptno;                                    -- (5)得到第二行数据,执行成功
    COMMIT;                                                             -- (6)COMMIT后游标指向第三行之
                                                                                前的位置,此时并没有被fetched
    UPDATE department SET location='FLOOR2' WHERE CURRENT OF c_dept;    -- (7)这行
                                                                         命令执行失败,因为此时游标没有指向
                                                                      任何行,此时游标在第二行和第三行之间
    FETCH FROM c_dept INTO v_deptno;                                    -- (8)成功
    UPDATE department SET location='FLOOR3' WHERE CURRENT OF c_dept;    -- (9)成功
    COMMIT;                                                             -- (10)
    FETCH FROM c_dept INTO v_deptno;                                    -- (11)成功
    UPDATE department SET location='FLOOR4' WHERE CURRENT OF c_dept;    -- (12)成
                                                                            功,此时第三行和第四行的值都变了
    ROLLBACK;                                                           -- (13)第四行的值还原。
                                                                          ROLLBACK之后游标关闭了,
                                                                          所有的锁也都释放了
    FETCH FROM c_dept INTO v_deptno;                                    -- (14)错误
    UPDATE department SET location='FLOOR5' WHERE CURRENT OF c_dept;    -- (15)
    CLOSE c_dept;
    RETURN 0;
END ud
上述存储过程执行前:
DEPTNO LOCATION
------ --------
A00    -
B01    -
C01    -
D01    -
D11    -
上述存储过程执行后:
DEPTNO LOCATION
------ --------
A00    FLOOR1
B01    -
C01    FLOOR3
D01    -
D11    -
如果上例中的游标没有声明为WITH HOLD,从(5)到(15)的执行都会失败。因为游标会在COMMIT或ROLLBACK之后隐性关闭
 
 
   存储过程中的Save Points可用于保存事务回滚的间断点
>>- SAVEPOINT--savepoint-name----+--------+--------------------->
                                                    '-UNIQUE-'
>--ON ROLLBACK RETAIN CURSORS--+--------------------------+----><
                                                  '-ON ROLLBACK RETAIN LOCKS-'
 
savepoint-name不能以'SYS'开头,否则会报SQLSTATE 42939的错误。UNIQUE选项表示这个save point name不会在Save Point活动期中被reused。ON ROLLBACK RETAIN CURSORS使游标在rollback发生之后还被保留。附加的ON ROLLBACK RETAIN LOCKS防止在ROLLBACK之后锁丢失
  在一个事务中,可以定义多个save points
  使用save points的伪代码:
savepoint A;
Do program logic;
savepoint B;
Do more program logic;
savepoint C;
Do even more program logic;
之后就可以用含有SAVE POINT的ROLLBACK:ROLLBACK TO SAVEPOINT savepoint-name
如果ROLLBACK到了最后一个save point,那么这个save point之前的save point都还是活动的(active),你依旧可以ROLL BACK到更早的save point
例如:
savepoint a;
Do program logic;
savepoint b;
Do more program logic;
savepoint c;
Do even more program logic;
ROLLBACK TO SAVEPOINT c;           (1)将事务数据返回到save point c
Do some new logic;
ROLLBACK TO SAVEPOINT a;           (2)将事务数据返回到save point a
Do some more logic;
ROLLBACK TO SAVEPOINT b;           (3)错误,因为此时save point b已经不存在了
 
游标除了可以在存储过程中处理数据外,还可以用于返回结果集
比如:
CREATE PROCEDURE read_emp ( )
    LANGUAGE SQL
    SPECIFIC read_emp                           
DYNAMIC RESULT SETS 1                            --(1)如果想用游标返回结果集到一个应用程序,必须声明DYNAMIC RESULT SETS
re: BEGIN
    -- Procedure logic
    DECLARE c_emp CURSOR WITH RETURN FOR         --(2)
        SELECT salary, bonus, comm
        FROM employee
        WHERE job!='PRES';
    OPEN c_emp;                                  --(3)为客户端保持游标打开
END re
此时只返回了所有符合条件的员工的salary, bonus和commission字段。之后,结果集就可以被另外的存储过程或客户端程序调用
   
    在存储过程中我们除了数据操作语言(Data Manipulation Language (DML):SELECT, DELETE和UPDATE),还可以使用数据定义语言(Data Definition Language (DDL)),比如定义一个表。我们可以在存储过程中定义一个表,然后用游标返回结果集。但是游标声明必须在BEGIN ... END的一开始,但如果这样的话,此时表还没有建立,编译时会报错。但如果先声明表,编译也会报错。这是我们可以用BEGIN ... END可以嵌套这个特性。我们在存储过程末尾嵌套一个BEGIN ... END来声明游标
如:
CREATE PROCEDURE create_and_return ( )
    LANGUAGE SQL
    SPECIFIC create_and_return                  
DYNAMIC RESULT SETS 1
cr: BEGIN
    -- Procedure logic
    CREATE TABLE mytable (sid INT);
    INSERT INTO mytable VALUES (1);
    INSERT INTO mytable VALUES (2);
    BEGIN                                        --(1)
    DECLARE c_cur CURSOR WITH RETURN
        FOR SELECT *
            FROM mytable;
    OPEN c_cur;                                  --(2)
    END;                                         --(3)OPEN必须在这个嵌套的BEGIN ... END中,因为游
                                                      标的定义只在这个BEGIN ... END中有效
END cr
   
    有时我们不只返回一个结果集,若返回多个结果集,要求:
     1.CREATE PROCEDURE中的DYNAMIC RESULT SETS子句写明想返回的结果集的数量
     2.为每一个结果集声明含有WITH RETURN的游标
     3.保证所有游标返回给客户端是打开的
 例如:
CREATE PROCEDURE read_emp_multi ( )
    LANGUAGE SQL
    SPECIFIC read_emp_multi               
    DYNAMIC RESULT SETS 3                  --(1)
re: BEGIN
    -- Procedure logic
    DECLARE c_salary CURSOR WITH RETURN FOR
        SELECT salary
          FROM employee;
    DECLARE c_bonus CURSOR WITH RETURN FOR
        SELECT bonus
          FROM employee;
    DECLARE c_comm CURSOR WITH RETURN FOR
        SELECT comm
          FROM employee;
    OPEN c_salary;
    OPEN c_bonus;
    OPEN c_comm;
END re
游标打开的顺序反映了结果集返回给客户端的顺序
    当在存储过程中使用游标的时候,会影响其他应用和人们使用这个数据库。锁的类型取决于游标的类型和DB2的隔离级别(isolation level)
 
    锁模式(Lock Modes):
Table  Row Lock Descriptions

Lock Mode

Applicable Object Type

Description

S (Share)

Rows, blocks, tables

The lock owner and all concurrent applications can read, but not update, the locked data.

U (Update)

Rows, blocks, tables

The lock owner can update data. Other UOW can read the data in the locked object, but cannot attempt to update it.

X (Exclusive)

Rows, blocks, tables, bufferpools

The lock owner can both read and update data in the locked object. Only uncommitted read applications can access the locked object.

 
 
 

一.概念:

游标:用来查询数据库,获取记录集合(结果集)的指针,可以让开发者一次访问一行结果集,在每条结果集上作操作。

二.分类:

1.静态游标:
分为显式游标和隐式游标。

2REF游标:
是一种引用类型,类似于指针。

三.详细内容:

1.显式游标:

 CURSOR游标名(参数) [返回值类型] IS
  Select
语句
生命周期:

a.打开游标(OPEN)
解析,绑定。。。不会从数据库检索数据

b.从游标中获取记录(FETCH INTO)
执行查询,返回结果集。通常定义局域变量作为从游标获取数据的缓冲区。

c.关闭游标(CLOSE)
完成游标处理,用户不能从游标中获取行。还可以重新打开。

选项:参数和返回类型
set serveroutput on
declare
 cursor emp_cur ( p_deptid in number) is
select * from employees where department_id = p_deptid;

l_emp employees%rowtype;
begin
 dbms_output.put_line('Getting employees from department 30');
open emp_cur(30);
 loop
  fetch emp_cur into l_emp;
  exit when emp_cur%notfound;
  dbms_output.put_line('Employee id '|| l_emp.employee_id || ' is ');
  dbms_output.put_line(l_emp.first_name || ' ' || l_emp.last_name);
 end loop;
 close emp_cur;

 dbms_output.put_line('Getting employees from department 90');
open emp_cur(90);
 loop
  fetch emp_cur into l_emp;
  exit when emp_cur%notfound;
  dbms_output.put_line('Employee id '|| l_emp.employee_id || ' is ');
  dbms_output.put_line(l_emp.first_name || ' ' || l_emp.last_name);
 end loop;
 close emp_cur;
end;
/

 

2.隐式游标:

不用明确建立游标变量,分两种:
a.
PL/SQL中使用DML语言,使用ORACLE提供的名为SQL的隐示游标
b.CURSOR FOR LOOP
,用于for loop语句
a.
举例:

declare
begin
 update departments set department_name=department_name;
 --where 1=2;
 
 dbms_output.put_line('update '|| sql%rowcount ||' records');
end;
/


b.
举例:

declare
begin
 for my_dept_rec in ( select department_name, department_id from departments)
 loop
  dbms_output.put_line(my_dept_rec.department_id || ' : ' || my_dept_rec.department_name);
 end loop;
end;
/


c.
举例:

单独select

declare
 l_empno emp.EMPLOYEE_ID%type;
-- l_ename emp.ename%type;
begin
 select EMPLOYEE_ID   
  into l_empno
 from emp;
 --where rownum =1;
 dbms_output.put_line(l_empno);
end;
/
使用INTO获取值,只能返回一行。

 

游标属性:

%FOUND:变量最后从游标中获取记录的时候,在结果集中找到了记录。
%NOTFOUND
:变量最后从游标中获取记录的时候,在结果集中没有找到记录。
%ROWCOUNT
:当前时刻已经从游标中获取的记录数量。
%ISOPEN
:是否打开。


Declare
 Cursor emps is
 Select * from employees where rownum<6 order by 1;
 
 Emp employees%rowtype;
 Row number :=1;
Begin
 Open emps;
 Fetch emps into emp;
 
 Loop
  If emps%found then
   Dbms_output.put_line('Looping over record '||row|| ' of ' || emps%rowcount);
   Fetch emps into emp;
   Row := row + 1;
  Elsif emps%notfound then
   Exit;  ---exit loop, not IF
  End if;
 End loop;
 
 If emps%isopen then
  Close emps;
 End if;
End;
/

 

显式和隐式游标的区别:

尽量使用隐式游标,避免编写附加的游标控制代码(声明,打开,获取,关闭),也不需要声明变量来保存从游标中获取的数据。

 

3REF CURSOR游标:

动态游标,在运行的时候才能确定游标使用的查询。分类:
1
.强类型(限制)REF CURSOR,规定返回类型
2
.弱类型(非限制)REF CURSOR,不规定返回类型,可以获取任何结果集。


TYPE ref_cursor_name IS REF CURSOR [RETURN return_type]


Declare
 Type refcur_t is ref cursor;
 
 Type emp_refcur_t is ref cursor return employee%rowtype;
Begin
 Null;
End;
/


强类型举例:

declare
 --
声明记录类型
 type emp_job_rec is record(
  employee_id number,
  employee_name varchar2(50),
  job_title varchar2(30)
 );
 --
声明REF CURSOR,返回值为该记录类型
 type emp_job_refcur_type is ref cursor
  return emp_job_rec;
 --
定义REF CURSOR游标的变量
 emp_refcur emp_job_refcur_type;

 emp_job emp_job_rec;
begin
 open emp_refcur for
  select e.employee_id,
    e.first_name || ' ' ||e.last_name "employee_name",
    j.job_title
  from employees e, jobs j
  where e.job_id = j.job_id and rownum < 11 order by 1;

 fetch emp_refcur into emp_job;
 while emp_refcur%found loop
  dbms_output.put_line(emp_job.employee_name || '''s job is ');
  dbms_output.put_line(emp_job.job_title);
  fetch emp_refcur into emp_job;
 end loop;
end;

阅读全文
0 0

相关文章推荐

img
取 消
img