ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 动态SQL——构造通用动态页面查询

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

原创 Linux操作系统 作者:bq_wang 时间:2011-03-30 09:30:16 0 删除 编辑

节选自《剑破冰山——oracle开发艺术》一书,书籍销售地址:
http://product.china-pub.com/197199
http://product.dangdang.com/product.aspx?product_id=20981228&ref=book-02-L
http://www.amazon.cn/mn/detailApp?uid=477-1785695-2022958&ref=RK&asin=B004CMIVM6

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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/6517/viewspace-691274/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2007-12-07

  • 博文量
    412
  • 访问量
    1133827