在Oracle存储过程中实现类Mybatis的动态sql

客户要求把所有sql从MyBatis里移植到存储过程里面，这种需求很恐怖，但在客户面前毫无话语权，只好硬着头皮上。

思路为

• 用正则替换掉占位符
• 采用oracle的dbms_sql工具包执行动态sql
• 记录每一步执行的日志

生产环境远比这复杂，这里写一个小测试做演示。

首先创建工具包

CREATE OR REPLACE PACKAGE DATA_UTIL
AS
    type gt_varchar_tab is table of varchar2(4000) index by varchar2(30);
    --------------------------------------------------------
    -- gt_varchar_tab类似java中的HashMap对象,用来存储值
    --------------------------------------------------------

    --------------------------------------------------------
    -- replace placeholder with value
    --------------------------------------------------------
    procedure prc_repl_param
    ( p_text                    in out  nocopy  varchar2 
    , p_param_str               in              varchar2
    , p_param_value             in              varchar2
    )
    ;
    --------------------------------------------------------
    -- replace placeholder with dynamic sql
    --------------------------------------------------------
    procedure prc_eval_param
    ( p_text                    in out  nocopy  varchar2 
    , p_param_map               in out  nocopy  DATA_UTIL.gt_varchar_tab
    , p_param_str               in              varchar2
    , p_param_value             in              varchar2
    , p_append_to_map           in              number default 1
    )
    ;
    --------------------------------------------------------
    -- execute dynamic sql
    --------------------------------------------------------
    procedure prc_execute_sql
    ( p_sql                     in out  nocopy  varchar2
    , p_map_str                 in              DATA_UTIL.gt_varchar_tab
    , p_results                 out             SYS_REFCURSOR
    )
    ;
END DATA_UTIL;
/

下面是包的主体

• 记录日志

  由于动态sql实在难以维护，采用了github上一个logger工具，将输入值跟生成的sql记录下来
  logger工具

• .绑定变量并执行
  采用了oracle工具包：dbms_sql

CREATE OR REPLACE PACKAGE BODY DATA_UTIL
AS
    -- replace parameter with the actual value 
    -- parameter is required and should be surrounded by double lines '__'
    procedure prc_repl_param
    ( p_text                    in out  nocopy  varchar2 
    , p_param_str               in              varchar2
    , p_param_value             in              varchar2
    )
    is
      lv_scope logger_logs.scope%type := lower($$PLSQL_UNIT) || '.' || 'DATA_UTIL.prc_repl_param';
    begin
      logger.log(p_param_str||'='||substr(p_param_value,1,10)||'...',lv_scope)
      ;
      if trim(p_param_value) is not null then
        p_text := regexp_replace(p_text, '<\/?'||p_param_str||'?>',null,1,0,'in');
        p_text := regexp_replace(p_text, '__'||p_param_str||'__',p_param_value)
        ;
      else
        p_text := regexp_replace(p_text, '<'||p_param_str||'>.*<\/'||p_param_str||'>',null,1,0,'in');
      end if
      ;
    end prc_repl_param
    ;

    procedure prc_eval_param
    ( p_text                    in out  nocopy  varchar2 
    , p_param_map               in out  nocopy  DATA_UTIL.gt_varchar_tab
    , p_param_str               in              varchar2
    , p_param_value             in              varchar2
    , p_append_to_map           in              number default 1
    )
    is
      lv_scope logger_logs.scope%type := lower($$PLSQL_UNIT) || '.' || 'DATA_UTIL.prc_eval_param';
    begin
      logger.log('Parameter:'||p_param_str||'=['||p_param_value||']',lv_scope)
      ;
      if trim(p_param_value) is not null then
        p_text := regexp_replace(p_text, '<\/?'||p_param_str||'?>',null,1,0,'in');
        if p_append_to_map = 1 then
          p_param_map(p_param_str) := p_param_value;
        end if;
      else
        p_text := regexp_replace(p_text, '<'||p_param_str||'>.*?<\/'||p_param_str||'>',null,1,0,'in');
      end if
      ;
    end prc_eval_param
    ;

    procedure prc_execute_sql
    ( p_sql                     in out  nocopy  varchar2 
    , p_map_str                 in DATA_UTIL.gt_varchar_tab
    , p_results                 out SYS_REFCURSOR
    )
    is
      lv_scope logger_logs.scope%type := lower($$PLSQL_UNIT) || '.' || 'DATA_UTIL.prc_repl_param';

      lv_curid                number;
      lv_ret                  number;
      lv_map_idx              varchar2(300);
    begin
      logger.log('Execute SQL' ,lv_scope)
      ;
      ----------------------------------
      -- open cursor
      ----------------------------------
      lv_curid := dbms_sql.open_cursor;
      ----------------------------------
      -- parse Dynamic SQL
      ----------------------------------
      dbms_sql.parse(lv_curid, p_sql, dbms_sql.native);

      ----------------------------------
      ---- Bind variables which is not null
      ----------------------------------
      lv_map_idx := p_map_str.first;
      while (lv_map_idx is not null) 
      loop
        dbms_sql.bind_variable(lv_curid, lv_map_idx, p_map_str(lv_map_idx));
        lv_map_idx := p_map_str.next(lv_map_idx);
      end loop;
      ----------------------------------
      ---- execute Dynamic SQL
      ----------------------------------    
      lv_ret := dbms_sql.execute(lv_curid);
      ----------------------------------
      --  converts a SQL cursor number to a sys_refcursor
      ---------------------------------- 
      p_results := dbms_sql.to_refcursor(lv_curid)
      ;
    end prc_execute_sql
    ;

