oracle存储过程及Java调用（转）

初次研究出bug的地方非常多，所以注意点非常多，花了我三天时间除尽所有bug，我会把注意点都列出来，可能有落下的地方，还请多指正，相互探讨。

首先上最终测试成功版存储过程代码：（里面代码可能不尽对你都有用，借鉴参考吧，我全贴出来也是为了我以后好查）

说一下jar包用的是ojdbc14.jar，至于什么class12.jar、ojdbc6.jar啊应该都可以，只要一种就可以了。

说说我的需求，以便让大家更顺利的看懂我的代码，我的需求是：存储过程从Java端接收两个参数userid（用户）和topicid（话题），在存储过程进行循环查询当前用户对当前话题的点赞记录,如果有记录，则record为设置1，没有则为0，最后返回一个结果集，是反应 用户=>话题=>record相互对应的关系表。

好了不废话了，上代码吧，学习阶段，所以难免情绪波动和啰嗦，也是希望以最直白能懂的方式叙述出来。

[sql] view plaincopyprint?

1. ------------在数据库建立一个type,对应JAVA端要传入的对象结构 :    
2. create or replace type tp_arr3 as Object      
3. (      
4.   userid   nvarchar2(40),    --这里从varchar2改成nvarchar2类型才能跟Java的String匹配上    
5.   topicid nvarchar2(40),  
6.   record nvarchar2(4)      
7. )  

[sql] view plaincopyprint?

1. ---多次测试创建可能会出现“ORA-02303: 无法使用类型或表的相关性来删除或取代一个类型”这个错误，这时只要换一个类型名字再创建就可以了  
2. ----------  
3. CREATE OR REPLACE TYPE tp_arr_tbl3 AS TABLE OF tp_arr3  
4. --------------创建包 ，创建一个游标类型用来放输出参数   
5. create or replace package testpkg as   
6.  type testcur is ref cursor;  
7.  end testpkg;  
8. ------创建存储过程 ，定义两个参数，一个入参，是一个对象类型数组（这种类型应该可以满足大部分复杂需求了），一个出参，是用游标存放查询值   
9.  CREATE OR REPLACE  procedure findRecord(type_obj IN tp_arr_tbl3,result out testpkg.testcur)    
10.   as   
11.   t tp_arr3;   
12.   sql2 varchar2(500);  
13.   sql3 varchar2(500);   
14.   v_count varchar2(4);     --临时中间变量，用来存放对应的record   
15.  BEGIN       
16.     sql2 :='drop table tb_temp';  
17.      sql3 :='CREATE TABLE tb_temp( userid varchar2(40),topicid varchar2(40) primary key, record varchar2(4))';  
18.        execute immediate sql2;  
19.        execute immediate sql3;  
20.          
21.        FOR i IN type_obj.first()..type_obj.last()   
22.        LOOP  
23.        t:= type_obj(i);  
24.        select count(*) into v_count from scott.tb_praise_rel where userid=t.userid and topicid=t.topicid;  
25.        dbms_output.put_line( t.userid || '=>'||t.topicid ||'=>' || v_count);  
26.        insert into tb_temp values (t.userid,t.topicid, v_count);  
27.       END LOOP;  
28.       COMMIT;  
29.         
30.       open result for select * from tb_temp;  
31.  END;  
32. --------------执行存储过程   
33.   
34. declare   
35. ta tp_arr_tbl3:=tp_arr_tbl3();      --对象的声明   
36. t tp_arr3:=tp_arr3('0','0','0');    --声明及赋初值（必要步骤）  
37. begin  
38. for i in 1..12 loop  
39. ta.extend;  
40.   t.userid:='1';  
41.   t.topicid:=i;  
42.   t.record:='0';  
43.   ta(i):=t;  
44.   findRecord(ta);  
45.   end loop;  
46.   end findRecord;  
47.   
48.  ----------------------表查询测试部分   
49.  select * from tb_temp;  
50.    
51.  select * from scott.tb_praise_rel;  
52.    
53.  select userid from scott.tb_praise_rel where userid='1' and topicid='1';  
54. -----------------------游标测试，后来没用，可以略过   
55.  cursor testcur is select userid,topicid from scott.tb_praise_rel;   
56.   cur testcur%rowtype;  
57.     
58.    open testcur;  
59.     loop   
60.      fetch testcur into cur;  
61.      exit when testcur%notfound;  
62.      dbms_output.put_line( 'userid:' || cur.userid || ',topicid:' || cur.topicid );  
63.      update tb_temp set record='1' where userid=cur.userid and topicid=cur.topicid;  
64.      end loop;  
65.      dbms_output.put_line('----------------------');  
66.      close testcur;  
67.       COMMIT;  

