第二篇稍微不LOW一丢丢的存储过程_存储过程readflag-优快云博客

本文链接：https://blog.youkuaiyun.com/z912167269/article/details/90903311

本文介绍了一个用于同步物料信息的存储过程，该过程能够根据提供的物料ID检查WMS系统中是否存在对应物料，若不存在则插入新记录，若存在则进行更新。同时，该过程还会更新中间表中的物料读取状态。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一.存储过程

CREATE OR REPLACE PACKAGE BODY PA_MATERIALS IS
  /*
  * 功能:获取中间表数据,插入更新物料信息
  * 作者：KAIQI.ZHAO
  * 创建日期 2019-06-05
  * 最后修改时间 2019-06-05
  */
  procedure sp_material(v_material_id IN varchar2, V_MSG OUT VARCHAR2)
  --传入id,传出异常信息,执行状态,处理时间
   is
    v_con number;
    PRAGMA AUTONOMOUS_TRANSACTION; --开启自定义事务
    SIGING_EXCEPTION EXCEPTION; --自定义异常
  begin
    --根据id判断WMS是否有该物料,如果有更新,没有插入
    SELECT count(1) into v_con FROM WMS_ITEM where id = v_material_id;
    if v_con = 0 then
      INSERT INTO WMS_ITEM
        (ID,
         CREATED_TIME,
         UPDATE_TIME,
         BASE_UNIT,
         CODE,
         NAME,
         MATERIAL_TYPE_CODE,
         MATERIALSPECS,
         MATERIALTYPE)
        SELECT MATERIAL_ID,
               ERP_IMPORT_TIME,
               LAST_CHG_TIME,
               UNIT_NAME,
               MATERIAL_CODE,
               MATERIAL_NAME,
               MATERIAL_TYPE_CODE,
               MATERIAL_SPECS,
               MATERIAL_TYPE
          FROM MATERIALS material
         WHERE MATERIAL_ID = v_material_id;
    else
      --否则，更新WMS物料表中此ID的数据
      UPDATE WMS_ITEM
         SET (CREATED_TIME,
              UPDATE_TIME,
              BASE_UNIT,
              CODE,
              NAME,
              MATERIAL_TYPE_CODE,
              MATERIALSPECS,
              MATERIALTYPE) =
             (SELECT ERP_IMPORT_TIME,
                     LAST_CHG_TIME,
                     UNIT_NAME,
                     MATERIAL_CODE,
                     MATERIAL_NAME,
                     MATERIAL_TYPE_CODE,
                     MATERIAL_SPECS,
                     MATERIAL_TYPE
                FROM MATERIALS
               WHERE MATERIAL_ID = v_material_id)
       WHERE ID = TO_NUMBER(v_material_id);
    end if;
    --更新中间表中的是否已读字段，将未读改为已读
    UPDATE MATERIALS
       SET IS_READ = '1', WMS_RECEIVE_TIME = TO_CHAR(SYSDATE())
     WHERE MATERIAL_ID = v_material_id;
    COMMIT;
  
  EXCEPTION
    WHEN SIGING_EXCEPTION THEN
      ROLLBACK;
      --RAISE_APPLICATION_ERROR(-20001, V_MSG);
    --UPDATE WMS_OTM_CARRIER
    --SET READFLAG = 'Y', READDATE = SYSDATE, ERRORINFO = V_MSG
    --WHERE CARRIER_ID = v_code;
    --COMMIT;
    WHEN OTHERS THEN
      ROLLBACK;
      --RAISE_APPLICATION_ERROR(-20999, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    --UPDATE WMS_OTM_CARRIER
    --SET READFLAG = 'Y', READDATE = SYSDATE, ERRORINFO = V_MSG
    --WHERE CARRIER_ID = v_code;
    --COMMIT;
  end sp_material;
END PA_MATERIALS;

二.包

CREATE OR REPLACE PACKAGE PA_MATERIALS IS
  /*
  * 功能:物料表数据接口
  * 作者：KAIQI.ZHAO
  * 创建日期 2019-06-05
  * 最后修改时间 2019-06-05
  */
  --根据传入的id,来插入更新物料基础资料
  PROCEDURE sp_material(v_material_id IN varchar2, V_MSG OUT VARCHAR2);

END PA_MATERIALS;

三.包的实体

CREATE OR REPLACE PACKAGE BODY PA_MATERIALS IS
  /*
  * 功能:获取中间表数据,插入更新物料信息
  * 作者：KAIQI.ZHAO
  * 创建日期 2019-06-05
  * 最后修改时间 2019-06-05
  */
  procedure sp_material(v_material_id IN varchar2, V_MSG OUT VARCHAR2)
  --传入id,传出异常信息,执行状态,处理时间
   is
    v_con number;
    PRAGMA AUTONOMOUS_TRANSACTION; --开启自定义事务
    SIGING_EXCEPTION EXCEPTION; --自定义异常
  begin
    --根据id判断WMS是否有该物料,如果有更新,没有插入
    SELECT count(1) into v_con FROM WMS_ITEM where id = v_material_id;
    if v_con = 0 then
      INSERT INTO WMS_ITEM
        (ID,
         CREATED_TIME,
         UPDATE_TIME,
         BASE_UNIT,
         CODE,
         NAME,
         MATERIAL_TYPE_CODE,
         MATERIALSPECS,
         MATERIALTYPE)
        SELECT MATERIAL_ID,
               ERP_IMPORT_TIME,
               LAST_CHG_TIME,
               UNIT_NAME,
               MATERIAL_CODE,
               MATERIAL_NAME,
               MATERIAL_TYPE_CODE,
               MATERIAL_SPECS,
               MATERIAL_TYPE
          FROM MATERIALS material
         WHERE MATERIAL_ID = v_material_id;
    else
      --否则，更新WMS物料表中此ID的数据
      UPDATE WMS_ITEM
         SET (CREATED_TIME,
              UPDATE_TIME,
              BASE_UNIT,
              CODE,
              NAME,
              MATERIAL_TYPE_CODE,
              MATERIALSPECS,
              MATERIALTYPE) =
             (SELECT ERP_IMPORT_TIME,
                     LAST_CHG_TIME,
                     UNIT_NAME,
                     MATERIAL_CODE,
                     MATERIAL_NAME,
                     MATERIAL_TYPE_CODE,
                     MATERIAL_SPECS,
                     MATERIAL_TYPE
                FROM MATERIALS
               WHERE MATERIAL_ID = v_material_id)
       WHERE ID = TO_NUMBER(v_material_id);
    end if;
    --更新中间表中的是否已读字段，将未读改为已读
    UPDATE MATERIALS
       SET IS_READ = '1', WMS_RECEIVE_TIME = TO_CHAR(SYSDATE())
     WHERE MATERIAL_ID = v_material_id;
    COMMIT;
  
  EXCEPTION
    WHEN SIGING_EXCEPTION THEN
      ROLLBACK;
      --RAISE_APPLICATION_ERROR(-20001, V_MSG);
    --UPDATE WMS_OTM_CARRIER
    --SET READFLAG = 'Y', READDATE = SYSDATE, ERRORINFO = V_MSG
    --WHERE CARRIER_ID = v_code;
    --COMMIT;
    WHEN OTHERS THEN
      ROLLBACK;
      --RAISE_APPLICATION_ERROR(-20999, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
    --UPDATE WMS_OTM_CARRIER
    --SET READFLAG = 'Y', READDATE = SYSDATE, ERRORINFO = V_MSG
    --WHERE CARRIER_ID = v_code;
    --COMMIT;
  end sp_material;
END PA_MATERIALS;