Chinaunix首页 | 论坛 | 认证专区 | 博客 登录 | 注册

成孜论的博客

成孜论 大数据 hadoop DB2 Oracle 数据库 云计算

  • 博客访问: 171931
  • 博文数量: 21
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 1939
  • 用 户 组: 普通用户
  • 注册时间: 2013-03-25 09:22
个人简介

数据库高级专家。国内顶尖数据专家,出版数据专著多部,发表数据论文多篇,近年参与多项金融、国防、电信等大型数据工程。 http://www.china-pub.com/main/sale/renwu/luminary.asp?id=9608

文章分类
文章存档

2015年(2)

2013年(19)

我的朋友
微信关注

IT168企业级官微



微信号:IT168qiye



系统架构师大会



微信号:SACC2013

订阅
热词专题
DB2存储过程示例 2013-07-01 06:21:15

分类: DB2/Informix

  下面是对于创建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 @
阅读(7312) | 评论(0) | 转发(3) |
给主人留下些什么吧!~~
评论热议
请登录后评论。

登录 注册