注意点：1.测试用户起初用的Scott，发现没有执行权限，对其进行赋予dba权限还是不行，遂后来用的system；2.多次测试创建可能会出现“ORA-02303: 无法使用类型或表的相关性来删除或取代一个类型”这个错误，这时只要换一个类型名字再创建就可以了；3.由于我的tb_temp表有唯一字段约束，所以存储过程每次进来先删表，再建表，再插入数据；4.记得该打分号的地方不要漏，不该打的地方不要多；5.执行存储过程的时候，要先声明并赋初值，不然也会报错；6.注意pl/sql里执行存储过程测试赋值时候ta.extend不能少；6.自定义类型要注意的地方很多，比如nvarchar2和JavaString类型的定义；7.简单说tp_arr3 类型是指一条记录，tp_arr_tbl3是指多条记录；

[sql] view plaincopyprint?

1. --点赞关系表   
2. create table tb_praise_rel(  
3.        id varchar2(40) primary key,  
4.        userid varchar2(40),             --用户id   
5.        topicid  varchar2(40),            --话题id   
6.        remarks1 varchar2(3000),        --备用字段   
7.        remarks2 varchar2(3000),  
8.        remarks3 varchar2(3000)  
9. );  

再上Java调用代码：

[java] view plaincopyprint?

1. package com.lofter.svntesr;  
2.   
3. import java.sql.Array;  
4. import java.sql.CallableStatement;  
5. import java.sql.Connection;  
6. import java.sql.DriverManager;  
7. import java.sql.PreparedStatement;  
8. import java.sql.ResultSet;  
9. import java.sql.SQLException;  
10. import java.util.ArrayList;  
11. import java.util.List;  
12. import java.util.Map;  
13.   
14. import oracle.jdbc.OracleTypes;  
15. import oracle.sql.STRUCT;  
16. import oracle.sql.StructDescriptor;  
17.   
18. import com.lofter.bean.ProcedureBean;  
19.   
20. public class ProcedureTest3 {  
21.   
22.     /** 
23.      * @param args 
24.      */  
25.     public static void main(String[] args) {  
26.         try {    
27.                 
28.             Class.forName("oracle.jdbc.driver.OracleDriver");    
29.     
30.             String url = "jdbc:oracle:thin:@localhost:1521:orcl";    
31.     
32.             //网上很多卡在获取con这个地方的，我最初也是，说是什么jar包问题，删掉class12啊，什么oracle与apache连接池冲突啊，但是我其实是存储过程没写对，最后绕了一圈回来还是用的这种方法测试通过，并没有加((org.apache.commons.dbcp.PoolableConnection) conn).getInnermostDelegate()  
33.             Connection con = DriverManager.getConnection(url, "system", "a");    
34.     
35.       //      PreparedStatement pstmt = null;    
36.             CallableStatement cs = null;  
37.             ResultSet rs=null;  
38.               
39.             List<ProcedureBean> list = new ArrayList<ProcedureBean>();  
40.             for (int i = 1; i <= 12; i++) {  
41.                 String r = i + "";  
42.                 list.add(new ProcedureBean("1", r, "0"));  
43.             }  
44.         //  list.add(new ProcedureBean("1","5f60b0f0-03d9-4671-b945-936fe821fe19", "0"));  
45.               
46.             //如果存储过程是用我这种对象数组as object类型，则java调用这一步必不可少，这是对之前在pl/sql中声明的tp_arr3 类型的映射，表示在pl/sql中去匹配你自定义的类型  
47.             //还有注意要大写,不然可能会报“无效名称模式”  
48.             StructDescriptor recDesc = StructDescriptor.createDescriptor(  
49.                     "TP_ARR3", con);  
50.   
51.             //这一步是将你自定义的类型转化成oracle自己的类型，即STRUCT，相当于一个Object类，因为oracle的开发人员也不知道你会定义一个什么名字的类型，反正只用提供一个规则，最后大家都照着这个规则来转化就是了  
52.             ArrayList<STRUCT> pstruct = new ArrayList<STRUCT>();  
53.             for (ProcedureBean pb : list) {  
54.                 System.out.println(pb);  
55.                 Object[] objs = new Object[3];  
56.                 objs[0] = pb.getUserid();  
57.                 objs[1] = pb.getTopicid();  
58.                 objs[2] = pb.getRecord();  
59.                 STRUCT item = new STRUCT(recDesc, con, objs);  
60.                 pstruct.add(item);  
61.             }  
62.               
63.             //这是第二步映射，映射我在oracle中自定义的tp_arr_tbl3类型，注意也要大写，网上也有说要加包名，不是同一个用户要加用户前缀什么的，我没有加，测试也通过，可能不是极端情况吧  
64.             oracle.sql.ArrayDescriptor desc = oracle.sql.ArrayDescriptor.createDescriptor("TP_ARR_TBL3", con);    
65.                 
66.             oracle.sql.ARRAY array = new oracle.sql.ARRAY(desc, con, pstruct.toArray());   
67.               
68.             //也有说调用的时候要加包名的  
69.             cs = con.prepareCall("{call findRecord(?,?)}");  
70.               
71.             //设置参数这里，1、2分别对应存储过程findRecord(?,?)中参数的位置，注意位置不要错了  
72.             cs.setArray(1, array);  
73.             cs.registerOutParameter(2, OracleTypes.CURSOR);  
74.             cs.execute();  
75.             rs=(ResultSet) cs.getObject(2); //取数据也是根据对应参数位置来的  
76.               
77.             while( rs.next() ){  
78.                 System.out.println("result : \t" + rs.getString(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3));  
79.             }  
80.             con.commit();  
81.         } catch (Exception e) {    
82.     
83.             e.printStackTrace();    
84.     
85.         }    
86.     
87.     }      
88.     }  

