Oracle批量sql

INSERT INTO SFC_WORKCENTER (ID,DATETIME_CREATED,USER_CREATED,DATETIME_MODIFIED,USER_MODIFIED,STATE,ENTERPRISE_ID,ORG_ID,
    WORKCENTER_CODE,WORKCENTER_NAME,WORKCENTER_DESC,WORKCENTER_TYPE,DEPARTMENT_OWNER,IS_BOTTLENECK,FIX_EQUIPMENTS,
    FIX_OPERATORS,PARENT_WORKCENTER_ID,WORKCENTER_SEQ,RESOURCE_ID,IN_WORKGROUP_ARRANGE,SUPPLY_TO,WORKCENTER_PATH,
    MAP_WORKCENTER_CODE,MAP_WORKCENTER_NAME,SEQ_FOR_DBSYNC
) 
WITH CTE AS (
	SELECT SYS_GUID() AS ID, SYSDATE AS DATETIME_CREATED/*创建时间*/, X.USER_CREATED/*创建人*/, SYSDATE/*修改时间*/, X.USER_MODIFIED /*修改人*/,
        'A' AS STATE, '*' AS ENTERPRISE_ID, 'C03' AS ORG_ID, REPLACE(X.EQUIPMENT_CODE, '-LJCS', '-P') AS WORKCENTER_CODE,
        CASE WHEN X.DEPARTMENT_OWNER = '4' THEN '四厂' || SUBSTR(X.EQUIPMENT_NAME, -3) || '号打印机'
            WHEN X.DEPARTMENT_OWNER = '5' THEN '五厂' || SUBSTR(X.EQUIPMENT_NAME, -3) || '号打印机'END AS WORKCENTER_NAME,
        NULL AS WORKCENTER_DESC, 'WORKCENTER' AS WORKCENTER_TYPE, X.DEPARTMENT_OWNER, 'N' AS IS_BOTTLENECK,  '1' AS FIX_EQUIPMENTS, 
        '0' AS FIX_OPERATORS, NULL AS PARENT_WORKCENTER_ID, 0 AS WORKCENTER_SEQ,  NULL AS RESOURCE_ID, 'N' AS IN_WORKGROUP_ARRANGE,
        NULL AS SUPPLY_TO, '/' || REPLACE(X.EQUIPMENT_CODE, '-LJCS', '-P') AS WORKCENTER_PATH, NULL AS MAP_WORKCENTER_CODE,
        NULL AS MAP_WORKCENTER_NAME, 1 AS SEQ_FOR_DBSYNC
    FROM FND_EQUIPMENT X WHERE X.EQUIPMENT_TYPE_CODE = 'LJ-AUTO'
        AND X.EQUIPMENT_CODE LIKE '%LJCS%'
    ORDER BY EQUIPMENT_CODE
)
SELECT * FROM CTE X
WHERE NOT EXISTS(
    SELECT 1 FROM SFC_WORKCENTER Y WHERE X.WORKCENTER_CODE = Y.WORKCENTER_CODE
);

INSERT INTO SFC_WORKCENTER (ID,DATETIME_CREATED,USER_CREATED,DATETIME_MODIFIED,USER_MODIFIED,STATE,ENTERPRISE_ID,ORG_ID,
    WORKCENTER_CODE,WORKCENTER_NAME,WORKCENTER_DESC,WORKCENTER_TYPE,DEPARTMENT_OWNER,IS_BOTTLENECK,FIX_EQUIPMENTS,
    FIX_OPERATORS,PARENT_WORKCENTER_ID,WORKCENTER_SEQ,RESOURCE_ID,IN_WORKGROUP_ARRANGE,SUPPLY_TO,WORKCENTER_PATH,
    MAP_WORKCENTER_CODE,MAP_WORKCENTER_NAME,SEQ_FOR_DBSYNC
)
WITH CTE AS (
    SELECT SYS_GUID() AS ID, SYSDATE AS DATETIME_CREATED/*创建时间*/, X.USER_CREATED/*创建人*/, SYSDATE/*修改时间*/, X.USER_MODIFIED /*修改人*/,
        'A' AS STATE, '*' AS ENTERPRISE_ID, 'C03' AS ORG_ID, REPLACE(X.EQUIPMENT_CODE, '-LJCS', '') AS WORKCENTER_CODE, X.EQUIPMENT_NAME,
        NULL AS WORKCENTER_DESC, 'LINE' AS WORKCENTER_TYPE, X.DEPARTMENT_OWNER, 'N' AS IS_BOTTLENECK,  '1' AS FIX_EQUIPMENTS, 
        '0' AS FIX_OPERATORS, NULL AS PARENT_WORKCENTER_ID, 0 AS WORKCENTER_SEQ,  X.ID AS RESOURCE_ID, 'N' AS IN_WORKGROUP_ARRANGE,
        NULL AS SUPPLY_TO, '/' || REPLACE(X.EQUIPMENT_CODE, '-LJCS', '') AS WORKCENTER_PATH, NULL AS MAP_WORKCENTER_CODE,
       NULL AS MAP_WORKCENTER_NAME, 1 AS SEQ_FOR_DBSYNC
    FROM FND_EQUIPMENT X WHERE X.EQUIPMENT_TYPE_CODE = 'LJ-AUTO'
        AND X.EQUIPMENT_CODE LIKE '%LJCS%'
	ORDER BY EQUIPMENT_CODE
)
SELECT * FROM CTE X
WHERE NOT EXISTS(
    SELECT 1 FROM SFC_WORKCENTER Y WHERE X.WORKCENTER_CODE = Y.WORKCENTER_CODE
);


