SQL变量，怎么做？

Question

我有这个 SQL:

DROP TABLE MISSINGTABLE;

CREATE TABLE MISSINGTABLE (
  TABLE_NAME VARCHAR2 (70),
  DESCRIPTION VARCHAR2 (1000)
)

CREATE OR REPLACE PROCEDURE MISSINGTABLES AS
  BEGIN
      INSERT INTO MISSINGTABLE
      ((((SELECT TABLE_NAME, 'Missing Table on PEKA_ERP_001' Description FROM ALL_TABLES WHERE OWNER = 'ASE_ERP_001')
      MINUS
      (SELECT TABLE_NAME, 'Missing Table on PEKA_ERP_001' Description FROM ALL_TABLES WHERE OWNER = 'PEKA_ERP_001'))
        UNION
      ((SELECT TABLE_NAME, 'Missing Table on ASE_ERP_001' Description FROM ALL_TABLES WHERE OWNER = 'PEKA_ERP_001')
      MINUS
      (SELECT TABLE_NAME, 'Missing Table on ASE_ERP_001' Description FROM ALL_TABLES WHERE OWNER = 'ASE_ERP_001'))));
  END;

所以，您怎么看，我创建了一个表，然后创建了一个过程，它填充了该表。现在我想要这些参数的 2 个变量:'PEKA_ERP_001' 和 'ASE_ERP_001'(所以我并不总是需要手动编写它，因为这个值变化很大)

我试过了(只包含上述声明的第一部分):

DECLARE
  S1 VARCHAR2(100) := 'ASE_ERP_001';
  S2 VARCHAR2(100) := 'PEKA_ERP_001';
  TableMissing VARCHAR(100) := 'Missing Table on ';
  Apostrophe VARCHAR(10) := '''';
  BEGIN
      EXECUTE IMMEDIATE ('CREATE OR REPLACE PROCEDURE MISSINGTABLES AS BEGIN INSERT INTO MISSINGTABLE (SELECT TABLE_NAME, ' || Apostrophe || TableMissing || S2 || Apostrophe || ' Description FROM ALL_TAB_COLUMNS WHERE OWNER = ' || Apostrophe || S1 || Apostrophe || ')' || ' END;');
END;

它创建了过程，但过程本身包含“创建或替换过程”，并向我显示错误...(我无法执行过程)

谁能帮帮我？ 我怎样才能在头部编写第一个有效的 SQL 语句，只有 2 个变量，ASE_ERP_001 和 PEKA_ERP_001？

编辑:

声明:

 DECLARE
  S1 VARCHAR2(100) := 'ASE_ERP_001';
  S2 VARCHAR2(100) := 'PEKA_ERP_001';
  TabelleFehlt VARCHAR(100) := 'Diese Tabelle fehlt ';
  Hochkomma VARCHAR(10) := '''';
  BEGIN
      EXECUTE IMMEDIATE ('CREATE OR REPLACE PROCEDURE MISSINGTABLES AS BEGIN INSERT INTO MISSINGTABLE (SELECT TABLE_NAME, ' || Hochkomma || TabelleFehlt || S2 || Hochkomma || ' Beschreibung FROM ALL_TAB_COLUMNS WHERE OWNER = ' || Hochkomma || S1 || Hochkomma || ') END;');
END;

上面的语句创建了一个过程。但它也向我展示了这一点:

    ORA-06512: in Row 7
    24344. 00000 -  "success with compilation error"
    *Cause:    A sql/plsql compilation error occurred.
    *Action:   Return OCI_SUCCESS_WITH_INFO along with the error code

过程本身包含:

    create or replace
PROCEDURE MISSINGTABLES AS BEGIN INSERT INTO MISSINGTABLE (SELECT TABLE_NAME, 'Diese Tabelle fehlt PEKA_ERP_001' Beschreibung FROM ALL_TAB_COLUMNS WHERE OWNER = 'ASE_ERP_001') END;

但它不应该包含“Create or Replace Procedure MISSINGTABLES”等，只有 INSERT 语句，我无论如何都无法执行该过程..

更好的方法是使用 bpgergo 中的脚本，如果可以的话。

Answer 1

我希望我没有混淆论点，你应该再检查一遍

CREATE OR REPLACE PROCEDURE MISSINGTABLES (p_1 in varchar2, p_2 in varchar2)
AS
  BEGIN
      INSERT INTO MISSINGTABLE
      ((((SELECT TABLE_NAME, 'Missing Table on '||p_1 Description FROM ALL_TABLES WHERE OWNER = p_2)
      MINUS
      (SELECT TABLE_NAME, 'Missing Table on '||p_1 Description FROM ALL_TABLES WHERE OWNER = p_1))
        UNION
      ((SELECT TABLE_NAME, 'Missing Table on '||p_2 Description FROM ALL_TABLES WHERE OWNER = p_1)
      MINUS
      (SELECT TABLE_NAME, 'Missing Table on '||p_2 Description FROM ALL_TABLES WHERE OWNER = p_2))));
END;

编辑

你可以这样调用它:

begin
    MISSINGTABLES ('PEKA_ERP_001', 'ASE_ERP_001');
end;