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   --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 sqlbegindeclare 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 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 SQLBEGIN   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        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        FETCH c1 INTO medianSalary;        SET v_counter = v_counter + 1;      END WHILE;   END CASE;END @