dba 存储过程基本文档

浏览数：19 / 时间：2015年06月12日

在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 @

语法说明
1、procedure-name: 存储过程的名字，在同一个数据库的同一模式下，不能存在存储过程名相同参数数目相同的存储过程，即使参数的类型不同也不行。leizhimin 51cto技术博客

2、(IN | OUT | INOUT parameter-name data-type,...) ：传入参数
IN：输入参数
OUT：输出参数
INOUT：作为输入输出参数
parameter-name：参数名字，在此存储过程中唯一的标识符。
data-type:参数类型，可以接收SQL类型和创建的表。不支持LONG VARCHAR, LONG VARGRAPHIC, DATALINK, REFERENCE和用户自定义类型。

3、SPECIFIC specific-name：唯一的特定名称（别名），可以用存储过程名代替，这个特定名称用于dorp存储过程，或者给存储过程添加注视

用，但不能调用存储过程。如果不指定，则数据库会自动生成一个yymmddhhmmsshhn时间戳的名字。推荐给出别名。

4、DYNAMIC RESULT SETS integer：指定存储过程返回结果的最大数量。存储过程中虽然没有return语句，但是却能返回结果集。

5、CONTAINS SQL, READS SQL DATA, MODIFIES SQL DATA：指定存储过程中的SQL访问级别
CONTAINS SQL：表示存储过程可以执行中，既不可读取 SQL 数据，也不可修改 SQL 数据。
READS SQL DATA：表示存储过程可以执行中，可读取SQL，但不可修改 SQL 数据。
MODIFIES SQL DATA：表示存储过程可以执行任何 SQL 语句。可以对数据库中的数据进行增加、删除和修改。

6、DETERMINISTIC or NOT DETERMINISTIC：表示存储过程是动态或者非动态的。动态的返回的值是不确定的。非动态的存储过程每次执行返回

的值是相同的。

7、CALLED ON NULL INPUT：表示可以调用存储过程而不管任何的输入参数是否为NULL，并且，任何的OUT或者INOUT参数可以返回一个NULL或者

非空值。检验参数是否为NULL是在过程中进行的。

8、INHERIT SPECIAL REGISTERS：表示继承专用寄存器。

9、OLD SAVEPOINT LEVEL or NEW SAVEPOINT LEVEL：建立存储点。OLD SAVEPOINT LEVEL是默认的存储点。

10、LANGUAGE SQL：指定程序的主体用的是SQL语言。

11、EXTERNAL ACTION or NO EXTERNAL ACTION：表示存储过程是否执行一些改变理数据库状态的活动，而不通过数据库管理器管。默认是

EXTERNAL ACTION。如果指定为NO EXTERNAL ACTION ,则数据库会确定最最佳优化方案。

12、PARAMETER CCSID：指定所有输出字符串数据的编码,默认为UNICODE编码数据库为PARAMETER CCSID UNICODE

，其他的数据库默认为PARAMETER CCSID 3 ASCII。

13、SQL-procedure-body：存储过程的主体

例子1：产生一个SQL存储过程，返回员工的平均薪水. 返回所有员工超过平均薪水的数额，结果集包括name, position, and salary字段。

CREATE PROCEDURE MEDIAN_RESULT_SET (OUT medianSalary DOUBLE)
RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE v_numRecords INT DEFAULT 1;
DECLARE v_counter INT DEFAULT 0;
DECLARE c1 CURSOR FOR
SELECT CAST(salary AS DOUBLE)
FROM staff
ORDER BY salary;
DECLARE c2 CURSOR WITH RETURN FOR
SELECT name, job, CAST(salary AS INTEGER)
FROM staff
WHERE salary > medianSalary
ORDER BY salary;
DECLARE EXIT HANDLER FOR NOT FOUND
SET medianSalary = 6666;
SET medianSalary = 0;
SELECT COUNT(*) INTO v_numRecords
FROM STAFF;
OPEN c1;
WHILE v_counter < (v_numRecords / 2 + 1)
DO
FETCH c1 INTO medianSalary;
SET v_counter = v_counter + 1;
END WHILE;
CLOSE c1;
OPEN c2;

