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

    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;
	
  

執行這個過程

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

處理這個有三個辦法
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('沒找到數據');
end;
  

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

    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;
  

這是一種比較好的處理方式了。不會因為這個異常而引起程序中斷。
3.使用游標

    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異常。完全交由程序員來進行控制了。

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

    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,'對v_postype賦值時，找到多條數據');
  end;
 dbms_output.put_line(v_postype);
end;
  

需要注意的是一定要加上對no_data_found的處理，對出現多條記錄的情況則繼續拋出異常，讓上一層來處理。
總之對于select into的語句需要注意這兩種情況了。需要妥當處理啊。

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

    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','團體資料變更');
    v_posTypeList.extend;
    v_posTypeList(3) := PosType('A003','受益人變更');
    v_posTypeList.extend;
    v_posTypeList(4) := PosType('A004','續期交費方式變更');
    open p_ref_postypeList for  select * from table(cast (v_posTypeList as PosTypeTable));
  end;
end procpkg;
  

在包頭中定義了一個游標變量,并把它作為存儲過程的參數類型。
在存儲過程中定義了一個嵌套表變量，對數據寫進嵌套表中，然后把嵌套表進行類型轉換為table,游標變量從這個嵌套表中進行查詢。外部程序調用這個游標。
所以這個過程需要定義兩個類型。

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

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

在外部通過pl/sql來調用這個過程非常簡單。

    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;
  

注意：對于游標變量，不能使用for循環來處理。因為for循環會隱式的執行open動作。而通過open for來打開的游標%isopen是為true的。也就是默認打開的。Open一個已經open的游標是錯誤的。所以不能使用for循環來處理游標變量。

我們主要討論的是如何通過jdbc調用來處理這個輸出參數。

    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.參數配置

    <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.調用過程
  <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");
	}
  

這個跟jdbc的方式非常的相似.
我們使用的是ibatis的2.0版本，比較麻煩。
如果是使用2.2以上版本就非常簡單的。
因為可以在parameterMap中定義一個resultMap.這樣就無需要自己定義處理器了。
可以從分析2.0和2.0的dtd文件知道。

存儲過程常用技巧3