定义两个包（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 阅读(5171)
评论(0) 编辑 收藏 所属分类:
数据库日记 、
java文件操作