CREATE PROCEDURE proc_with_variables (IN p_empno VARCHAR(6))
LANGUAGE SQL
SPECIFIC proc_with_vars -- applies to LUW and iSeries
-- WLM ENVIRONMENT <env> -- applies to zSeries
BEGIN
DECLARE v_empno VARCHAR(6);
DECLARE v_total, v_count INTEGER DEFAULT 0;
SELECT empno INTO v_empno FROM employee WHERE empno = p_empno ;
END
==============================================================================
CREATE PROCEDURE set_variables ()
LANGUAGE SQL
SPECIFIC set_variables -- applies to LUW and iSeries
-- WLM ENVIRONMENT <env> -- applies to zSeries
BEGIN
DECLARE v_rcount INTEGER;
DECLARE v_max DECIMAL(9,2);
DECLARE v_adate,v_another DATE;
DECLARE v_total INTEGER DEFAULT 0; -- (1)
SET v_total = v_total + 1; -- (2)
SELECT MAX(salary) INTO v_max FROM employee; -- (3)
VALUES CURRENT DATE INTO v_adate; -- (4)
SELECT CURRENT DATE, CURRENT DATE
INTO v_adate, v_another
FROM SYSIBM.SYSDUMMY1; -- (5)
END
CREATE PROCEDURE registersample ( OUT p_start TIMESTAMP
, OUT p_end TIMESTAMP
, OUT p_c1 TIMESTAMP
, OUT p_c2 TIME
, OUT p_user CHAR(20))
LANGUAGE SQL
SPECIFIC registersample -- applies to LUW and iSeries
-- WLM ENVIRONMENT <env> -- applies to zSeries
BEGIN
CREATE TABLE datetab (c1 TIMESTAMP,c2 TIME,c3 DATE);
VALUES CURRENT TIMESTAMP INTO p_start; -- (1)
INSERT INTO datetab VALUES( CURRENT TIMESTAMP
, CURRENT TIME
, CURRENT DATE + 3 DAYS); -- (2)
SELECT c1,c2 INTO p_c1,p_c2 FROM datetab;
VALUES CURRENT TIMESTAMP INTO p_end;
SET p_user = USER; -- (3)
DROP TABLE datetab;
END
添加新员工的一段存储过程:
=================
CREATE PROCEDURE add_new_employee ( IN p_empno VARCHAR(6) -- (1)
, IN p_firstnme CHAR(12)
, IN p_midinit CHAR(1)
, IN p_lastname VARCHAR(15)
, IN p_deptname VARCHAR(30)
, IN p_edlevel SMALLINT
, OUT p_status VARCHAR(100)
, OUT p_ts TIMESTAMP)
LANGUAGE SQL
SPECIFIC add_new_employee -- applies to LUW and iSeries
-- WLM ENVIRONMENT <env> -- applies to zSeries
BEGIN
DECLARE v_deptno CHAR(3) DEFAULT ‘ ‘; -- (2)
DECLARE v_create_ts TIMESTAMP; -- (3)
SET v_create_ts = CURRENT TIMESTAMP;
/* Get the corresponding department number */
SELECT deptno
INTO v_deptno -- (4)
FROM department
WHERE deptname = p_deptname;
/* Insert new employee into table */ -- (5)
INSERT INTO employee ( empno
, firstnme
, midinit
, lastname
, workdept
, hiredate
, edlevel)
VALUES ( p_empno
, p_firstnme
, p_midinit
, p_lastname
, v_deptno
, DATE(v_create_ts)
, p_edlevel );
SET p_status = ‘Employee added‘; -- (6)
SET p_ts = v_create_ts; -- (7)
END
=================
一个小例子：
CREATE PROCEDURE p2( IN TRY_SQLCODE VARCHAR(10))
DYNAMIC RESULT SETS 1
LANGUAGE SQL
BEGIN
DECLARE SQLCODE INT;
DECLARE ERR5 VARCHAR(40) default ‘初始值‘;
DECLARE ERR3 VARCHAR(40);
DECLARE ERR2 VARCHAR(40);
DECLARE ERR1 VARCHAR(40);
DECLARE ERRID VARCHAR(40);
DECLARE CUR_SQLCODE CURSOR WITH RETURN TO CLIENT FOR SELECT * FROM RI;
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET ERR5 = char(SQLCODE);
SELECT ID
INTO ERRID
FROM RINGS
WHERE ID=TRY_SQLCODE; --创造各种sqlcode条件的参数
IF SQLCODE = 100 THEN
SET ERR1=‘NOT FOUND‘;
INSERT INTO RINGS VALUES(‘1‘,ERR1);
ELSEIF SQLCODE < 0 THEN
SET ERR3 = ‘EXCEPTION‘;
INSERT INTO RINGS VALUES(‘3‘,ERR3);
END IF;
INSERT INTO RINGS VALUES(‘100‘,ERR5);
COMMIT;
OPEN CUR_SQLCODE;
END
1. JAVA 调用db2存储过程最简单的例子：
存储过程创建代码：
sql 代码
SET SCHEMA IES ;
Create procedure ies.test()
LANGUAGE SQL
Update t_ryxx set xm =’xy’ where ryxxid=’xm’
java 代码
conn = DbMaster.getConn();
System.out.println("begin………");
proc = conn.prepareCall("{call test()}");
proc.execute();
2. Java调用db2带输入参数存储过程的例子：
Db2创建存储过程的代码：
sql 代码
Drop procedure ies.test();
SET SCHEMA IES ;
Create procedure ies.test(in i_ryxxid varchar(50))
LANGUAGE SQL
Update t_ryxx set xm =’xy’ where ryxxid=i_ryxxid
java 代码
conn = DbMaster.getConn();
System.out.println("begin");
proc = conn.prepareCall("{call test(?)}");
proc.setString(1,"RY0003");
proc.execute();
System.out.println("end:");
3.有输入输出参数的代码：
创建存储过程的代码：
sql 代码
SET SCHEMA IES ;
CREATE PROCEDURE IES.test (IN in_ryxxid varchar(50),out out_xm varchar(50))
LANGUAGE SQL
select xm into out_xm from ies.t_ryxx where ryxxid=in_ryxxid;
java 代码
proc = conn.prepareCall("{ call test(?,?)}");
proc.setString(1, "011900380103");
proc.registerOutParameter(2, Types.VARCHAR);
proc.execute();
String xm = proc.getString(2);
System.out.println("end:"+xm);
4.带有游标的存储过程（不知道这里的游标什么时候close的。刚开始学，不懂菜鸟真郁闷）
创建存储过程的代码：（这个存储过程的具体看上面一篇文章：DB2 存储过程开发最佳实践（转载）的最佳实践 3：正确设定游标的返回类型。http://acme1921209.javaeye.com/blog/97829）
sql 代码
SET SCHEMA IES ;
CREATE PROCEDURE IES.test (IN in_state varchar(50))
result set 1
language sql
P1:BEGIN
DECLARE CUR cursor with return for select rybh,xm from ies.t_ryxx where ryzt=in_state with ur;
OPEN CUR;
END P1;
java 代码
proc = conn.prepareCall("{ call test(?)}");
proc.setString(1, "停用");
proc.execute();
rst = proc.getResultSet();
while(rst.next()){
System.err.println(rst.getString(1)+" "+rst.getString(2));
}
====返回多个结果集的处理方法：
db2 8.2 存储过程创建代码：
sql 代码
create procedure getpeople()
dynamic result sets 2
READS SQL DATA
LANGUAGE SQL
BEGIN
DECLARE rs1 CURSOR WITH RETURN TO CLIENT FOR
SELECT RYBH,XM FROM IES.T_RYXX WHERE RYZT=‘停用‘ with ur;
DECLARE rs2 CURSOR WITH RETURN TO CALLER FOR
SELECT RYBH ,XM FROM IES.T_RYXX WHERE RYZT=‘正常‘ with ur;
OPEN rs1;
OPEN rs2;
END;
java 代码
proc = conn.prepareCall("{ call getpeople()}");
proc.execute();
rst = proc.getResultSet();
int i = 2 ,j = 0;
while(rst.next()&&j//j的作用：记录太多了，只想打印几个测试下
System.out.println(rst.getString(1)+" "+rst.getString(2));
j++;
}
System.err.println("---------------------------------------------");
if (proc.getMoreResults()){ //getMoreResults()具体看api文档
j = 0;
while(rst.next()&&j//j的作用：记录太多了，只想打印几个测试下
System.out.println(rst.getString(1)+" "+rst.getString(2));
j++;
}
}
j = 0;
==============================================================================
db2 存储过程常用语句格式
http://hi.baidu.com/heiru/blog/item/fb4132adb07e7e074a36d631.html
----定义
DECLARE CC VARCHAR(4000);
DECLARE SQLSTR VARCHAR(4000);
DECLARE st STATEMENT;
DECLARE CUR CURSOR WITH RETURN TO CLIENT FOR CC;
----执行动态SQL不返回
PREPARE st FROM SQLSTR;
EXECUTE st;
----执行动态SQL返回
PREPARE CC FROM SQLSTR;
OPEN CUR;
----判断是否为空，使用值替代
COALESCE(判断对象，替代值)
----定义临时表
DECLARE GLOBAL TEMPORARY TABLE SESSION.TempResultTable
(
Organization int,
OrganizationName varchar(100),
AnimalTypeName varchar(20),
ProcessType int,
OperatorName varchar(100),
OperateCount int
)
WITH REPLACE -- 如果存在此临时表，则替换
NOT LOGGED;
----字符串函数
Substr
----隐形游标迭代
for 游标名 as select....... do
使用游标名.字段名
内容区块
end for;
----直接返回值或变量
declare rs1 cursor with return to caller for select 0 from sysibm.sysdummy1;
----判断表是否存在
select count(*) into @exists from syscat.tables where tabschema = current schema and tabname=‘ZY_PROCESSLOG‘;
----取前面N条记录
FETCH FIRST N ROWS ONLY
----定义返回值
declare rs0 cursor with return to caller for select 0 from sysibm.sysdummy1;
declare rs1 cursor with return to caller for select 1 from sysibm.sysdummy1;
----得到插入的自增长列最大值
VALUES IDENTITY_VAL_LOCAL() INTO 变量
==============================================================================
DB2中执行动态SQL的例子
http://www.cnblogs.com/kfarvid/archive/2009/11/03/1595064.html
CREATE PROCEDURE REFERESH_ZHAOGW
(
IN ODD_TABLE_NAME VARCHAR(100),
IN ODS_TABLE_NAME VARCHAR(100)
)
LANGUAGE SQL
BEGIN
DECLARE SSQL VARCHAR(1000) ;
SET SSQL=‘CREATE TABLE ‘||ODS_TABLE_NAME||‘ AS SELECT * FROM ‘||ODD_TABLE_NAME ;
PREPARE S1 FROM SSQL;
EXECUTE S1;
END;