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

我就是我 2022-04-17 04:17 298阅读 0赞

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