检查teradata中是否存在volatile表

Question

Volatile表很棒，在某种程度上可以与sql server(我的背景)中的临时表相媲美。是否有一种方法来检查是否一个易失性表已经存在?以下代码在第一次运行时无法运行:

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

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

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

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

Answer 1

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

但是您可以像下面这样创建一个存储过程:

/*
   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;

它只会忽略Table doesn't exist错误，但仍然会在无效的权限上失败，等等

如果你用自己的用户作为数据库调用它，它也适用于Volatile Tables:

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

Answer 2

您可以使用这个存储过程来检查是否存在volatile表:

REPLACE PROCEDURE test_db.VOLATILE_EXISTS(
  IN volatile_table_name varchar(30),
  OUT exists_ind integer -- 1 if it exists, else 0
)
SQL SECURITY INVOKER
BEGIN
  DECLARE ignored_condition varchar(100) default '';
  DECLARE table_does_not_exist CONDITION FOR SQLSTATE '42000';
  DECLARE sql_string varchar(100);
  DECLARE CONTINUE HANDLER FOR table_does_not_exist
     SET ignored_condition = 'table does not exist';
  SET exists_ind = 0;
  SET sql_string =  'select 1 from '||volatile_table_name||' where 1=0';
  BEGIN
    DECLARE c1 CURSOR FOR s1;
    PREPARE s1 FROM sql_string;
    OPEN c1;
  END;
  IF ignored_condition = '' THEN
    SET exists_ind = 1;
  END IF;
END;

请注意，如果VT不存在，并且您在BEGIN TRANSACTION/END TRANSACTION中调用此SP，"表不存在异常"虽然处理了，但会导致事务回滚作为副作用。

Answer 3

与其在您尝试创建它们之前删除它们，不如在您完成它们之后在脚本中删除它们。这将始终有效，将允许您重新运行而不会出现问题，并将释放volatile表所使用的spool空间。