学习_Oracle_安全的数据库脚本

包含升级以及回滚

--update
declare
    icount number;
begin
    select count(*) into icount from user_tables where table_name = upper('CGW_T_KAFKAMQCONF');
    if icount = 0 then
        execute immediate '
        create table CGW_T_KAFKAMQCONF(
		   TopicName         		VARCHAR2(100)		not null,
		   BrokerUrl				VARCHAR2(300)		not null,
		   KafkaParam				VARCHAR2(300)		not null,
		   TopicParam			    VARCHAR2(300)		not null,
		   Partition				INTEGER default -1  not null,
		   GroupId                  INTEGER,
		   constraint PK_CGW_T_KAFKAMQCONF primary key (TopicName) using index tablespace TS_MDSP_IDX
		) tablespace TS_MDSP_DATA
        ';
    
        commit;
    end if;    
end;
/

declare
icount INTEGER;
begin
    select count(*) into icount from CGW_T_PLUGIN where PLUGINNAME = 'kafkamqproducer';
    if icount = 0
    then
insert into CGW_T_PLUGIN (PLUGINNAME, NODETYPE, NODEID, MAJORVERSION, MINORVERSION, STATUS, ISPLATPLUGIN, MODIFIEDTIME, PLUGINDESC, PLUGINPROVISION)
select 'kafkamqproducer', 10, 0, 1, 0, 1, 0, null, 'kafkamqproducer', 'Huawei Technologies Co., Ltd.' from dual
where not exists (select 1 from CGW_T_AUTH where INTERFACEID = '0x100008B8' and NEID = '6900000001');
    end if;
commit;
end;
/

declare
icount INTEGER;
begin
    select count(*) into icount from CGW_T_KAFKAMQCONF where TopicName = 'PayBind';
    if icount = 0
    then
insert into CGW_T_KAFKAMQCONF (TopicName, BrokerUrl, KafkaParam, TopicParam, Partition)
select 'PayBind', '10.136.95.6:9092', 'queue.buffering.max.messages=100000', 'message.timeout.ms=30000', -1 from dual
where not exists (select 1 from CGW_T_AUTH where INTERFACEID = '0x100008B8' and NEID = '6900000001');
    end if;
commit;
end;
/



--rollback

delete from CGW_T_PLUGIN where PLUGINNAME = 'kafkamqproducer'
/

delete from CGW_T_KAFKAMQCONF where TopicName = 'PayBind'
/

declare
    icount number;
begin
    select count(*) into icount from user_tables where table_name = upper('CGW_T_KAFKAMQCONF');
    if icount > 0 then
        execute immediate 'DROP table CGW_T_KAFKAMQCONF ';   
        commit;
    end if;    
end;
/