oracle預存程序及Java調用

標籤：oracle   預存程序   java   

初次研究出bug的地方非常多，所以注意點非常多，花了我三天時間除盡所有bug，我會把注意點都列出來，可能有落下的地方，還請多指正，相互探討。

首先上最終測試成功版預存程序代碼：（裡面代碼可能不盡對你都有用，借鑒參考吧，我全貼出來也是為了我以後好查）

說一下jar包用的是ojdbc14.jar，至於什麼class12.jar、ojdbc6.jar啊應該都可以，只要一種就可以了。

說說我的需求，以便讓大家更順利的看懂My Code，我的需求是：預存程序從Java端接收兩個參數userid（使用者）和topicid（話題），在預存程序進行迴圈查詢目前使用者對當前話題的點贊記錄,如果有記錄，則record為設定1，沒有則為0，最後返回一個結果集，是反應 使用者=>話題=>record相互對應的關係表。

好了不廢話了，上代碼吧，學習階段，所以難免情緒波動和囉嗦，也是希望以最直白能懂的方式敘述出來。

------------在資料庫建立一個type,對應JAVA端要傳入的對象結構 :  create or replace type tp_arr3 as Object    (      userid   nvarchar2(40),    --這裡從varchar2改成nvarchar2類型才能跟Java的String匹配上    topicid nvarchar2(40),  record nvarchar2(4)    )

---多次測試建立可能會出現“ORA-02303: 無法使用類型或表的相關性來刪除或取代一個類型”這個錯誤，這時只要換一個類型名字再建立就可以了----------CREATE OR REPLACE TYPE tp_arr_tbl3 AS TABLE OF tp_arr3--------------建立包 ，建立一個遊標類型用來放輸出參數 create or replace package testpkg as  type testcur is ref cursor; end testpkg;------建立預存程序 ，定義兩個參數，一個入參，是一個物件類型數組（這種類型應該可以滿足大部分複雜需求了），一個出參，是用遊標存放查詢值  CREATE OR REPLACE  procedure findRecord(type_obj IN tp_arr_tbl3,result out testpkg.testcur)    as   t tp_arr3;   sql2 varchar2(500);  sql3 varchar2(500);   v_count varchar2(4);     --臨時中間變數，用來存放對應的record  BEGIN         sql2 :='drop table tb_temp';     sql3 :='CREATE TABLE tb_temp( userid varchar2(40),topicid varchar2(40) primary key, record varchar2(4))';       execute immediate sql2;       execute immediate sql3;              FOR i IN type_obj.first()..type_obj.last()        LOOP       t:= type_obj(i);       select count(*) into v_count from scott.tb_praise_rel where userid=t.userid and topicid=t.topicid;       dbms_output.put_line( t.userid || '=>'||t.topicid ||'=>' || v_count);       insert into tb_temp values (t.userid,t.topicid, v_count);      END LOOP;      COMMIT;            open result for select * from tb_temp; END;--------------執行預存程序 declare ta tp_arr_tbl3:=tp_arr_tbl3();      --對象的聲明 t tp_arr3:=tp_arr3('0','0','0');    --聲明及賦初值（必要步驟）beginfor i in 1..12 loopta.extend;  t.userid:='1';  t.topicid:=i;  t.record:='0';  ta(i):=t;  findRecord(ta);  end loop;  end findRecord; ----------------------表查詢測試部分  select * from tb_temp;  select * from scott.tb_praise_rel;  select userid from scott.tb_praise_rel where userid='1' and topicid='1';-----------------------遊標測試，後來沒用，可以略過  cursor testcur is select userid,topicid from scott.tb_praise_rel;   cur testcur%rowtype;     open testcur;    loop      fetch testcur into cur;     exit when testcur%notfound;     dbms_output.put_line( 'userid:' || cur.userid || ',topicid:' || cur.topicid );     update tb_temp set record='1' where userid=cur.userid and topicid=cur.topicid;     end loop;     dbms_output.put_line('----------------------');     close testcur;      COMMIT;

注意點：1.測試使用者起初用的Scott，發現沒有執行許可權，對其進行賦予dba許可權還是不行，遂後來用的system；

2.多次測試建立可能會出現“ORA-02303: 無法使用類型或表的相關性來刪除或取代一個類型”這個錯誤，這時只要換一個類型名字再建立就可以了；

3.由於我的tb_temp表有唯一欄位約束，所以預存程序每次進來先刪表，再建表，再插入資料；

4.記得該打分號的地方不要漏，不該打的地方不要多；

5.執行預存程序的時候，要先聲明並賦初值，不然也會報錯；

6.注意pl/sql裡執行預存程序測試賦值時候ta.extend不能少；

6.自訂類型要注意的地方很多，比如nvarchar2和JavaString類型的定義；

7.簡單說<span style="font-family: Arial, Helvetica, sans-serif;">tp_arr3 類型是指一條記錄，</span><span style="font-family: Arial, Helvetica, sans-serif;">tp_arr_tbl3是指多條記錄；</span>

--點贊關係表 create table tb_praise_rel(       id varchar2(40) primary key,       userid varchar2(40),             --使用者id        topicid  varchar2(40),            --話題id        remarks1 varchar2(3000),        --備用欄位        remarks2 varchar2(3000),       remarks3 varchar2(3000));

再上Java調用代碼：

