Plsql下存储过程的游标使用
希望能对大家有帮助吧。
create or replace package loss is
type temp_cur is ref cursor ;
TYPE indexByTab IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER ;
procedure loss_proc(chr_PolicyNo in VARCHAR2, --** 保单号
num_DangerNo in NUMBER, --** 危险单位序号,默认1
DamageDate in date, --** 出险时间
chr_Currency in VARCHAR2, --** 赔付币种
num_SumClaim in NUMBER, --** 总估损金额
chr_BlnLargeLoss out VARCHAR2, --** 是否重大赔案:0,否;1,是
chr_BlnCashLoss out VARCHAR2 --** 是否现金赔款:0,否;1,是
);
end loss;
create or replace package body loss is
procedure loss_proc(chr_PolicyNo in VARCHAR2, --** 保单号
num_DangerNo in NUMBER, --** 危险单位序号,默认1
DamageDate in date, --** 出险时间
chr_Currency in VARCHAR2, --** 赔付币种
num_SumClaim in NUMBER, --** 总估损金额
chr_BlnLargeLoss out VARCHAR2, --** 是否重大赔案:0,否;1,是
chr_BlnCashLoss out VARCHAR2 --** 是否现金赔款:0,否;1,是
) is
cur_trail temp_cur;
chr_treatyno indexByTab;
num_ClaimShare indexByTab;
chr_ControlFlag varchar2(1);
chr_largelossflag varchar2(1);
chr_cashlossflag varchar2(1);
num_LargeLossValue number(14,2);
n_cnt number(4):=1;
begin
--根据保单号取得合约号以及其所占金额
chr_BlnLargeLoss:='0';
chr_BlnCashLoss:='0';
if not cur_trail%isopen then
open cur_trail for
select treatyno,
round(sharerate*num_SumClaim,2)
from prpCreinsTrial
where policyno = chr_PolicyNo
and dangerno = num_DangerNo
and substr(reinsmode,0,1) = 2;
end if;
--根据合约号取得其
--largelossflag(整个危险或是本公司所占份额估损标记)
--ControlFlag(共同理赔,理赔控制)
--for i in 1..fetch_status loop
loop
fetch cur_trail into
chr_treatyno(n_cnt),
num_ClaimShare(n_cnt);
--当游标指向未尾时自动退出游标
exit when(cur_trail%notfound);
select controlflag,
largelossflag,
cashlossflag,
LargeLossValue
into chr_ControlFlag,
chr_largelossflag,
chr_cashlossflag,
num_LargeLossValue
from fhtreaty
where treatyno = chr_treatyno(n_cnt);
--判断是否属于重大赔案
if chr_ControlFlag = '1' and chr_BlnLargeLoss = '0' then
if chr_largelossflag = '1'and num_ClaimShare(n_cnt) >= num_LargeLossValue then
chr_BlnLargeLoss:= '1';
end if;
else if chr_largelossflag = '0' and num_SumClaim >= num_LargeLossValue then
chr_BlnLargeLoss:= '1';
end if;
end if;
--现金赔款决断
if chr_BlnCashLoss = '0' then
if chr_cashlossflag = '1' and num_ClaimShare(n_cnt) >= num_LargeLossValue then
chr_BlnCashLoss:= '1';
end if;
else if chr_cashlossflag = '0' and num_SumClaim >= num_LargeLossValue then
chr_BlnCashLoss:= '1';
end if;
end if;
n_cnt:=n_cnt+1;
end loop;
--关闭游标
if cur_trail%isopen then
close cur_trail;
end if;
end loss_proc;
end loss;
以上是包头部分和包体部分,另外,我写了个测试程序,出贴出来吧。
package com.sinosoft.reins.exam.action;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.driver.OracleDriver;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import com.sinosoft.reins.exam.Dto.OracleRe_absDto;
import com.sinosoft.reins.exam.Dto.OracleRe_paraDto;
import com.sinosoft.sysframework.web.control.BaseProcessAction;
public class Loss extends BaseProcessAction {
/**
* 调用存储过程的action,仅作为示范,应该放在bl层
* @param request http请求
* @throws Exception
*/
// public void referenceOraclePackage(HttpServletRequest request,
// HttpServletResponse response) throws Exception {
public static void main(String[] args){
try{
OracleDriver oracleDriver = new oracle.jdbc.OracleDriver();
DriverManager.registerDriver(oracleDriver);
String url="jdbc:oracle:thin^^^^^^^^;
String user="*";
String password="*";
Connection ocacleconn= DriverManager.getConnection(url,user,password);
String query = "{call loss.loss_proc(?,?,?,?,?,?,?)}";
System.out.println("==========ocacleconn的类型:============"+ocacleconn.getClass().getName()) ;
//必须用OracleCallableStatement类型才能提供比JDBC API更多的方法,以支持复杂数据结构的传递
OracleCallableStatement cstmt = (OracleCallableStatement)ocacleconn.prepareCall(query);
//设定入参类型 OracleTypes.STRUCT对应OBJECT类型;OracleTypes.ARRAY对应TABLE类型
String policyno = "1002005132006000019";
String dangerno = "1";
java.sql.Date damagedate =java.sql.Date.valueOf("2006-07-26") ;
String currency = "CNY";
String sumnopaid = "1000000000000";
String largelossflag = "0";
String cashlossflag = "0";
cstmt.setObject(1,policyno,OracleTypes.VARCHAR);
cstmt.setObject(2,dangerno,OracleTypes.VARCHAR);
cstmt.setObject(3,damagedate,OracleTypes.DATE);
cstmt.setObject(4,currency,OracleTypes.VARCHAR);
cstmt.setObject(5,sumnopaid,OracleTypes.VARCHAR);
//设定返参类型 IndexTableOutParameter对应索引表类型;OracleTypes.CURSOR对应TABLE的游标
cstmt.registerOutParameter(6,OracleTypes.VARCHAR);
cstmt.registerOutParameter(7,OracleTypes.VARCHAR);
//执行存储过程
cstmt.execute();
String largeloss = (String)cstmt.getString(6);
String cashloss = (String)cstmt.getString(7);
System.out.println("返回参数largeloss is :"+largeloss);
System.out.println("cashloss is :"+cashloss);
//以下过程是为了控制台显示
cstmt.close();
}
catch (Throwable ex){
ex.printStackTrace();
}
}
create or replace package loss is
type temp_cur is ref cursor ;
TYPE indexByTab IS TABLE OF VARCHAR2(20) INDEX BY BINARY_INTEGER ;
procedure loss_proc(chr_PolicyNo in VARCHAR2, --** 保单号
num_DangerNo in NUMBER, --** 危险单位序号,默认1
DamageDate in date, --** 出险时间
chr_Currency in VARCHAR2, --** 赔付币种
num_SumClaim in NUMBER, --** 总估损金额
chr_BlnLargeLoss out VARCHAR2, --** 是否重大赔案:0,否;1,是
chr_BlnCashLoss out VARCHAR2 --** 是否现金赔款:0,否;1,是
);
end loss;
create or replace package body loss is
procedure loss_proc(chr_PolicyNo in VARCHAR2, --** 保单号
num_DangerNo in NUMBER, --** 危险单位序号,默认1
DamageDate in date, --** 出险时间
chr_Currency in VARCHAR2, --** 赔付币种
num_SumClaim in NUMBER, --** 总估损金额
chr_BlnLargeLoss out VARCHAR2, --** 是否重大赔案:0,否;1,是
chr_BlnCashLoss out VARCHAR2 --** 是否现金赔款:0,否;1,是
) is
cur_trail temp_cur;
chr_treatyno indexByTab;
num_ClaimShare indexByTab;
chr_ControlFlag varchar2(1);
chr_largelossflag varchar2(1);
chr_cashlossflag varchar2(1);
num_LargeLossValue number(14,2);
n_cnt number(4):=1;
begin
--根据保单号取得合约号以及其所占金额
chr_BlnLargeLoss:='0';
chr_BlnCashLoss:='0';
if not cur_trail%isopen then
open cur_trail for
select treatyno,
round(sharerate*num_SumClaim,2)
from prpCreinsTrial
where policyno = chr_PolicyNo
and dangerno = num_DangerNo
and substr(reinsmode,0,1) = 2;
end if;
--根据合约号取得其
--largelossflag(整个危险或是本公司所占份额估损标记)
--ControlFlag(共同理赔,理赔控制)
--for i in 1..fetch_status loop
loop
fetch cur_trail into
chr_treatyno(n_cnt),
num_ClaimShare(n_cnt);
--当游标指向未尾时自动退出游标
exit when(cur_trail%notfound);
select controlflag,
largelossflag,
cashlossflag,
LargeLossValue
into chr_ControlFlag,
chr_largelossflag,
chr_cashlossflag,
num_LargeLossValue
from fhtreaty
where treatyno = chr_treatyno(n_cnt);
--判断是否属于重大赔案
if chr_ControlFlag = '1' and chr_BlnLargeLoss = '0' then
if chr_largelossflag = '1'and num_ClaimShare(n_cnt) >= num_LargeLossValue then
chr_BlnLargeLoss:= '1';
end if;
else if chr_largelossflag = '0' and num_SumClaim >= num_LargeLossValue then
chr_BlnLargeLoss:= '1';
end if;
end if;
--现金赔款决断
if chr_BlnCashLoss = '0' then
if chr_cashlossflag = '1' and num_ClaimShare(n_cnt) >= num_LargeLossValue then
chr_BlnCashLoss:= '1';
end if;
else if chr_cashlossflag = '0' and num_SumClaim >= num_LargeLossValue then
chr_BlnCashLoss:= '1';
end if;
end if;
n_cnt:=n_cnt+1;
end loop;
--关闭游标
if cur_trail%isopen then
close cur_trail;
end if;
end loss_proc;
end loss;
以上是包头部分和包体部分,另外,我写了个测试程序,出贴出来吧。
package com.sinosoft.reins.exam.action;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import javax.servlet.ServletOutputStream;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.driver.OracleCallableStatement;
import oracle.jdbc.driver.OracleDriver;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import com.sinosoft.reins.exam.Dto.OracleRe_absDto;
import com.sinosoft.reins.exam.Dto.OracleRe_paraDto;
import com.sinosoft.sysframework.web.control.BaseProcessAction;
public class Loss extends BaseProcessAction {
/**
* 调用存储过程的action,仅作为示范,应该放在bl层
* @param request http请求
* @throws Exception
*/
// public void referenceOraclePackage(HttpServletRequest request,
// HttpServletResponse response) throws Exception {
public static void main(String[] args){
try{
OracleDriver oracleDriver = new oracle.jdbc.OracleDriver();
DriverManager.registerDriver(oracleDriver);
String url="jdbc:oracle:thin^^^^^^^^;
String user="*";
String password="*";
Connection ocacleconn= DriverManager.getConnection(url,user,password);
String query = "{call loss.loss_proc(?,?,?,?,?,?,?)}";
System.out.println("==========ocacleconn的类型:============"+ocacleconn.getClass().getName()) ;
//必须用OracleCallableStatement类型才能提供比JDBC API更多的方法,以支持复杂数据结构的传递
OracleCallableStatement cstmt = (OracleCallableStatement)ocacleconn.prepareCall(query);
//设定入参类型 OracleTypes.STRUCT对应OBJECT类型;OracleTypes.ARRAY对应TABLE类型
String policyno = "1002005132006000019";
String dangerno = "1";
java.sql.Date damagedate =java.sql.Date.valueOf("2006-07-26") ;
String currency = "CNY";
String sumnopaid = "1000000000000";
String largelossflag = "0";
String cashlossflag = "0";
cstmt.setObject(1,policyno,OracleTypes.VARCHAR);
cstmt.setObject(2,dangerno,OracleTypes.VARCHAR);
cstmt.setObject(3,damagedate,OracleTypes.DATE);
cstmt.setObject(4,currency,OracleTypes.VARCHAR);
cstmt.setObject(5,sumnopaid,OracleTypes.VARCHAR);
//设定返参类型 IndexTableOutParameter对应索引表类型;OracleTypes.CURSOR对应TABLE的游标
cstmt.registerOutParameter(6,OracleTypes.VARCHAR);
cstmt.registerOutParameter(7,OracleTypes.VARCHAR);
//执行存储过程
cstmt.execute();
String largeloss = (String)cstmt.getString(6);
String cashloss = (String)cstmt.getString(7);
System.out.println("返回参数largeloss is :"+largeloss);
System.out.println("cashloss is :"+cashloss);
//以下过程是为了控制台显示
cstmt.close();
}
catch (Throwable ex){
ex.printStackTrace();
}
}