3.4 select into不可乎視的問題
我們知道在pl/sql中要想從數(shù)據(jù)表中向變量賦值，需要使用select into 子句。
但是它會(huì)帶動(dòng)來一些問題，如果查詢沒有記錄時(shí)，會(huì)拋出no_data_found異常。
如果有多條記錄時(shí)，會(huì)拋出too_many_rows異常。
這個(gè)是比較糟糕的。一旦拋出了異常，就會(huì)讓過程中斷。特別是no_data_found這種異常，沒有嚴(yán)重到要讓程序中斷的地步，可以完全交給由程序進(jìn)行處理。

    create or replace procedure procexception(p varchar2)
as 
  v_postype varchar2(20);
begin
   select pos_type into v_postype from pos_type_tbl where 1=0;
    dbms_output.put_line(v_postype);
end;
	
  

執(zhí)行這個(gè)過程

    SQL> exec procexception('a');
報(bào)錯(cuò)
ORA-01403: no data found
ORA-06512: at "LIFEMAN.PROCEXCEPTION", line 6
ORA-06512: at line 1
  

處理這個(gè)有三個(gè)辦法
1． 直接加上異常處理。

    create or replace procedure procexception(p varchar2)
as 
  v_postype varchar2(20);
  
begin
   select pos_type into v_postype from pos_type_tbl where 1=0;
    dbms_output.put_line(v_postype);
exception 
  when no_data_found then
    dbms_output.put_line('沒找到數(shù)據(jù)');
end;
  

這樣做換湯不換藥，程序仍然被中斷。可能這樣不是我們所想要的。
2. select into做為一個(gè)獨(dú)立的塊，在這個(gè)塊中進(jìn)行異常處理

    create or replace procedure procexception(p varchar2)
as 
  v_postype varchar2(20);
  
begin
  begin
   select pos_type into v_postype from pos_type_tbl where 1=0;
    dbms_output.put_line(v_postype);
 exception 
  when no_data_found then
    v_postype := '';
  end;
  dbms_output.put_line(v_postype);
end;
  

這是一種比較好的處理方式了。不會(huì)因?yàn)檫@個(gè)異常而引起程序中斷。
3.使用游標(biāo)

    create or replace procedure procexception(p varchar2)
as 
  v_postype varchar2(20);
  cursor c_postype is select pos_type  from pos_type_tbl where 1=0;
begin
  open c_postype;
    fetch c_postype into v_postype;
  close c_postype;
  dbms_output.put_line(v_postype);
end;
  

這樣就完全的避免了no_data_found異常。完全交由程序員來進(jìn)行控制了。

第二種情況是too_many_rows 異常的問題。
Too_many_rows 這個(gè)問題比起no_data_found要復(fù)雜一些。
給一個(gè)變量賦值時(shí)，但是查詢結(jié)果有多個(gè)記錄。
處理這種問題也有兩種情況：
1． 多條數(shù)據(jù)是可以接受的，也就是說從結(jié)果集中隨便取一個(gè)值就行。這種情況應(yīng)該很極端了吧，如果出現(xiàn)這種情況，也說明了程序的嚴(yán)謹(jǐn)性存在問題。
2． 多條數(shù)據(jù)是不可以被接受的，在這種情況肯定是程序的邏輯出了問題，也說是說原來根本就不會(huì)想到它會(huì)產(chǎn)生多條記錄。
對(duì)于第一種情況，就必須采用游標(biāo)來處理，而對(duì)于第二種情況就必須使用內(nèi)部塊來處理，重新拋出異常。
多條數(shù)據(jù)可以接受,隨便取一條，這個(gè)跟no_data_found的處理方式一樣，使用游標(biāo)。
我這里僅說第二種情況，不可接受多條數(shù)據(jù)，但是不要忘了處理no_data_found哦。這就不能使用游標(biāo)了，必須使用內(nèi)部塊。

    create or replace procedure procexception2(p varchar2)
as 
  v_postype varchar2(20);
 
begin
  begin
    select pos_type into v_postype from pos_type_tbl where rownum < 5;
  exception
    when no_data_found then
      v_postype :=null;
    when too_many_rows then
      raise_application_error(-20000,'對(duì)v_postype賦值時(shí)，找到多條數(shù)據(jù)');
  end;
 dbms_output.put_line(v_postype);
end;
  

需要注意的是一定要加上對(duì)no_data_found的處理，對(duì)出現(xiàn)多條記錄的情況則繼續(xù)拋出異常，讓上一層來處理。
總之對(duì)于select into的語(yǔ)句需要注意這兩種情況了。需要妥當(dāng)處理啊。

3.5 在存儲(chǔ)過程中返回結(jié)果集
我們使用存儲(chǔ)過程都是返回值都是單一的，有時(shí)我們需要從過程中返回一個(gè)集合。即多條數(shù)據(jù)。這有幾種解決方案。比較簡(jiǎn)單的做法是寫臨時(shí)表，但是這種做法不靈活。而且維護(hù)麻煩。我們可以使用嵌套表來實(shí)現(xiàn).沒有一個(gè)集合類型能夠與java的jdbc類型匹配。這就是對(duì)象與關(guān)系數(shù)據(jù)庫(kù)的阻抗吧。數(shù)據(jù)庫(kù)的對(duì)象并不能夠完全轉(zhuǎn)換為編程語(yǔ)言的對(duì)象，還必須使用關(guān)系數(shù)據(jù)庫(kù)的處理方式。

    create or replace package procpkg is
   type refcursor is ref cursor;
   procedure procrefcursor(p varchar2, p_ref_postypeList  out refcursor);
