存储过程及游标

一个游标(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.