熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

oracle存儲過程分頁代碼

2013-11-13 16:08:08  來源: Oracle 

  oracle存儲過程分頁代碼是怎麼實現的我們來看下實驗小編為您整理的方法吧!

  /*******存儲過程分頁代碼**********/

  包頭

  create or replace package pck_my is

  type c_my is ref cursor;

  procedure page_moed(

  v_table in varchar 表名

  current_page in out number當前頁

  pageSize in out number頁行數

  total out number總行數

  countPage out number總頁數

  c_cursor out pck_myc_my游標

  );

  end pck_my;

  body

  create or replace package body pck_my as

  procedure page_moed(

  v_table in varchar

  current_page in out number

  pageSize in out number

  total out number

  countPage out number

  c_cursor out pck_myc_my

  )is

  v_sql varchar();

  v_max number;

  v_min number;

  e_table exception;

  begin

  判斷參數

  if v_table is null then

  raise e_table;

  return;

  end if;

  if current_page is null then

  current_page:=;

  end if;

  if pageSize<= then

  pageSize:=;

  end if;

  計算 最大行 最小行

  v_max:=(current_page+)*pageSize;

  v_min:=current_page*pageSize;

  獲取數據

  v_sql:= select *

  from (select filminfo* rownum as t from || v_table || where rownum <=|| v_max||)

  where t > ||v_min;

  open c_cursor for v_sql;

  計算總行數

  v_sql:=select count(*) from || v_table;

  execute immediate v_sql into total;

  計算總頁數

  if mod(totalpageSize)= then

  countPage:=total/pageSize;

  else

  countPage:=total/pageSize+;

  end if;

  exception

  exception

  when e_table then

  dbms_outputput_line(表名不能為空);

  end;

  end pck_my;

   exet

  select * from filminfo

  java測試代碼

  [html]

  package comrui;

  import javasqlCallableStatement;

  import javasqlConnection;

  import javasqlDriverManager;

  import javasqlResultSet;

  import javasqlSQLException;

  public class Pckage {

  /**

  * @param args

  */

  public static void main(String[] args) {

  Connection con;

  ResultSet rs;

  CallableStatement cs;

  try {

  ClassforName(oraclejdbcdriverOracleDriver);

  con=DriverManagergetConnection(jdbc:oracle:thin:@localhost::ABCtenementrui);

  String sql={call pck_mypage_moed(??????)};

  cs=conprepareCall(sql);

  //指定類型

  /* v_table in varchar

  current_page in out number

  pageSize in out number

  total out number

  countPage out number

  c_cursor out pck_myc_my

  */

  //cssetString( null);

  cssetString( filminfo);

  cssetInt( );

  cssetInt();

  csregisterOutParameter(oraclejdbcOracleTypesNUMBER);

  csregisterOutParameter( oraclejdbcOracleTypesNUMBER);

  csregisterOutParameter( oraclejdbcOracleTypesCURSOR);

  csexecute();

  int total=csgetInt();//總行數

  int countPage=csgetInt();//總頁數

  rs=(ResultSet)csgetObject();//result

  Systemoutprintln(總行數+total+\t總頁數+countPage);

  Systemoutprintln();

  while(rsnext()){

  Systemoutprintln(FILMNAME:+rsgetString(FILMNAME)+\tFILMID:+rsgetInt(FILMID));

  }

  } catch (ClassNotFoundException e) {

  // TODO Autogenerated catch block

  eprintStackTrace();

  } catch (SQLException e) {

  // TODO Autogenerated catch block

  eprintStackTrace();

  }

  }

  }


From:http://tw.wingwit.com/Article/program/Oracle/201311/17872.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.