检查teradata中是否存在易失性表 [英] check if volatile table exists in teradata

问题描述

Volatile 表很棒，有点类似于 sql server 中的临时表(我的背景).有没有办法检查易失性表是否已经存在?这段代码在第一次运行时不起作用:

Volatile table are great and somewhat comparable to temp tables in sql server (my background). Is there a way to check if a volatile table exists already? This code won't work when it is run for the first time:

DROP TABLE SomeVolatileTable;
CREATE VOLATILE TABLE SomeVolatileTable AS
(
    SELECT 
        TOP 10 *
    FROM  SomeSourceTable
) WITH DATA ON COMMIT PRESERVE ROWS;

在sql server中你可以检查一个临时表是否存在:

In sql server you can check if a temporary table exists:

IF OBJECT_ID('tempdb..#SomeTempTable') IS NOT NULL DROP TABLE #SomeTempTable

Teradata 中是否存在类似的东西?

Does something similar exist in Teradata?

推荐答案

除了返回 all VT 的 HELP VOLATILE TABLE 之外，没有办法检查特定的 Volatile Table 是否存在.

There's no way to check if a specific Volatile Table exists besides HELP VOLATILE TABLE which returns all VT.

但是您可能会创建一个如下所示的存储过程:

But you might create a Stored Procedure like the following:

/*
   Drop a table ignoring 3807 error (Table doesn't exist)
*/

REPLACE PROCEDURE drop_table_if_exists
(
  IN db_name VARCHAR(128) CHARACTER SET Unicode,
  IN tbl_name VARCHAR(128) CHARACTER SET Unicode,
  OUT msg VARCHAR(400) CHARACTER SET Unicode
) SQL SECURITY INVOKER
BEGIN
   DECLARE full_name VARCHAR(361)  CHARACTER SET Unicode;

   DECLARE sql_stmt VARCHAR(500)  CHARACTER SET Unicode;
   DECLARE exit HANDLER FOR SqlException
   BEGIN
      IF SqlCode = 3807 THEN SET msg = full_name || ' doesn''t exist.';
      ELSE
        RESIGNAL;
      END IF;
   END;

   SET full_name = '"' || Coalesce(db_name,DATABASE) || '"."' || tbl_name || '"';

   SET sql_stmt = 'DROP TABLE ' || full_name || ';';

   EXECUTE IMMEDIATE sql_stmt;

   SET msg = full_name || ' dropped.';
END;

它只会忽略表不存在错误，但仍然会因无效权限等而失败

It will only ignore the Table doesn't exist error, but still fail on invalid rights, etc.

如果您使用自己的用户作为数据库调用它，它也适用于易失性表:

If you call it with your own user as database it also works for Volatile Tables:

CALL drop_table_if_exists(USER,'SomeVolatileTable', msg);

这篇关于检查teradata中是否存在易失性表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！