甲骨文 : Create Table as Select statement and Select query on created table in Single Stored Procedure

Question

我想要使用 Create Table ... Select ... 语句创建临时表的存储过程。然后从同一个创建的表中选择记录。最后删除创建的表...

我希望所有这些功能都在同一个存储过程中。

我已经创建了以下存储过程。但是我得到了以下错误

PL/SQL: ORA-00942: table or view does not exist

这是我的程序

DECLARE
TEMP_TBL VARCHAR2(4000);
TBL_NAME VARCHAR2(200) := 'ABC_TEST';

BEGIN
TEMP_TBL := 'CREATE TABLE MY_TAB_COL AS(SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,to_lob(DATA_DEFAULT) AS DATA_DEFAULT,NULLABLE FROM ALL_TAB_COLS WHERE TABLE_NAME=''' || TBL_NAME || ''')';

DBMS_OUTPUT.PUT_LINE(TEMP_TBL);
EXECUTE IMMEDIATE TEMP_TBL;

FOR DD_COLUMNS IN
(SELECT TABLE_NAME FROM MY_TAB_COL)
LOOP 
  DBMS_OUTPUT.PUT_LINE('DD_COLUMNS.TABLE_NAME');

END LOOP;
END;

Answer 1

我不确定您为什么想要这样的场景，并且不建议在生产中使用此代码。我很确定在 99% 的情况下，这种方法可以用其他方式编写。但是你可以试试下面的代码(你程序中的一切都应该使用动态sql)

declare
   TBL_NAME varchar2(200) := 'ABC_TEST';

   lv_Sql varchar2(4000);

begin
   lv_Sql := 'CREATE TABLE MY_TAB_COL AS(SELECT TABLE_NAME,COLUMN_NAME,DATA_TYPE,to_lob(DATA_DEFAULT) AS DATA_DEFAULT,NULLABLE FROM ALL_TAB_COLS WHERE TABLE_NAME=''' ||
             TBL_NAME || ''')';

   DBMS_OUTPUT.PUT_LINE(lv_Sql);
   execute immediate lv_Sql;

   lv_Sql := 'begin
                 for DD_COLUMNS in (select TABLE_NAME from MY_TAB_COL) loop
                    DBMS_OUTPUT.PUT_LINE(DD_COLUMNS.TABLE_NAME);   
                 end loop;
              end;';

   DBMS_OUTPUT.PUT_LINE(lv_Sql);
   execute immediate lv_Sql;

   for R in (select *
               from user_objects
              where object_name = 'MY_TAB_COL'
                and object_type = 'TABLE') loop
      DBMS_OUTPUT.PUT_LINE('drop table ' || R.Object_Name);
      execute immediate 'drop table ' || R.Object_Name;
   end loop;
end;