Question

只有当指数存在时,你才会放弃指数?

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.

我发现,如果指数存在的话,情况就是这样:

select index_name
from user_indexes
where table_name =  myTable 
and index_name= myIndexName

但我不知道如何把它同起来。

DROP INDEX myIndexName

Answer 1

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;
/

Answer 2

Don 生存。如有必要,请退约并抓住例外......

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;
/

Answer 3

在Oracle,你可以把DDL和DML混为一谈。为此,您需要与以下网站一道开展工作:rel=“nofollow noreferer”>。

因此,首先检查指数的存在。

其次,通过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;

该法典是我头部的顶部,你可能需要将其固定下来,但这提供了想法。

希望这一帮助!

Answer 4

我提出了一种程序,可以多次称之为:

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 ;

现在可以不止一次地称之为:

CALL ClearIndex( employees_no_index , employees );
CALL ClearIndex( salaries_no_index , salaries );
CALL ClearIndex( titles_no_index , titles );

Answer 5

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;

友情链接