从不存在的表中选择 [英] Select from table that does not exist
问题描述
我有一个关于ORACLE的问题,我写了一个PLSQL CODE来检查一个表是否存在,如果存在,那么我从该表中选择一个东西..pseudocode类似于:
I have a question regarding ORACLE, I wrote a PLSQL CODE that checks if a table exists, if it exists then I select something from this table..pseudocode is like:
如果(表存在) 从表中选择....
if (table exists) Select from table where....
问题是,即使表条件不存在且select语句从未执行,即使表不存在,我也总是会收到错误消息.
the problem is that I always get an error if the table does not exist, even if the if condition is never met and the select statement is never executed.
我认为这是因为在编译时检查了我的代码:"select from ..",如果表不存在,则会打印错误.我该如何解决这样的问题?..这是我的代码的样子(我使用了通用名称):
I think it is because my code is checked at compile time: "select from.." and then it prints an error if the table does not exist. How can I solve such an issue?.. here is how my code looks like (I used generic names):
DECLARE
v_table_exists NUMBER;
BEGIN
SELECT NVL(MAX(1), 0)
INTO v_table_exists
FROM ALL_TABLES
WHERE TABLE_NAME = 'TABLE_TEST';
IF v_table_exists = 1 THEN
INSERT INTO MY_TABLE(COLUMN1, COLUMN2, COLUMN3, COLUMN4)
SELECT 1234,
5678,
T.COLUMN_TEST1,
T.COLUMN_TEST2
FROM TABLE_TEST T
WHERE T.FLAG = 1;
END IF;
END;
推荐答案
问题恰好在于您的过程con未被引用,因为它引用的对象不存在.为此,您可能需要一些动态SQL;例如:
The issue is exactly in the fact that your procedure con not be compiled as it refers to a non existing object; you may need some dynamic SQL for this; for example:
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;
即使表不存在,过程也会编译;如果您立即拨打电话,您将获得:
The procedure compiles even if the table does not exist; if you call it now, you get:
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.
然后,如果您创建表并再次调用该过程:
Then, if you create the table and call the procedure again:
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查询;如果您执行的操作不同于SELECT
,则必须删除INTO
子句.
The same way I showed a SELECT
, you can do an UPDATE
or whatever SQL query you need; if you do something different from a SELECT
, the INTO
clause has to be removed.
例如,假设您需要插入另一个表中,则应以这种方式编辑上面的代码:
For example, say you need to insert into a different table, the above code should be edited this way:
if vCheckExists = 1 then
execute immediate 'insert into target(a, b, c) select a, 1, 100 from NON_EXISTING_TABLE';
end if;
这篇关于从不存在的表中选择的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!