oracle 检查数据库中是否存在表 - PL SQL
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3525448/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Check if table exists in the database - PL SQL
提问by Goran
I'm new in PL SQL, and I need to check if table exist on server and drop it.
我是 PL SQL 的新手，我需要检查服务器上是否存在表并将其删除。
Thanks in advance, Goran
提前致谢，戈兰
回答by schoetbi
you can query the tablenames
你可以查询表名
select tname from tab where tname = 'TABLE_NAME_TO_SEARCH_FOR';
回答by Kevin Crowell
select tname from tab where tname = 'TABLE_NAME';
回答by Jeffrey Kemp
The most efficient method is, don't. Just drop the table. If the table didn't exist already, it'll raise an exception.
最有效的方法是，不要。把桌子放下就行了。如果该表尚不存在，则会引发异常。
Running a query just before dropping the table is just wasting time doing what Oracle will do automatically for you.
在删除表之前运行查询只是浪费时间做 Oracle 会自动为您做的事情。
You can handle the exception however you want, e.g.:
您可以根据需要处理异常，例如：
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE "MYTABLE"';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE = -942 THEN
DBMS_OUTPUT.put_line('the table did not exist!');
ELSE
RAISE;
END IF;
END;
回答by Peter Henderson
This is where the true power of the information schema comes in. A simple query will point you in the right direction
这就是信息模式的真正威力所在。一个简单的查询将为您指明正确的方向
SELECT
*
FROM
information_schema.tables
WHERE
table_name='salesorders';
This can then be used in plpg function
然后可以在 plpg 函数中使用它
CREATE OR REPLACE FUNCTION table_exists(v_table text)
RETURNS boolean AS
$BODY$
DECLARE
v_count int;
v_sql text;
BEGIN
v_sql =
'SELECT ' ||
' count(1) ' ||
'FROM ' ||
' information_schema.tables ' ||
'WHERE ' ||
E' table_name=\'' || v_table || E'\'';
EXECUTE v_sql INTO v_count;
RETURN v_count>0;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
Use the function
使用功能
select * from table_exists('salesordesrs');
That should be enough to get you going.
这应该足以让你继续前进。
OOPS Seems I misread the original posters question. I've answered for PostgreSQL.
哎呀似乎我误读了原来的海报问题。我已经回答了 PostgreSQL。
Peter.
彼得。
回答by Rubén CG
I had some troubles with the solutions above, as my DB has a peculiar tree structure. This should give every table in your schema:
我在上述解决方案中遇到了一些麻烦，因为我的数据库具有特殊的树结构。这应该为您的架构中的每个表提供：
SELECT
table_name
FROM
all_tables
WHERE
table_name = '<your table here>'
