使用动态SQL构造通用动态页面查询

由于动态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;

本文出自 “不胜人生一场醉” 博客，请务必保留此出处http://baoqiangwang.blog.51cto.com/1554549/530544

查看本栏目更多精彩内容：http://www.bianceng.cn/database/basis/

以上是小编为您精心准备的的内容，在的博客、问答、公众号、人物、课程等栏目也有的相关内容，欢迎继续使用右上角搜索按钮进行搜索sql
， 查询
， 动态查询
， 动态
， 字段
， varchar2
， sql动态查询
varchar
sql动态构造条件子句、建筑构造通用图集、建筑构造通用图集88j、条件构造器 通用查询、动态构造二维数组，以便于您获取更多的相关知识。