DB2存储过程示例

  下面是对于创建DB2存储过程的一个示例，正在入手这项学习的朋友可以参考。
在db2命令行中进行操作:
 1.开始-运行,输入CMD,执行db2cw命令进入db2命令行模式
 2.再输入db2后,便可以输入SQL语句进行执行;如果不输入,则需要db2 "SQL语句"来执行
备注,命令回车提示成功后,对于INSERT则一定要进行COMMIT,否则无效
--创建表结构
db2 "create table tablename as (SELECT row_number(),zd FROM tablename2) definition only"
--创建新表
db2 "create table tablename(a varchar(14))"
--删除表
db2 "drop table table_name"
--向表中插入数据
db2 "INSERT INTO tablename(zd1,zd2,zd3,zd4,zd5) VALUES ('aaaaa','110','1111','2222','2012-09-11')"
--查询表中数据
db2 "SELECT * FROM tablename"
--删除表中前10000万记录
db2 "delete from (select * from tablename fetch first 10000 rows only) where 1=1"
--提交
db2 "commit"
--查询系统当前时间
db2 "select current time from sysibm.sysdummy1"
--查询表中总记录数
db2 "select count(0) from tablename"
了解执行存储过程需要的时间:
 db2 "select current time from sysibm.sysdummy1"
 db2 "CALL 存储过程名()"--调用存储过程
 db2 "commit"--提交
 db2 "select current time from sysibm.sysdummy1"


====编写一个存储过程造数据,5个字段(1个日期类型[值相对固定],3个字段随机从已存在的表中取,1个字段同另1字段一起作主键)
分析与结果:
没接触过db2的话,看看当前不会的知识点
1.如何对现有表的字段值进行随机取值
 select int(rand()*100) from sysibm.sysdummy1;
2.如何找到db2类似于oracle的rownum唯一物理行
 select ZD,  ROW_NUMBER() over(order by ZD desc) as rownum  FROM ORA_TABLE1;
 SELECT ZD FROM (SELECT ZD,  ROW_NUMBER() over(order by ZD desc) as rownum  FROM ORA_TABLE1 ) WHERE ROWNUM=100;
3.db2中的循环语法,存储过程的结构是什么
 WHILE i<10 DO
   --SQL语句
   SET i=i+1;
 END WHILE;
 可以在db2的samples文件夹中找到whiles.db2实例进行参考
4.如何执行存储过程
 命令行模式中输入
 db2 -td@ -vf whiles.db2
 db2 "CALL dept_median(51, ?)--若无参数,则db2 "call dept_median()"


====结果(优化中)
create procedure test_pro()
language sql
begin
declare i int default 1001;
declare aa int default 1;
declare t int;
declare total_605 int;
declare total_606 int;
declare total_NEED_CODE int;
declare a605 char(3);
declare a606 char(4);
declare abankcode char(14);
SELECT count(NEED_ZD)+1 INTO total_605 FROM ORA_DICTIONARY WHERE TYPE_A='605';
SELECT count(NEED_ZD)+1 INTO total_606 FROM ORA_DICTIONARY WHERE TYPE_A='606';
SELECT count(NEED_CODE)+1 INTO total_NEED_CODE FROM TABLE_INFO;
while i<=10000 do
 select int(rand()*(total_605+1)) into t from sysibm.sysdummy1;
 SELECT NEED_ZD into a605 FROM (select NEED_ZD,  ROW_NUMBER() over(order by NEED_ZD desc) as rownum  FROM ORA_DICTIONARY WHERE TYPE_A='605' ) WHERE ROWNUM=t;
 select int(rand()*(total_606+1)) into t from sysibm.sysdummy1;
 SELECT NEED_ZD into a606 FROM (select NEED_ZD,  ROW_NUMBER() over(order by NEED_ZD desc) as rownum  FROM ORA_DICTIONARY WHERE TYPE_A='606' ) WHERE ROWNUM=t;
 select int(rand()*(total_NEED_CODE+1)) into t from sysibm.sysdummy1;
 SELECT NEED_CODE into abankcode FROM (select NEED_CODE,  ROW_NUMBER() over(order by NEED_CODE desc) as rownum  FROM TABLE_INFO ) WHERE ROWNUM=t;
 INSERT INTO czystru(ZD1,ZD2,ZD3,ZD4,ZD5) VALUES(char(aa),a605,a606,abankcode,'2010-08-03');
 if (mod(i,1000)=0)
 then
  commit; 
  end if;
  set aa=aa+1;
  set i=i+1;
end while;
END @


====db2自带的whiles.db2例子
CREATE PROCEDURE dept_median
(IN deptNumber SMALLINT, OUT medianSalary DOUBLE)
LANGUAGE SQL
BEGIN
   DECLARE SQLCODE INTEGER;
   DECLARE SQLSTATE CHAR(5);
   DECLARE v_numRecords INT DEFAULT 1;
   DECLARE v_counter INT DEFAULT 0;
   DECLARE v_mod INT DEFAULT 0;
   DECLARE v_salary1 DOUBLE DEFAULT 0;
   DECLARE v_salary2 DOUBLE DEFAULT 0;
   DECLARE c1 CURSOR FOR
     SELECT CAST(salary AS DOUBLE) FROM staff
     WHERE DEPT = deptNumber
     ORDER BY salary;
   DECLARE EXIT HANDLER FOR NOT FOUND
     SET medianSalary = 6666;
   -- initialize OUT parameter
   SET medianSalary = 0;
   SELECT COUNT(*) INTO v_numRecords FROM staff
     WHERE DEPT = deptNumber;
   OPEN c1;
   SET v_mod = MOD(v_numRecords, 2);
   CASE v_mod
    WHEN 0 THEN
      WHILE v_counter < (v_numRecords / 2 + 1) DO
        SET v_salary1 = v_salary2;
        FETCH c1 INTO v_salary2;
        SET v_counter = v_counter + 1;
      END WHILE;
      SET medianSalary = (v_salary1 + v_salary2)/2;
    WHEN 1 THEN
      WHILE v_counter < (v_numRecords / 2 + 1) DO
        FETCH c1 INTO medianSalary;
        SET v_counter = v_counter + 1;
      END WHILE;
   END CASE;
END @