下面是对于创建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 @ 01-20 23:30