package com.lofter.svntesr;import java.sql.Array;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.PreparedStatement;import java.sql.ResultSet;import java.sql.SQLException;import java.util.ArrayList;import java.util.List;import java.util.Map;import oracle.jdbc.OracleTypes;import oracle.sql.STRUCT;import oracle.sql.StructDescriptor;import com.lofter.bean.ProcedureBean;public class ProcedureTest3 {/** * @param args */public static void main(String[] args) {try {                Class.forName("oracle.jdbc.driver.OracleDriver");                String url = "jdbc:oracle:thin:@localhost:1521:orcl";                //網上很多卡在擷取con這個地方的，我最初也是，說是什麼jar包問題，刪掉class12啊，什麼oracle與apache串連池衝突啊，但是我其實是預存程序沒寫對，最後繞了一圈回來還是用的這種方法測試通過，並沒有加((org.apache.commons.dbcp.PoolableConnection) conn).getInnermostDelegate()            Connection con = DriverManager.getConnection(url, "system", "a");          //      PreparedStatement pstmt = null;              CallableStatement cs = null;            ResultSet rs=null;                        List<ProcedureBean> list = new ArrayList<ProcedureBean>();    for (int i = 1; i <= 12; i++) {    String r = i + "";    list.add(new ProcedureBean("1", r, "0"));    }    //list.add(new ProcedureBean("1","5f60b0f0-03d9-4671-b945-936fe821fe19", "0"));        //如果預存程序是用我這種對象數組as object類型，則java調用這一步必不可少，這是對之前在pl/sql中聲明的tp_arr3 類型的映射，表示在pl/sql中去匹配你自訂的類型    //還有注意要大寫,不然可能會報“無效名稱模式”    StructDescriptor recDesc = StructDescriptor.createDescriptor("TP_ARR3", con);    //這一步是將你自訂的類型轉化成oracle自己的類型，即STRUCT，相當於一個Object類，因為oracle的開發人員也不知道你會定義一個什麼名字的類型，反正只用提供一個規則，最後大家都照著這個規則來轉化就是了ArrayList<STRUCT> pstruct = new ArrayList<STRUCT>();for (ProcedureBean pb : list) {System.out.println(pb);Object[] objs = new Object[3];objs[0] = pb.getUserid();objs[1] = pb.getTopicid();objs[2] = pb.getRecord();STRUCT item = new STRUCT(recDesc, con, objs);pstruct.add(item);}//這是第二步映射，映射我在oracle中自訂的tp_arr_tbl3類型，注意也要大寫，網上也有說要加包名，不是同一個使用者要加使用者首碼什麼的，我沒有加，測試也通過，可能不是極端情況吧oracle.sql.ArrayDescriptor desc = oracle.sql.ArrayDescriptor.createDescriptor("TP_ARR_TBL3", con);                oracle.sql.ARRAY array = new oracle.sql.ARRAY(desc, con, pstruct.toArray());                         //也有說調用的時候要加包名的            cs = con.prepareCall("{call findRecord(?,?)}");                        //設定參數這裡，1、2分別對應預存程序findRecord(?,?)中參數的位置，注意位置不要錯了            cs.setArray(1, array);            cs.registerOutParameter(2, OracleTypes.CURSOR);            cs.execute();            rs=(ResultSet) cs.getObject(2);//取資料也是根據對應參數位置來的                        while( rs.next() ){            System.out.println("result : \t" + rs.getString(1)+"\t"+rs.getString(2)+"\t"+rs.getString(3));            }            con.commit();        } catch (Exception e) {                e.printStackTrace();            }        }    }

Java調用注意：基本上注意事項都以注釋的方式寫在代碼裡了，也有可能沒想起來，很多bug資訊由於測試通過心切，沒能及時複製下來。注意不要導錯包

2.錯誤資訊“Message file ‘oracle.jdbc.driver.Messages‘ is missing.”，可能是你寫錯了或類型與oracle中不匹配，不要去找什麼jar包啊什麼的，網上資訊也不多，我在這繞了好久，多檢查一下上面提到的加包名、大小寫、轉類型什麼的；

還有其他沒想起來或沒碰到的bug只有親們多結合錯誤資訊猜測，多動手測測，相信就會迎刃而解了。

測試的javaBean：

package com.lofter.bean;import java.io.Serializable;public class ProcedureBean implements Serializable {private static final long serialVersionUID = 809894604693791308L;private String userid;private String topicid;private String record;public ProcedureBean() {super();}public ProcedureBean(String userid, String topicid, String record) {super();this.userid = userid;this.topicid = topicid;this.record = record;}public String getUserid() {return userid;}public void setUserid(String userid) {this.userid = userid;}public String getTopicid() {return topicid;}public void setTopicid(String topicid) {this.topicid = topicid;}public String getRecord() {return record;}public void setRecord(String record) {this.record = record;}@Overridepublic String toString() {return "ProcedureBean [userid=" + userid + ", topicid=" + topicid+ ", record=" + record + "]";}}

折騰了我好幾天研究這個東西，主要是要研究對象數群組類型的，從文法都不清楚，只能參照著能看懂大概的別人代碼揣測著寫，到最後測試通過，一把辛酸淚啊，兩天研究到淩晨四點，敲了不知多少遍斷行符號鍵，點了不知多少次運行（其實也沒多少，可能也是對之前調試所有bug過程的一次發泄），因為複雜類型的參數網上很多沒講清楚，也有很多bug，所以沒辦法就用。測試期間bug不斷，一直百度，也翻了下平時都沒認真看過的教材，最後終於打通從PL/SQL調用到Java調用，其實bug出最多在Java調用上，各種類型不符，只能說搜尋引擎真強大，互連網真強大，大資料真強大。最後我想說的是：“人就怕認真”。

oracle預存程序及Java調用