数据库存储过程以及对存储过程调用
来源：互联网 发布：hadoop java 统计订单 编辑：程序博客网 时间：2024/06/05 16:18
首先，来个存储过程的模板：
create or replace procedure 存储过程名(
/*声明入参和返回参数*/
ORDER_ID in varchar2, --in 表示入参
POLICYNO in varchar2,
flag out int --out 标识返回参数
)
is
/*声明存储过程变量*/
err_exception varchar2(10),
sqlStr varchar2(2000);
begin
/*声明pl/sql程序体*/
COMMIT;
--异常处理
EXCEPTION
WHEN others THEN
ROLLBACK;
end;
--------------------
/*声明：下面这段sql直接执行 */
declare
/*声明返回参数*/
flag number:=0;
begin
/*调用存储过程*/
sp_insurance('a','b',flag); /*a,b是入参，flag是返参*/
/*打印返回参数*/
dbms_output.put_line(flag);
end;
--------------------------
再来个具体代码：
create or replace procedure sp_order_proposal(
/*声明入参和返回参数*/
VAR_ORDER_NO in varchar2,
flag out int,
err_msg out varchar2
)
is
/*声明存储过程变量*/
app_exception Exception;
sqlStr varchar2(2000);
VAR_PROPOSAL_ID varchar2(50);
begin
/*声明pl/sql程序体*/
select sys_guid() into VAR_PROPOSAL_ID from dual;
/* 1.1 插入投保单表 */
insert into TEC_ORDER_PROPOSAL(
PROPOSAL_ID ,
APPLYDATE ,
CVALIDATE ,
AGENTCODE ,
AGENTNAME ,
AGENTTEL ,
PLANUNIT ,
ACTPREMIUM ,
POLICYPRINTTYPE ,
ORDER_PRICE ,
ORDER_PREMIUM ,
CREATE_DATE ,
UPDATE_DATE ,
ORDER_ID
)
select
VAR_PROPOSAL_ID,
to_date(replace(extractvalue(xmltype(a.message),'/request/proposal/proposalInfo/applyDate '),'-'),'yyyymmdd') as APPLYDATE ,
to_date(replace(extractvalue(xmltype(a.message),'/request/proposal/proposalInfo/cValiDate '),'-'),'yyyymmdd') as CVALIDATE ,
extractvalue(xmltype(a.message),'/request/proposal/proposalInfo/agencyCode ') as AGENTCODE ,
extractvalue(xmltype(a.message),'/request/proposal/proposalInfo/agencyName ') as AGENTNAME ,
extractvalue(xmltype(a.message),'/request/proposal/proposalInfo/agentTel ') as AGENTTEL ,
extractvalue(xmltype(a.message),'/request/proposal/proposalInfo/planUnit ') as PLANUNIT ,
extractvalue(xmltype(a.message),'/request/proposal/proposalInfo/actPremium ') as ACTPREMIUM ,
extractvalue(xmltype(a.message),'/request/proposal/proposalInfo/policyPrintType') as POLICYPRINTTYPE ,
null as ORDER_PRICE ,
null as ORDER_PREMIUM ,
sysdate as CREATE_DATE ,
sysdate as UPDATE_DATE ,
a.order_id as ORDER_ID
from
TEC_INSURE_RECORD a ,TEC_ORDER b
where
a.ORDER_ID = b.ORDER_ID
and a.stage='A' and b.ORDER_NO=VAR_ORDER_NO;
/* 1.2 插入投保单的投保人表 */
insert into TEC_ORDER_POLICYHOLDER(
PH_ID ,
INSURE_NAME ,
INSURE_SEX ,
INSURE_BIRTHDAY ,
INSURE_IDTYPE ,
INSURE_IDNUM ,
INSURE_CERTIEXPIREDATE,
SOCIALSECURITYSTATUS ,
NATIONALITY ,
MARRIAGEID ,
WORKCOMPANY ,
JOB ,
JOBCATEID ,
INSURE_PHONE ,
HOMETEL ,
INSURE_EMAIL ,
INSURE_PROVINCE ,
INSURE_CITY ,
INSURE_DISTRICE ,
INSURE_ADDRESS ,
POSTALCODE ,
INCOME ,
INCOMESOURCE ,
HEIGHT ,
WEIGHT ,
CREATE_DATE ,
UPDATE_DATE ,
PROPOSAL_ID
)
select
sys_guid() as PH_ID ,
extractvalue(xmltype(a.message),'/request/proposal/policyHolder/firstName ') as INSURE_NAME ,
extractvalue(xmltype(a.message),'/request/proposal/policyHolder/gender ') as INSURE_SEX ,
extractvalue(xmltype(a.message),'/request/proposal/policyHolder/birthday ') as INSURE_BIRTHDAY ,
extractvalue(xmltype(a.message),'/request/proposal/policyHolder/certiType ') as INSURE_IDTYPE ,
extractvalue(xmltype(a.message),'/request/proposal/policyHolder/certiCode ') as INSURE_IDNUM ,
to_date(replace(extractvalue(xmltype(a.message),'/request/proposal/policyHolder/certiExpireDate '),'-'),'yyyymmdd') as INSURE_CERTIEXPIREDATE,
extractvalue(xmltype(a.message),'/request/proposal/policyHolder/socialSecurityStatus ') as SOCIALSECURITYSTATUS ,
extractvalue(xmltype(a.message),'/request/proposal/policyHolder/nationality ') as NATIONALITY ,
extractvalue(xmltype(a.message),'/request/proposal/policyHolder/marriageId ') as MARRIAGEID ,
extractvalue(xmltype(a.message),'/request/proposal/policyHolder/workCompany ') as WORKCOMPANY ,
extractvalue(xmltype(a.message),'/request/proposal/policyHolder/job ') as JOB ,
extractvalue(xmltype(a.message),'/request/proposal/policyHolder/jobCateId ') as JOBCATEID ,
extractvalue(xmltype(a.message),'/request/proposal/policyHolder/mobile ') as INSURE_PHONE ,
extractvalue(xmltype(a.message),'/request/proposal/policyHolder/homeTel ') as HOMETEL ,
extractvalue(xmltype(a.message),'/request/proposal/policyHolder/email ') as INSURE_EMAIL ,
extractvalue(xmltype(a.message),'/request/proposal/policyHolder/STATE ') as INSURE_PROVINCE ,
extractvalue(xmltype(a.message),'/request/proposal/policyHolder/CITY ') as INSURE_CITY ,
extractvalue(xmltype(a.message),'/request/proposal/policyHolder/Ward ') as INSURE_DISTRICE ,
extractvalue(xmltype(a.message),'/request/proposal/policyHolder/address1 ') as INSURE_ADDRESS ,
extractvalue(xmltype(a.message),'/request/proposal/policyHolder/postalCode ') as POSTALCODE ,
extractvalue(xmltype(a.message),'/request/proposal/policyHolder/income ') as INCOME ,
extractvalue(xmltype(a.message),'/request/proposal/policyHolder/incomeSource ') as INCOMESOURCE ,
extractvalue(xmltype(a.message),'/request/proposal/policyHolder/height ') as HEIGHT ,
extractvalue(xmltype(a.message),'/request/proposal/policyHolder/weight ') as WEIGHT ,
sysdate as CREATE_DATE ,
sysdate as UPDATE_DATE ,
VAR_PROPOSAL_ID
from
TEC_INSURE_RECORD a ,TEC_ORDER b
where
a.ORDER_ID = b.ORDER_ID
and a.stage='A' and b.ORDER_NO=VAR_ORDER_NO;
/* 1.3 插入投保单的被保人表 */
insert into TEC_ORDER_INSURED(
INSURED_ID ,
INSURE_RELATIONSHIP ,
INSURED_NAME ,
INSURED_SEX ,
INSURED_BIRTHDAY ,
INSURED_IDTYPE ,
INSURED_IDNUM ,
INSURED_CERTIEXPIREDATE,
SOCIALSECURITYSTATUS ,
NATIONALITY ,
MARRIAGEID ,
WORKCOMPANY ,
JOB ,
JOBCATEID ,
INSURE_PHONE ,
HOMETEL ,
INSURE_EMAIL ,
INSURE_PROVINCE ,
INSURE_CITY ,
INSURE_DISTRICE ,
INSURE_ADDRESS ,
INCOME ,
POSTALCODE ,
RELATIONTOLA1 ,
CREATE_DATE ,
UPDATE_DATE ,
PROPOSAL_ID
)
select
sys_guid() as INSURED_ID ,
extractvalue(xmltype(a.message),'/request/proposal/insureds/insured/relationToPh ') as INSURE_RELATIONSHIP ,
extractvalue(xmltype(a.message),'/request/proposal/insureds/insured/firstName ') as INSURED_NAME ,
extractvalue(xmltype(a.message),'/request/proposal/insureds/insured/gender ') as INSURED_SEX ,
extractvalue(xmltype(a.message),'/request/proposal/insureds/insured/birthday ') as INSURED_BIRTHDAY ,
extractvalue(xmltype(a.message),'/request/proposal/insureds/insured/certiType ') as INSURED_IDTYPE ,
extractvalue(xmltype(a.message),'/request/proposal/insureds/insured/certiCode ') as INSURED_IDNUM ,
to_date(replace(extractvalue(xmltype(a.message),'/request/proposal/insureds/insured/certiExpireDate '),'-'),'yyyymmdd') as INSURED_CERTIEXPIREDATE ,
extractvalue(xmltype(a.message),'/request/proposal/insureds/insured/socialSecurityStatus ') as SOCIALSECURITYSTATUS ,
extractvalue(xmltype(a.message),'/request/proposal/insureds/insured/nationality ') as NATIONALITY ,
extractvalue(xmltype(a.message),'/request/proposal/insureds/insured/marriageId ') as MARRIAGEID ,
extractvalue(xmltype(a.message),'/request/proposal/insureds/insured/workCompany ') as WORKCOMPANY ,
extractvalue(xmltype(a.message),'/request/proposal/insureds/insured/job ') as JOB ,
extractvalue(xmltype(a.message),'/request/proposal/insureds/insured/jobCateId ') as JOBCATEID ,
extractvalue(xmltype(a.message),'/request/proposal/insureds/insured/mobile ') as INSURE_PHONE ,
extractvalue(xmltype(a.message),'/request/proposal/insureds/insured/homeTel ') as HOMETEL ,
extractvalue(xmltype(a.message),'/request/proposal/insureds/insured/email ') as INSURE_EMAIL ,
extractvalue(xmltype(a.message),'/request/proposal/insureds/insured/cityProvince ') as INSURE_PROVINCE ,
extractvalue(xmltype(a.message),'/request/proposal/insureds/insured/district ') as INSURE_CITY ,
extractvalue(xmltype(a.message),'/request/proposal/insureds/insured/ward ') as INSURE_DISTRICE ,
extractvalue(xmltype(a.message),'/request/proposal/insureds/insured/address1 ') as INSURE_ADDRESS ,
extractvalue(xmltype(message),'/request/proposal/insureds/insured/income ') as INCOME ,
extractvalue(xmltype(message),'/request/proposal/insureds/insured/postalCode ') as POSTALCODE ,
extractvalue(xmltype(message),'/request/proposal/insureds/insured/relationToLA1 ') as RELATIONTOLA1 ,
sysdate as CREATE_DATE ,
sysdate as UPDATE_DATE ,
VAR_PROPOSAL_ID
from
TEC_INSURE_RECORD a ,TEC_ORDER b
where
a.ORDER_ID = b.ORDER_ID
and a.stage='A' and b.ORDER_NO=VAR_ORDER_NO;
/* 1.4 插入投保单的受益人表
insert into TEC_ORDER_BENEFICIARY(
BENEFICIARY_ID ,
BENEFICIARY_NAME ,
GENDER ,
BIRTHDAY ,
CERTITYPE ,
CERTICODE ,
CERTIEXPIREDATE ,
NATIONALITY ,
INSURED_NAME ,
DESIGNATION ,
SHAREORDER ,
SHARERATE ,
CREATE_DATE ,
UPDATE_DATE ,
PROPOSAL_ID
)
select
sys_guid() as BENEFICIARY_ID ,
extractvalue(xmltype(message),'/request/proposal/beneficiarys/firstName ') as BENEFICIARY_NAME ,
extractvalue(xmltype(message),'/request/proposal/beneficiarys/gender ') as GENDER ,
to_date(replace(extractvalue(xmltype(message),'/request/proposal/beneficiarys/birthday '),'-'),'yyyymmdd') as BIRTHDAY ,
extractvalue(xmltype(message),'/request/proposal/beneficiarys/certiType ') as CERTITYPE ,
extractvalue(xmltype(message),'/request/proposal/beneficiarys/certiCode ') as CERTICODE ,
to_date(replace(extractvalue(xmltype(message),'/request/proposal/beneficiarys/certiExpireDate'),'-'),'yyyymmdd') as CERTIEXPIREDATE ,
extractvalue(xmltype(message),'/request/proposal/beneficiarys/nationality ') as NATIONALITY ,
extractvalue(xmltype(message),'/request/proposal/beneficiarys/insuredName ') as INSURED_NAME ,
extractvalue(xmltype(message),'/request/proposal/beneficiarys/designation ') as DESIGNATION ,
extractvalue(xmltype(message),'/request/proposal/beneficiarys/shareOrder ') as SHAREORDER ,
extractvalue(xmltype(message),'/request/proposal/beneficiarys/shareRate ') as SHARERATE ,
sysdate as CREATE_DATE ,
sysdate as UPDATE_DATE ,
VAR_PROPOSAL_ID
from
TEC_INSURE_RECORD a ,TEC_ORDER b
where
a.ORDER_ID = b.ORDER_ID
and a.stage='A' and b.ORDER_NO=VAR_ORDER_NO;
*/
/* 1.5 插入投保单的险别表 */
insert into TEC_ORDER_RISK(
RISK_ID ,
INTERNALID ,
COVERAGEPERIOD ,
COVERAGEYEAR ,
AMOUNT ,
UNIT ,
INITIALTYPE ,
CHARGEPERIOD ,
CHARGEYEAR ,
BENEFIT_LEVEL ,
DISCOUNT_RATE ,
PREMIUM ,
EXEMPTIONAMOUNT ,
COMPENSATERATE ,
CREATE_DATE ,
UPDATE_DATE ,
PROPOSAL_ID
)
select
sys_guid() as RISK_ID ,
extractvalue(xmltype(a.message),'/request/proposal/coverages/coverage/internalId ') as INTERNALID ,
extractvalue(xmltype(a.message),'/request/proposal/coverages/coverage/coveragePeriod ') as COVERAGEPERIOD ,
extractvalue(xmltype(a.message),'/request/proposal/coverages/coverage/coverageYear ') as COVERAGEYEAR ,
extractvalue(xmltype(a.message),'/request/proposal/coverages/coverage/amount ') as AMOUNT ,
extractvalue(xmltype(a.message),'/request/proposal/coverages/coverage/unit ') as UNIT ,
extractvalue(xmltype(a.message),'/request/proposal/coverages/coverage/initialType ') as INITIALTYPE ,
extractvalue(xmltype(a.message),'/request/proposal/coverages/coverage/chargePeriod ') as CHARGEPERIOD ,
extractvalue(xmltype(a.message),'/request/proposal/coverages/coverage/chargeYear ') as CHARGEYEAR ,
extractvalue(xmltype(a.message),'/request/proposal/coverages/coverage/benefit_level ') as BENEFIT_LEVEL ,
extractvalue(xmltype(a.message),'/request/proposal/coverages/coverage/discount_rate ') as DISCOUNT_RATE ,
extractvalue(xmltype(a.message),'/request/proposal/coverages/coverage/premium ') as PREMIUM ,
extractvalue(xmltype(a.message),'/request/proposal/coverages/coverage/exemptionAmount') as EXEMPTIONAMOUNT ,
extractvalue(xmltype(a.message),'/request/proposal/coverages/coverage/compensateRate ') as COMPENSATERATE ,
sysdate as CREATE_DATE ,
sysdate as UPDATE_DATE ,
VAR_PROPOSAL_ID
from
TEC_INSURE_RECORD a ,TEC_ORDER b
where
a.ORDER_ID = b.ORDER_ID
and a.stage='A' and b.ORDER_NO=VAR_ORDER_NO;
/* 2.1 update投保单的保额、保费、产品份数 */
update TEC_ORDER_PROPOSAL a
set(a.ORDER_PRICE,a.ORDER_PREMIUM,a.PLANUNIT) =
(select sum(b.AMOUNT),sum(b.PREMIUM),sum(b.UNIT) from TEC_ORDER_RISK b where a.proposal_id=b.proposal_id and a.proposal_id=VAR_PROPOSAL_ID)
where exists
(select 'X' from TEC_ORDER_RISK b where a.proposal_id=b.proposal_id and a.proposal_id=VAR_PROPOSAL_ID);
/* 2.2 update订单表的保额、保费、投保单件数、订单状态 */
update TEC_ORDER a
set(a.ORDER_PRICE,a.ORDER_PREMIUM,a.UNIT,a.ORDER_STATUS) =
(select sum(b.ORDER_PRICE),sum(b.ORDER_PREMIUM),sum(1),'1' from TEC_ORDER_PROPOSAL b
where a.order_id=b.order_id and a.order_no=VAR_ORDER_NO)
where exists
(select 'X' from TEC_ORDER_PROPOSAL b where a.order_id=b.order_id and a.order_no=VAR_ORDER_NO);
COMMIT;
flag:=1;
--异常处理
EXCEPTION
WHEN NO_DATA_FOUND THEN
err_msg:='数据库中符合记录的数据';
DBMS_OUTPUT.PUT_LINE('数据库中符合记录的数据');
flag:=0;
WHEN TOO_MANY_ROWS THEN
err_msg:='程序运行错误!存在多行记录,请使用游标';
DBMS_OUTPUT.PUT_LINE('程序运行错误!存在多行记录,请使用游标');
flag:=0;
WHEN others THEN
ROLLBACK;
err_msg:=SQLCODE||'---'||SQLERRM;
DBMS_OUTPUT.PUT_LINE('SP_ORDER_INSURE='||SQLCODE||'---'||SQLERRM);
flag:=0;
end;
======================================================
// JDBCTemplate是Spring对jdbc的封装 （包自己导）
@ManagedBean
public class InsureDao{
@Autowired
private JdbcTemplate jdbcTemplate;
@Autowired
private NamedParameterJdbcTemplate namedParameterJdbcTemplate;
/**
* 调用核保后的存储过程
* out flag：1 成功 0 失败
* err_msg 失败信息
* */
@SuppressWarnings({ "unchecked", "rawtypes" })
public Map<String,String> insertCallInsure(final String orderNo) throws Exception {
@SuppressWarnings("unused")
Map map = (Map) jdbcTemplate.execute(
new CallableStatementCreator() {
@Override
public CallableStatement createCallableStatement(Connection con) throws SQLException {
String callSql = "{call sp_order_proposal(?,?,?)}";// 调用的sql
CallableStatement cs = con.prepareCall(callSql);
cs.setString(1, orderNo);// 设置输入参数的值
cs.registerOutParameter(2, Types.INTEGER);
cs.registerOutParameter(3, Types.VARCHAR);// 注册输出参数的类型
return cs;
}
}, new CallableStatementCallback() {
public Object doInCallableStatement(CallableStatement cs) throws SQLException,DataAccessException {
cs.executeUpdate();
// ResultSet rs = (ResultSet) cs.getObject(2);// 获取游标一行的值
// while (rs.next()) {// 转换每行的返回值到Map中
// Map rowMap = new HashMap();
// rowMap.put("id", rs.getString("id"));
// rowMap.put("name", rs.getString("name"));
// }
// rs.close();
int flag = cs.getInt(2);
String err_msg = cs.getString(3);
System.out.println("核保调用存储过程 返回 code 1:Y ，0:N ==="+flag);
System.out.println("核保调用存储过程 返回 message ==="+err_msg);
Map<String,String> mp = new HashMap<String,String>();
mp.put("code", (String) (flag == 1 ? "Y" : 'N'));
mp.put("message", err_msg);
return mp;
}
});
return map;
}
}
=========================================================
- 数据库存储过程以及对存储过程调用
- C#中对数据库的基本操作（增删改以及调用存储过程）
- 数据库存储过程以及好处
- asp调用数据库存储过程
- asp调用数据库存储过程
- 数据库存储过程调用须知
- JAVA调用数据库存储过程
- Java调用数据库存储过程
- java调用数据库存储过程
- JAVA调用数据库存储过程
- JAVA调用数据库存储过程
- JAVA调用数据库存储过程
- JAVA调用数据库存储过程
- powerbuilder调用数据库存储过程
- JAVA调用数据库存储过程
- 数据库存储过程的调用
- JAVA调用数据库存储过程
- 数据库存储过程及其调用
- 单项加密算法
- 黑马程序员——java中的异常
- Apache CXF快速入门基础教程
- java基础复习之集合
- 04-EL表达式中的if...else....
- 数据库存储过程以及对存储过程调用
- nodejs + express4.x + mongodb 创建登陆
- 跳表
- 黑马程序员—OC学习笔记—继承与super关键字运用
- WebSocket+HTML5实现在线聊天室
- Appium键盘事件
- 自定义 Dialog
- 步步为营_Android开发课[3]_Activity学习
- C4.5 树剪枝
