Oracle Stored Procedure paging code

Oracle Stored Procedure paging code

[Html]/********* Stored Procedure paging code **********/-- Baotou create or replace package pck_my is type c_my is ref cursor; procedure page_moed (v_table in varchar2, -- table name current_page in out number, -- current page pageSize in out number, -- total number of page rows out number, -- total number of rows countPage out number, -- total number of pages c_cursor out pck_my.c_my -- cursor); end pck_my; -- body create or replace package body pck_my as procedure page_moed (v_table in varchar2, current _ Page in out number, pageSize in out number, total out number, countPage out number, c_cursor out pck_my.c_my) is v_ SQL varchar2 (1000); v_max number; v_min number; e_table exception; begin -- determine the parameter if v_table is null then raise e_table; -- return; end if; if current_page is null then current_page: = 1; end if; if pageSize <= 0 then pageSize: = 5; end if; -- calculate the smallest row of the largest row v_max: = (current_page + 1) * pageSize; v_min: = Current_page * pageSize; -- obtain data 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; -- calculate the total number of rows v_ SQL: = 'select count (*) from' | v_table; execute immediate v_ SQL into total; -- calculate the total number of pages if mod (total, pageSize) = 0 then countPage: = total/pageSize; else countPage: = total/pageSize + 1; end if; -- exception wh En e_table then dbms_output.put_line ('table name cannot be blank '); end pck_my; -- exet select * from filminfo java test code [html] package com. rui; import java. SQL. callableStatement; import java. SQL. connection; import java. SQL. driverManager; import java. SQL. resultSet; import java. SQL. SQLException; public class Pckage {/*** @ param args */public static void main (String [] args) {Connection con; ResultSet rs; Callabl EStatement cs; try {Class. forName ("oracle. jdbc. driver. oracleDriver "); con = DriverManager. getConnection ("jdbc: oracle: thin :@ localhost: 1521: ABC", "tenement", "rui "); string SQL = "{call pck_my.page_moed (?,?,?,?,?,?)} "; Cs = con. prepareCall (SQL); // specify the type/* v_table in varchar2, current_page in out number, pageSize in out number, total out number, countPage out number, c_cursor out pck_my.c_my * // cs. setString (1, null); cs. setString (1, "filminfo"); cs. setInt (2, 3); cs. setInt (3, 5); cs. registerOutParameter (4, oracle. jdbc. oracleTypes. NUMBER); cs. registerOutParameter (5, oracle. jdbc. oracleTypes. NUMBER); cs. registerOutParameter (6, oracle. jdbc. oracleTypes. CURSOR); cs.exe cute (); int total = cs. getInt (4); // The total number of rows int countPage = cs. getInt (5); // the total number of pages rs = (ResultSet) cs. getObject (6); // result System. out. println ("total number of rows:" + total + "\ t total number of pages" + countPage); System. out. println ("------------------------------------"); while (rs. next () {System. out. println ("FILMNAME:" + rs. getString ("FILMNAME") + "\ tFILMID:" + rs. getInt ("FILMID");} catch (ClassNotFoundException e) {// TODO Auto-generated catch block e. printStackTrace ();} catch (SQLException e) {// TODO Auto-generated catch block e. printStackTrace ();}}}