sinat_17531791
qq52214373
2015-09-21 09:27

mybatis调用oracle存储过程报错

  • oracle
  • mybatis
  • 存储

错误如下

 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>
  • 点赞
  • 回答
  • 收藏
  • 复制链接分享

3条回答