带排序的oracle分页存储过程

 输入order by 的sqeuence是,应该为“ desc”或者“ asc”

若输入两个order by则,v_order_field=" a[sequence] ,order by b "

CREATE OR REPLACE PROCEDURE TABLEPAGE_SELECT(v_page_size  int, --the size of a page of list <p>                      v_current_pageint,--thecurrentpageoflist</p> <p>                      v_table_namevarchar2,--thetalbename</p> <p>                      v_order_field varchar2,--theorderfield</p> <p>                      v_order_sequencevarchar2,--theordersequenceshouldby"_desc"or"_asc",_isblank.</p> <p>                      --v_sql_select varchar2,--theselectsqlforprocedure</p> <p>                      --v_sql_count  varchar2,--thecountsqlforprocedure</p> <p>                      --v_out_recordcountOUTint,--thenumofreturnrows</p> <p>                      p_cursorOUTrefcursor_pkg.return_cursor)as</p> <p> v_sql    varchar2(3000);--thesqlforselectallrowsoflist</p> <p> v_sql_count varchar2(3000);--thecountsqlforprocedure</p> <p> v_sql_order varchar2(2000);--theorderoflist</p> <p> v_count   int;--theamountrowsfooriginallist</p> <p> v_endrownum int;--theendrownumofthecurrentpage</p> <p> v_startrownumint;--thestartrownumofthecurrentpage</p> <p>BEGIN</p> <p> ----settheorderoflist</p> <p> if v_order_field!='NO'then</p> <p>  v_sql_order:='ORDERBY'||v_order_field||''||v_order_sequence;</p> <p> else</p> <p>  v_sql_order:='';</p> <p> endif;</p> <p> ----catchtheamountrowsoflist</p> <p> v_sql_count:='SELECTCOUNT(ROWNUM)FROM'||v_table_name;</p> <p> executeimmediatev_sql_countintov_count;</p> <p> --v_out_recordcount:=v_count;</p> <p> ----setthevalueofstartandendrow</p> <p> ifv_order_sequence='desc'then</p> <p>  v_endrownum:=v_count-(v_current_page-1)*v_page_size;</p> <p>  v_startrownum:=v_endrownum-v_page_size+1;</p> <p> else</p> <p>  v_endrownum:=v_current_page*v_page_size;</p> <p>  v_startrownum:=v_endrownum-v_page_size+1;</p> <p> endif;</p> <p> ----thesqlforpageslide</p> <p> v_sql:='SELECT*FROM(SELECT'||v_table_name||'.*,rownumrnFROM'||v_table_name||'WHERErownum<='||</p> <p>     to_char(v_endrownum)||''||v_sql_order||') WHERErn>='||</p> <p>     to_char(v_startrownum)||''||v_sql_order;</p> <p> openp_cursorforv_sql;</p> <p>ENDTABLEPAGE_SELECT;

相关推荐