由于动态SQL 特有的灵活性,我们很容易的按照某种共性去构造通用和重用功能的代码,例如基于某个表的动态字段查询;
但凡事有利有弊;首先动态SQL 语句无法在编译前期检查SQL 是否正确,必须等到运行期才会发现问题;其次静态SQL 是一次解析,多次执行,虽然动态SQL 也可以使用绑定变量的方式,但是也会带来一些意想不到的性能问题,例如绑定变量在SQL 要访问的表存在数据倾斜时会提供错误的执行计划;最后动态SQL 语句可读性较差,比较难以维护。
下面我们就以比较经典的分页功能为例:
CREATE OR REPLACE Procedure sp_exec_dynamic_page
(
i_tablename VARCHAR2, -- 表名 employees e,departments d
i_tablecolumn VARCHAR2, -- 查询列 a.employee_id,b.department_name
i_where VARCHAR2, -- 查询条件 b.department_name like 'S%'
i_ordercolumn VARCHAR2, -- 排序 b.department_name desc
i_pagesize NUMBER, -- 每页大小 20
i_curpage NUMBER, -- 当前页 6
o_rowcount OUT NUMBER, -- 返回总条数
o_pagecount OUT NUMBER, -- 返回总页数
o_cursor OUT ref_cursor.t_RetDataSet -- 返回分页结果集
)
IS
v_startrecord INT;
v_endrecord INT;
v_pagesize INT;
v_curpage INT;
v_tablecolumn VARCHAR2(2000);
v_where VARCHAR2(2000);
v_ordercolumn VARCHAR2(200);
v_count_sql VARCHAR2(2000);
v_select_sql VARCHAR2(2000);
BEGIN
-- 如果没有表名称,则直接返回异常消息
-- 如果没有字段,则表示全部字段
IF i_tablecolumn IS NOT NULL THEN
v_tablecolumn:=i_tablecolumn;
ELSE
v_tablecolumn:=' * ';
END IF;
-- 可以没有 WHERE 条件
IF i_where IS NOT NULL THEN
v_where:=' WHERE 1=1 AND '||i_where||' ';
ELSE
v_where:=' WHERE 1=1 ';
END IF;
-- 可以没有ORDER BY 条件
IF i_ordercolumn IS NULL THEN
v_ordercolumn:=' ';
ELSE
v_ordercolumn:=' ORDER BY '||i_ordercolumn;
END IF;
-- 如果未指定查询页,则默认为首页
IF i_curpage IS NULL OR i_curpage<1 THEN
v_curpage:=1;
ELSE
v_curpage:=i_curpage;
END IF;
-- 如果未指定每页记录数,则默认为10 条记录
IF i_pagesize IS NULL THEN
v_pagesize:=10;
ELSE
v_pagesize:=i_pagesize;
END IF;
-- 查询总条数
v_count_sql:='SELECT COUNT(*) FROM '||i_tablename||v_where;
-- 构造最核心的查询语句
v_select_sql:='(SELECT '||v_tablecolumn||' FROM '||i_tablename||v_where||v_ordercolumn||') e';
-- 执行查询, 查询总条数
EXECUTE IMMEDIATE v_count_sql INTO o_rowcount;
DBMS_OUTPUT.PUT_LINE(' 查询总条数SQL=>'||v_count_sql);
DBMS_OUTPUT.PUT_LINE(' 查询总条数Count='||o_rowcount);
-- 得到总页数,并进行处理
IF MOD(o_rowcount,i_pagesize)=0 THEN
o_pagecount:=o_rowcount/i_pagesize;
ELSE
o_pagecount:=FLOOR(o_rowcount/i_pagesize)+1;
END IF;
-- 如果当前页大于最大页数,则取最大页数
IF i_curpage>o_pagecount THEN
v_curpage:=o_pagecount;
END IF;
-- 设置开始结束的记录数
v_startRecord := (v_curpage - 1) * v_pagesize + 1;
v_endRecord := v_curpage * v_pagesize;
-- 进行完整的动态SQL 语句拼写
v_select_sql:='SELECT * FROM '||
'( '||
' SELECT e.*,ROWNUM rn '||
' FROM '||
v_select_sql||
' WHERE ROWNUM<='||v_endRecord||
') '||
' WHERE rn>='||v_startRecord;
DBMS_OUTPUT.PUT_LINE(' 查询SQL=>'||v_select_sql);
OPEN o_cursor FOR v_select_sql;
END;