Oracle存储过程 -- 删除索引、序列、触发器

–删除索引 参数：索引名称
create or replace procedure TRYDROPINDEX(indexName in varchar2) is
stmt VARCHAR2(2000);
row1 number;
begin
select count(index_name) into row1 from user_indexes where index_name = indexName;
IF (row1>0) THEN
stmt :='DROP INDEX '|| indexName;
execute immediate stmt;
END IF;
end TRYDROPINDEX;
–调用示例
CALL trydropindex(‘IDX_IMP_VALUATION_DATA_1’);

–删除索引 参数：索引名称
CREATE OR REPLACE PROCEDURE tryDropSequence(tableName IN varchar2) is
stmt VARCHAR2(2000);
row1 number;
begin
select count(sequence_name)–
into row1–
from user_sequences–
where sequence_name = upper(tableName);–
IF (row1 > 0) THEN–
stmt := 'DROP SEQUENCE ’ || tableName;–
execute immediate stmt;–
END IF;–
END;

–调用示例
CALL trydroptable(‘TTRD_IMP_VALUATION_OBJ_SUM’);

–删除触发器 参数：触发器名称
CREATE OR REPLACE PROCEDURE tryDropTrigger(triggerName IN varchar2) is
stmt VARCHAR2(2000);
row1 number;
begin
select count(table_name) into row1 from user_triggers where trigger_name=upper(triggerName);
IF (row1>0) THEN
stmt :='DROP TRIGGER '|| triggerName;
execute immediate stmt;
END IF;
END tryDropTrigger;
–调用示例
CALL trydroptable(‘USER_ADD_TRIG’);