END DATA_UTIL;
/

测试

• 表结构

表结构使用了经典的emp表 emp表

• 日志查看
  logger会将动态sql记录在表logger_logs里面
  select * from logger_logs;

declare
  lv_scope   logger_logs.scope%type := lower($$PLSQL_UNIT) || '.' ||
                                       'PRC_LIST';
  lv_params  logger.tab_param;
  lv_curid   number;
  lv_ret     number;
  lv_sql_str varchar2(32000);
  lv_map_str DATA_UTIL.gt_varchar_tab;
  lv_map_idx varchar2(300);
  ----这里模拟几个输入值
  p_empno      number := 7839;
  p_ename      varchar2(10) := 'KING';
  p_hasno_comm number(1) default 1;
  p_resultset  SYS_REFCURSOR;
  l_rec        emp%rowtype;
begin
  --------------------------------------------------
  ----Recording parameters,set unique identifier for session
  --------------------------------------------------
  dbms_session.set_identifier(systimestamp);
  logger.append_param(lv_params, 'p_empno', p_empno);
  logger.append_param(lv_params, 'p_ename', p_ename);
  logger.append_param(lv_params, 'p_hasno_comm', p_hasno_comm);
  ----begin scope
  logger.log('START', lv_scope, null, lv_params);
  lv_sql_str := 'select * from emp where  
                                   <p_empno> empno = :p_empno  </p_empno> 
                                   <p_ename> and ename = :p_ename  </p_ename>  
                                   <p_hasno_comm> and  comm is null </p_hasno_comm>  
                                   ';
  DATA_UTIL.prc_eval_param(lv_sql_str, lv_map_str, 'p_empno', p_empno);
  DATA_UTIL.prc_eval_param(lv_sql_str, lv_map_str, 'p_ename', p_ename);
  DATA_UTIL.prc_eval_param(lv_sql_str,
                           lv_map_str,
                           'p_hasno_comm',
                           p_hasno_comm,
                           0);
  ----execute main sql
  DATA_UTIL.prc_execute_sql(lv_sql_str,
                            lv_map_str,
                            p_results => p_resultset);
  LOOP
    FETCH p_resultset
      INTO l_rec;
    EXIT WHEN p_resultset%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(l_rec.ename || ' ' || l_rec.empno);
  END LOOP;
  logger.log(lv_sql_str, lv_scope);
  ----scope end
  logger.log('END', lv_scope);
end;