如何检查SQL Anywhere中是否存在临时表？

I want to write a SQL IF statement that checks whether or not a local temporary table exists, but those kinds of tables are not recorded in the SQL Anywhere system catalog.

我想编写一个SQL IF语句来检查是否存在本地临时表,但这些表不会记录在SQL Anywhere系统目录中。

3 个解决方案

#1

Note that you can do this in 11.0.1 and higher:

请注意,您可以在11.0.1及更高版本中执行此操作:

DROP TABLE IF EXISTS t;

#2

If you're asking the question, "How do I drop a local temporary table without raising an error if it doesn't exist?" then the answer's simple: just DROP it and ignore any error:

如果您问的问题是“如果不存在错误,如何删除本地临时表?”然后答案很简单:只需DROP它并忽略任何错误:

BEGIN
   DROP TABLE t;
   EXCEPTION WHEN OTHERS THEN
END;

If you really need to know the answer to the question "Does table t exist?" you can query the table and analyze the resulting SQLSTATE. The following function makes use of several features:

如果你真的需要知道问题的答案“表格是否存在?”您可以查询表并分析生成的SQLSTATE。以下功能使用了以下几个功能:

ON EXCEPTION RESUME ignores any exception raised by the SELECT and passes control to the IF statement.

ON EXCEPTION RESUME忽略SELECT引发的任何异常并将控制传递给IF语句。
EXECUTE IMMEDIATE lets you write a query where the table name is in a string variable.

EXECUTE IMMEDIATE允许您编写一个查询,其中表名在字符串变量中。
TOP 1 makes sure that only one row is selected even if the table contains a million rows.

TOP 1确保即使表包含一百万行,也只选择一行。
ORDER BY 1 lets you meet the requirement that TOP can only be used when the result set is ordered.

ORDER BY 1允许您满足TOP只能在订购结果集时使用的要求。
SELECT 1 relieves you of the burden of specifying a column name.

SELECT 1减轻了指定列名的负担。
INTO @dummy means the SELECT (and consequently the EXECUTE IMMEDIATE) doesn't return a result set.

INTO @dummy意味着SELECT(因此EXECUTE IMMEDIATE)不返回结果集。

If the SELECT works, it's either going to set SQLSTATE to '00000' for success or '02000' for row not found. Any other SQLSTATE means there's some serious problem with the table... like it doesn't exist.

如果SELECT工作,它要么将SQLSTATE设置为'00000'表示成功,要么将'02000'设置为未找到行。任何其他SQLSTATE意味着表有一些严重的问题......就像它不存在一样。

CREATE FUNCTION f_table_is_ok
   ( IN @table_name VARCHAR ( 128 ) )
   RETURNS INTEGER
   ON EXCEPTION RESUME
BEGIN
   DECLARE @dummy INTEGER;
   EXECUTE IMMEDIATE STRING (
      'SELECT TOP 1 1 INTO @dummy FROM ',
      @table_name,
      ' ORDER BY 1' );
   IF SQLSTATE IN ( '00000', '02000' ) THEN
      RETURN 1
   ELSE
      RETURN 0
   END IF;
END;

Here's some test code:

这是一些测试代码:

BEGIN
DECLARE LOCAL TEMPORARY TABLE tt ( c INTEGER );
DECLARE LOCAL TEMPORARY TABLE "t t" ( c INTEGER );
SELECT f_table_is_ok ( 'asdf' );
SELECT f_table_is_ok ( 'tt' );
SELECT f_table_is_ok ( '"t t"' );
SELECT f_table_is_ok ( '"SYS"."SYSTABLE"' );
END;

#3

just try to drop it anyways and ignore the error...

只是试着放弃它而忽略错误......

BEGIN
DROP TABLE table;
EXCEPTION WHEN OTHERS THEN

BEGIN DROP TABLE表;除此之外的例外情况

END;

#1