关于DB2存储过程的几个小实例

================
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;