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语句，只有两个变量--，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

程序Itselfs包含以下内容：

    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;

但它不应该只包含“创建或替换过程MISSINGTABLES”等插入STatement，我无论如何也不能执行该过程。

更好的方法是使用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;

Answer 2

您试图立即执行的SQL将被计算为：

CREATE OR REPLACE PROCEDURE MISSINGTABLES AS 
BEGIN 
INSERT INTO MISSINGTABLE 
(SELECT TABLE_NAME, COLUMN_NAME, 'Missing Table on PEKA_ERP_001' Beschreibung 
 FROM ALL_TAB_COLUMNS WHERE OWNER = 'ASE_ERP_001')
END;

这可能不是您真正想要的逻辑，但眼前的问题是，您正在尝试填充一个名为Beschreibung的不存在的第三列，而不是填充第二列DESCRIPTION。

Answer 3

我可以建议改进你的选择吗？

这里有一个可能的替代方案：

SELECT
  TABLE_NAME,
  'Missing Table on'
  || CASE MAX(OWNER) WHEN 'PEKA_ERP_001' THEN 'ASE_ERP_001' ELSE 'PEKA_ERP_001' END
  AS Description
FROM ALL_TABLES
WHERE OWNER IN ('PEKA_ERP_001', 'ASE_ERP_001')
GROUP BY TABLE_NAME
HAVING COUNT(*) = 1

此查询只返回TABLE_NAME只有一个OWNER的行。丢失表的所有者随后显示为正在测试的两个表中的另一个。

使用参数，整个CREATE PROCEDURE语句可能如下所示：

CREATE OR REPLACE PROCEDURE MISSINGTABLES
(
  owner1 IN varchar2,
  owner2 IN varchar2
)
AS
BEGIN
  INSERT INTO MISSINGTABLE
  (
  SELECT
    TABLE_NAME,
    'Missing Table on'
    || CASE MAX(OWNER) WHEN owner1 THEN owner2 ELSE owner1 END
    AS Description
  FROM ALL_TABLES
  WHERE OWNER IN (owner1, owner2)
  GROUP BY TABLE_NAME
  HAVING COUNT(*) = 1
  );
END;