帶排序的oracle分頁預存程序

來源:互聯網
上載者:User

  輸入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
                       v_current_page int, --the current page of list
                       v_table_name varchar2, --the talbe name
                       v_order_field varchar2,--the order field
                       v_order_sequence varchar2,--the order sequence should by "_desc"or "_asc",_is blank.
                       --v_sql_select  varchar2, --the select sql for procedure
                       --v_sql_count  varchar2, --the count sql for procedure
                       --v_out_recordcount OUT int, --the num of return rows
                       p_cursor OUT refcursor_pkg.return_cursor) as
 v_sql     varchar2(3000); --the sql for select all rows of list
 v_sql_count  varchar2(3000); --the count sql for procedure
 v_sql_order  varchar2(2000); --the order of list
 v_count    int; -- the amount rows fo original list
 v_endrownum  int; --the end row num of the current page
 v_startrownum int; --the start row num of the current page
BEGIN
 ----set the order of list
 if v_order_field!='NO' then
  v_sql_order :=' ORDER BY '|| v_order_field ||' '||v_order_sequence;
 else
   v_sql_order :='';
 end if;
 ----catch the amount rows of list
 v_sql_count:='SELECT COUNT(ROWNUM) FROM '||v_table_name;
 execute immediate v_sql_count into v_count;
 -- v_out_recordcount := v_count;
 ----set the value of start and end row
 if v_order_sequence='desc' then
  v_endrownum:=v_count-(v_current_page-1)*v_page_size;
  v_startrownum:=v_endrownum - v_page_size + 1;
 else
  v_endrownum:= v_current_page * v_page_size;
  v_startrownum := v_endrownum - v_page_size + 1;
 end if;
 ----the sql for page slide
 v_sql := 'SELECT * FROM (SELECT '||v_table_name||'.*, rownum rn FROM '||v_table_name||' WHERE rownum <= ' ||
      to_char(v_endrownum) ||' '|| v_sql_order||') WHERE rn >= ' ||
      to_char(v_startrownum)||' '||v_sql_order;
 open p_cursor for v_sql;
END TABLEPAGE_SELECT;


相關文章

Beyond APAC's No.1 Cloud

19.6% IaaS Market Share in Asia Pacific - Gartner IT Service report, 2018

Learn more >

Apsara Conference 2019

The Rise of Data Intelligence, September 25th - 27th, Hangzhou, China

Learn more >

Alibaba Cloud Free Trial

Learn and experience the power of Alibaba Cloud with a free trial worth $300-1200 USD

Learn more >

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。