存储过程：
将DESCRIBE替换为CREATE OR REPLACE PROCEDURE，后跟过程的名称。删除冒号：在变量MAN_ID、REL和EXT_B之前，因为它们在此上下文中不是绑定变量。在代码的末尾添加/以执行过程创建。下面是修改后的代码：

CREATE OR REPLACE PROCEDURE InsertOrUpdateSWProfile AS
  TYPE FUNCTIONALITIES IS VARRAY(12) OF VARCHAR2(10);
  FUNCS  FUNCTIONALITIES := FUNCTIONALITIES('Func1', 'Func2', 'Func3', 'Func4', 'Func5', 'Func6', 'Func7', 'Func8', 'Func9', 'Func10', 'Func11', 'Func12');
  MAN_ID  NUMBER(12);
  SW_PRO_ID  NUMBER(12);
  REL  VARCHAR2(10) := 'Release';
  EXT_B  VARCHAR2(10) := 'ExtensionB';
BEGIN
  SELECT ID INTO MAN_ID FROM MANUFACTURERS WHERE MANUFACTURER = 'EE';
  SELECT ID INTO SW_PRO_ID FROM SW_PROFILE WHERE MANUFACTURER_ID = MAN_ID AND RELEASE = REL AND EXTENSIONB = EXT_B;

  FOR i IN 1..FUNCS.COUNT LOOP
    EXECUTE IMMEDIATE
      'INSERT INTO SW_PROFILE_FEATURE (ID, SW_LVC_PROFILE_ID, FEATURE, CREATION_DATE)
          SELECT SQ_SW_PROFILE_FEATURE_ID.NEXTVAL, ''' || SW_PRO_ID || ''', ''' || FUNCS(i) || ''', SYSDATE FROM DUAL
        WHERE NOT EXISTS (SELECT NULL FROM RMM.SW_PROFILE_FEATURE WHERE SW_PROFILE_ID = ''' || SW_PRO_ID || ''' AND FEATURE = ''' || FUNCS(i) || ''')';

    EXECUTE IMMEDIATE
      'UPDATE SW_PROFILE_FEATURE SET SW_LVC_PROFILE_ID = ''' || SW_PRO_ID || ''', FEATURE = ''' || FUNCS(i) || ''', CREATION_DATE = SYSDATE
        WHERE ROWID IN (SELECT ROWID FROM SW_PROFILE_FEATURE WHERE SW_PROFILE_ID = ''' || SW_PRO_ID || ''' AND FEATURE = ''' || FUNCS(i) || ''')';
  END LOOP;

  COMMIT COMMENT 'Inserted/Updated SW_PROFILE_FEATURE records';
EXCEPTION
WHEN OTHERS THEN
  IF SQLCODE != -1 THEN RAISE; END IF;
END InsertOrUpdateSWProfile;
/

:MAN_ID是一个外部定义的绑定变量。MAN_ID是本地定义的PL/SQL变量。
如果你想使用局部变量，那么不要用:作为前缀。
但是，对于EXECUTE IMMEDIATE语句，您应该尽可能使用绑定变量，然后为它们提供USING子句，以将值从PL/SQL范围传递到EXECUTE IMMEDIATE语句的SQL范围（并防止SQL注入漏洞）：

DECLARE
  TYPE FUNCTIONALITIES IS VARRAY(12) of VARCHAR2(10);
  FUNCS  FUNCTIONALITIES := FUNCTIONALITIES('Func1', 'Func2', 'Func3', 'Func4', 'Func5', 'Func6', 'Func7', 'Func8', 'Func9', 'Func10', 'Func11', 'Func12');
  MAN_ID  NUMBER(12);
  SW_PRO_ID  NUMBER(12);
  REL  VARCHAR2(10) := 'Release';
  EXT_B  VARCHAR2(10) := 'ExtensionB';
BEGIN
  select ID
  into   MAN_ID
  from   MANUFACTURERS
  where  MANUFACTURER = 'EE';

  select ID
  into   SW_PRO_ID
  from   SW_PROFILE
  where  MANUFACTURER_ID = MAN_ID
  and    RELEASE         = REL
  and    EXTENSIONB      = EXT_B;

  FOR i IN 1..FUNC.COUNT LOOP
    EXECUTE IMMEDIATE
      'INSERT INTO SW_PROFILE_FEATURE (ID, SW_LVC_PROFILE_ID, FEATURE, CREATION_DATE)
          SELECT SQ_SW_PROFILE_FEATURE_ID.NEXTVAL, :SW_PRO_ID, :func, SYSDATE FROM DUAL
        WHERE NOT EXISTS (SELECT NULL FROM RMM.SW_PROFILE_FEATURE WHERE SW_PROFILE_ID = :SW_PRO_ID AND FEATURE = :func)'
      USING SW_PRO_ID, FUNCS(i), SW_PRO_ID, FUNCS(i);

    EXECUTE IMMEDIATE
      'UPDATE SW_PROFILE_FEATURE SET SW_LVC_PROFILE_ID = :SW_PRO_ID, FEATURE = :func, CREATION_DATE = SYSDATE
        WHERE ROWID IN (SELECT ROWID FROM SW_PROFILE_FEATURE WHERE SW_PROFILE_ID = :SW_PRO_ID AND FEATURE = :func)'
      USING SW_PRO_ID, FUNCS(i), SW_PRO_ID, FUNCS(i);

  END LOOP;

  COMMIT;
  -- Inserted new SW_PROFILE_FEATURE records;

EXCEPTION
WHEN OTHERS THEN
  IF SQLCODE != -1 THEN RAISE; END IF;
END;
/

您可能不需要使用EXECUTE IMMEDIATE，并且可以在一个MERGE语句中完成所有操作：

MERGE INTO SW_PROFILE_FEATURE dst
USING (
  WITH functionalities (func) AS (
    SELECT 'Func1' FROM DUAL UNION ALL
    SELECT 'Func2' FROM DUAL UNION ALL
    SELECT 'Func3' FROM DUAL UNION ALL
    SELECT 'Func4' FROM DUAL UNION ALL
    SELECT 'Func5' FROM DUAL UNION ALL
    SELECT 'Func6' FROM DUAL UNION ALL
    SELECT 'Func7' FROM DUAL UNION ALL
    SELECT 'Func8' FROM DUAL UNION ALL
    SELECT 'Func9' FROM DUAL UNION ALL
    SELECT 'Func10' FROM DUAL UNION ALL
    SELECT 'Func11' FROM DUAL UNION ALL
    SELECT 'Func12' FROM DUAL
  )
  SELECT s.id,
         f.func
  FROM   manufacturers m
         INNER JOIN sw_profile s
         ON MANUFACTURER_ID = MAN_ID
         CROSS JOIN functionalities f
  WHERE  s.release      = 'Release'
  AND    s.extensionb   = 'ExtensionB'
  AND    m.manufacturer = 'EE'
) src
ON (dst.sw_profile_id = src.id AND dst.feature = src.func)
WHEN NOT MATCHED THEN
  INSERT (ID, SW_LVC_PROFILE_ID, FEATURE, CREATION_DATE)
  VALUES (SQ_SW_PROFILE_FEATURE_ID.NEXTVAL, src.id, src.func, SYSDATE)
WHEN MATCHED THEN
  UPDATE
  SET SW_LVC_PROFILE_ID = src.id,
      CREATION_DATE     = SYSDATE;

**仅用于系统管理员将执行一次的“更新”**MERGE语句允许您将来自源的合并数据组合到目标表中，使您能够插入不存在的记录并更新存在的记录。

DECLARE
  TYPE FUNCTIONALITIES IS VARRAY(12) OF VARCHAR2(10);
  FUNCS  FUNCTIONALITIES := FUNCTIONALITIES('Func1', 'Func2', 'Func3', 'Func4', 'Func5', 'Func6', 'Func7', 'Func8', 'Func9', 'Func10', 'Func11', 'Func12');
  MAN_ID  NUMBER(12);
  SW_PRO_ID  NUMBER(12);
  REL  VARCHAR2(10) := 'Release';
  EXT_B  VARCHAR2(10) := 'ExtensionB';
BEGIN
  SELECT ID INTO MAN_ID FROM MANUFACTURERS WHERE MANUFACTURER = 'EE';
  SELECT ID INTO SW_PRO_ID FROM SW_PROFILE WHERE MANUFACTURER_ID = MAN_ID AND RELEASE = REL AND EXTENSIONB = EXT_B;

  FOR i IN 1..FUNCS.COUNT LOOP
    EXECUTE IMMEDIATE '
      MERGE INTO SW_PROFILE_FEATURE SPF USING DUAL
      ON (SPF.SW_PROFILE_ID = ' || SW_PRO_ID || ' AND SPF.FEATURE = ''' || FUNCS(i) || ''')
      WHEN MATCHED THEN
        UPDATE SET SPF.SW_LVC_PROFILE_ID = ' || SW_PRO_ID || ', SPF.FEATURE = ''' || FUNCS(i) || ''', SPF.CREATION_DATE = SYSDATE
      WHEN NOT MATCHED THEN
        INSERT (ID, SW_LVC_PROFILE_ID, FEATURE, CREATION_DATE)
        VALUES (SQ_SW_PROFILE_FEATURE_ID.NEXTVAL, ' || SW_PRO_ID || ', ''' || FUNCS(i) || ''', SYSDATE)';
  END LOOP;

  COMMIT COMMENT 'Inserted/Updated SW_PROFILE_FEATURE records';
EXCEPTION
WHEN OTHERS THEN
  IF SQLCODE != -1 THEN RAISE; END IF;
END;
/