从不存在的表中选择

问题的确切原因在于，您的过程没有被编译，因为它引用了不存在的对象。为此，您可能需要一些动态SQL；例如：

create or replace procedure checkTable is    vCheckExists  number;    vNum          number;begin    -- check if the table exists    select count(1)    into vCheckExists    from user_tables    where table_name = 'NON_EXISTING_TABLE';    --     if vCheckExists = 1 then        -- query the table with dynamic SQL        execute immediate 'select count(1) from NON_EXISTING_TABLE'        into vNum;    else        vNum := -1;    end if;    dbms_output.put_line(vNum);end;

即使表不存在，该过程也会编译；如果立即致电，您将获得：

SQL> select count(1) from NON_EXISTING_TABLE;select count(1) from NON_EXISTING_TABLE          *ERROR at line 1:ORA-00942: table or view does not existSQL> exec checkTable;-1PL/SQL procedure successfully completed.

然后，如果您创建表并再次调用该过程：

SQL> create table NON_EXISTING_TABLE(a) as select 1 from dual;Table created.SQL> exec checkTable;1PL/SQL procedure successfully completed.

与我显示的方式相同

SELECt

UPDATE

SELECT

INTO

例如，假设您需要插入另一个表中，则应以这种方式编辑上面的代码：

if vCheckExists = 1 then    execute immediate 'insert into target(a, b, c) select a, 1, 100 from NON_EXISTING_TABLE';end if;