Constructing a General dynamic page query using dynamic SQL

Because of the flexibility inherent in dynamic SQL, it is easy to construct generic and reusable code, such as dynamic field queries based on a table, in a common sense;

But everything has its pros and cons; first, dynamic SQL statements cannot check that SQL is correct at compile time, and must wait until the runtime to discover the problem; second, static SQL is a parse, executed multiple times, although dynamic SQL can also use the way of binding variables, but also brings some unexpected performance problems, For example, a binding variable provides an incorrect execution plan when there is data skew in the table that SQL is accessing, and the last dynamic SQL statement is less readable and harder to maintain.

Here we take the classic pagination feature as an example:

CREATE OR REPLACE Procedure sp_exec_dynamic_page (I_tablename VARCHAR2,--table name Employees E,departments D I_tablecolumn VARCHAR2,--Query column A.employee_id,b.department_name i_where VARCHAR2,--Query criteria B.departm Ent_name like ' s% ' I_ordercolumn VARCHAR2,--Sort b.department_name desc i_pagesize number, per page size I_curpage number,--current page 6 o_rowcount out,--Returns the total bar O_pagecount out
    
  -Returns the total number of pages o_cursor out Ref_cursor.t_retdataset--Returns the paging result set) 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 there is no table name, return the exception message directly--if there are no fields, all fields if I_tablecolumn is not NULL THEN v_t AblecolUmn:=i_tablecolumn;
    
  ELSE v_tablecolumn:= ' * ';
    
  End IF; --You can have no where condition IF i_where is not NULL THEN v_where:= ' where 1=1 and ' | | i_where| | '
    
  ';
    
  ELSE v_where:= ' where 1=1 ';
    
  End IF;
    
  --there can be no order by condition IF I_ordercolumn is NULL THEN v_ordercolumn:= '; ELSE v_ordercolumn:= ' ORDER BY ' | |
    
  I_ordercolumn;
    
  End IF;
    
  --Default Home if I_curpage is NULL OR i_curpage<1 THEN v_curpage:=1 If no query page is specified;
    
  ELSE V_curpage:=i_curpage;
    
  End IF;
    
  --If you do not specify the number of records per page, the default is 10 records if I_pagesize is NULL THEN v_pagesize:=10;
    
  ELSE v_pagesize:=i_pagesize;
    
  End IF; --Total number of queries v_count_sql:= ' SELECT count (*) from ' | | i_tablename| |
    
  V_where; --Constructing the most core query statement v_select_sql:= ' (SELECT ' | | v_tablecolumn| | ' From ' | | i_tablename| | v_where| | v_ordercolumn| | ')
    
     
    
  E '; --Execute Query, query total number of bars execute IMMEDIATE v_count_sqL into O_rowcount; Dbms_output. Put_Line (' query total number of sql=> ' | |
    
  V_COUNT_SQL); Dbms_output. Put_Line (' query total number of count= ' | |
    
     
    
  O_rowcount);
    
  --Get the total number of pages and handle the 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 the current page is greater than the maximum number of pages, take the maximum number of pages if I_curpage>o_pagecount THEN v_curpage:=o_pagecount;
    
  End IF;
    
  --Set the number of records to start at the end V_startrecord: = (v_curpage-1) * v_pagesize + 1;
    
  V_endrecord: = V_curpage * v_pagesize;
    
                --Complete dynamic SQL statement spelling 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 (' Query sql=> ' | | V_SELECT_SQL);
    
     
    
OPEN o_cursor for V_select_sql; End;

This article from the "Life is a drunken" blog, please be sure to retain this source http://baoqiangwang.blog.51cto.com/1554549/530544

See more highlights of this column: http://www.bianceng.cn/database/basis/