下面是对于创建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 @

阅读(582) | 评论(0) | 转发(0) |