ORACLE存储过程

ORACLE存储过程

ORACLE存储过程

create  or replace procedure dbcheck.data_save is
begin
  
insert into DBCHECK.DB_USER a(hostid,username,account_status,created,last_modify_date)
with t as(
select  b.dbid,b.username,b.account_status,b.created,b.ins_date,row_number() over (partition by dbid,username order by created desc) rn
from dbcheck.db_user_orcl_mid b
),
s as(
select  b.dbid,b.host,b.username,b.ins_date,row_number() over (partition by dbid,username order by ins_date desc) rn
from dbcheck.temp_mysql_users b
)

select dbid,username,account_status,created,ins_date 
from t where rn =1 
union all
select dbid,username,null,null,ins_date
from s where rn =1 ;

commit;

end;

call dbcheck.data_save();

###创建序列
create sequence DBCHECK.db_user_seq
minvalue 1
maxvalue 9999999
start with 1
increment by 1
nocache;

##创建触发器
create or replace trigger DBCHECK.db_user_tri
before insert on DBCHECK.DB_USER
for each row
begin
select DBCHECK.db_user_seq.nextval into :new.id from dual;
end;

##################

create or replace package dbcheck.pkg_user_info is
       procedure orcl_info;
       procedure mysql_info;
       procedure mssql_info;
end ;

create or replace package body dbcheck.pkg_user_info is

    procedure orcl_info
    as
    begin

      insert into DBCHECK.DB_USER(hostid,username,account_status,created)
      with t1 as
       (select *
          from (select instance_id,
                       username,
                       account_status,
                       created,
                       row_number() over(partition by instance_id, username, account_status, created order by t.ins_date desc) rn
                  from dbcheck.db_user_orcl_mid t)
         where rn = 1)
      select instance_id, username, account_status, to_char(created,'yyyymmdd hh24:mi:ss') created
        from t1
       where not exists (select 1
                from DBCHECK.DB_USER t2
               where t1.instance_id = t2.hostid
                 and t1.username = t2.username);
       for c1 in(
                  with t1 as
                   (select *
                      from (select instance_id,
                                   username,
                                   account_status,
                                   created,
                                   row_number() over(partition by instance_id, username, account_status, created order by t.ins_date desc) rn
                              from dbcheck.db_user_orcl_mid t)
                     where rn = 1)
                  select instance_id, username, account_status, created
                    from t1
                   where not exists (select 1
                            from DBCHECK.DB_USER t2
                           where t1.instance_id = t2.hostid
                             and t1.username = t2.username
                             and t1.account_status=t2.account_status
                             and to_char(t1.created,'yyyymmdd hh24:mi:ss')=t2.created)
                 ) loop
            update DBCHECK.DB_USER
               set account_status = c1.account_status
                  ,created = to_char(c1.created,'yyyymmdd hh24:mi:ss')
             where hostid=c1.instance_id
               and username=c1.username;
        end loop;

    EXCEPTION
      WHEN OTHERS THEN
        null;
    end;


    --mysql
    procedure mysql_info

    is
    begin

    insert into DBCHECK.DB_USER(hostid,username,account_status,created)

    with m1 as
     (select *
        from (select instance_id,
                     username,
                     null as account_status,
                     null as created,
                     row_number() over(partition by instance_id, username  order by m.ins_date desc) rn
                from dbcheck.temp_mysql_users m)
       where rn = 1)

    select instance_id, username, account_status, to_char(created,'yyyymmdd hh24:mi:ss') created
      from m1
     where not exists (select 1
              from DBCHECK.DB_USER t2
             where m1.instance_id = t2.hostid
               and m1.username = t2.username);


     for c2 in(
                with m1 as
                 (select *
                    from (select instance_id,
                         username,
                         null as account_status,
                         null as created,
                         row_number() over(partition by instance_id, username order by m.ins_date desc) rn
                            from dbcheck.temp_mysql_users m)
                   where rn = 1)
                select instance_id, username, account_status, created
                  from m1
                 where not exists (select 1
                          from DBCHECK.DB_USER t2
                         where m1.instance_id = t2.hostid
                           and m1.username = t2.username
                           and m1.account_status=t2.account_status
                           and to_char(m1.created,'yyyymmdd hh24:mi:ss')=t2.created)
               ) loop
          update DBCHECK.DB_USER
             set account_status = c2.account_status
                ,created = to_char(c2.created,'yyyymmdd hh24:mi:ss')
           where hostid=c2.instance_id
             and username=c2.username;
      end loop;
      EXCEPTION
      WHEN OTHERS THEN
        null;
    end;


    --sqlserver

    procedure mssql_info
    is
    begin

      insert into DBCHECK.DB_USER(hostid,username,account_status,created)
      with t1 as
       (select *
          from (select dbunq_id,
                       name,
                       is_disabled,
                       create_date,
                       row_number() over(partition by dbunq_id, name, is_disabled, create_date order by t.ins_date desc) rn
                  from dbcheck.db_mssql_account_req t)
         where rn = 1)
      select dbunq_id, name, is_disabled, to_char(create_date,'yyyymmdd hh24:mi:ss') create_date
        from t1
       where not exists (select 1
                from DBCHECK.DB_USER t2
               where t1.dbunq_id = t2.hostid
                 and t1.name = t2.username);
       for c1 in(
                  with t1 as
                   (select *
                      from (select dbunq_id,
                                   name,
                                   is_disabled,
                                   create_date,
                                   row_number() over(partition by dbunq_id, name, is_disabled, create_date order by t.ins_date desc) rn
                              from dbcheck.db_mssql_account_req t)
                     where rn = 1)
                  select dbunq_id, name, is_disabled, create_date
                    from t1
                   where not exists (select 1
                            from DBCHECK.DB_USER t2
                           where t1.dbunq_id = t2.hostid
                             and t1.name = t2.username
                             and t1.is_disabled=t2.account_status
                             and to_char(t1.create_date,'yyyymmdd hh24:mi:ss')=t2.created)
                 ) loop
            update DBCHECK.DB_USER
               set account_status = c1.is_disabled
                  ,created = to_char(c1.create_date,'yyyymmdd hh24:mi:ss')
             where hostid=c1.dbunq_id
               and username=c1.name;
        end loop;

    EXCEPTION
      WHEN OTHERS THEN
        null;
    end;

end ;