end procpkg;

create or replace package body procpkg is
  procedure procrefcursor(p varchar2, p_ref_postypeList out  refcursor)
  is
    v_posTypeList PosTypeTable;
  begin
    v_posTypeList :=PosTypeTable();--初始化嵌套表
    v_posTypeList.extend;
    v_posTypeList(1) := PosType('A001','客戶資料變更');
    v_posTypeList.extend;
    v_posTypeList(2) := PosType('A002','團(tuán)體資料變更');
    v_posTypeList.extend;
    v_posTypeList(3) := PosType('A003','受益人變更');
    v_posTypeList.extend;
    v_posTypeList(4) := PosType('A004','續(xù)期交費(fèi)方式變更');
    open p_ref_postypeList for  select * from table(cast (v_posTypeList as PosTypeTable));
  end;
end procpkg;
  

在包頭中定義了一個(gè)游標(biāo)變量,并把它作為存儲(chǔ)過程的參數(shù)類型。
在存儲(chǔ)過程中定義了一個(gè)嵌套表變量，對(duì)數(shù)據(jù)寫進(jìn)嵌套表中，然后把嵌套表進(jìn)行類型轉(zhuǎn)換為table,游標(biāo)變量從這個(gè)嵌套表中進(jìn)行查詢。外部程序調(diào)用這個(gè)游標(biāo)。
所以這個(gè)過程需要定義兩個(gè)類型。

    create or replace type PosType as Object (
  posType varchar2(20),
  description varchar2(50)
);
  

create or replace type PosTypeTable is table of PosType;
需要注意，這兩個(gè)類型不能定義在包頭中，必須單獨(dú)定義，這樣java層才能使用。

在外部通過pl/sql來調(diào)用這個(gè)過程非常簡(jiǎn)單。

    set serveroutput on;
declare 
  type refcursor is ref cursor;
  v_ref_postype refcursor;
  v_postype varchar2(20);
  v_desc varchar2(50);
begin
  procpkg.procrefcursor('a',v_ref_postype);
  loop
    fetch  v_ref_postype into v_postype,v_desc;
    exit when v_ref_postype%notfound;
    dbms_output.put_line('posType:'|| v_postype || ';description:' || v_desc);
  end loop;
end;
  

注意：對(duì)于游標(biāo)變量，不能使用for循環(huán)來處理。因?yàn)閒or循環(huán)會(huì)隱式的執(zhí)行open動(dòng)作。而通過open for來打開的游標(biāo)%isopen是為true的。也就是默認(rèn)打開的。Open一個(gè)已經(jīng)open的游標(biāo)是錯(cuò)誤的。所以不能使用for循環(huán)來處理游標(biāo)變量。

我們主要討論的是如何通過jdbc調(diào)用來處理這個(gè)輸出參數(shù)。

    conn = this.getDataSource().getConnection();
CallableStatement call = conn.prepareCall("{call procpkg.procrefcursor(?,?)}");
call.setString(1, null);
call.registerOutParameter(2, OracleTypes.CURSOR);
call.execute();
ResultSet rsResult = (ResultSet) call.getObject(2);
while (rsResult.next()) {
  String posType = rsResult.getString("posType");
  String description = rsResult.getString("description");
  ......
}
  

這就是jdbc的處理方法。

Ibatis處理方法：
1.參數(shù)配置

    <parameterMap id="PosTypeMAP" class="java.util.Map"> 
 <parameter property="p" jdbcType="VARCHAR" javaType="java.lang.String" /> 
 <parameter property="p_ref_postypeList" jdbcType="ORACLECURSOR" javaType="java.sql.ResultSet" mode="OUT" typeHandler="com.palic.elis.pos.dayprocset.integration.dao.impl.CursorHandlerCallBack" /> 
</parameterMap>

2.調(diào)用過程
  <procedure id ="procrefcursor" parameterMap ="PosTypeMAP">
      {call procpkg.procrefcursor(?,?)}
  </procedure>

3.定義自己的處理器
  public class CursorHandlerCallBack implements TypeHandler{
	public Object getResult(CallableStatement cs, int index) throws SQLException {
		ResultSet rs = (ResultSet)cs.getObject(index);
        List result = new ArrayList();
		while(rs.next()) {
			String postype =rs.getString(1);
			String description = rs.getString(2);
			CodeTableItemDTO posTypeItem = new CodeTableItemDTO();
			posTypeItem.setCode(postype);
			posTypeItem.setDescription(description);
			result.add(posTypeItem);
		}
		return result;
	}



4. dao方法
	public List procPostype() {
		String p = "";
		Map para = new HashMap();
		para.put("p",p);
		para.put("p_ref_postypeList",null);
		 this.getSqlMapClientTemplate().queryForList("pos_dayprocset.procrefcursor",  para);
		 return (List)para.get("p_ref_postypeList");
	}
  

這個(gè)跟jdbc的方式非常的相似.
我們使用的是ibatis的2.0版本，比較麻煩。
如果是使用2.2以上版本就非常簡(jiǎn)單的。
因?yàn)榭梢栽趐arameterMap中定義一個(gè)resultMap.這樣就無(wú)需要自己定義處理器了。
可以從分析2.0和2.0的dtd文件知道。

存儲(chǔ)過程常用技巧3