oracle PLSQL -“SP2-0552:绑定变量未声明”,但似乎不是冒号问题

e4eetjau  于 2023-10-16  发布在  Oracle
关注(0)|答案(3)|浏览(575)

我尝试了SQL Developer中的plsql脚本,它返回SP2-0552:绑定变量“MAN_ID”未声明
(我改变了值和一些名称,所以我希望没有错别字)

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_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_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 new SW_PROFILE_FEATURE records';

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

我必须做一系列的12个插入,这是由不同的特征字段的值cointains功能的名称。由于某些原因,其中一些可能已经进入表中,所以在这种情况下,我必须更新它们,以确保它们在最后会像预期的那样。
所以我想把所有12个功能放在一个varray中,然后用一个“insert where not exists”和一个“update where”匹配主键来循环它们。
我试着在互联网上寻找类似的问题,但大多数人的问题只是把一个冒号前的变量到“选择到”语句。这似乎不是我的情况。
(If你也发现其他问题的脚本,请告诉我)

x8diyxa7

x8diyxa71#

存储过程:
将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;
/
pu3pd22g

pu3pd22g2#

: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;
y53ybaqx

y53ybaqx3#

**仅用于系统管理员将执行一次的“更新”**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;
/

相关问题