Java解析存储过程的表 java执行存储过程的代码

 

1.       JAVA 调用db2存储过程最简单的例子：

存储过程创建代码：

<o:p></o:p>

sql 代码

1. SET SCHEMA
2.     
3. Create procedure
4.     
5.  LANGUAGE SQL    
6.     
7. Update t_ryxx set xm =’xy’ where ryxxid=’xm’

java 代码

1. conn = DbMaster.getConn();      
2. System.out.println("begin………");      
3. proc = conn.prepareCall("{call test()}");      
4. proc.execute();

<o:p></o:p>

2. Java调用db2带输入参数存储过程的例子：

<o:p></o:p>
Db2创建存储过程的代码：<o:p></o:p>

         sql 代码 
       
1. Drop procedure ies.test();   
2. SET SCHEMA IES      ;    
3. Create procedureinvarchar(50))    
4. LANGUAGE SQL   
5. Update t_ryxx set xm =’xy’ where ryxxid=i_ryxxid    
6.   
java 代码
1. conn = DbMaster.getConn();   
2. System.out.println("begin");   
3. proc = conn.prepareCall("{call test(?)}");   
4. proc.setString(1,"RY0003");   
5. proc.execute();   
6. System.out.println("end:");

3.有输入输出参数的代码：

创建存储过程的代码：

sql 代码 
         
1. SET SCHEMA IES      ;   
2. CREATE PROCEDUREINvarchar(50),out out_xm varchar(50))    
3.    LANGUAGE SQL   
4.   
5. select xm into out_xm from ies.t_ryxx where ryxxid=in_ryxxid;    

            java 代码 
          
1. proc = conn.prepareCall("{ call test(?,?)}");   
2. proc.setString(1, "011900380103");   
3. proc.registerOutParameter(2, Types.VARCHAR);   
4. proc.execute();   
5. String xm = proc.getString(2);   
6. System.out.println("end:"+xm);

4.带有游标的存储过程（不知道这里的游标什么时候close的。刚开始学，不懂 菜鸟真郁闷）

创建存储过程的代码：

sql 代码 
           
1. SET SCHEMA IES      ;   
2. CREATE PROCEDUREINvarchar(50))    
3. result set 1   
4. language sql   
5. P1:BEGIN  
6. DECLARE CUR cursor with return for select rybh,xm from ies.t_ryxx where ryzt=in_state with ur;    
7.   
8. OPEN CUR;    
9.   
10.   
11. END P1;      

              java 代码 
            
1. proc = conn.prepareCall("{ call test(?)}");   
2. proc.setString(1, "停用");   
3. proc.execute();   
4. rst = proc.getResultSet();   
5. while(rst.next()){    
6.      System.err.println(rst.getString(1)+"     "+rst.getString(2));   
7.      }  
====返回多个结果集的处理方法：
db2 8.2 存储过程创建代码：

               sql 代码 
             
1. create procedure getpeople()   
2. dynamic result sets 2    
3. READS SQL DATA   
4. LANGUAGE SQL   
5. BEGIN   
6.      DECLARE rs1 CURSOR WITH RETURN TO CLIENT FOR  
7.              SELECT RYBH,XM FROM IES.T_RYXX WHERE RYZT='停用' with ur;   
8.     DECLARE rs2 CURSOR WITH RETURN TO CALLER FOR   
9.             SELECT RYBH ,XM FROM IES.T_RYXX WHERE RYZT='正常' with ur;           
10.     OPEN rs1;   
11.     OPEN rs2;   
12. END;                

                java 代码 
              
1. proc = conn.prepareCall("{ call getpeople()}");   
2. proc.execute();   
3. rst = proc.getResultSet();   
4. int i = 2 ,j = 0;    
5. while(rst.next()&&j//j的作用：记录太多了，只想打印几个测试下    
6.       System.out.println(rst.getString(1)+"     "+rst.getString(2));   
7.        j++;   
8. }   
9. System.err.println("---------------------------------------------");   
10. if (proc.getMoreResults()){  //getMoreResults()具体看api文档    
11.      j = 0;   
12.     while(rst.next()&&j//j的作用：记录太多了，只想打印几个测试下    
13.      System.out.println(rst.getString(1)+"     "+rst.getString(2));   
14.      j++;   
15.          }   
16.      }   
17. j = 0;