    最近job调度平台的项目要核对数据，需要编写一个存储过程来获取生产库上相关表的总记录条数，
通过与测试库上的数据条数进行对比来进行大致的核对。由于我之前没写过DB2的过程，所以写起来
比较费劲，不过最终还是完成了。
    具体是这样的：我们先通过SQL在生产上查找出与项目相关的表，在存储过程中声明一个临时表来
存放这些表名，并用另一个字段来存储各表的记录条数；
    然后，声明一个游标来获取所有的表名（这里游标不要从临时表里来获取数据，在临时表声明前就
要声明游标，游标数据同临时表一样通过SQL来获取）；
    接着就是通过游标来生成动态的SQL。在DB2中动态SQL中包含SELECT或者VALUES是不能够直接执行
的，这也是一直困扰我的地方。在网上看了好多相关的资料，经过多次尝试，最终找到了解决办法。
要执行包含SELECT或者VALUES的动态SQL 语句，要做以下几步：
1.声明一个存放动态SQL的变量 v_sql
2.声明一个statement类型的变量 v_stmt
3.为statement变量声明一个游标 c2
4.写好动态SQL语句 set v_sql='select count(*) from dbo.'||v_1;--(v_1为表名)
5.prepare v_stmt from v_sql
6.打开游标c2，fetch c2 into v_count; 关闭游标

    最后用v_count的值来更新临时表，进行数据导出；
    call sysproc.admin_cmd('export to /home/db2inst1/validate/validate.csv of del select * from session.tmp');
     导出数据时，我想直接导出到本地，刚一开始就写了个本地的路径，结果报错了，后来才知道要一个远程服务器
的路径才可以，并且用户要有写入的权限。

完整的代码如下:

点击(此处)折叠或打开

drop procedure dbo.validate ();
create procedure dbo.validate ()
language sql
begin
DECLARE SQLCODE INT DEFAULT 0;
DECLARE v_1 VARCHAR(40);
DECLARE v_sql VARCHAR(1000);
--DECLARE v_sql2 VARCHAR(1000);
declare v_count int;
DECLARE v_stmt statement;
declare c2 cursor for v_stmt;
DECLARE C1 CURSOR FOR select tabname from syscat.tables where tabschema=\'DBO\' and tabname not like \'%_TEMP%\' and tabname not like \'%BACKUP%\' AND TYPE!=\'V\' AND TABNAME NOT LIKE \'TEST%\'
AND TABNAME NOT LIKE \'TEMP_%\' AND TABNAME NOT LIKE \'TEST\' AND TABNAME NOT LIKE \'TMP_%\' and tabname NOT LIKE \'%1\'
and tabname NOT LIKE \'%2\'
and tabname NOT LIKE \'%3\'
and tabname NOT LIKE \'%4\'
and tabname NOT LIKE \'%_BAK\'
and tabname NOT LIKE \'%_2013%\'
and tabname NOT LIKE \'%_2014%\' ;
--fetch first 10 rows only;
DECLARE GLOBAL TEMPORARY TABLE SESSION.TMP
(TABLENAME VARCHAR(40),
COUNTT INT)
WITH REPLACE
NOT LOGGED;
INSERT INTO SESSION.TMP select tabname,0 from syscat.tables where tabschema=\'DBO\' and tabname not like \'%_TEMP%\' and tabname not like \'%BACKUP%\' AND TYPE!=\'V\' AND TABNAME NOT LIKE \'TEST%\'
AND TABNAME NOT LIKE \'TEMP_%\' AND TABNAME NOT LIKE \'TEST\' AND TABNAME NOT LIKE \'TMP_%\' and tabname NOT LIKE \'%1\'
and tabname NOT LIKE \'%2\'
and tabname NOT LIKE \'%3\'
and tabname NOT LIKE \'%4\'
and tabname NOT LIKE \'%_BAK\'
and tabname NOT LIKE \'%_2013%\'
and tabname NOT LIKE \'%_2014%\';
open C1;
cursorloop:
loop
fetch C1 into v_1;
IF SQLCODE=100 THEN LEAVE CURSORLOOP;
END IF;
SET v_sql=\'select count(*) from dbo.\'||v_1;
prepare v_stmt from v_sql;
open c2;
fetch c2 into v_count;
close c2;
UPDATE SESSION.TMP SET COUNTT=v_count WHERE TABLENAME=v_1;
END LOOP;
CLOSE C1;
call sysproc.admin_cmd(\'export to /home/db2inst1/validate/validate.csv of del select * from session.tmp\');
--insert into dbo.testpl select * from session.tmp;
END;
call dbo.validate ()

ITPub博客

db2存储过程之动态SQL

最新文章