调用存储过程报错 大佬帮忙看下 啥情况

浮云若水 2017-11-27 03:23:02

{"message":"\n### Error updating database. Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '2-SOETERBROEK/ALEXANDER JO-NY83HJ934' for key 'supplierId'\n### The error may involve com.ziztour.common.db.mapper.p.SupplierInfoMapper.procSupplierEmployee-Inline\n### The error occurred while setting parameters\n### SQL: {call pro_supplier_employee( ?, ?, ? )}\n### Cause: com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '2-SOETERBROEK/ALEXANDER JO-NY83HJ934' for key 'supplierId'\n; SQL []; Duplicate entry '2-SOETERBROEK/ALEXANDER JO-NY83HJ934' for key 'supplierId'; nested exception is com.mysql.jdbc.exceptions.jdbc4.MySQLIntegrityConstraintViolationException: Duplicate entry '2-SOETERBROEK/ALEXANDER JO-NY83HJ934' for key 'supplierId'","statusCode":700,"success":false}


存储过程

DROP PROCEDURE IF EXISTS pro_supplier_employee;
-- 供应商关联企业 员工以及常用旅客白名单 处理
CREATE PROCEDURE pro_supplier_employee(
IN companyId VARCHAR(20000), -- 企业ID
IN supplierId BIGINT,-- 供应商ID
OUT result INT -- 返回结果
)
BEGIN
DECLARE spid bigint DEFAULT supplierId;
DECLARE cid bigint;
DECLARE eid bigint;
DECLARE ph varchar(50);
DECLARE idt int;
DECLARE bday date;
DECLARE sx int;
DECLARE iname varchar(50);
DECLARE icard varchar(50);
DECLARE Cur_1 CURSOR FOR SELECT a.cid,a.eid,a.ph,a.idt,a.bday,a.sx,a.iname,a.icard from view_supplier_personlInfo a group by a.iname,a.icard;
DECLARE Cur_2 CURSOR FOR SELECT a.cid,a.eid,a.ph,a.idt,a.bday,a.sx,a.iname,a.icard from view_supplier_employ a group by a.iname,a.icard;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET result = 1;#错误定义,标记循环结束
SET @companyId=companyId;
SET @supplierId=supplierId;

DROP VIEW IF EXISTS view_supplier_personlInfo;
DROP VIEW IF EXISTS view_supplier_employ;

-- 查找员工关联的常用旅客的 idCard (非同事关系)
SET @sel3=" create view view_supplier_personlInfo as SELECT companyId as cid,empId as eid,phone as ph,idType as idt,birthday as bday,sex as sx,TRIM(case when LENGTH(name)>0 then name when (name is null or LENGTH(name)<1 ) then CONCAT(case when lastName is null then '' else lastName end ,' ',case when firstName is null then '' else firstName end ) end) as iname ,idcard as icard from b_company_employee_personlInfo where empId in (";
SET @sel3=CONCAT(@sel3,"select id from b_company_employee where FIND_IN_SET(companyId,'",@companyId,"') and state =1 ) and isColleague=0 and state=1 ");
SET @sel3=CONCAT(@sel3," and (( length(name)>0 and idcard is not null and idtype is not null) or (length(lastName)>0 and idcard is not null and idtype is not null) or ( length(firstName)>0 and idcard is not null and idtype is not null))");
-- 查询关联企业的员工ID (插入白名单使用)
SET @esql2=" create view view_supplier_employ as select id as eid,companyId as cid,phone as ph,idType as idt,birthday as bday,sex as sx,TRIM(case when LENGTH(name)>0 then name when (name is null or LENGTH(name)<1 ) then CONCAT(case when lastName is null then '' else lastName end ,' ',case when firstName is null then '' else firstName end ) end) as iname ,idcard as icard from b_company_employee where FIND_IN_SET(companyId,'";
set @esql2=CONCAT(@esql2,@companyId,"') and state =1 and (( length(name)>0 and idcard is not null and idtype is not null) or (length(lastName)>0 and idcard is not null and idtype is not null) or ( length(firstName)>0 and idcard is not null and idtype is not null))");

PREPARE stmt_sel3 FROM @sel3;
EXECUTE stmt_sel3;
DEALLOCATE PREPARE stmt_sel3;
PREPARE stmt_esql2 FROM @esql2;
EXECUTE stmt_esql2;
DEALLOCATE PREPARE stmt_esql2;

-- 错误定义,标记循环结束

SET result = 0;#只有定义为0,新的循环才能继续。
/* 打开光标 */
OPEN Cur_1;
/* 循环执行 */
REPEAT
FETCH Cur_1 INTO cid,eid,ph,idt,bday,sx,iname,icard;

set @ct=0;
select count(1) into @ct from supplier_company_employ_relation sc where sc.name=iname and sc.idCard=icard and sc.supplierId=spid;
IF NOT result THEN
IF @ct<1 THEN
if cid is null then
insert INTO supplier_company_employ_relation (supplierId,companyId,employeeId,isRecord,phone,name,idType,idCard,birthday,sex,createTime,state) values (spid,0,eid,0,ph,iname,idt,icard,bday,sx,now(),1);
ELSE
insert INTO supplier_company_employ_relation (supplierId,companyId,employeeId,isRecord,phone,name,idType,idCard,birthday,sex,createTime,state) values (spid,cid,eid,0,ph,iname,idt,icard,bday,sx,now(),1);
end if;

end if;
END IF;
UNTIL result END REPEAT; #result=1时退出被循
/*关闭光标*/
CLOSE Cur_1;
SET result = 0;#只有定义为0,新的循环才能继续。
OPEN Cur_2;
REPEAT
FETCH Cur_2 INTO cid,eid,ph,idt,bday,sx,iname,icard;
IF NOT result THEN
set @ct1=0;
select count(1) into @ct1 from supplier_company_employ_relation sc where sc.name=iname and sc.idCard=icard and sc.supplierId=spid;
if @ct1 <1 then
insert INTO supplier_company_employ_relation(supplierId,companyId,employeeId,isRecord,phone,name,idType,idCard,birthday,sex,createTime,state) values (spid,cid,eid,0,ph,iname,idt,icard,bday,sx,now(),1);
end if;
END IF;
UNTIL result END REPEAT;
CLOSE Cur_2;
DROP VIEW IF EXISTS view_supplier_personlInfo;
DROP VIEW IF EXISTS view_supplier_employ;
SET result = 1;
-- select @companyId;
END;

...全文
179 4 打赏 收藏 举报
写回复
4 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
shinesky 2017-11-28
来接分的
  • 打赏
  • 举报
回复
吉普赛的歌 2017-11-27
好吧, 恭喜
  • 打赏
  • 举报
回复
浮云若水 2017-11-27
撒分了
  • 打赏
  • 举报
回复
浮云若水 2017-11-27
好了 问题找到了 因为生产环境数据 比较多 所以一开始报这错比较懵逼, 刚才抽丝剥茧,最终看到有个旅客的名字超过50个字符了 ,原因也是我存储过程中异常处理不到位,导致后台抛出的异常,看的云里雾里。
  • 打赏
  • 举报
回复
相关推荐
发帖
MySQL

5.6w+

社区成员

MySQL相关内容讨论专区
社区管理员
  • MySQL
加入社区
帖子事件
创建了帖子
2017-11-27 03:23
社区公告
暂无公告