使用存储过程（带游标）代替Java代码实现业务功能(MySQL)

场景

之前在一个项目中遇到过一个统计功能，需要根据各种条件进行统计。然后采用了Java实现，即是将需要的数据直接从数据库中查询出来，然后用Java进行各种加工处理。实现是实现了但是代码却很复杂、可读性极差。于是，想办法直接在数据库中进行加工处理（使用存储过程 ），然后将处理后的数据预先存入表中（新建）。业务上需要统计的时候，就直接调用存储过程，再从存有处理后的数据的表中查询数据。

Java实现及SQL实现对比

客户端（Java）实现

/** * @description 检查单统计service实现 * @author Michael Corleone * @date 2019年12月24日 * @version 1.0.0 */ @Service @Transactional public class ReadOrderStatisticsServiceImpl implements ReadOrderStatisticsService { @Autowired ReadOrderStatisticsMapper cssMapper; @Override public PageInfo<ChecklistStatisticsDto> getAllByYear(ChecklistStatisticsDto checklistStatisticsDto) { /* * 获取当前登录用户所属的用户类型，及该类型对应的查询条件 * 0超管 ：无需查询条件 --->查询出所有的数据 * 1平台运营人员：当前用户id --->查询出属于该运营人员(病例表中所属运营人员ID)的所有数据， 以及不属于任何人的数据 * 2平台其他用户：无需查询条件 --->查询出所有的数据 * 3所属医联体用户：当前用户所属医联体id --->查询出该用户所属医联体对应的数据 */ SysUser login = (SysUser) SecurityUtils.getSubject().getPrincipal(); if (login == null) return null; // TODO Integer sysType = login.getSysType(); Map<String,Integer> condition = new HashMap<String,Integer>(); switch (sysType) { case 0: condition.put("sysType", sysType); break; case 1: condition.put("sysType", sysType); condition.put("mId", login.getId()); break; case 2: condition.put("sysType", sysType); break; case 3: condition.put("sysType", sysType); condition.put("mcbId", login.getMcbId()); break; } initDataWithYear(condition); initDataWithYearMonth(condition); // 根据条件（包括分页信息）查询检查单统计数据 PageHelper.startPage(checklistStatisticsDto.getPage(), PageUtil.PAGE_SIZE); List<ChecklistStatisticsDto> nums = cssMapper.queryNumsWithYear(checklistStatisticsDto,condition); /** * 初始化当年对应的每月的数据量， 如果当前没有数据 则全部默认值为0 */ for (ChecklistStatisticsDto csd : nums) { ChecklistStatisticsDto[] arrays = new ChecklistStatisticsDto[12]; for (int i = 0; i < 12; i++) { arrays[i] = new ChecklistStatisticsDto(); } Calendar cal = Calendar.getInstance(); List<ChecklistStatisticsDto> cswymddl = csd.getEachMonthCount(); for (ChecklistStatisticsDto cswymdd : cswymddl) { int month = Integer.parseInt(cswymdd.getMonths()); arrays[month - 1] = cswymdd; } List<ChecklistStatisticsDto> eachMonthCount = Arrays.asList(arrays); csd.setEachMonthCount(eachMonthCount); } PageInfo<ChecklistStatisticsDto> records = new PageInfo<ChecklistStatisticsDto>(nums); return records; } @Override public PageInfo<ChecklistStatisticsDto> getAll(ChecklistStatisticsDto checklistStatisticsDto) { /* * 获取当前登录用户所属的用户类型，及该类型对应的查询条件 * 0超管 ：无需查询条件 --->查询出所有的数据 * 1平台运营人员：当前用户id --->查询出属于该运营人员(病例表中所属运营人员ID)的所有数据， 以及不属于任何人的数据 * 2平台其他用户：无需查询条件 --->查询出所有的数据 * 3所属医联体用户：当前用户所属医联体id --->查询出该用户所属医联体对应的数据 */ SysUser login = (SysUser) SecurityUtils.getSubject().getPrincipal(); if (login == null) return null; // TODO Integer sysType = login.getSysType(); Map<String,Integer> condition = new HashMap<String,Integer>(); switch (sysType) { case 0: condition.put("sysType", sysType); break; case 1: condition.put("sysType", sysType); condition.put("mId", login.getId()); break; case 2: condition.put("sysType", sysType); break; case 3: condition.put("sysType", sysType); condition.put("mcbId", login.getMcbId()); break; } initDataWithYearMonth(condition); intiDataWithYearMonthDay(condition); // 根据条件（包括分页信息）查询检查单统计数据 PageHelper.startPage(checklistStatisticsDto.getPage(), PageUtil.PAGE_SIZE); List<ChecklistStatisticsDto> nums = cssMapper.queryNums(checklistStatisticsDto,condition); /** * 初始化当月对应的每天的统计数量， 如果当前没有数据 则全部默认值为0 */ for (ChecklistStatisticsDto csd : nums) { ChecklistStatisticsDto[] arrays = new ChecklistStatisticsDto[31]; for (int i = 0; i < 31; i++) { arrays[i] = new ChecklistStatisticsDto(); } List<ChecklistStatisticsDto> cswymddl = csd.getEachDayCount(); for (ChecklistStatisticsDto cswymdd : cswymddl) { String createTime = cswymdd.getCreateTime(); int day = Integer.parseInt(createTime.split("-")[2].split(" ")[0]); arrays[day - 1] = cswymdd; } List<ChecklistStatisticsDto> eachDayCount = Arrays.asList(arrays); csd.setEachDayCount(eachDayCount); } PageInfo<ChecklistStatisticsDto> records = new PageInfo<ChecklistStatisticsDto>(nums); return records; } /** * * @Description 初始化数据：1、查询小肠胶囊视图（带年）、胃胶囊视图（带年）数据 2、 组装数据 3、删除旧数据 4、插入新组装的数据到表中 * @author John Abruzzi * @date 2020年1月7日 */ private void initDataWithYear(Map<String,Integer> condition) { // 检查单统计 返回集合 List<ChecklistStatisticsDto> checklistNums = new ArrayList<ChecklistStatisticsDto>(); // 查询小肠胶囊集合 List<ChecklistStatisticsDto> ic = cssMapper.getIntestineCountWithYear(condition); // 查询胃胶囊集合 List<ChecklistStatisticsDto> sc = cssMapper.getStomachCountWithYear(condition); // 既有小肠胶囊数据又有胃胶囊数据 boolean bothFlag = true; // 没有小肠胶囊数据 if (ic == null || ic.size() == 0) { for (ChecklistStatisticsDto css : sc) { css.setTotalNum(css.getStomachNum()); } bothFlag = false; checklistNums = sc; } // 没有胃胶囊数据 if (sc == null || sc.size() == 0) { for (ChecklistStatisticsDto css : ic) { css.setTotalNum(css.getIntestineNum()); } bothFlag = false; checklistNums = ic; } if (bothFlag) { // 临时存放（医联体名称+机构名称+年份+月份）相同的 小肠胶囊和胃胶囊数据，key--->小肠胶囊， value--->胃胶囊 Map<ChecklistStatisticsDto, ChecklistStatisticsDto> temp = new HashMap<ChecklistStatisticsDto, ChecklistStatisticsDto>(); // 相同的数据放入temp临时map中 for (ChecklistStatisticsDto intestine : ic) { for (ChecklistStatisticsDto stomach : sc) { if (sameNameAndDateWithYear(intestine, stomach)) { temp.put(intestine, stomach); } } } // 组合相同的数据 并放入最终的集合中 Iterator<Entry<ChecklistStatisticsDto, ChecklistStatisticsDto>> it = temp.entrySet().iterator(); while (it.hasNext()) { Entry<ChecklistStatisticsDto, ChecklistStatisticsDto> entry = it.next(); ChecklistStatisticsDto intestine = entry.getKey(); ChecklistStatisticsDto stomach = entry.getValue(); intestine.setStomachNum(stomach.getStomachNum()); intestine.setTotalNum(intestine.getIntestineNum() + stomach.getStomachNum()); checklistNums.add(intestine); } // 忽略相同的部分将 不同的部分设值 并放入最终的集合中， Set<ChecklistStatisticsDto> intestines = temp.keySet(); ok: for (ChecklistStatisticsDto intestine : ic) { // 小肠胶囊 for (ChecklistStatisticsDto csd : intestines) { if (sameNameAndDateWithYear(intestine, csd)) continue ok; } intestine.setTotalNum(intestine.getIntestineNum()); checklistNums.add(intestine); } ok: for (ChecklistStatisticsDto stomach : sc) { // 胃胶囊 for (ChecklistStatisticsDto csd : intestines) { if (sameNameAndDateWithYear(stomach, csd)) continue ok; } stomach.setTotalNum(stomach.getStomachNum()); checklistNums.add(stomach); } } cssMapper.delAllWithYear();// 删除所有旧数据 if (checklistNums.size() > 0) // 如果没有数据则不插入 cssMapper.batchInsertWithYear(checklistNums); // 插入实时数据 } /** * @Description 初始化数据：1、查询小肠胶囊视图（带年月）、胃胶囊视图（带年月）数据 2、 组装数据 3、删除旧数据 4、插入新组装的数据到表中 * @author John Abruzzi * @date 2020年1月6日 */ private void initDataWithYearMonth(Map<String,Integer> condition) { // 检查单统计 返回集合 List<ChecklistStatisticsDto> checklistNums = new ArrayList<ChecklistStatisticsDto>(); // 查询小肠胶囊集合 List<ChecklistStatisticsDto> ic = cssMapper.getIntestineCount(condition); // 查询胃胶囊集合 List<ChecklistStatisticsDto> sc = cssMapper.getStomachCount(condition); // 既有小肠胶囊数据又有胃胶囊数据 boolean bothFlag = true; // 没有小肠胶囊数据 if (ic == null || ic.size() == 0) { for (ChecklistStatisticsDto css : sc) { css.setTotalNum(css.getStomachNum()); } bothFlag = false; checklistNums = sc; } // 没有胃胶囊数据 if (sc == null || sc.size() == 0) { for (ChecklistStatisticsDto css : ic) { css.setTotalNum(css.getIntestineNum()); } bothFlag = false; checklistNums = ic; } if (bothFlag) { // 临时存放（医联体名称+机构名称+年份+月份）相同的 小肠胶囊和胃胶囊数据，key--->小肠胶囊， value--->胃胶囊 Map<ChecklistStatisticsDto, ChecklistStatisticsDto> temp = new HashMap<ChecklistStatisticsDto, ChecklistStatisticsDto>(); // 相同的数据放入temp临时map中 for (ChecklistStatisticsDto intestine : ic) { for (ChecklistStatisticsDto stomach : sc) { if (sameNameAndDate(intestine, stomach)) { temp.put(intestine, stomach); } } } // 组合相同的数据 并放入最终的集合中 Iterator<Entry<ChecklistStatisticsDto, ChecklistStatisticsDto>> it = temp.entrySet().iterator(); while (it.hasNext()) { Entry<ChecklistStatisticsDto, ChecklistStatisticsDto> entry = it.next(); ChecklistStatisticsDto intestine = entry.getKey(); ChecklistStatisticsDto stomach = entry.getValue(); intestine.setStomachNum(stomach.getStomachNum()); intestine.setTotalNum(intestine.getIntestineNum() + stomach.getStomachNum()); checklistNums.add(intestine); } // 忽略相同的部分将 不同的部分设值 并放入最终的集合中， Set<ChecklistStatisticsDto> intestines = temp.keySet(); ok: for (ChecklistStatisticsDto intestine : ic) { // 小肠胶囊 for (ChecklistStatisticsDto csd : intestines) { if (sameNameAndDate(intestine, csd)) continue ok; } intestine.setTotalNum(intestine.getIntestineNum()); checklistNums.add(intestine); } ok: for (ChecklistStatisticsDto stomach : sc) { // 胃胶囊 for (ChecklistStatisticsDto csd : intestines) { if (sameNameAndDate(stomach, csd)) continue ok; } stomach.setTotalNum(stomach.getStomachNum()); checklistNums.add(stomach); } } cssMapper.delAll();// 删除所有旧数据 if (checklistNums.size() > 0) // 如果没有数据则不插入 cssMapper.batchInsert(checklistNums); // 插入实时数据 } /** * * @Description 初始化数据：1、查询小肠胶囊视图（带年月日）、胃胶囊视图（带年月日）数据 2、 组装数据 3、删除旧数据 * 4、插入新组装的数据到表中 * @author John Abruzzi * @date 2020年1月7日 */ private void intiDataWithYearMonthDay(Map<String,Integer> condition) { // 检查单统计 返回集合 List<ChecklistStatisticsDto> checklistNums = new ArrayList<ChecklistStatisticsDto>(); // 查询小肠胶囊集合 List<ChecklistStatisticsDto> ic = cssMapper.getIntestineCountWithYearMonthDay(condition); // 查询胃胶囊集合 List<ChecklistStatisticsDto> sc = cssMapper.getStomachCountWithYearMonthDay(condition); // 既有小肠胶囊数据又有胃胶囊数据 boolean bothFlag = true; // 没有小肠胶囊数据 if (ic == null || ic.size() == 0) { for (ChecklistStatisticsDto css : sc) { css.setTotalNum(css.getStomachNum()); } bothFlag = false; checklistNums = sc; } // 没有胃胶囊数据 if (sc == null || sc.size() == 0) { for (ChecklistStatisticsDto css : ic) { css.setTotalNum(css.getIntestineNum()); } bothFlag = false; checklistNums = ic; } if (bothFlag) { // 临时存放（医联体名称+机构名称+年份+月份）相同的 小肠胶囊和胃胶囊数据，key--->小肠胶囊， value--->胃胶囊 Map<ChecklistStatisticsDto, ChecklistStatisticsDto> temp = new HashMap<ChecklistStatisticsDto, ChecklistStatisticsDto>(); // 相同的数据放入temp临时map中 for (ChecklistStatisticsDto intestine : ic) { for (ChecklistStatisticsDto stomach : sc) { if (sameNameAndDateWithYearMonthDay(intestine, stomach)) { temp.put(intestine, stomach); } } } // 组合相同的数据 并放入最终的集合中 Iterator<Entry<ChecklistStatisticsDto, ChecklistStatisticsDto>> it = temp .entrySet().iterator(); while (it.hasNext()) { Entry<ChecklistStatisticsDto, ChecklistStatisticsDto> entry = it.next(); ChecklistStatisticsDto intestine = entry.getKey(); ChecklistStatisticsDto stomach = entry.getValue(); intestine.setStomachNum(stomach.getStomachNum()); intestine.setTotalNum(intestine.getIntestineNum() + stomach.getStomachNum()); checklistNums.add(intestine); } // 忽略相同的部分将 不同的部分设值 并放入最终的集合中， Set<ChecklistStatisticsDto> intestines = temp.keySet(); ok: for (ChecklistStatisticsDto intestine : ic) { // 小肠胶囊 for (ChecklistStatisticsDto csd : intestines) { if (sameNameAndDateWithYearMonthDay(intestine, csd)) continue ok; } intestine.setTotalNum(intestine.getIntestineNum()); checklistNums.add(intestine); } ok: for (ChecklistStatisticsDto stomach : sc) { // 胃胶囊 for (ChecklistStatisticsDto csd : intestines) { if (sameNameAndDateWithYearMonthDay(stomach, csd)) continue ok; } stomach.setTotalNum(stomach.getStomachNum()); checklistNums.add(stomach); } } cssMapper.delAllWithYearMonthDay();// 删除所有旧数据 if (checklistNums.size() > 0) // 如果没有数据则不插入 cssMapper.batchInsertWithYearMonthDay(checklistNums); // 插入实时数据 } /** * * @Description 判断（医联体名称+机构名称+年份+月份） 是否相等 * @param one * @param another * @return * @author John Abruzzi * @date 2019年12月24日 */ private boolean sameNameAndDate(ChecklistStatisticsDto one, ChecklistStatisticsDto another) { /* * 判断时间是否为空 */ if(one.getYears() == null || one.getMonths() == null || another.getYears() == null || another.getMonths() == null) { return false; } try { if (one.getMcbName().trim().equals(another.getMcbName().trim()) // && one.getOrgName().trim().equals(another.getOrgName().trim()) && one.getExpectName().trim().equals(another.getExpectName().trim()) && one.getYears().trim().equals(another.getYears()) && one.getMonths().trim().equals(another.getMonths().trim())) { return true; } } catch (Exception e) { e.printStackTrace(); } return false; } /** * * @Description 判断（医联体名称+机构名称+创建时间--年月日） 是否相等 * @param one * @param another * @return * @author John Abruzzi * @date 2019年12月24日 */ private boolean sameNameAndDateWithYearMonthDay(ChecklistStatisticsDto one, ChecklistStatisticsDto another) { /* * 判断时间是否为空 */ if(one.getCreateTime() == null || another.getCreateTime() == null) { return false; } try { if (one.getMcbName().trim().equals(another.getMcbName().trim()) // && one.getOrgName().trim().equals(another.getOrgName().trim()) && one.getExpectName().trim().equals(another.getExpectName().trim()) && one.getCreateTime().equals(another.getCreateTime())) { return true; } } catch (Exception e) { e.printStackTrace(); } return false; } /** * * @Description 判断（医联体名称+机构名称+年份） 是否相等 * @param one * @param another * @return * @author John Abruzzi * @date 2019年12月24日 */ private boolean sameNameAndDateWithYear(ChecklistStatisticsDto one, ChecklistStatisticsDto another) { /* * 判断时间是否为空 */ if(one.getYears() == null || another.getYears() == null ) { return false; } try { if (one.getMcbName().trim().equals(another.getMcbName().trim()) // && one.getOrgName().trim().equals(another.getOrgName().trim()) && one.getExpectName().trim().equals(another.getExpectName().trim()) && one.getYears().trim().equals(another.getYears().trim())) { return true; } } catch (Exception e) { e.printStackTrace(); } return false; } @Override public String[] getAllYears() { /* * 获取当前登录用户所属的用户类型，及该类型对应的查询条件 * 0超管 ：无需查询条件 --->查询出所有的数据 * 1平台运营人员：当前用户id --->查询出属于该运营人员(病例表中所属运营人员ID)的所有数据， 以及不属于任何人的数据 * 2平台其他用户：无需查询条件 --->查询出所有的数据 * 3所属医联体用户：当前用户所属医联体id --->查询出该用户所属医联体对应的数据 */ SysUser login = (SysUser) SecurityUtils.getSubject().getPrincipal(); if (login == null) return null; // TODO Integer sysType = login.getSysType(); Map<String,Integer> condition = new HashMap<String,Integer>(); switch (sysType) { case 0: condition.put("sysType", sysType); break; case 1: condition.put("sysType", sysType); condition.put("mId", login.getId()); break; case 2: condition.put("sysType", sysType); break; case 3: condition.put("sysType", sysType); condition.put("mcbId", login.getMcbId()); break; } String[] allYears = cssMapper.getAllYears(condition); return allYears; } }

数据库（SQL）实现

主要使用了3个存储过程实现

第一个 CREATE DEFINER = `root` @`%` PROCEDURE `statics_proce_year_readorder` ( ) BEGIN DECLARE done BOOLEAN DEFAULT 0; DECLARE var_mcb_id INT; DECLARE var_m_id INT; DECLARE var_mcb_name VARCHAR ( 50 ); DECLARE var_org_name VARCHAR ( 50 ); DECLARE var_expect_id INT; DECLARE var_expect_name VARCHAR ( 30 ); DECLARE var_intestine_num INT; DECLARE var_stomach_num INT; DECLARE var_total_num INT; DECLARE var_years VARCHAR ( 4 ); DECLARE intestine_view_cursor CURSOR FOR SELECT mcb.id AS mcb_id, mcb.NAME AS mcb_name, org.`name` AS org_name, ept.username AS expect_name, ept.id AS expect_id, c.m_id AS m_id, DATE_FORMAT( c.create_time, '%Y' ) AS years, COUNT( c.inspection_subjects ) AS intestine_num FROM `case` c, organization org, medical_conjoined_body mcb, expect ept, checklist cl, endoscopicreport er WHERE c.inspection_subjects = '小肠胶囊' AND c.imcd_id = mcb.id AND org.id = c.inspection_organization_id AND c.expert_id = ept.id AND cl.case_id = c.id AND er.check_no = cl.check_no AND er.audit_status = 2 GROUP BY DATE_FORMAT( c.create_time, '%Y' ), ept.username, mcb.`name`; DECLARE stomach_view_cursor CURSOR FOR SELECT mcb.id AS mcb_id, mcb.NAME AS mcb_name, org.`name` AS org_name, ept.username AS expect_name, ept.id AS expect_id, c.m_id AS m_id, DATE_FORMAT( c.create_time, '%Y' ) AS years, COUNT( c.inspection_subjects ) AS stomach_num FROM `case` c, organization org, medical_conjoined_body mcb, expect ept, checklist cl, endoscopicreport er WHERE c.inspection_subjects = '胃胶囊' AND c.imcd_id = mcb.id AND org.id = c.inspection_organization_id AND c.expert_id = ept.id AND cl.case_id = c.id AND er.check_no = cl.check_no AND er.audit_status = 2 GROUP BY DATE_FORMAT( c.create_time, '%Y' ), ept.username, mcb.`name`; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1; OPEN intestine_view_cursor; REPEAT FETCH intestine_view_cursor INTO var_mcb_id, var_mcb_name, var_org_name, var_expect_name, var_expect_id, var_m_id, var_years, var_intestine_num; INSERT IGNORE INTO checklistCountWithYearOfReadorder ( mcb_id, mcb_name, org_name, expect_name, expect_id, m_id, years, intestine_num, stomach_num, total_num ) VALUES ( var_mcb_id, var_mcb_name, var_org_name, var_expect_name, var_expect_id, var_m_id, var_years, var_intestine_num, 0, var_intestine_num ); UNTIL done END REPEAT; CLOSE intestine_view_cursor; SET done = 0; OPEN stomach_view_cursor; REPEAT FETCH stomach_view_cursor INTO var_mcb_id, var_mcb_name, var_org_name, var_expect_name, var_expect_id, var_m_id, var_years, var_stomach_num; INSERT INTO checklistCountWithYearOfReadorder ( mcb_id, mcb_name, org_name, expect_name, expect_id, m_id, years, intestine_num, stomach_num, total_num ) VALUES ( var_mcb_id, var_mcb_name, var_org_name, var_expect_name, var_expect_id, var_m_id, var_years, 0, var_stomach_num, var_stomach_num ) ON DUPLICATE KEY UPDATE stomach_num = var_stomach_num, total_num = intestine_num + var_stomach_num; UNTIL done END REPEAT; CLOSE stomach_view_cursor; SET done = 0; END 第二个 CREATE DEFINER = `root` @`%` PROCEDURE `statics_proce_year_month_readorder` ( IN sysType INT, IN mId INT, IN mcbId INT ) BEGIN DECLARE done BOOLEAN DEFAULT 0; DECLARE var_mcb_id INT; DECLARE var_m_id INT; DECLARE var_mcb_name VARCHAR ( 50 ); DECLARE var_org_name VARCHAR ( 50 ); DECLARE var_expect_id INT; DECLARE var_expect_name VARCHAR ( 30 ); DECLARE var_intestine_num INT; DECLARE var_stomach_num INT; DECLARE var_total_num INT; DECLARE var_years VARCHAR ( 4 ); DECLARE var_months VARCHAR ( 2 ); DECLARE intestine_view_cursor CURSOR FOR SELECT mcb.id AS mcb_id, mcb.`name` AS mcb_name, org.`name` AS org_name, ept.username AS expect_name, ept.id AS expect_id, c.m_id AS m_id, DATE_FORMAT( c.create_time, '%Y' ) AS years, DATE_FORMAT( c.create_time, '%c' ) AS months, COUNT( c.inspection_subjects ) AS intestine_num FROM `case` c, organization org, medical_conjoined_body mcb, expect ept, checklist cl, endoscopicreport er WHERE c.inspection_subjects = '小肠胶囊' AND c.imcd_id = mcb.id AND org.id = c.inspection_organization_id AND c.expert_id = ept.id AND cl.case_id = c.id AND er.check_no = cl.check_no AND er.audit_status = 2 AND ( c.m_id = IF ( IF ( sysType NOT IN ( 0, 2, 3 ), TRUE, FALSE ), mId, c.m_id ) OR c.m_id = IF ( IF ( sysType NOT IN ( 0, 2, 3 ), TRUE, FALSE ), NULL, c.m_id ) ) AND mcb.id = IF ( IF ( sysType NOT IN ( 0, 1, 2 ), TRUE, FALSE ), mcbId, mcb.id ) GROUP BY DATE_FORMAT( c.create_time, '%Y' ), DATE_FORMAT( c.create_time, '%c' ), ept.username; DECLARE stomach_view_cursor CURSOR FOR SELECT mcb.id AS mcb_id, mcb.NAME AS mcb_name, org.`name` AS org_name, ept.username AS expect_name, ept.id AS expect_id, c.m_id AS m_id, DATE_FORMAT( c.create_time, '%Y' ) AS years, DATE_FORMAT( c.create_time, '%c' ) AS months, COUNT( c.inspection_subjects ) AS stomach_num FROM `case` c, organization org, medical_conjoined_body mcb, expect ept, checklist cl, endoscopicreport er WHERE c.inspection_subjects = '胃胶囊' AND c.imcd_id = mcb.id AND org.id = c.inspection_organization_id AND c.expert_id = ept.id AND cl.case_id = c.id AND er.check_no = cl.check_no AND er.audit_status = 2 GROUP BY DATE_FORMAT( c.create_time, '%Y' ), DATE_FORMAT( c.create_time, '%c' ), ept.username;-- DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done=1; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; DELETE FROM checklistCountOfReadorder; OPEN intestine_view_cursor; IF done = 1 THEN CLOSE intestine_view_cursor; ELSEIF done = 0 THEN REPEAT FETCH intestine_view_cursor INTO var_mcb_id, var_mcb_name, var_org_name, var_expect_name, var_expect_id, var_m_id, var_years, var_months, var_intestine_num; INSERT IGNORE INTO checklistCountOfReadorder ( mcb_id, mcb_name, org_name, expect_name, expect_id, m_id, years, months, intestine_num, stomach_num, total_num ) VALUES ( var_mcb_id, var_mcb_name, var_org_name, var_expect_name, var_expect_id, var_m_id, var_years, var_months, var_intestine_num, 0, var_intestine_num ); UNTIL done END REPEAT; END IF; CLOSE intestine_view_cursor; SET done = 0;-- OPEN stomach_view_cursor; -- -- REPEAT -- FETCH stomach_view_cursor INTO var_mcb_id,var_mcb_name,var_org_name,var_expect_name,var_expect_id,var_m_id,var_years,var_months,var_stomach_num; -- -- INSERT INTO checklistCountOfReadorder(mcb_id,mcb_name,org_name,expect_name,expect_id,m_id,years,months,intestine_num,stomach_num,total_num) VALUES(var_mcb_id,var_mcb_name,var_org_name,var_expect_name,var_expect_id,var_m_id,var_years,var_months,0,var_stomach_num,var_stomach_num) ON DUPLICATE KEY UPDATE stomach_num=var_stomach_num,total_num=intestine_num+var_stomach_num; -- -- UNTIL done END REPEAT; -- -- CLOSE stomach_view_cursor; SET done = 0; END 第三个 CREATE DEFINER = `root` @`%` PROCEDURE `statics_proce_year_month_day_readorder` ( ) BEGIN DECLARE done BOOLEAN DEFAULT 0; DECLARE var_mcb_id INT; DECLARE var_m_id INT; DECLARE var_mcb_name VARCHAR ( 50 ); DECLARE var_org_name VARCHAR ( 50 ); DECLARE var_expect_id INT; DECLARE var_expect_name VARCHAR ( 30 ); DECLARE var_intestine_num INT; DECLARE var_stomach_num INT; DECLARE var_total_num INT; DECLARE var_create_time DATETIME;-- 1、创建游标完成基本数据的统计（胃胶囊v1及小肠胶囊v2） DECLARE intestine_view_cursor CURSOR FOR SELECT mcb.id AS mcb_id, mcb.NAME AS mcb_name, org.`name` AS org_name, ept.username AS expect_name, ept.id AS expect_id, c.m_id AS m_id, DATE_FORMAT( c.create_time, '%Y-%c-%d' ) AS create_time, COUNT( c.inspection_subjects ) AS intestine_num FROM `case` c, organization org, medical_conjoined_body mcb, expect ept, checklist cl, endoscopicreport er WHERE c.inspection_subjects = '小肠胶囊' AND c.imcd_id = mcb.id AND org.id = c.inspection_organization_id AND c.expert_id = ept.id AND cl.case_id = c.id AND er.check_no = cl.check_no AND er.audit_status = 2 GROUP BY DATE_FORMAT( c.create_time, '%Y%c%d' ), ept.username, mcb.`name`; DECLARE stomach_view_cursor CURSOR FOR SELECT mcb.id AS mcb_id, mcb.NAME AS mcb_name, org.`name` AS org_name, ept.username AS expect_name, ept.id AS expect_id, c.m_id AS m_id, DATE_FORMAT( c.create_time, '%Y-%c-%d' ) AS create_time, COUNT( c.inspection_subjects ) AS stomach_num FROM `case` c, organization org, medical_conjoined_body mcb, expect ept, checklist cl, endoscopicreport er WHERE c.inspection_subjects = '胃胶囊' AND c.imcd_id = mcb.id AND org.id = c.inspection_organization_id AND c.expert_id = ept.id AND cl.case_id = c.id AND er.check_no = cl.check_no AND er.audit_status = 2 GROUP BY DATE_FORMAT( c.create_time, '%Y%c%d' ), ept.username, mcb.`name`; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;-- 2、任选一个游标（假定为v1:胃胶囊）中的数据插入到完整表t1信息（页面可直接查询并呈现的信息）：则小肠胶囊的数量设置为0，总数量total_num设置为胃胶囊的值 OPEN intestine_view_cursor; REPEAT FETCH intestine_view_cursor INTO var_mcb_id, var_mcb_name, var_org_name, var_expect_name, var_expect_id, var_m_id, var_create_time, var_intestine_num; INSERT IGNORE INTO checklistCountWithYearMonthDayOfReadorder ( mcb_id, mcb_name, org_name, expect_name, expect_id, m_id, create_time, intestine_num, stomach_num, total_num ) VALUES ( var_mcb_id, var_mcb_name, var_org_name, var_expect_name, var_expect_id, var_m_id, var_create_time, var_intestine_num, 0, var_intestine_num ); UNTIL done END REPEAT; CLOSE intestine_view_cursor; SET done = 0;-- 3、取另外一个游标（根据2中的假设则为v2：小肠胶囊）中的数据 插入到完整表t1中: -- 采用insert...on duplicate key update statement: -- a、如果联合唯一键（mcb_id+org_name+expect_id+year+months）重复则更新该条记录，将intestine_num字段的值更新到表t1中 -- 同时，将stomach_num中值和intestine_num的值相加然后传入到total_num字段中去。 -- b、如果联合唯一键不重复则插入数据，同时将stomach_num设置为0，total_num设置为intestine_num的值 OPEN stomach_view_cursor; REPEAT FETCH stomach_view_cursor INTO var_mcb_id, var_mcb_name, var_org_name, var_expect_name, var_expect_id, var_m_id, var_create_time, var_stomach_num; INSERT INTO checklistCountWithYearMonthDayOfReadorder ( mcb_id, mcb_name, org_name, expect_name, expect_id, m_id, create_time, intestine_num, stomach_num, total_num ) VALUES ( var_mcb_id, var_mcb_name, var_org_name, var_expect_name, var_expect_id, var_m_id, var_create_time, 0, var_stomach_num, var_stomach_num ) ON DUPLICATE KEY UPDATE stomach_num = var_stomach_num, total_num = intestine_num + var_stomach_num; UNTIL done END REPEAT; CLOSE stomach_view_cursor; SET done = 0; END

结语

虽然用SQL实现的代码量也比较大，但是SQL的逻辑更简单、可读性更高，并且使用Java实现也会写不少的SQL语句，所以，综合来讲使用SQL实现更加容易。