Java调用注意：基本上注意事项都以注释的方式写在代码里了，也有可能没想起来，很多bug信息由于测试通过心切，没能及时复制下来。注意不要导错包

2.错误信息“Message file 'oracle.jdbc.driver.Messages' is missing.”，可能是你写错了或类型与oracle中不匹配，不要去找什么jar包啊什么的，网上信息也不多，我在这绕了好久，多检查一下上面提到的加包名、大小写、转类型什么的；

还有其他没想起来或没碰到的bug只有亲们多结合错误信息猜测，多动手测测，相信就会迎刃而解了。

测试的javaBean：

[java] view plaincopyprint?

1. package com.lofter.bean;  
2.   
3. import java.io.Serializable;  
4.   
5. public class ProcedureBean implements Serializable {  
6.   
7.     private static final long serialVersionUID = 809894604693791308L;  
8.     private String userid;  
9.     private String topicid;  
10.     private String record;  
11.   
12.     public ProcedureBean() {  
13.         super();  
14.     }  
15.   
16.     public ProcedureBean(String userid, String topicid, String record) {  
17.         super();  
18.         this.userid = userid;  
19.         this.topicid = topicid;  
20.         this.record = record;  
21.     }  
22.   
23.     public String getUserid() {  
24.         return userid;  
25.     }  
26.   
27.     public void setUserid(String userid) {  
28.         this.userid = userid;  
29.     }  
30.   
31.     public String getTopicid() {  
32.         return topicid;  
33.     }  
34.   
35.     public void setTopicid(String topicid) {  
36.         this.topicid = topicid;  
37.     }  
38.   
39.     public String getRecord() {  
40.         return record;  
41.     }  
42.   
43.     public void setRecord(String record) {  
44.         this.record = record;  
45.     }  
46.   
47.     @Override  
48.     public String toString() {  
49.         return "ProcedureBean [userid=" + userid + ", topicid=" + topicid  
50.                 + ", record=" + record + "]";  
51.     }  
52.   
53. }