如何在 Oracle 中检查索引
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/640390/
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
How To Check for an Index in Oracle
提问by Doug
I am writing a schema upgrade script for a product that depends on an Oracle database. In one area, I need to create an index on a table - if that index does not already exist. Is there an easy way to check for the existence of an index that I know the name of in an Oracle script?
我正在为依赖 Oracle 数据库的产品编写架构升级脚本。在一个方面，我需要在表上创建一个索引 - 如果该索引尚不存在。是否有一种简单的方法可以检查我知道 Oracle 脚本中名称的索引是否存在？
It would be similar to this in SQL Server: IF NOT EXISTS (SELECT * FROM SYSINDEXES WHERE NAME = 'myIndex') // Then create my myIndex
它与 SQL Server 中的类似：IF NOT EXISTS (SELECT * FROM SYSINDEXES WHERE NAME = 'myIndex') // 然后创建我的 myIndex
回答by erikkallen
select count(*) from user_indexes where index_name = 'myIndex'
select count(*) from user_indexes where index_name = 'myIndex'
sqlplus won't support IF..., though, so you'll have to use anonymous PL/SQL blocks, which means EXECUTE IMMEDIATE to do DDL.
sqlplus 不支持 IF...，但是，因此您必须使用匿名 PL/SQL 块，这意味着 EXECUTE IMMEDIATE 来执行 DDL。
DECLARE
i INTEGER;
BEGIN
SELECT COUNT(*) INTO i FROM user_indexes WHERE index_name = 'MYINDEX';
IF i = 0 THEN
EXECUTE IMMEDIATE 'CREATE INDEX myIndex ...';
END IF;
END;
/
Edit: as pointed out, Oracle stores unquoted object names in all uppercase.
编辑：正如所指出的，Oracle 以全部大写形式存储未加引号的对象名称。
