Страницы

Поиск по вопросам

вторник, 31 марта 2020 г.

Уничтожить последовательность если она существует

#sql #oracle


Перед запуском сценария хочу проверить есть ли в БД секвенция и если есть - дропнуть
её. Но DROP SEQUENCE не работает в PLSQL, а IF не работает в SQL. Как быть? Сейчас
в начало сценария приписал:

DECLARE
    V_TEMP_NUM NUMBER(9) := 0;
BEGIN
SELECT COUNT(*) INTO V_TEMP_NUM FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'TMM_TEMP10_SEQ';
IF V_TEMP_NUM > 0 THEN
  DROP SEQUENCE TMM_TEMP10_SEQ;
END IF;
CREATE SEQUENCE TMM_TEMP10_SEQ
    MINVALUE 0
    START WITH 10
    INCREMENT BY 10
    CACHE 20;
END;


Как я и сказал, ругается на DROP.
    


Ответы

Ответ 1



Много лет назад я написал небольшую процедуру, которая реализует логику: CREATE/ALTER/DROP IF EXISTS... Можно воспользоваться ей в вашем случае: create or replace procedure admin.re_run_ddl (p_sql in varchar2) AUTHID CURRENT_USER as l_line varchar2(500) default rpad('-',20,'-'); l_cr varchar2(2) default chr(10); l_footer varchar2(500) default l_cr||rpad('*',20,'*'); l_ignore_txt varchar2(200) default 'IGNORING --> '; ORA_00955 EXCEPTION; ORA_01430 EXCEPTION; ORA_02260 EXCEPTION; ORA_01408 EXCEPTION; ORA_00942 EXCEPTION; ORA_02275 EXCEPTION; ORA_01418 EXCEPTION; ORA_02443 EXCEPTION; ORA_01442 EXCEPTION; ORA_01434 EXCEPTION; ORA_01543 EXCEPTION; ORA_00904 EXCEPTION; ORA_02261 EXCEPTION; ORA_04043 EXCEPTION; ORA_02289 EXCEPTION; PRAGMA EXCEPTION_INIT(ORA_00955, -00955); --ORA-00955: name is already used by an existing object PRAGMA EXCEPTION_INIT(ORA_01430, -01430); --ORA-01430: column being added already exists in table PRAGMA EXCEPTION_INIT(ORA_02260, -02260); --ORA-02260: table can have only one primary key PRAGMA EXCEPTION_INIT(ORA_01408, -01408); --ORA-01408: such column list already indexed PRAGMA EXCEPTION_INIT(ORA_00942, -00942); --ORA-00942: table or view does not exist PRAGMA EXCEPTION_INIT(ORA_02275, -02275); --ORA-02275: such a referential constraint already exists in the table PRAGMA EXCEPTION_INIT(ORA_01418, -01418); --ORA-01418: specified index does not exist PRAGMA EXCEPTION_INIT(ORA_02443, -02443); --ORA-02443: Cannot drop constraint - nonexistent constraint PRAGMA EXCEPTION_INIT(ORA_01442, -01442); --ORA-01442: column to be modified to NOT NULL is already NOT NULL PRAGMA EXCEPTION_INIT(ORA_01434, -01434); --ORA-01434: private synonym to be dropped does not exist PRAGMA EXCEPTION_INIT(ORA_01543, -01543); --ORA-01543: tablespace '' already exists PRAGMA EXCEPTION_INIT(ORA_00904, -00904); --ORA-00904: "%s: invalid identifier" PRAGMA EXCEPTION_INIT(ORA_02261, -02261); --ORA-02261: "such unique or primary key already exists in the table" PRAGMA EXCEPTION_INIT(ORA_04043, -04043); --ORA-04043: object %s does not exist PRAGMA EXCEPTION_INIT(ORA_02289, -02289); --ORA-02289: sequence does not exist procedure p( p_str in varchar2 ,p_maxlength in int default 120 ) is i int := 1; begin dbms_output.enable( NULL ); while ( (length(substr(p_str,i,p_maxlength))) = p_maxlength ) loop dbms_output.put_line(substr(p_str,i,p_maxlength)); i := i + p_maxlength; end loop; dbms_output.put_line(substr(p_str,i,p_maxlength)); end p; begin p( 'EXEC:'||l_cr||l_line||l_cr||p_sql||l_cr||l_line ); execute immediate p_sql; p( 'done.' ); exception when ORA_00955 or ORA_01430 or ORA_02260 or ORA_01408 or ORA_00942 or ORA_02275 or ORA_01418 or ORA_02443 or ORA_01442 or ORA_01434 or ORA_01543 or ORA_00904 or ORA_02261 or ORA_04043 or ORA_02289 then p( l_ignore_txt || SQLERRM || l_footer ); when OTHERS then p( SQLERRM ); p( DBMS_UTILITY.FORMAT_ERROR_BACKTRACE ); p( l_footer ); RAISE; end; / show err Пример использования: prompt clean-up ... begin admin.re_run_ddl('drop sequence BLA_BLA_BLA'); admin.re_run_ddl('drop procedure BLA_BLA_BLA'); admin.re_run_ddl('drop table BLA_BLA_BLA'); end; /

Ответ 2



А на дроп ругается из за этого (execute immediate), о чём нам напоминает Коннор на AskTOM: DDL - считается редким событием в Oracle (в отличие от других СУБД) DECLARE V_TEMP_NUM NUMBER(9) := 0; BEGIN SELECT COUNT(*) INTO V_TEMP_NUM FROM USER_SEQUENCES WHERE SEQUENCE_NAME = 'TMM_TEMP10_SEQ'; IF V_TEMP_NUM > 0 THEN execute immediate 'DROP SEQUENCE TMM_TEMP10_SEQ'; END IF; execute immediate ' CREATE SEQUENCE TMM_TEMP10_SEQ MINVALUE 0 START WITH 10 INCREMENT BY 10 CACHE 20'; END;

Комментариев нет:

Отправить комментарий