【Oracle】实现Oracle数据库对象的一键升级

引言

    公司内部的项目比较倾向于将业务逻辑放在oracle存储过程中实现，所以每次项目升级都涉及到很多的oracle表，存储过程等数据库对象的升级。然而采取的升级方式是比较"原始"的,每一个对象都是从开发库拷贝内容,再到测试库,正式库中黏贴,执行。这样的方式,工作量大而繁琐,容易出错出漏。为此，我编写了一个小程序，实现oracle对象的一键升级。下面给出主要实现逻辑。

1.获取对象的执行sql语句

    利用oracle的dbms_metadata包来获取相关对象的执行语句，用oracle存储过程来实现。对象名作为输入参数，返回参数为包含执行sql的游标，主要代码如下：

create or replace procedure p_SYSTEM_UPDATECONTENT_sel(
in_vc_name  in varchar2,
RETURNMSG      OUT  Varchar2,  
retcursor    out Sys_Refcursor  
)
is
   retcon clob;
   vc_name varchar2(1000);
   RETTYPE varchar2(1000):=''; 
begin
    RETURNMSG:='';
    vc_name:=upper(in_vc_name);
    begin 
    select dbms_metadata.get_ddl('PROCEDURE',vc_name) INTO  retcon FROM DUAL;
       RETTYPE:='存储过程';
    Exception
     When Others Then
        null;
    end;
    
    if RETTYPE is not null then
           open retcursor for  select  retcon as retcontent,in_vc_name as prcname,RETTYPE as RETURNTYPE from dual ;   
    else  
       RETURNMSG:='不存在此名称相关内容!';
       open retcursor for  select  '' as retcontent ,in_vc_name as prcname, '' as RETURNTYPE from dual;
    end if ;
    
Exception
     When Others Then
      open retcursor for  select  '' as retcontent ,in_vc_name as prcname, '' as RETURNTYPE from dual;
      RETURNMSG:=f_Get_Errormsg;
end p_SYSTEM_UPDATECONTENT_sel;

2.执行对象sql语句

    在存储过程中利用execute immediate直接执行sql语句，实现对象的升级，主要代码如下：

create or replace procedure p_SYSTEM_UPDATECONTENT_ins(
in_VC_CONTENT in clob,   --提交内容
RETURNMSG      OUT  Varchar2  
) is

   vc_oldstr clob:='select 1 from dual';
     v_name varchar2(100);
     n_issuc number(1):=1;
     vc_ret varchar2(1000);
begin
      v_name:=upper(in_vc_name);
   
    begin 
      execute immediate in_VC_CONTENT; 
    Exception
        When Others Then
              select  '编译出错:'||wm_concat('在'||t.line||'行,'||t.text||'    ') into vc_ret
               from DBA_ERRORS t where   t.name =v_name;
            --   dbms_output.put_line(vc_ret);
               n_issuc:=0;
               execute immediate vc_oldstr; 
          else
           
            vc_ret:='编译出错:'||f_Get_Errormsg;
            n_issuc:=0;

    end;
    
   commit;     
    RETURNMSG:=replace(replace(replace(replace(vc_ret,'<',''),'>',''),'&',''),'"',''); 
                                 
Exception
    When Others Then
         rollback;
         RETURNMSG:=f_Get_Errormsg; 
end p_SYSTEM_UPDATECONTENT_ins;

3.编写程序实现一键升级

    客户端的代码逻辑主要是调用开发库中的p_SYSTEM_UPDATECONTENT_sel得到对象的升级内容，再调用测试库或正式库的p_SYSTEM_UPDATECONTENT_ins执行sql实现升级 ，我是用.net的wpf来实现，逻辑比较简单，就不多说了，最终界面如下：

小结

    上文大致的给出了程序的实现逻辑，主要是利用oracle的dbms_metadata包获取对象的执行sql。其他对象如表，索引，序列等的升级也是类似的实现，相关资料可以查询dbms_metadata的用法。如果您有更好的建议，请评论留言，感激不尽！