时间戳

首页 > 解决方案 > CASE when EXISTS (SELECT ...) seems to always returns true

sql - CASE when EXISTS (SELECT ...) seems to always returns true

问题描述

I need to test if a table exists. If it exists, I need to get the rowcount of that table, else I need to set the rowcount value to NULL.

Here is a snippet of the query I'am using : 

SELECT 
    CASE 
    -- TABLE A does not exists
        WHEN EXISTS (Select TABLE_NAME from ALL_TABLES where TABLE_NAME ='A') 
            THEN (SELECT COUNT(*) FROM SYS."A") 
            ELSE NULL
        END AS TABLE_count
FROM dual;

When executing this query, I get the following error message : 

SQL Error [942] [42000]: ORA-00942: table or view does not exist

It seems that even if the EXISTS returns 0 rows, the THEN clause is always executed instead of going into the ELSE clause.

I don't understand the behavior here. Any idea ?

Thanks

标签： sqloracleplsql

解决方案

您不能在纯 sql 中执行此操作，因为查询是作为一个整体解析的，包括部分SELECT COUNT(*) FROM SYS."A" 所以如果表SYS."A"不存在，则整个查询解析失败。

另一种方法是使用 pl/sql。如果表存在，则创建一个计算行数的函数，如果不存在，则返回 null。

考虑以下示例：

create or replace function get_table_count(sTableName varchar2)
return number
is
  iCount number;
begin
  for c in (
    select null
    from   ALL_TABLES 
    where  TABLE_NAME = sTableName
  ) loop
    execute immediate 'select count(*) from '||sTableName into iCount;
    return iCount;
  end loop;
  return null;
end;
/

select get_table_count('DUAL') c1, get_table_count('ABSENT_TABLE') c2  from dual

返回

C1 C2
1  (null)

推荐阅读