DB2数据库存储过程小结_数据库问题与总结资源-CSDN文库

存储过程

需积分: 16 25 浏览量 2010-03-29 22:23:26 上传评论收藏 92KB DOC 举报

资源推荐

资源详情

资源评论

查看文章



存储过程之游标笔记小结 1[转]

2008-12-27 11:24

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

剩余24页未读，继续阅读

评论收藏

内容反馈

hualuxidian

粉丝: 2
资源: 12

DB2数据库存储过程小结

在db2数据库写存储过程

DB2数据库存储过程入门

DB2存储过程官方教程

db2数据库存储过程.ppt

DB2存储过程写法介绍

DB2数据库使用小结

DB2存储过程

db2数据库安装包、db2数据库安装驱动

DB2数据库存储过程技术文档

DB2数据库备份恢复小结.doc

db2数据库性能优化小技巧

DB2存储过程介绍

DB2 存储过程 教程

DB2存储过程例

DB2数据库函数小结

DB2存储过程.chm

db2数据库培训文档

DB2数据库性能调整和优化 牛新庄 PDF

ＪＤＢＣ连接ＤＢ２数据库需要的ｊａｒ包

DB2数据库用户手册

Linux系统重启db2数据库命令实例详解.docx

IBM出的DB2存储过程教程

DB2存储过程语法格式

DB2存储过程-基础教程

DB2 存储过程SQL

JDBC调DB2存储过程

最新资源

DB2 存储过程教程

DB2数据库性能调整和优化牛新庄 PDF