如何在Oracle中检查索引 [英] How To Check for an Index in Oracle

问题描述

我正在为依赖Oracle数据库的产品编写模式升级脚本.在一个区域中，我需要在表上创建索引-如果该索引尚不存在.是否有一种简单的方法来检查Oracle脚本中我知道名称的索引的存在?

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?

在SQL Server中将与此类似: 如果不存在(SELECT * FROM SYSINDEXES WHERE NAME ='myIndex') //然后创建我的myIndex

It would be similar to this in SQL Server: IF NOT EXISTS (SELECT * FROM SYSINDEXES WHERE NAME = 'myIndex') // Then create my myIndex

推荐答案

从user_indexes中选择count(*)，其中index_name ='myIndex'

select count(*) from user_indexes where index_name = 'myIndex'

sqlplus不支持IF ...，因此您必须使用匿名PL/SQL块，这意味着必须立即执行DDL.

sqlplus won't support IF..., though, so you'll have to use anonymous PL/SQL blocks, which means EXECUTE IMMEDIATE to do 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;
/

如前所述，Oracle用大写字母存储未加引号的对象名称.

as pointed out, Oracle stores unquoted object names in all uppercase.

这篇关于如何在Oracle中检查索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！