客户要求把所有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;