问题描述
我正在为依赖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中检查索引的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!