程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> oracle存儲過程及Java調用

oracle存儲過程及Java調用

編輯：Oracle教程

oracle存儲過程及Java調用

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

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

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

說說我的需求，以便讓大家更順利的看懂我的代碼，我的需求是：存儲過程從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');    --聲明及賦初值（必要步驟）
begin
for i in 1..12 loop
ta.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;
	}

	@Override
	public String toString() {
		return "ProcedureBean [userid=" + userid + ", topicid=" + topicid
				+ ", record=" + record + "]";
	}

}

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