错误如下
java.sql.SQLException: ORA-06550: 第 1 行, 第 7 列:
PLS-00201: 必须声明标识符 'DOINENTITYDATA'
ORA-06550: 第 1 行, 第 7 列:
PL/SQL: Statement ignored
-- 替换或创建存储过程
CREATE OR REPLACE PROCEDURE doInentityData as
--声明游标 存储ODS的有效数据
CURSOR datas IS
SELECT
--身份证号
person.CertiCode IDNumber,
--姓名
person.PersonName pname,
--理赔编码
person.ClaimQueryNo ClaimQueryNo,
--出险时间
regist.DamageDate ClaimTime,
--涉案车牌号
endcase.LicenseNo CarNumber,
--涉案公司
person.CompanyCode CompaniesInvolved,
--地区代码
person.AreaCode AreaCode ,
--结案时间
endcase.LastEndCaseTime ClosedTime,
--赔款总金额
endcase.SumPaid ClaimAmount,
--保单号
statu.PolicyNo PolicyNo ,
--伤亡人员类型
person.MedicalType MedicalType,
--险种 V3101开始的是商业险
CASE
WHEN instr(person.ClaimQueryNo,'V3101',1,1) <> '0' THEN '2'
ELSE '1'
END Coverage
--在四张临时表查询数据
FROM SIALPERSONTEMP person ,
SIALCLAIMSTATUSTEMP statu,
SIALREGISTEMP regist,
SIALENDCASETEMP endcase
--表关系
WHERE person.USERCODE=statu.USERCODE
AND statu.USERCODE=regist.USERCODE
AND regist.USERCODE=endcase.USERCODE ;
-- 查询出的一行数据
hang datas%rowtype;
-- 身份信息主键id
idcard SIDENTITY.IdentityID%type;
-- 理赔信息信息是否存在,从而判断添加还是更新
flag number(10,2) ;
BEGIN
--打开游标
OPEN datas;
--循环处理查询出的数据
LOOP FETCH datas INTO hang;
Exit WHEN datas%notfound;
--插入身份信息表,并返回主键
INSERT INTO SIDENTITY (IdentityID,IDNumber,IDType,DataSource,"NAME",CheckStatus,CancelStatus,NameStatus,ValidStatus)
VALUES (INENTITY_NUM.NEXTVAL ,hang.IDNumber, '01', '1', hang.pname, '1', '0', '1', '1') returning IdentityID INTO idcard;
-- 查询理赔信息存不存在,存才更新,不存在插入
SELECT count(1) INTO flag FROM SCLAIM WHERE IDENTITYID=idcard AND CLAIMSENCODING=hang.ClaimQueryNo;
--判断理赔信息是否存在?
IF flag>0 --更新理赔信息
THEN
UPDATE SCLAIM SET ClaimTime=hang.ClaimTime , CarNumber=hang.CarNumber, CompaniesInvolved=hang.CompaniesInvolved , AreaCode=hang.AreaCode, ClosedTime=hang.ClosedTime, ClaimAmount=hang.ClaimAmount, PolicyNo =hang.PolicyNo, Coverage=hang.Coverage, MedicalType=hang.MedicalType,ModifyTime=sysdate,ValidStatus='1' WHERE IDENTITYID=idcard AND CLAIMSENCODING=hang.ClaimQueryNo;
ELSE
INSERT INTO SCLAIM (IdentityID,ClaimsEncoding,ClaimTime,CarNumber,CompaniesInvolved,AreaCode,ClosedTime ,ClaimAmount,PolicyNo,Coverage,MedicalType,StorageTime,ValidStatus)
VALUES ( idcard, hang.ClaimQueryNo, hang.ClaimTime,hang.CarNumber, hang.CompaniesInvolved, hang.AreaCode, hang.ClosedTime, hang.ClaimAmount,hang.PolicyNo,hang.Coverage, hang.MedicalType, sysdate, '1' );
END IF;
END LOOP;
--将四张表的临时数据插入到正式表中
--INSERT INTO SIALREGIS (CLAIMQUERYNO,USERCODE,AREACODE,COMPANYCODE,DAMAGEDATE,VALIDSTATUS) SELECT CLAIMQUERYNO, USERCODE, AREACODE, COMPANYCODE, DAMAGEDATE, VALIDSTATUS FROM SIALREGISTEMP;
--INSERT INTO SIALCLAIMSTATUS (CLAIMQUERYNO,USERCODE,AREACODE,COMPANYCODE,POLICYNO,VALIDSTATUS) SELECT CLAIMQUERYNO, USERCODE, AREACODE, COMPANYCODE, POLICYNO, VALIDSTATUS FROM SIALCLAIMSTATUSTEMP ;
--INSERT INTO SIALENDCASE (CLAIMQUERYNO,USERCODE,AREACODE,COMPANYCODE,LICENSENO,LASTENDCASETIME,SUMPAID,VALIDSTATUS) SELECT CLAIMQUERYNO,USERCODE, AREACODE, COMPANYCODE, LICENSENO, LASTENDCASETIME, SUMPAID, VALIDSTATUS FROM SIALENDCASETEMP ;
---INSERT INTO SIALPERSON (CLAIMQUERYNO,USERCODE,AREACODE,SERIALNO,COMPANYCODE,PERSONNAME,CERTITYPE,CERTICODE,UNDERDEFLOSS,PERSONPROPERTY, MEDICALTYPE,VALIDSTATUS)
--SELECT CLAIMQUERYNO,USERCODE,AREACODE,SERIALNO,COMPANYCODE,PERSONNAME, CERTITYPE,CERTICODE, UNDERDEFLOSS, PERSONPROPERTY, MEDICALTYPE, VALIDSTATUS FROM SIALPERSONTEMP ;
--删除临时表的数据
--DELETE FROM SIALREGISTEMP;
--DELETE FROM SIALCLAIMSTATUSTEMP;
--DELETE FROM SIALENDCASETEMP;
--DELETE FROM SIALPERSONTEMP;
END;
<update id="doInentityData" statementType="CALLABLE">
{call Inentity.DOINENTITYDATA()}
</update>