1. JAVA 調用db2存儲過(guò)程最簡(jiǎn)單的例子：
存儲過(guò)程創(chuàng )建代碼：
<o:p></o:p>
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();
<o:p></o:p>
2. Java調用db2帶輸入參數存儲過(guò)程的例子：<o:p></o:p>
Db2創(chuàng )建存儲過(guò)程的代碼：<o:p></o:p>
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.有輸入輸出參數的代碼：
創(chuàng )建存儲過(guò)程的代碼：
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.帶有游標的存儲過(guò)程（不知道這里的游標什么時(shí)候close的。剛開(kāi)始學(xué)，不懂 菜鳥(niǎo)真郁悶）
創(chuàng )建存儲過(guò)程的代碼：（這個(gè)存儲過(guò)程的具體看上面一篇文章：DB2 存儲過(guò)程開(kāi)發(fā)最佳實(shí)踐（轉載）的最佳實(shí)踐 3：正確設定游標的返回類(lèi)型。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));
- }
====返回多個(gè)結果集的處理方法：
db2 8.2 存儲過(guò)程創(chuàng )建代碼：
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
- System.out.println(rst.getString(1)+" "+rst.getString(2));
- j++;
- }
- System.err.println("---------------------------------------------");
- if (proc.getMoreResults()){
- j = 0;
- while(rst.next()&&j
- System.out.println(rst.getString(1)+" "+rst.getString(2));
- j++;
- }
- }
- j = 0;
