从不存在的表中选择

问题恰恰在于,您的过程无法编译,因为它引用了一个不存在的对象; 你可能需要一些动态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 exist


SQL> exec checkTable;
-1

PL/SQL procedure successfully completed.

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

SQL> create table NON_EXISTING_TABLE(a) as select 1 from dual;

Table created.

SQL> exec checkTable;
1

PL/SQL procedure successfully completed.

就像我展示的一样SELECT,你可以做一个UPDATE或者你需要的任何SQL查询; 如果您执行与a不同的操作SELECT,则INTO必须删除该子句.

例如,假设您需要插入到不同的表中,上面的代码应该以这种方式编辑:

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