INSERT INTO SFC_WORKSTATION (
    ID,DATETIME_CREATED,USER_CREATED, DATETIME_MODIFIED, USER_MODIFIED, STATE,ENTERPRISE_ID,ORG_ID,WORKSTATION_CODE,WORKSTATION_NAME,
    WORKSTATION_DESC,LINE_ID,STATION_CODE,WORKCENTER_ID,SEQ_FOR_DBSYNC
)
WITH CTE AS (
    SELECT SYS_GUID() AS ID, SYSDATE AS DATETIME_CREATED/*创建时间*/, X.USER_CREATED/*创建人*/, SYSDATE/*修改时间*/, X.USER_MODIFIED /*修改人*/,
            'A' AS STATE, '*' AS ENTERPRISE_ID, 'C03' AS ORG_ID, 
            CASE WHEN X.WORKCENTER_CODE LIKE '%L%' THEN REPLACE(WORKCENTER_CODE, 'L', 'LF-0') ELSE 
                REPLACE(WORKCENTER_CODE, '-', '-LJCS-')  END AS WORKSTATION_CODE,
            X.WORKCENTER_NAME AS WORKSTATION_NAME, 
            X.WORKCENTER_NAME AS WORKSTATION_DESC, X.ID AS LINE_ID, 'LJ'AS STATION_CODE, X.ID AS WORKCENTER_ID, 99
    FROM SFC_WORKCENTER X WHERE X.WORKCENTER_NAME  LIKE '%分选%' AND (X.WORKCENTER_CODE LIKE '4-%' OR X.WORKCENTER_CODE LIKE '5-%')
    ORDER BY X.WORKCENTER_CODE
)
SELECT * FROM CTE X
WHERE NOT EXISTS(
    SELECT 1 FROM SFC_WORKSTATION Y WHERE X.WORKSTATION_CODE = Y.WORKSTATION_CODE
);

新增设备及工作中心

insert into sfc_workcenter
SELECT sys_guid(),
       SYSDATE,
       '350915',
       SYSDATE,
       A.USER_MODIFIED,
       A.STATE,
       A.ENTERPRISE_ID,
       A.ORG_ID,
       '3'||SUBSTR(WORKCENTER_CODE,2),
       WORKCENTER_NAME,
       WORKCENTER_DESC,
       WORKCENTER_TYPE,
       '3',
       'N',
       FIX_EQUIPMENTS,
       FIX_OPERATORS,
       PARENT_WORKCENTER_ID,
       WORKCENTER_SEQ,
       B.ID,
       IN_WORKGROUP_ARRANGE,
       SUPPLY_TO,
       '/3'||SUBSTR(WORKCENTER_PATH,3),
       '3'||SUBSTR(MAP_WORKCENTER_CODE,2),
       MAP_WORKCENTER_NAME,
       '0'
  FROM sfc_workcenter A, FND_EQUIPMENT b
 where substr(a.workcenter_code, -3) = substr(b.equipment_code, -3)
   and a.WORKCENTER_TYPE = 'LINE'
   and b.EQUIPMENT_NAME LIKE '%双轨在线分选机%';

insert into sfc_workcenter
SELECT sys_guid(),
       SYSDATE,
       '350915',
       SYSDATE,
       A.USER_MODIFIED,
       A.STATE,
       A.ENTERPRISE_ID,
       A.ORG_ID,
       '3'||SUBSTR(WORKCENTER_CODE,-4),
       WORKCENTER_NAME,
       WORKCENTER_DESC,
       WORKCENTER_TYPE,
       '3',
       'N',
       FIX_EQUIPMENTS,
       FIX_OPERATORS,
       PARENT_WORKCENTER_ID,
       WORKCENTER_SEQ,
       B.ID,
       IN_WORKGROUP_ARRANGE,
       SUPPLY_TO,
       '/3'||SUBSTR(WORKCENTER_PATH,3),
       '3'||SUBSTR(MAP_WORKCENTER_CODE,2),
       MAP_WORKCENTER_NAME,
       '0'
  FROM sfc_workcenter A, FND_EQUIPMENT b
 where substr(a.WORKCENTER_NAME, -3) = substr(b.equipment_code, -3)
   and a.WORKCENTER_TYPE = 'LINE'
   AND A.DEPARTMENT_OWNER = '1'
   and B.equipment_NAME LIKE '离线分选机%'
   AND A.WORKCENTER_NAME LIKE '离线分选机%'
   AND substr(A.WORKCENTER_CODE,-2,1) < 3  ;

   
   INSERT INTO SFC_WORKSTATION
   SELECT sys_guid(),
       '350915',
       SYSDATE,
       SYSDATE,
       A.USER_MODIFIED,
       A.STATE,
       A.ENTERPRISE_ID,
       A.ORG_ID,'3'||SUBSTR(WORKSTATION_CODE,2),WORKSTATION_NAME,WORKSTATION_DESC,B.ID,'CS',B.ID,'0' FROM SFC_WORKSTATION A,SFC_WORKCENTER B WHERE A.WORKSTATION_NAME = B.WORKCENTER_NAME AND B.WORKCENTER_TYPE = 'LINE' AND B.DEPARTMENT_OWNER = '3';