MYSQL调用存储过程并返回结果

最新推荐文章于 2024-08-04 17:36:46 发布

跌入凡尘的张公子

最新推荐文章于 2024-08-04 17:36:46 发布

阅读量1.2k

点赞数 2

文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/zh555888/article/details/130726250

版权

存储过程如下所示：

CREATE DEFINER=`mysqladmin`@`%` PROCEDURE `P_ANALYZE_PO_RELATED`(
  IN p_issue_code VARCHAR (50),
  IN p_issue_line_code varchar (50),
  out p_batch_no varchar (500)
)
BEGIN
  #'t_sc_tm_p_analyze_po_related_temp' t_sc_tm_p_analyze_po_related_bills_temp
  #更新订单数量
  declare v_issue_id varchar (36) ;
  declare v_batch_no varchar (60) default uuid() ;
  declare v_group_no int default 0 ;
  DECLARE v_group INT DEFAULT 0 ;
  declare v_count int default 0 ;
  declare v_count_a int default 0 ;
  declare v_count_b int default 0 ;
  declare v_car_apply_code varchar (50) ;
  declare v_transport_code varchar (50) ;
  DECLARE v_tray VARCHAR (50) ;
  DECLARE done INT DEFAULT FALSE ;
  DECLARE cur_car_apply CURSOR FOR 
  SELECT DISTINCT 
    am.car_apply_code 
  FROM
    t_sc_tm_bu_car_apply_d ad 
    INNER JOIN t_sc_tm_bu_car_apply_m am 
      ON ad.car_apply_m_id = am.car_apply_m_id 
    INNER JOIN t_sc_tm_bu_tray_info t 
      ON ad.tray_label_code = t.tray_label_code 
    INNER JOIN t_sc_tm_bu_tray_pack_rel tr 
      ON t.tray_id = tr.tray_id 
    INNER JOIN t_sc_tm_bu_pack_label p 
      ON tr.pack_lab_code = p.pack_lab_code 
  WHERE p.issue_code = p_issue_code 
    AND p.issue_line_code = p_issue_line_code ;
  ##################自送托盘
  DECLARE cur_car_scheme_tray CURSOR FOR 
  SELECT DISTINCT 
    tp.`TRANSPORT_CODE` 
  FROM
    t_sc_tm_bu_car_scheme tp 
    INNER JOIN t_sc_tm_bu_assigncar_scheme s 
      ON tp.`CAR_SCHEME_ID` = s.`CAR_SCHEME_ID` 
    INNER JOIN t_sc_tm_bu_assigncar_detail_tyay sdt 
      ON sdt.`ASSIGNCAR_SCHEME_ID` = s.`ASSIGNCAR_SCHEME_ID` 
    INNER JOIN t_sc_tm_bu_tray_info t 
      ON t.`TRAY_LABEL_CODE` = sdt.`TRAY_LABEL_CODE` 
    INNER JOIN t_sc_tm_bu_tray_pack_rel tr 
      ON t.tray_id = tr.tray_id 
    INNER JOIN t_sc_tm_bu_pack_label p 
      ON p.`PACK_LAB_CODE` = tr.`PACK_LAB_CODE` 
  WHERE tp.`LOGISTICS_FLAG` IN ('0', '2') 
    AND tp.`TRAN_CONTENT_TYPE` = '1' 
    AND p.`ISSUE_CODE` = p_issue_code 
    AND p.`ISSUE_LINE_CODE` = p_issue_line_code ;
  ###################自送包装
  DECLARE cur_car_scheme_pack CURSOR FOR 
  SELECT DISTINCT 
    tp.`TRANSPORT_CODE` 
  FROM
    t_sc_tm_bu_car_scheme tp 
    INNER JOIN t_sc_tm_bu_assigncar_scheme s 
      ON tp.`CAR_SCHEME_ID` = s.`CAR_SCHEME_ID` 
    INNER JOIN t_sc_tm_bu_assigncar_detail_pack sdp 
      ON sdp.`ASSIGNCAR_SCHEME_ID` = s.`ASSIGNCAR_SCHEME_ID` 
    INNER JOIN t_sc_tm_bu_pack_label p 
      ON p.`PACK_LAB_CODE` = sdp.`PACK_LAB_CODE` 
  WHERE tp.`LOGISTICS_FLAG` IN ('0', '2') 
    AND tp.`TRAN_CONTENT_TYPE` = '2' 
    AND p.`ISSUE_CODE` = p_issue_code 
    AND p.`ISSUE_LINE_CODE` = p_issue_line_code ;
  ###################未定托盘
  declare cur_uncertain_tray cursor for 
  SELECT DISTINCT 
    t.`TRAY_LABEL_CODE` 
  FROM
    t_sc_tm_bu_tray_info t 
    INNER JOIN t_sc_tm_bu_tray_pack_rel tr 
      ON t.tray_id = tr.tray_id 
    INNER JOIN t_sc_tm_bu_pack_label p 
      ON tr.`PACK_LAB_CODE` = p.`PACK_LAB_CODE` 
  WHERE p.`ISSUE_CODE` = p_issue_code 
    AND p.`ISSUE_LINE_CODE` = p_issue_line_code 
    AND t.`PROCESS_FLAG` IN ('1', '2') 
    and p.rel_tray = '1' ;
  ###################更新可删标记
  DECLARE cur_group CURSOR FOR 
  SELECT DISTINCT 
    t.group_no 
  from
    t_sc_tm_p_analyze_po_related_temp t 
  where t.batch_no = v_batch_no ;
  ################退出标记
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE ;
  DELETE 
  FROM
    t_sc_tm_p_analyze_po_related_temp 
  WHERE create_date < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 3 DAY) ;
  DELETE 
  FROM
    t_sc_tm_p_analyze_po_related_bills_temp 
  WHERE create_date < DATE_SUB(CURRENT_TIMESTAMP, INTERVAL 3 DAY) ;
  set p_batch_no = v_batch_no ;
  #取货
  open cur_car_apply ;
  car_apply :
  loop
    fetch cur_car_apply into v_car_apply_code ;
    if (done) 
    then leave car_apply ;
    end if ;
    select 
      count(1) into v_count 
    from
      t_sc_tm_p_analyze_po_related_bills_temp t 
    where t.batch_no = v_batch_no 
      and t.bill_type = 'CAR_APPLY' 
      and t.bill_code = v_car_apply_code ;
    if (v_count = 0) 
    then set v_group_no = v_group_no + 1 ;
    INSERT INTO t_sc_tm_p_analyze_po_related_bills_temp (
      batch_no,
      group_no,
      bill_type,
      bill_code
    ) 
    SELECT DISTINCT 
      v_batch_no,
      v_group_no,
      'CAR_APPLY',
      am.car_apply_code 
    FROM
      t_sc_tm_bu_car_apply_d ad 
      INNER JOIN t_sc_tm_bu_car_apply_m am 
        ON ad.car_apply_m_id = am.car_apply_m_id 
      INNER JOIN t_sc_tm_bu_tray_info t 
        ON ad.tray_label_code = t.tray_label_code 
      INNER JOIN t_sc_tm_bu_assigncar_detail_tyay sdt 
        ON sdt.tray_label_code = t.tray_label_code 
      INNER JOIN t_sc_tm_bu_tray_pack_rel tr 
        ON t.tray_id = tr.tray_id 
      INNER JOIN t_sc_tm_bu_pack_label p 
        ON tr.pack_lab_code = p.pack_lab_code 
    WHERE am.car_apply_code = v_car_apply_code ;
    cur_apply_scheme :
    loop
      select 
        count(1) into v_count_a 
      from
        t_sc_tm_p_analyze_po_related_bills_temp t 
      where t.batch_no = v_batch_no 
        and t.group_no = v_group_no ;
      INSERT INTO t_sc_tm_p_analyze_po_related_bills_temp (
        batch_no,
        group_no,
        bill_type,
        bill_code
      ) 
      SELECT DISTINCT 
        v_batch_no,
        v_group_no,
        'CAR_SCHEME',
        tp.transport_code 
      FROM
        t_sc_tm_bu_car_scheme tp 
        INNER JOIN t_sc_tm_bu_assigncar_scheme s 
          ON tp.car_scheme_id = s.car_scheme_id 
        INNER JOIN t_sc_tm_bu_assigncar_detail_tyay sdt 
          ON s.assigncar_scheme_id = sdt.assigncar_scheme_id 
      WHERE sdt.tray_label_code IN 
        (SELECT 
          t.tray_label_code 
        FROM
          t_sc_tm_bu_car_apply_d ad 
          INNER JOIN t_sc_tm_bu_car_apply_m am 
            ON ad.car_apply_m_id = am.car_apply_m_id 
          INNER JOIN t_sc_tm_bu_tray_info t 
            ON ad.tray_label_code = t.tray_label_code 
          INNER JOIN t_sc_tm_bu_assigncar_detail_tyay sdt 
            ON sdt.tray_label_code = t.tray_label_code 
          INNER JOIN t_sc_tm_bu_tray_pack_rel tr 
            ON t.tray_id = tr.tray_id 
          INNER JOIN t_sc_tm_bu_pack_label p 
            ON tr.pack_lab_code = p.pack_lab_code 
        WHERE am.car_apply_code IN 
          (SELECT 
            bill_code 
          FROM
            t_sc_tm_p_analyze_po_related_bills_temp tmp 
          WHERE tmp.batch_no = v_batch_no 
            AND tmp.group_no = v_group_no 
            AND tmp.bill_type = 'CAR_APPLY')) 
        AND NOT EXISTS 
        (SELECT 
          bill_code 
        FROM
          t_sc_tm_p_analyze_po_related_bills_temp tmp 
        WHERE tmp.batch_no = v_batch_no 
          AND tmp.group_no = v_group_no 
          AND tmp.bill_type = 'CAR_SCHEME' 
          AND tmp.bill_code = tp.transport_code) ;
      INSERT INTO t_sc_tm_p_analyze_po_related_bills_temp (
        batch_no,
        group_no,
        bill_type,
        bill_code
      ) 
      SELECT DISTINCT 
        v_batch_no,
        v_group_no,
        'CAR_APPLY',
        am.car_apply_code 
      FROM
        t_sc_tm_bu_car_apply_d ad 
        INNER JOIN t_sc_tm_bu_car_apply_m am 
          ON ad.car_apply_m_id = am.car_apply_m_id 
        INNER JOIN t_sc_tm_bu_tray_info t 
          ON ad.tray_label_code = t.tray_label_code 
        INNER JOIN t_sc_tm_bu_assigncar_detail_tyay sdt 
          ON sdt.tray_label_code = t.tray_label_code 
        INNER JOIN t_sc_tm_bu_tray_pack_rel tr 
          ON t.tray_id = tr.tray_id 
        INNER JOIN t_sc_tm_bu_pack_label p 
          ON tr.pack_lab_code = p.pack_lab_code 
      WHERE t.tray_label_code IN 
        (SELECT DISTINCT 
          sdt.tray_label_code 
        FROM
          t_sc_tm_bu_car_scheme tp 
          INNER JOIN t_sc_tm_bu_assigncar_scheme s 
            ON tp.car_scheme_id = s.car_scheme_id 
          INNER JOIN t_sc_tm_bu_assigncar_detail_tyay sdt 
            ON s.assigncar_scheme_id = sdt.assigncar_scheme_id 
        WHERE tp.transport_code IN 
          (SELECT 
            bill_code 
          FROM
            t_sc_tm_p_analyze_po_related_bills_temp tmp 
          WHERE tmp.batch_no = v_batch_no 
            and tmp.group_no = v_group_no 
            AND tmp.bill_type = 'CAR_SCHEME')) 
        AND NOT EXISTS 
        (SELECT 
          bill_code 
        FROM
          t_sc_tm_p_analyze_po_related_bills_temp tmp 
        WHERE tmp.batch_no = v_batch_no 
          and tmp.group_no = v_group_no 
          AND tmp.bill_type = 'CAR_APPLY' 
          AND tmp.bill_code = am.car_apply_code) ;
      select 
        count(1) into v_count_b 
      from
        t_sc_tm_p_analyze_po_related_bills_temp t 
      where t.batch_no = v_batch_no 
        and t.group_no = v_group_no ;
      if (v_count_a = v_count_b) 
      then 
      INSERT INTO t_sc_tm_p_analyze_po_related_temp (
        batch_no,
        group_no,
        issue_code,
        issue_line_code,
        pack_label_code,
        tray_label_code,
        car_apply_code,
        transport_code,
        scheme_code,
        deliver_code,
        pack_qty,
        SHIPMENT_STATUS,
        RECEIVE_STATUS,
        GROUP_TYPE
      ) 
      SELECT 
        v_batch_no,
        v_group_no,
        p.issue_code,
        p.issue_line_code,
        p.pack_lab_code,
        t.tray_label_code,
        am.car_apply_code,
        tp.transport_code,
        s.scheme_code,
        d.deliver_code,
        p.pack_qty,
        p.SHIPMENT_STATUS,
        p.RECEIVE_STATUS,
        '取货' 
      FROM
        t_sc_tm_bu_car_apply_d ad 
        INNER JOIN t_sc_tm_bu_car_apply_m am 
          ON ad.car_apply_m_id = am.car_apply_m_id 
        LEFT JOIN t_sc_tm_bu_tray_info t 
          ON ad.tray_label_code = t.tray_label_code 
        LEFT JOIN t_sc_tm_bu_assigncar_detail_tyay sdt 
          ON sdt.tray_label_code = t.tray_label_code 
        LEFT JOIN t_sc_tm_bu_tray_pack_rel tr 
          ON t.tray_id = tr.tray_id 
        LEFT JOIN t_sc_tm_bu_pack_label p 
          ON tr.`PACK_LAB_CODE` = p.`PACK_LAB_CODE` 
        LEFT JOIN t_sc_tm_bu_assigncar_scheme s 
          ON sdt.assigncar_scheme_id = s.assigncar_scheme_id 
        LEFT JOIN t_sc_tm_bu_car_scheme tp 
          ON s.car_scheme_id = tp.car_scheme_id 
        LEFT JOIN t_sc_tm_bu_deliver_sendmain_d dd 
          ON dd.pack_lab_code = p.pack_lab_code 
        LEFT JOIN t_sc_tm_bu_deliver_sendmain d 
          ON d.cpr_sm_sendmain_m_id = dd.cpr_sm_sendmain_m_id 
      WHERE am.car_apply_code in 
        (SELECT 
          bill_code 
        FROM
          t_sc_tm_p_analyze_po_related_bills_temp tmp 
        WHERE tmp.batch_no = v_batch_no 
          and tmp.group_no = v_group_no 
          AND tmp.bill_type = 'CAR_APPLY') ;
      leave cur_apply_scheme ;
      end if ;
    end loop cur_apply_scheme ;
    end if ;
  end loop car_apply ;
  CLOSE cur_car_apply ;
  #自送托盘
  set done = false ;
  open cur_car_scheme_tray ;
  car_scheme_tray :
  LOOP
    FETCH cur_car_scheme_tray INTO v_transport_code ;
    IF (done) 
    THEN LEAVE car_scheme_tray ;
    END IF ;
    set v_group_no := v_group_no + 1 ;
    INSERT INTO t_sc_tm_p_analyze_po_related_temp (
      batch_no,
      group_no,
      issue_code,
      issue_line_code,
      pack_label_code,
      tray_label_code,
      car_apply_code,
      transport_code,
      scheme_code,
      deliver_code,
      pack_qty,
      SHIPMENT_STATUS,
      RECEIVE_STATUS,
      GROUP_TYPE
    ) 
    SELECT 
      v_batch_no,
      v_group_no,
      p.issue_code,
      p.issue_line_code,
      p.pack_lab_code,
      t.tray_label_code,
      '',
      tp.transport_code,
      s.scheme_code,
      d.deliver_code,
      p.pack_qty,
      p.SHIPMENT_STATUS,
      p.RECEIVE_STATUS,
      '自送托盘' 
    FROM
      t_sc_tm_bu_tray_info t 
      LEFT JOIN t_sc_tm_bu_assigncar_detail_tyay sdt 
        ON sdt.tray_label_code = t.tray_label_code 
      LEFT JOIN t_sc_tm_bu_tray_pack_rel tr 
        ON t.tray_id = tr.tray_id 
      LEFT JOIN t_sc_tm_bu_pack_label p 
        ON tr.`PACK_LAB_CODE` = p.`PACK_LAB_CODE` 
      LEFT JOIN t_sc_tm_bu_assigncar_scheme s 
        ON sdt.assigncar_scheme_id = s.assigncar_scheme_id 
      LEFT JOIN t_sc_tm_bu_car_scheme tp 
        ON s.car_scheme_id = tp.car_scheme_id 
      LEFT JOIN t_sc_tm_bu_deliver_sendmain_d dd 
        ON dd.pack_lab_code = p.pack_lab_code 
      LEFT JOIN t_sc_tm_bu_deliver_sendmain d 
        ON d.cpr_sm_sendmain_m_id = dd.cpr_sm_sendmain_m_id 
    WHERE tp.transport_code = v_transport_code ;
  end loop car_scheme_tray ;
  close cur_car_scheme_tray ;
  #自送包装
  SET done = FALSE ;
  open cur_car_scheme_pack ;
  car_scheme_pack :
  LOOP
    FETCH cur_car_scheme_pack INTO v_transport_code ;
    IF (done) 
    THEN LEAVE car_scheme_pack ;
    END IF ;
    set v_group_no := v_group_no + 1 ;
    INSERT INTO t_sc_tm_p_analyze_po_related_temp (
      batch_no,
      group_no,
      issue_code,
      issue_line_code,
      pack_label_code,
      tray_label_code,
      car_apply_code,
      transport_code,
      scheme_code,
      deliver_code,
      pack_qty,
      SHIPMENT_STATUS,
      RECEIVE_STATUS,
      GROUP_TYPE
    ) 
    SELECT 
      v_batch_no,
      v_group_no,
      p.issue_code,
      p.issue_line_code,
      p.pack_lab_code,
      '',
      '',
      tp.transport_code,
      s.scheme_code,
      d.deliver_code,
      p.pack_qty,
      p.SHIPMENT_STATUS,
      p.RECEIVE_STATUS,
      '自送包装' 
    FROM
      t_sc_tm_bu_assigncar_detail_pack sdp 
      LEFT JOIN t_sc_tm_bu_pack_label p 
        ON sdp.`PACK_LAB_CODE` = p.`PACK_LAB_CODE` 
      LEFT JOIN t_sc_tm_bu_assigncar_scheme s 
        ON sdp.assigncar_scheme_id = s.assigncar_scheme_id 
      LEFT JOIN t_sc_tm_bu_car_scheme tp 
        ON s.car_scheme_id = tp.car_scheme_id 
      LEFT JOIN t_sc_tm_bu_deliver_sendmain_d dd 
        ON dd.pack_lab_code = p.pack_lab_code 
      LEFT JOIN t_sc_tm_bu_deliver_sendmain d 
        ON d.cpr_sm_sendmain_m_id = dd.cpr_sm_sendmain_m_id 
    WHERE tp.transport_code = v_transport_code ;
  END LOOP car_scheme_pack ;
  close cur_car_scheme_pack ;
  #未定托盘
  SET done = FALSE ;
  OPEN cur_uncertain_tray ;
  uncertain_tray :
  LOOP
    FETCH cur_uncertain_tray INTO v_tray ;
    IF (done) 
    THEN LEAVE uncertain_tray ;
    END IF ;
    SET v_group_no := v_group_no + 1 ;
    INSERT INTO t_sc_tm_p_analyze_po_related_temp (
      batch_no,
      group_no,
      issue_code,
      issue_line_code,
      pack_label_code,
      tray_label_code,
      car_apply_code,
      transport_code,
      scheme_code,
      deliver_code,
      pack_qty,
      SHIPMENT_STATUS,
      RECEIVE_STATUS,
      GROUP_TYPE
    ) 
    SELECT 
      v_batch_no,
      v_group_no,
      p.issue_code,
      p.issue_line_code,
      p.pack_lab_code,
      t.tray_label_code,
      '',
      tp.transport_code,
      s.scheme_code,
      d.deliver_code,
      p.pack_qty,
      p.SHIPMENT_STATUS,
      p.RECEIVE_STATUS,
      '未定托盘' 
    FROM
      t_sc_tm_bu_tray_info t 
      LEFT JOIN t_sc_tm_bu_assigncar_detail_tyay sdt 
        ON sdt.tray_label_code = t.tray_label_code 
      LEFT JOIN t_sc_tm_bu_tray_pack_rel tr 
        ON t.tray_id = tr.tray_id 
      LEFT JOIN t_sc_tm_bu_pack_label p 
        ON tr.`PACK_LAB_CODE` = p.`PACK_LAB_CODE` 
      LEFT JOIN t_sc_tm_bu_assigncar_scheme s 
        ON sdt.assigncar_scheme_id = s.assigncar_scheme_id 
      LEFT JOIN t_sc_tm_bu_car_scheme tp 
        ON s.car_scheme_id = tp.car_scheme_id 
      LEFT JOIN t_sc_tm_bu_deliver_sendmain_d dd 
        ON dd.pack_lab_code = p.pack_lab_code 
      LEFT JOIN t_sc_tm_bu_deliver_sendmain d 
        ON d.cpr_sm_sendmain_m_id = dd.cpr_sm_sendmain_m_id 
    WHERE t.tray_label_code = v_tray ;
  END LOOP uncertain_tray ;
  CLOSE cur_uncertain_tray ;
  #未定包装  
  SET done = FALSE ;
  SET v_group_no := v_group_no + 1 ;
  INSERT INTO t_sc_tm_p_analyze_po_related_temp (
    batch_no,
    group_no,
    issue_code,
    issue_line_code,
    pack_label_code,
    tray_label_code,
    car_apply_code,
    transport_code,
    scheme_code,
    deliver_code,
    pack_qty,
    SHIPMENT_STATUS,
    RECEIVE_STATUS,
    GROUP_TYPE
  ) 
  SELECT 
    v_batch_no,
    v_group_no,
    p.issue_code,
    p.issue_line_code,
    p.pack_lab_code,
    '',
    '',
    '',
    '',
    '',
    p.pack_qty,
    p.SHIPMENT_STATUS,
    p.RECEIVE_STATUS,
    '未定包装' 
  FROM
    t_sc_tm_bu_pack_label p 
  WHERE p.`REL_TRAY` = '0' 
    AND p.PROCESS_FLAG IN ('1', '2') 
    AND p.`ISSUE_CODE` = p_issue_code 
    AND p.`ISSUE_LINE_CODE` = p_issue_line_code ;
  ###########更新可删除标记    
  SET done = FALSE ;
  OPEN cur_group ;
  all_group :
  LOOP
    FETCH cur_group INTO v_group ;
    IF (done) 
    THEN LEAVE all_group ;
    END IF ;
    select 
      count(1) into v_count 
    from
      t_sc_tm_p_analyze_po_related_temp t 
    where t.batch_no = v_batch_no 
      and t.group_no = v_group 
      and (
        ifnull(t.shipment_status, '0') = 1 
        or IFNULL(t.receive_status, '0') = 1
      ) ;
    if (v_count = 0) 
    then 
    update 
      t_sc_tm_p_analyze_po_related_temp t 
    set
      t.is_deletable = '1' 
    WHERE t.batch_no = v_batch_no 
      AND t.group_no = v_group ;
    end if ;
  END LOOP all_group ;
  CLOSE cur_group ;
END

调用过程如下：
service层

# issueCode，issueLineCode为调用参数
HashMap<String, String> map1 = new HashMap<>();
map1.put("issueCode",map.get("CGDDH").toString());
map1.put("issueLineCode",map.get("CGDDHH").toString());
closeOrderRelDao.getAnalyzePo(map1);

DAO层

  void getAnalyzePo(Map map);

XML层

-- po 为返回值，这个值会自动保存到入参的map里面
<select id="getAnalyzePo" resultType="Map" statementType="CALLABLE">
        CALL p_analyze_po_related( #{issueCode,mode=IN},
            #{issueLineCode,mode=IN},
            #{po,mode=OUT,jdbcType=VARCHAR})
</select>