oracle 动态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;