DB2存储过程使用动态游标的例子
游标 应用 实例
游标应用实例全文共四篇示例，供读者参考第一篇示例：游标是一种在数据库中用于处理查询结果集的机制，它可以在查询结果集中移动并访问各个记录。
游标在数据库应用中具有广泛的用途，可以在数据检索、更新和删除等操作中发挥重要的作用。
本文将结合实际案例，介绍游标在数据库应用中的应用示例。
我们来了解一下游标的基本概念。
游标主要用于遍历数据库中的查询结果集，它允许程序员逐条处理查询结果。
游标通常包括打开游标、获取记录、关闭游标等操作。
在数据库应用中，游标经常与存储过程、触发器等数据库对象一起使用，以实现对数据库的复杂操作。
下面我们以一个简单的实例来展示游标在数据库应用中的应用。
假设我们有一个名为“employee”的表，包含员工的姓名和工资信息。
我们需要计算员工的平均工资并输出每个员工的姓名和离平均工资的偏差。
这时就可以使用游标来实现这个需求。
我们需要创建一个存储过程来实现计算平均工资和输出员工信息的功能。
以下是一个简单的存储过程示例：```sqlDELIMITERCREATE PROCEDURE calculate_average_salary()BEGINDECLARE done INT DEFAULT FALSE;DECLARE emp_name VARCHAR(50);DECLARE emp_salary FLOAT;DECLARE avg_salary FLOAT;DECLARE cur CURSOR FOR SELECT name, salary FROM employee;OPEN cur;FETCH cur INTO emp_name, emp_salary;IF emp_salary IS NULL THENLEAVE;END IF;SET avg_salary = (SELECT AVG(salary) FROM employee);WHILE NOT done DOIF emp_salary > avg_salary THENSELECT CONCAT(emp_name, ' has salary above average') AS result;ELSESELECT CONCAT(emp_name, ' has salary below average') AS result;END IF;FETCH cur INTO emp_name, emp_salary;IF emp_salary IS NULL THENSET done = TRUE;END IF;END WHILE;CLOSE cur;ENDDELIMITER ;```在上面的存储过程中，我们首先声明了一些变量用于存储员工的姓名、工资和平均工资信息。
DB2存储过程学习笔记
创建：db2-td@-vf createSQLproc.db2--end@ (此处的@可替换成其他符号)调用：db2call过程名(参数)1 基础--声明变量：DECLARE<variable-name><data-type><DEFAULT constant>--赋值：SET x=10;SET y=(SELECT SUM(c1)from T1);VALUES10INTO x;SELECT SUM(c1)INTO y from T1;--会话全局变量:CREATE VARIABLE var_name DATATYPE[DEAFULT value];2 、数组2.1定义CREATE TYPE mynames as VARCHAR(30)ARRAY[];--定义数组2.2声明DECLARE nameArr mynames;--声明数组2.3赋值SET TESTARR=ARRAY[1,2,3,4,5,6,7,8,9,10];SET TESTARR=ARRAY[VALU ES(1),(2)];--方法1,使用SET语句SELECT SUM(NUM)INTO TESTARR[1]FROM(VALUES(1),(2))AS TEMP(NU M);--方法2,使用VALUES INTO语句VALUES1INTO TESTARR[1];--方法3,使用SELECT INTO语句SET TESTARR[1]=1;--方法4,使用ARRAY构造函数2.4操作数组的函数ARRAY_DELETE：删除数组元素TRIM_ARRAY：从右开始删除指定数目个元素ARRAY_FIRST：返回数组中第一个元素ARRAY_LAST：返回数组中最后一个元素ARRAY_NEXT：返回数组下一个元素ARRAY_PRIOR：返回数组前一个元素ARRAY_VARIABLE：返回参数指定的元素ARRAY_EXISTS：判断数组是否有元素CARDINALITY：返回数组中元素的个数MAX_CARDINALITY：返回数组中元素的个数UNNEST：将数组转换为表3 复合语句语法：label:BEGIN[ATOMIC|NOT ATOMIC]--ATOMIC关键字封装的复合语句被当作一个处理单元--变量声明、过程逻辑等END label4流程控制--条件判断IFIF<condition>THEN<SQL procedure statement>;ELSEIF<condition>THEN<SQL procedure statement>;ELSE<SQL procedure statement>;END IF;IF FRIEND='张三'THENSET MSG='你好，张三';ELSEIF FRIEND='李四'THENSET MSG='你好，李四';ELSESET MSG='对不起，我不认识你';END IF;--循环WhileWHILE<condition>DO<sql statements>;END WHILE;WHILE I<=10DOSET NUM=NUM+I;SET I=I+1;END WHILE;--循环forFOR<loop_name>AS<sql statements>DO<sql statements>;END FOR;FOR TEST AS SELECT I FROM(VALUES(1),(2),(3))AS TEMP(I)DOSET NUM=NUM+I;END FOR;--循环LOOPLABEL:LOOP<sql statements>;LEAVE LABEL;END LOOP LABEL;TEST_LOOP:LOOPSET NUM=NUM+I;SET I=I+1;IF I>10THENLEAVE TEST_LOOP;END IF;END LOOP TEST_LOOP;--循环RepeatREPEAT<sql statements>;UNTIL<condition>END REPEAT;REPEATSET NUM=NUM+I;SET I=I+1;UNTIL I>10END REPEAT;--其他关键字ITERATE label--。
db2存储过程动态游标及函数返回值总结
db2存储过程动态游标及函数返回值总结DB2存储过程是一种在数据库服务器上执行的事务处理程序，它可以包含SQL语句、控制结构和变量。
在存储过程中，我们经常会使用动态游标和函数返回值来实现一些特定的功能。
下面是关于DB2存储过程中动态游标和函数返回值的总结。
一、动态游标1.动态游标是在存储过程中动态定义的一种游标，它可以根据不同的条件进行查询，并返回满足条件的结果集。
动态游标的定义和使用步骤如下：1.1定义游标：使用DECLARECURSOR语句定义游标，并指定游标的名称和返回结果集的查询语句。
1.2打开游标：使用OPEN语句打开游标，并执行查询语句，将结果集保存在游标中。
1.3获取数据：使用FETCH语句获取游标中的数据，并进行相应的处理。
1.4关闭游标：使用CLOSE语句关闭游标，释放资源。
2.动态游标的优势：2.1灵活性：动态游标可以根据不同的条件查询不同的结果集，满足特定的业务需求。
2.2可读性：通过使用动态游标，可以使存储过程的代码更加清晰和易于理解。
2.3性能优化：动态游标可以根据实际情况进行优化，提高查询性能。
3.动态游标的注意事项：3.1游标的生命周期：动态游标的生命周期是在存储过程执行期间，一旦存储过程结束，游标也会自动关闭。
3.2游标的维护成本：动态游标的使用需要消耗一定的系统资源，所以在使用动态游标时需要注意资源的管理。
二、函数返回值1.函数返回值是存储过程中的一个重要特性，它可以将计算结果返回给调用者。
DB2支持返回多个值的函数，可以通过函数返回表、游标或者多个标量值来实现。
2.函数返回值的定义和使用步骤如下：2.1定义函数返回值：在存储过程中使用RETURNS子句定义函数返回的数据类型。
2.2设置函数返回值：在存储过程中使用SET语句设置函数返回的值。
2.3使用函数返回值：在调用存储过程时，可以使用SELECT语句或者VALUES语句获取函数返回的值。
3.函数返回值的优势：3.1灵活性：函数返回值可以根据实际需求返回不同的结果，满足不同的业务场景。
SQLSERVER和DB2存储过程规范实例
• 带输出存储过程示例
CREATE PROCEDURE titles_sum @@TITLE varchar(40) = \'%\', @@SUM money OUTPUT AS SELECT \'Title Name\' = title FROM titles WHERE title LIKE @@TITLE SELECT @@SUM = SUM(price) FROM titles WHERE title LIKE @@TITLE GO 说明 OUTPUT 变量必须在创建表和使用该变量时都进行定义。 参数名和变量名不一定要匹配，不过数据类型和参数位置必须匹配（除非使用 @@SUM = variable 形式）。
• 什么是SQL语言？
SQL语言是应用程序和SQL Server数据库之间的主要 编程接口。使用SQL语言编写代码时，可用两种方法 存储和执行代码。
① 第一种是在客户端存储代码，并创建向数据库管理系统发送S QL命令（或SQL语句）并处理返回结果给应用程序； ② 第二种是将这些发送的SQL语句存储在数据库管理系统中，这 些存储在数据库管理系统中的SQL语句就是存储过程。
• 使用 WITH ENCRYPTION 选项
WITH ENCRYPTION 子句对用户隐藏存储过程的文本。下例创建加密过程， 使用 sp_helptext 系统存储过程获取关于加密过程的信息，然后尝试直接从 sysco mments 表中获取关于该过程的信息。 GO USE pubs GO CREATE PROCEDURE encrypt_this WITH ENCRYPTION AS SELECT * FROM authors GO EXEC sp_helptext encrypt_this
DB2存储过程语法
DB2存储过程语法语法：CREATE PROCEDURE <schema-name>.<procedure-name> (参数) [属性] <语句>--参数：SQL PL 存储过程中有三种类型的参数：IN：输入参数(默认值,也可以不指定)OUT：输出参数INOUT：输入和输出参数--属性1、LANGUAGE SQL指定存储过程使用的语言。
LANGUAGE SQL 是其默认值。
还有其它的语言供选择，比如Java 或者C，可以将这一属性值分别设置为LANGUAGE JAVA 或者LANGUAGE C。
2、DYNAMIC RESULT SETS <n>如果您的存储过程将返回n 个结果集，那么需要填写这一选项。
3、SPECIFIC my_unique_name赋给存储过程一个唯一名称，如果不指定，系统将生成一个惟一的名称。
一个存储过程是可以被重载的，也就是说许多个不同的存储过程可以使用同一个名字，但这些存储过程所包含的参数数量不同。
通过使用SPECIFIC 关键字，您可以给每一个存储过程起一个唯一的名字，这可以使得我们对于存储过程的管理更加容易。
例如，要使用SPECIFIC 关键字来删除一个存储过程，您可以运行这样的命令：DROP SPECIFIC PROCEDURE。
如果没有使用SPECIFIC 这个关键字，您将不得不使用DROP PROCEDURE 命令，并且指明存储过程的名字及其参数，这样DB2 才能知道哪个被重载的存储过程是您想删除的。
4、SQL 访问级别NO SQL：存储过程中不能有SQL 语句CONTAINS SQL：存储过程中不能有可以修改或读数据的SQL 语句READS SQL：存储过程中不能有可以修改数据的SQL 语句MODIFIES SQL：存储过程中的SQL 语句既可以修改数据，也可以读数据默认值是MODIFIES SQL，一个存储过程不能调用具有更高SQL 数据访问级别的其他存储过程。
存储过程游标有用
存储过程游标有⽤-- 语法/*create [or replace] procedure 存储过程名称(参数名1 in|out 数据类型，参数名2 in|out 数据类型,...) as|is-- 声明变量begin-- 过程化语句end;*/--- 根据员⼯编号得到员⼯的年薪create or replace procedure getYearSal(eno in number , yearsal out number)asbeginselect sal * 12 + nvl(comm,0) into yearsal from emp where empno = eno;end;-- 访问存储过程declareys number;begingetYearSal(7788, ys);dbms_output.put_line('年薪'||ys);end;--- 给某员⼯涨⼯资（打印涨前的⼯资和涨后的⼯资）create or replace procedure updateSal(eno in number ,psal in number)isoldsal number;newsal number;begin-- 打印涨前的⼯资select sal into oldsal from emp where empno = eno;dbms_output.put_line('涨前的⼯资：'||oldsal);-- 涨⼯资update emp set sal = sal + psal where empno = eno;commit;-- 打印涨后的⼯资select sal into newsal from emp where empno = eno;dbms_output.put_line('涨后的⼯资：'||newsal);end;-- 访问只有输⼊参数的存储过程call updateSal(7788,100);---- 举例：返回游标的存储过程-- 得到某部门所有的员⼯信息create or replace procedure getEmps(dno in number ,emps out sys_refcursor)asbegin-- 给动态的游标赋值open emps for select * from emp where deptno = dno;end;-- 访问带有输出参数为游标的存储过程declareemps sys_refcursor;prow emp%rowtype;begingetEmps(20, emps);loopfetch emps into prow;exit when emps%notfound;dbms_output.put_line(prow.empno||','||prow.ename);end loop;close emps;end;游标## 四、游标&例外```--- 游标(集合): ⽤来处理返回多⾏记录的问题-- select into 语句只能解决返回⼀⾏记录的问题declarepname emp.ename%type;beginselect ename into pname from emp where deptno = 40; dbms_output.put_line(pname);end;--- 声明游标-- cursor 游标名 is sql查询语句;-- 遍历游标的过程-- 打开游标-- 提取游标中的⼀⾏内容: fetch 游标名 into 变量名;-- 循环语句， exit when 游标名%notfound;-- 关闭游标-- 举例：使⽤游标打印20号部门的员⼯姓名和⼯作declarecursor cur is select ename ,job from emp where deptno = 20; pname emp.ename%type;pjob emp.job%type;begin-- 打开游标open cur;loopfetch cur into pname,pjob;-- 当游标中没有记录时退出exit when cur%notfound;dbms_output.put_line(pname || ','|| pjob);end loop;-- 关闭游标close cur;end;-- 举例-- 使⽤游标对20号部门的员⼯涨⼯资(100)-- 找出20号部门的员⼯编号，更新⼯资declarecursor cur is select empno from emp where deptno = 20; begin-- for 循环:⾃动打开,关闭游标for c in cur loopupdate emp set sal = sal + 100 where empno = c.empno; end loop;end;update emp set sal = sal +100 where deptno = 20;### ⼋、触发器```---- 触发器（监听器): 监听表中的数据是否发⽣了改变/*create or replace trigger 触发器名称before |after -- 改变之前执⾏触发器还是之后执⾏insert|update|deleteon 表 -- 修改的是哪⼀张表[触发器的级别:表级的触发器，⾏级触发器]declarebeginend;*/-- 举例：添加⼀条记录（打印添加了⼀条记录）create or replace trigger insertEmpafterinserton empdeclarebegindbms_output.put_line('添加了⼀条记录');end;insert into emp(empno ,ename) values(1002,'zhangfei');-- 不能给员⼯降薪create or replace trigger notUpdateLowerSalbeforeupdateon empfor each row -- ⾏级的触发器：只要使⽤new,old 就必须使⽤⾏级触发器declarebeginif :new.sal < :old.sal then-- raise_application_error(p1,p2)-- p1 : 错误的编号：- 20001 ~ -20999-- p2 :错误的信息raise_application_error(-20001 , '不能给员⼯降薪');end if;end;update emp set sal = sal - 1 where empno = 7788;-- 使⽤触发器来模拟mysql中⾃增的效果create sequence tseq;create or replace trigger autoIncrementbeforeinserton empfor each rowdeclarebeginselect tseq.nextval into :new.empno from dual;end;insert into emp(empno,ename) values(100,'lisi');select * from emp;```。
DB2存储过程
--CURSOR---------->
>--FOR--+-select-statement
-+-------------><
.-WITHOUT HOLD-.
|--+--------------+---------------------------------------------|
DB2 支持以下创建数组数据类型的语法：
清单 2. 创建数组数据类型的语法
Sql代码
>>-CREATE TYPE—array-type-name--AS--| data-type |--ARRAY--[---------->
.-2147483647-------.
若要更改默认函数路径，则需要更新专用寄存器 CURRENT PATH。
游标
声明
SQL PL 提供 DECLARE cursor 语句来定义一个游标，并提供其他语句来支持返回其他结果集和游标处理。
下面是游标声明的语法： Leabharlann 清单 7. 游标声明的语法
>>-DECLARE--cursor-name
现在可以在 SQL 过程中使用这个数据类型：
清单 3. 在过程中使用数组数据类型
Sql代码
CREATE PROCEDURE PROC_VARRAY_test (out mynames names)
BEGIN
DECLARE v_pnumb numbers;
'-WITH HOLD----'
.-WITHOUT RETURN-------------.
|--+----------------------------+-------------------------------|
DB2存储过程
DB2 存储过程SQL Procedural Language简介DB2 SQL Procedural Language（SQL PL）是SQL Persistent Stored Module 语言标准的一个子集。
该标准结合了SQL 访问数据的方便性和编程语言的流控制。
通过SQL PL 当前的语句集合和语言特性，可以用SQL 开发综合的、高级的程序，例如函数、存储过程和触发器。
这样便可以将业务逻辑封装到易于维护的数据库对象中，从而提高数据库应用程序的性能。
SQL PL 支持本地和全局变量，包括声明和赋值，还支持条件语句和迭代语句、控制语句的转移、错误管理语句以及返回结果集的方法。
变量声明SQL 过程允许使用本地变量赋予和获取SQL 值，以支持所有SQL 逻辑。
在SQL 过程中，在代码中使用本地变量之前要先进行声明。
清单1 中的图演示了变量声明的语法：清单 1. 变量声明的语法SQL-variable-name定义本地变量的名称。
该名称不能与其他变量或参数名称相同，也不能与列名相同。
图1 显示了受支持的DB2 数据类型：图 1. DB2 数据类型DEFAULT值–如果没有指定，在声明时将赋值为NULL。
下面是变量声明的一些例子：∙DECLARE v_salary DEC(9,2) DEFAULT 0.0;∙DECLARE v_status char(3) DEFAULT ‘YES’;∙DECLARE v_descrition VARCHAR(80);∙DECLARE v1, v2 INT DEFAULT 0;请注意，从DB2 version 9.5 开始才支持在一个DECLARE 语句中声明多个相同数据类型的变量。
数组数据类型SQL 过程从9.5 版开始支持数组类型的变量和参数。
要定义一个数组类型的变量，需要先在数据库中创建该类型，然后在过程或应用程序中声明它。
数组是临时的值，可以在存储过程和应用程序中操纵它，但是不能将它存储到表中。
oracle存储过程和游标的使用
oracle存储过程和游标的使⽤oracle存储过程和游标的使⽤(2011-04-19 14:52:47)游标：⽤来查询数据库，获取记录集合（结果集）的指针，我们所说的游标通常是指显式游标，因此从现在起没有特别指明的情况，我们所说的游标都是指显式游标。
要在程序中使⽤游标，必须⾸先声明游标分类：静态游标：分为显式游标和隐式游标。
REF游标：是⼀种引⽤类型，类似于指针。
显式游标：CURSOR 游标名 ( 参数 ) [返回值类型] ISSelect 语句⽣命周期：在⼤多数时候我们在设计程序的时候都遵循下⾯的步骤： 1、打开游标 open cs1; 2、开始循环 while cs1%found loop | for column_name in .. LOOP 3、从游标中取值 fetch .. into.. | 4、检查那⼀⾏被返回 5、处理 6、关闭循环 end loop; 7、关闭游标 if cs1&isopen then close cs1;选项：参数和返回类型set serveroutput ondeclarecursor emp_cur ( p_deptid in number) isselect * from employees where department_id = p_deptid;l_emp employees%rowtype;begindbms_output.put_line('Getting employees from department 30');open emp_cur(30);loopfetch 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_st_name);end loop;close emp_cur;dbms_output.put_line('Getting employees from department 90');open emp_cur(90);loopfetch 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_st_name);end loop;close emp_cur;end;/隐式游标：不⽤明确建⽴游标变量，分两种：1.在PL/SQL中使⽤DML语⾔，使⽤ORACLE提供的名为SQL的隐⽰游标2.CURSOR FOR LOOP，⽤于for loop 语句1举例：declarebeginupdate departments set department_name=department_name;--where 1=2;dbms_output.put_line('update '|| sql%rowcount ||' records');end;/2举例：declarebeginfor my_dept_rec in ( select department_name, department_id from departments)loopdbms_output.put_line(my_dept_rec.department_id || ' : ' || my_dept_rec.department_name); end loop;end;/3举例：单独selectdeclarel_empno emp.EMPLOYEE_ID%type;-- l_ename emp.ename%type;beginselect EMPLOYEE_IDinto l_empnofrom emp;--where rownum =1;dbms_output.put_line(l_empno);end;/使⽤INTO获取值，只能返回⼀⾏。
DB2 中游标的使用以及 存储过程的写法
什么时候才会发生not found异常，以及db2中sqlcode的值是如何变化的？在db2中，一条select 语句也有可能发生not found异常，譬如declare sqlcode integer default 0;declare sql_code integer default 0;declare classCode varchar(40) ;select app_class_code into classCode from kf_app_class where app_name='无效记录';set sql_code=sqlcode;如果此时没有检索到记录，那么sqlcode的值为100，有的话为0;我们可以定义NOT FOUND 异常处理declare sqlcode integer default 0;declare sql_code integer default 0;declare classCode varchar(40) ;begindeclare continue handler for not foundbegin--注如果发生not found那么此时的sqlcode必定为100set sql_code=sqlcode;/*在这里sqlcode的值为100；*/--如果再次得到sqlcode的值那么它的值变为0set sql_code=sqlcode;/*这里sqlcode变成了0，因为上一条语句执行成功了，那么sqlcode变成了0*/end;select app_class_code into classCode from kf_app_class where app_name='无效记录';set sql_code=sqlcode;/*同理此时如果没有取到数据，那么会进declare continue handler ，返回后sqlcode的值也为0*/end;所以我们可以通过两种方法来捕获和处理not found方法1：begindeclare continue handler for not foundbegin--异常处理代码end;sql语句end;方法2：beginsql语句if sqlcode=100 then--异常处理代码end;问题2：定义了游标，怎么fecth一条记录，怎么进行循环？Q:定义了游标假设发生not found 异常，那么是在open cursorName的时候还是在fecth的时候发生异常？A：检验游标中的数据是否取完或者有无记录，应该在fecth的时候，而不是发生在open cursorName 的时候，下面一个例子详细的说明了游标使用过程begindeclare sqlcode integer default 0;declare app_code varchar(10);declare cursor1 cursor for select app_code from kf_app_class ;open cursor1;cursorLoop:loopfecth cursor1 into app_code ;if sqlcode=100 then leave cursorLoop;end if;end loop;end;Q:sqlcode 可以直接用吗？A:在db2中，如果要使用sqlcode那么必须在使用前declare;譬如declare sqlcode integer default 0;if sqlcode =? thenend if;附注db2的其他异常处理对应oracle的when other exceptionsdeclare exit handler for sqlwaring,sqlexcptionbegin－－处理异常end;当程序执行exit handler异常处理后，那么会退出程序，不会在接着执行，也就是declare exit handler for sqlwaring,sqlexcptionbegin－－处理异常end;sql语句1；sql语句2；执行sql语句1发生异常，会进入exit handler ，然后退出程序，不会在执行sql语句2 执行sql语句1发生异常，会进入exit handler ，然后退出程序，不会在执行sql语句2。
db2数据库存储过程
-17-
模块 - 其他语句
删除整个模块
− DROP MODULE myMod;
保留规格说明内容，删除实现
− ALTER MODULE myMod DROP BODY;
删除模块中的存储过程(SP)
− ALTER MODULE myMod DROP PROCEDURE myProc;
将模块的执行权限赋给joe
ITERATE FETCH_LOOP1;
END IF; INSERT INTO department(deptno, deptname, admdept) VALUES(‘NEW’, v_deptname, v_admdept); END LOOP FETCH_LOOP1;
-27-
GOTO语句
GOTO语句用于直接跳转到指定标签处。例如： IF v_DEPT = ‘D11’ GOTO bye; ……
DECLARE my_var INTEGER DEFAULT 6;
条件声明
DECLARE not_found CONDITION FOR SQLSTATE ‘02000’;
游标声明
DECLARE c1 CURSOR FOR select * from staff;
异常处理器声明
DECLARE EXIT HANDLER FOR SQLEXCEPTION …;
（DECLARE关键字，cl游标名称， CURSOR是必须有的，；指通过c1的游标来操作staff里所有的数据）最常用的最普 通的。
2.DECLARE c1 CURSOR WITH HOLD FOR select * form staff; 3.DECLARE c1 CURSOR WITH RETURN TO CALLER FOR select * form staff; 4.DECLARE c1 CURSOR WITH RETURN TO CLIENT FOR select * form staff;
关于DB2游标的一些要点
关于DB2游标的一些要点文章分类:数据库游标一般用来迭代结果集中的行为了在一个过程中处理一个游标的结果，需要做以下事情：在存储过程块的开头部分 DECLARE 游标。
打开该游标。
将游标的结果取出到之前已声明的本地变量中（隐式游标处理除外，在下面的 FOR 语句中将对此加以解释）。
关闭该游标。
（注意：如果现在不关闭游标，当过程终止时将隐式地关闭游标）。
注：游标的申明如果放在中间段，要用”begin。
end;”.段分割标志分割开；游标使用的步骤如下：1、说明游标。
说明游标的时候并不执行select语句。
declare <游标名> cursor for <select语句>;2、打开游标。
打开游标实际上是执行相应的select语句，把查询结果读取到缓冲区中。
这时候游标处于活动状态，指针指向查询结果集的第一条纪录。
open <游标名>;3、推进游标指针并读取当前纪录。
用fetch语句把游标指针向前推进一条纪录，同时将缓冲区中的当前纪录读取出来送到变量中。
fetch语句通常用在一个循环结构体中，通过循环执行fetch语句逐条取出结果集中的行进行处理。
现在好多数据库中，还允许任意方向任意步长易懂游标指针，而不仅仅是把游标指针向前推进一行了。
fetch <游标名> into <变量1>,<变量2>...4、关闭游标。
用close语句关闭游标，释放结果集占用的缓冲区及其他资源。
游标关闭后，就不再和原来的查询结果集相联系。
但游标可以再次打开，与新的查询结果相联系。
close <游标名>;基本结构:定义游标：DECLARE 游标名 CURSOR FORSelect 语句；打开游标：OPEN游标名；取值：FETCH游标名INTO 变量列表游标例子:--先插入测试数据create table test(id int,city char(20))insert into test values(1,'wuhan'),(2,'hangzhou'),(3,'chengdu')create procedure Test(out v_message varchar(500))LANGUAGE SQLBEGINDECLARE v_city char(20);DECLARE v_count int;SET v_message = '';select count(*) into v_count from test;BEGINDECLARE v_CUR CURSOR FOR SELECT city FROM test FOR READ ONLY; OPEN v_CUR;WHILE v_count > 0 DOFETCH v_CUR INTO v_city;set v_message = v_message ||v_city||' ';set v_count = v_count -1;end while;END;END@运行结果为:call Test(?)completed successfully.输出参数的值--------------------------参数名： V_MESSAGE参数值： wuhan hangzhou chengdu返回状态 = 0Statement processed successfully in 4.39 secs.除了这种结构外，还有一种使用for的游标的结构,例子如下:create procedure Test(out v_message varchar(500))LANGUAGE SQLBEGINDECLARE v_city char(20);DECLARE v_count int;SET v_message = '';FOR V1 AS CURSOR1 CURSOR FOR select city as v_city from testDOset v_message = v_message||v_city||' ';END FOR;END@运行结果:call Test(?)completed successfully.输出参数的值--------------------------参数名： V_MESSAGE参数值： wuhan hangzhou chengdu返回状态 = 0Statement processed successfully in 0.18 secs.可以看到第二种游标使用起来非常简单。
DB2-Cursorsample
DB2-CursorsampleDB2 简单的游标处理例子2011-11-19 16:12首先，以 db2 -td@ 进入系统。
也就是以 @ 作为语句终止字符测试表与数据db2 => select * from test_main@ID VALUE----------- ----------1 ONE2 TWO3 THREE3 条记录已选择。
output_debug 表，为只有1列的测试表，用于暂时存储中间过程的数据。
简单循环处理ITRUNCATE TABLE output_debug IMMEDIATE@BEGIN-- 暂存变量.DECLARE v_id INT;DECLARE v_value VARCHAR(10);DECLARE SQLCODE INT;-- 定义游标.DECLARE c_test_main CURSOR FORSELECT id, value FROM test_main;-- 打开游标.OPEN c_test_main;-- 填充数据.FETCH c_test_main INTO v_id, v_value;-- 假如检索到了数据，才处理.WHILE (SQLCODE = 0) DOINSERT INTO output_debug VALUES(v_value); -- 填充下一条数据.FETCH c_test_main INTO v_id, v_value;END WHILE;-- 关闭游标CLOSE c_test_main;END@db2 => select * from output_debug@DATA------------------------------------ONETWOTHREE3 条记录已选择。
简单循环处理IITRUNCATE TABLE output_debug IMMEDIATE@ BEGIN-- 暂存变量.DECLARE v_id INT;DECLARE v_value VARCHAR(10); DECLARE SQLCODE INT;-- 定义游标.DECLARE c_test_main CURSOR FOR SELECT id, value FROM test_main;-- 打开游标.OPEN c_test_main;MyLoop: LOOP-- 填充数据.FETCH c_test_main INTO v_id, v_value;IF (SQLCODE = 0) THEN-- 假如检索到了数据，插入 debug 表. INSERT INTO output_debug VALUES(v_value); ELSE-- 假如没有数据，跳出循环.LEAVE MyLoop;END IF;END LOOP;-- 关闭游标CLOSE c_test_main;END@db2 => select * from output_debug@ DATA------------------------------------ONETWOTHREE3 条记录已选择。
DB2中游标的使用
DB2中游标的使用注意commit和rollback使用游标时要特别注意如果没有加with hold 选项,在Commit和Rollback时,该游标将被关闭。
Commit 和Rollback有很多东西要注意。
特别小心游标的两种定义方式一种为declare continue handler for not foundbeginset v_notfound = 1;end;declare cursor1 cursor with hold for select market_code from tb_market_code for update;open cursor1;set v_notfound=0;fetch cursor1 into v_market_code;while v_notfound=0 Do--workset v_notfound=0;fetch cursor1 into v_market_code;end while;close cursor1;这种方式使用起来比较复杂，但也比较灵活。
特别是可以使用with hold 选项。
如果循环内有commit或r ollback 而要保持该cursor不被关闭，只能使用这种方式。
另一种为pcursor1: for loopcs1 as cousor1 cursor asselect market_code as market_codefrom tb_market_codefor updatedoend for;这种方式的优点是比较简单，不用（也不允许）使用open,fetch,close。
但不能使用with hold 选项。
如果在游标循环内要使用commit,rollback则不能使用这种方式。
如果没有c ommit或rollback的要求，推荐使用这种方式(看来For这种方式有问题)。
修改游标的当前记录的方法update tb_market_code set market_code='0' where current of cursor1;不过要注意将cursor1定义为可修改的游标declare cursor1 cursor for select market_code from tb_market_codefor update;for update 不能和GROUP BY、DISTINCT、ORDER BY、FOR READ ONLY及UNION, EXCEP T, or INTERSECT 但UNION ALL除外）一起使用。
db2 存储过程开发最佳实践
本文以 DB2 开发人员的角度介绍了在 DB2 存储过程开发中需要注意的事项和技巧。
新手如果能够按照本文介绍的最佳实践来开发存储过程，可以避免一些常见的错误，从而编写出高效的程序。
本文从初始化参数、游标、异常处理、临时表的使用以及如何寻找并 rebind 非法存储过程等常见问题进行了着重讨论,并且给出了示例代码。
DB2 提供的强大功能可以让开发人员创建出非常高效稳定的存储过程。
但对于初学者来说，开发出这样的程序并不容易。
本文主要讨论开发高效稳定的 DB2 存储过程的一些常用技巧和方法。
读者定位为具有一定开发经验的 DB2 开发经验的开发人员。
读者可以从本文学习到如何编写稳定、高效的存储过程。
并可以直接使用文章中提供的 DB2 代码，从而节省他们的开发和调试时间，提高效率。
本文以 DB2 开发人员的角度介绍了在 DB2 存储过程开发中需要注意的事项和技巧。
新手如果能够按照本文介绍的最佳实践来开发存储过程，可以避免一些常见的错误，从而编写出高效的程序。
本文从初始化参数、游标、异常处理、临时表的使用以及如何寻找并 rebind 非法存储过程等常见问题进行了着重讨论,并且给出了示例代码。
在存储过程中，开发人员能够声明和设置 SQL 变量、实现流程控制、处理异常、能够对数据进行插入、更新或者删除。
同时，客户应用（这里指调用存储过程的应用程序，它可以是 JDBC 的调用，也可以是 ODBC 和 CLI 等）和存储过程之间可以传递参数，并且从存储过程中返回结果集。
其中，使用 SQL 编写的 DB2 存储过程是在开发中常见的一种存储过程。
本文主要讨论此类存储过程。
最佳实践 1：在创建存储过程语句中提供必要的参数创建存储过程语句（CREATE PROCEDURE）可以包含很多参数，虽然从语法角度讲它们不是必须的，但是在创建存储过程时提供它们可以提高执行效率。
下面是一些常用的参数容许 SQL （allowed－SQL）容许 SQL （allowed－SQL）子句的值指定了存储过程是否会使用 SQL 语句，如果使用，其类型如何。
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
本文将为您介绍一个DB2存储过程使用动态游标的例子，如果您对动态游标的使用感兴趣的话，不妨一看，对您学习DB2的使用会有所帮助。
CREATE PROCEDURE data_wtptest( IN in_taskid_timestamp varchar(30),
OUT o_err_no int,
OUT o_err_msg varchar(1024))
LANGUAGE SQL
P1: BEGIN ATOMIC
--声明开始
--临时变量出错变量
DECLARE SQLCODE integer default 0;
DECLARE SQLStmt varchar(1024) default '';
DECLARE r_code integer default 0;
DECLARE state varchar(1024) default 'AAA';--记录程序当前所作工作
DECLARE at_end int DEFAULT 0;
DECLARE t_destnetid int default 0;
DECLARE t_recvid varchar(30) default '';
DECLARE SP_Name varchar(50) default 'data_wtptest';
--声明放游标的值
--声明动态游标存储变量
DECLARE stmt1 STATEMENT;
DECLARE c1 CURSOR FOR stmt1;
--声明出错处理
DECLARE EXIT HANDLER FOR SQLEXCEPTION
begin
set r_code=SQLCODE;
set o_err_no=1;
set o_err_msg='处理['||state||']出错,'||'错误代码SQLCODE:['||CHAR(r_code) || '].';
insert into fcc_sp_log(object,name,value)
values(SP_Name,in_taskid_timestamp,o_err_msg);
end;
DECLARE continue HANDLER for not found
begin
set at_end = 1;
set state='找到0行记录或已经到记录结尾.';
end;
--声明结束
SET state='[add]单独测试中,统计条数';
SET SQLStmt='SELECT count(*) FROM wtp_pre_download where task_timestamp = ?';
PREPARE stmt1 FROM SQLStmt;
OPEN c1 USING in_taskid_timestamp;
FETCH c1 INTO t_destnetid;
CLOSE c1;
SET state='[add]查具体信息';
SET at_end = 0;
SET SQLStmt='SELECT recv_userid FROM wtp_pre_download where task_timestamp = ?';
PREPARE c1 FROM SQLStmt;
OPEN c1 USING in_taskid_timestamp;
SET state='[add]************';
FETCH c1 INTO t_recvid;
SET state='[add]============';
insert into fcc_sp_log(object,name,value) values(SP_Name,in_taskid_timestamp,t_recvid);
END p1。