关于DB2存储过程的几个小实例
关于DB2存储过程的几个小实例2010-08-27 15:55
http://blog.myspace.cn/e/402393233.htm
==============================================================================
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;
==============================================================================
页:
[1]