纪念写的第一篇LOW到爆的存储过程！

CREATE OR REPLACE PROCEDURE MATERIALS_INTERFACE IS
  numb  INT;
  mid    VARCHAR2(50 CHAR);
  numb2 INT;
BEGIN
  LOOP
    --查询一条未读取未停用的中间表数据
    SELECT COUNT(*)
      INTO numb
      FROM MATERIALS
     WHERE IS_READ = '0'
       AND IS_STOP = '0'
       AND ROWNUM = 1;
    --如果不存在未读取未停用的数据则跳出循环
    IF numb = 0 THEN
      EXIT;
    ELSE
      --如果存在未读取未停用的中间表数据则查询出该数据的物料ID赋值给变量id
      SELECT MATERIAL_ID
        INTO mid
        FROM MATERIALS
       WHERE IS_READ = '0'
         AND IS_STOP = '0'
         AND ROWNUM = 1;
      --查询WMS物料表，是否存在这个ID的数据
      SELECT COUNT(*) INTO numb2 FROM WMS_ITEM WHERE ID = TO_NUMBER(mid);
      --如果不存在，则新建插入一条数据
      IF numb2 = 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 IS_READ = '0'
             AND IS_STOP = '0'
             AND ROWNUM = 1;
        --******************这里有个问题，根据A表创建B表的数据，B表中不属于A表的字段怎么赋值？*****************************
      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 IS_READ = '0'
                   AND IS_STOP = '0'
                   AND ROWNUM = 1)
                   WHERE ID = TO_NUMBER(mid);
        --判断结束
        END IF;
        --更新中间表中的是否已读字段，将未读改为已读
        UPDATE MATERIALS
           SET IS_READ = '1', WMS_RECEIVE_TIME = TO_CHAR(SYSDATE())
         WHERE IS_READ = '0'
           AND IS_STOP = '0'
           AND ROWNUM = 1;
        COMMIT;
    --判断结束
    END IF;
  --循环结束
  END LOOP;
END MATERIALS_INTERFACE;