定義兩個包（package）
CREATE?OR?REPLACE?PACKAGE?pro_package_test_001?AS
TYPE?Test_CURSOR01?IS?REF?CURSOR;
end?pro_package_test_001;
CREATE?OR?REPLACE?PACKAGE?pro_package_test_002?AS
TYPE?Test_CURSOR02?IS?REF?CURSOR;
end?pro_package_test_002; 定義存儲過程
CREATE?OR?REPLACE?PROCEDURE?pro_query_001
(
--參數IN表示輸入參數，OUT表示輸入參數，類型可以使用任意Oracle中的合法類型。
?in_lx??IN?Varchar2,
?p_cus_01?OUT?pro_package_test_001.Test_CURSOR01,
?p_cus_02?OUT?pro_package_test_002.Test_CURSOR02?????
)
AS
--定義變量
?vs_lx???VARCHAR2(1);???--變量
?vs_test1_id???VARCHAR2(100);???--變量
?vs_test1_mc???VARCHAR2(100);???--變量
?vs_test2_id???VARCHAR2(100);???--變量
?vs_test2_mc???VARCHAR2(100);???--變量
?--default_c?SYS_REFCURSOR;????
BEGIN
?--用輸入參數給變量賦初值。
?vs_lx:=?in_lx;
?--插入test1表。
?OPEN?p_cus_01?FOR??Select
????a.id?As?id1,
????a.mc?As?mc1,
????b.id?As?id2,
????b.mc?As?mc2?
????Into
????vs_test1_id,
????vs_test1_mc,
????vs_test2_id,
????vs_test2_mc?
????From?test1?a,test2?b?Where?a.id?=?b.id?And?a.lx?=?vs_lx;
???
????--if?p_cus_01%rowcount?=?0?then?
???--????p_cus_01:=default_c;?
???--?end?if;?
??
?OPEN?p_cus_02?FOR??Select
????id?As?id1,
????mc?As?mc1
????Into
????vs_test1_id,
????vs_test1_mc
????From?test2??Where?lx?=?vs_lx;??
???--?if?p_cus_02%rowcount?=?0?then?
????--???p_cus_02:=default_c;?
??--??end?if;???????
?--錯誤處理部分。OTHERS表示除了聲明外的任意錯誤。SQLERRM是系統內置變量保存了當前錯誤的詳細信息。
Exception
???WHEN?OTHERS?Then
???ROLLBACK;
???Return;
End?pro_query_001;
java 代碼調用
?????public?void?ProcQuery(String?procString,String?[]?params)?throws?Exception?
{?
?????????Session?session?=?null;
?????????Connection?conn?=?null;
?????????ResultSet?rs1?=?null;
?????????ResultSet?rs2?=?null;
???????????CallableStatement?proc?=?null;
???????????procString?=?"{call?pro_query_001(?,?,?)}";
???????????params?=?new?String?[1];
??????????params[0]="0";
?????????try?{????
??????????????session?=?getHibernateTemplate().getSessionFactory()
????????????.openSession();
??????????????conn?=?session.connection();?
?????????????proc?=?conn.prepareCall(procString);?
?????????????proc.setString(1,?params[0]);?????//傳入的參數
?????????????proc.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);
?????????????proc.registerOutParameter(3,oracle.jdbc.OracleTypes.CURSOR);?
?????????????proc.execute();?
?????????????rs1?=?(ResultSet)proc.getObject(2);//返回第一個游標
?????????????rs2?=?(ResultSet)proc.getObject(3);//返回第二個游標
????????????while(rs1?!=?null?&&?rs1.next())?{
??????????????????System.out.println(">>>"+rs1.getString("id1"));
??????????????????System.out.println(">>>"+rs1.getString("mc1"));
??????????????????System.out.println(">>>"+rs1.getString("id2"));
??????????????????System.out.println(">>>"+rs1.getString("mc2"));
????????????}
????????????while(rs2?!=?null?&&?rs2.next())?{
????????????????System.out.println(">>>"+rs1.getString("id1"));
????????????????System.out.println(">>>"+rs1.getString("mc1"));
??????????}????????????
?????????}?catch?(SQLException?e)?{???
?????????????e.printStackTrace();???
?????????????throw?new?Exception("調用存儲過程的時候發生錯誤[sql?=?"?+?procString?+?"]",?e);?????????
?????????}??finally?{
?????????????if?(proc?!=?null)
?????????????????proc.close();
?????????????if?(rs1?!=?null)
?????????????????rs1.close();
?????????????if?(rs2?!=?null)
?????????????????rs2.close();
?????????????if?(conn?!=?null)
?????????????conn.close();
?????????}?
?????}posted on 2010-01-20 09:52
JJCEA 閱讀(5179)
評論(0) 編輯 收藏 所屬分類:
數據庫日記 、
java文件操作