oracle 删除索引（如果存在）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2722630/
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
oracle drop index if exists
提问by CC.
How do you drop an index only if it exists?
如何仅在索引存在时删除索引？
It seems simple but I did found anything on the net. The idea is to drop it only if it exists, because if not, I will have an error and my process stops.
看起来很简单，但我确实在网上找到了任何东西。这个想法是只有在它存在时才删除它，因为如果不存在，我将出现错误并且我的进程停止。
I found this to find if the index exists:
我发现这是为了查找索引是否存在：
select index_name
from user_indexes
where table_name = 'myTable'
and index_name='myIndexName'
But I don't know how to put it together with
但我不知道如何把它放在一起
DROP INDEX myIndexName
回答by Samuel
Don't check for existence. Try to drop, and capture the exception if necessary...
不要检查是否存在。尝试删除，并在必要时捕获异常...
DECLARE
index_not_exists EXCEPTION;
PRAGMA EXCEPTION_INIT (index_not_exists, -1418);
BEGIN
EXECUTE IMMEDIATE 'drop index foo';
EXCEPTION
WHEN index_not_exists
THEN
NULL;
END;
/
回答by UltraCommit
DECLARE
COUNT_INDEXES INTEGER;
BEGIN
SELECT COUNT ( * )
INTO COUNT_INDEXES
FROM USER_INDEXES
WHERE INDEX_NAME = 'myIndexName';
-- Edited by UltraCommit, October 1st, 2019
-- Accepted answer has a race condition.
-- The index could have been dropped between the line that checks the count
-- and the execute immediate
IF COUNT_INDEXES > 0
THEN
EXECUTE IMMEDIATE 'DROP INDEX myIndexName';
END IF;
END;
/
回答by Will Marcouiller
In Oracle, you can't mix both DDL and DML. In order to do so, you need to work it around with the EXECUTE IMMEDIATEstatement.
在 Oracle 中，您不能混合使用 DDL 和 DML。为此，您需要使用EXECUTE IMMEDIATE语句来解决它。
So, first check for the existence of the index.
因此，首先检查索引是否存在。
Second, drop the index through the EXECUTE IMMEDIATE statement.
其次，通过 EXECUTE IMMEDIATE 语句删除索引。
DECLARE v_Exists NUMBER;
BEGIN
v_Exists := 0;
SELECT 1 INTO v_Exists
FROM USER_INDEXES
WHERE TABLE_NAME LIKE 'myTable'
AND INDEX_NAME LIKE 'myIndexName'
IF v_Exists = 1 THEN
EXECUTE IMMEDIATE "DROP INDEX myIndexName"
ENDIF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
This code is out the top of my head and you may need to fix it up a little, but this gives an idea.
这段代码出乎我的意料，您可能需要稍微修复一下，但这给出了一个想法。
Hope this helps! =)
希望这可以帮助！=)
回答by Theis
I made a procedure so it can be called several times:
我做了一个程序，所以它可以被多次调用：
DELIMITER
DROP PROCEDURE IF EXISTS ClearIndex
CREATE PROCEDURE ClearIndex(IN var_index VARCHAR(255),IN var_table VARCHAR(255))
BEGIN
SET @temp = concat('DROP INDEX ', var_index, ' ON ', var_table);
PREPARE stm1 FROM @temp;
BEGIN
DECLARE CONTINUE HANDLER FOR 1091 SELECT concat('Index ', var_index,' did not exist in ',var_table,', but was handled') AS 'INFO';
EXECUTE stm1;
END;
END
DELIMITER ;
Now it can be called more than once:
现在它可以被多次调用：
CALL ClearIndex('employees_no_index','employees');
CALL ClearIndex('salaries_no_index','salaries');
CALL ClearIndex('titles_no_index','titles');
回答by Sofiane
I hope this will help. It's a combination of all solution :) By the way thanks for the help !
我希望这将有所帮助。这是所有解决方案的组合:) 顺便感谢您的帮助！
CREATE OR REPLACE PROCEDURE CLEAR_INDEX(INDEX_NAME IN VARCHAR2) AS
BEGIN
EXECUTE IMMEDIATE 'drop index ' || INDEX_NAME;
EXCEPTION
WHEN OTHERS THEN
NULL;
END CLEAR_INDEX;
