JAVA执行带SYS_REFCURSOR游标的oracle存储过程返回结果集 会话级临时表
程序员文章站
2024-02-10 13:38:52
...
sys_refcursor是oracle9i以后系统定义的一个refcursor,主要作用是用于存储过程返回结果集。
存储过程结构如下:
CREATE OR REPLACE PROCEDURE SP_CalcChargePrice
/*
计算报备、评估收费基准价
@Charge=收费项目
@Amount=收费依据金额
@Kind=类别 1=报备 2=评估
@Year=暂定
*/
(
v_Charge IN VARCHAR2 DEFAULT NULL ,
v_AmountTemp IN NUMBER DEFAULT NULL ,
v_Kind IN NUMBER DEFAULT 1 ,
v_Year IN NUMBER DEFAULT 0 ,
v_Hours IN NUMBER DEFAULT 0,
v_Result OUT SYS_REFCURSOR
)
AS
BEGIN
--…………存储过程代码太长省略(400+行)…………
END;
存储过程带了6个参数,其中一个是OUT SYS_REFCURSOR类型用以返回结果集,
JAVA中调用该存储过程执行并返回结果集为ResultSet方法:
try {
DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
Connection conn = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:XE", "dbo_MTOA_XJICPA", "dbo_MTOA_XJICPA");
CallableStatement stmt = conn.prepareCall("{ call SP_CALCCHARGEPRICE(?,?,?,?,?,?) }");
stmt.setNString(1,"评估计件");
stmt.setDouble(2,16544.90);
stmt.setInt(3,2);
stmt.setInt(4,0);
stmt.setInt(5,0);
stmt.registerOutParameter(6, OracleTypes.CURSOR);
stmt.execute();
ResultSet rs = (ResultSet) stmt.getObject(6);
while (rs.next()) {
System.out.println(rs.getString("PRICE"));
}
rs.close();
rs = null;
stmt.close();
stmt = null;
conn.close();
conn = null;
}
catch (SQLException e) {
System.out.println(e.getLocalizedMessage());
}
oracle中调用方法(测试返回了4个字段值):
--调用方法
Declare
v_rent_rows SYS_REFCURSOR;
v_rent_row tt_ChargePrice_Result%rowType;
begin
SP_CalcChargePrice( '评估计件', 16544.90, 2, 0, 0,v_rent_rows);
Dbms_output.put_line('PRICE | RUNDATE | RESULT | UPAMOUNT');
loop
fetch v_rent_rows into v_rent_row;
exit when v_rent_rows%NOTFOUND;
Dbms_output.put_line(v_rent_row.PRICE||', '||v_rent_row.RUNDATE||', '||v_rent_row.RESULT||', '||v_rent_row.UPAMOUNT);
end loop;
close v_rent_rows;
end;
使用的临时表(会话级)建表语句:
CREATE Global Temporary Table TT_CHARGEPRICE_RESULT (
"KEYID" VARCHAR2(40 CHAR) NOT NULL ,
"CHARGEID" VARCHAR2(20 CHAR) NOT NULL ,
"IKIND" NUMBER(3) NOT NULL ,
"RUNDATE" VARCHAR2(200 CHAR) NOT NULL ,
"ILEVEL" NUMBER(10) NOT NULL ,
"BUSINESSNAME" VARCHAR2(50 CHAR) NOT NULL ,
"CHARGEPATTEM" NUMBER(18,6) ,
"UPAMOUNT" NUMBER(14,4) NOT NULL ,
"DOWNAMOUNT" NUMBER(14,4) NOT NULL ,
"ISYEAR" NUMBER(1) NOT NULL ,
"BASEPRICE" NUMBER(10,4) NOT NULL ,
"CALCWAY" VARCHAR2(1000 CHAR) NOT NULL ,
"WAYTYPE" NUMBER(10) NOT NULL ,
"PAYCOUNT" NUMBER(8,6) NOT NULL ,
"DISCOUNT" NUMBER(8,6) NOT NULL ,
"ISLOOP" NUMBER(1) NOT NULL ,
"RESULT" NUMBER(18,6) NOT NULL ,
"PRICE" NUMBER(18,6) NOT NULL
) on commit preserve rows
Oracle中会话级临时表为会话隔离,当前session存储过程查询生成的结果只有当前会话可见,会话结束清空,该会话没结束,其它新增会话查询此表也是空的。真正的会话隔离,而事务级临时表则是一提交事务则清空。
上一篇: Debian上安装Oracle客户端
下一篇: 一个纠结的问题