存储过程如下
create or replace package page
as
type mytype is ref cursor;
procedure mypro3(table_name in varchar2,count_num in int,page_num in int,page_num_value out int,mycur in out mytype);
end;
--创建包体--
create or replace package body page
as
procedure mypro3(table_name in varchar2,count_num in int,page_num in int,page_num_value out int,mycur in out mytype)
as
v_sql varchar(100);
v_sql_value varchar(100);
count_sum int;
page_sum int;
maxnum int;
minnum int;
begin
v_sql:='select count(*) from (' || table_name || ')';
execute immediate v_sql into count_sum;
if mod(count_sum,count_num)!=0 then
page_sum:= count_sum/count_num+1;
else
page_sum:= count_sum/count_num;
end if;
if page_num>page_sum then
page_num_value:=page_sum;
else if page_num<1 then
page_num_value:=1;
else
page_num_value:= page_num;
end if;
end if;
maxnum:=count_num*page_num_value;
minnum:=maxnum-count_num+1;
v_sql_value:='SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (SELECT * FROM ' || table_name || ') A WHERE ROWNUM <= ' || maxnum ||')WHERE RN >= ' || minnum;
open mycur for v_sql_value;
end;
end;select count(*) from scott.empSELECT * FROM ( SELECT A.*, ROWNUM RN FROM (SELECT * FROM scott.emp ORDER by empno) A WHERE ROWNUM <= 7)WHERE RN >= 3;调用
package DB;
import java.sql.*;import oracle.jdbc.OracleTypes;
public class test { /**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
Connection conn=DBCon.getCon();
ResultSet rs = null;
CallableStatement cstmt = null;
try {
cstmt = conn.prepareCall("{call page.mypro3(?,?,?,?,?)}");
cstmt.setString(1, "scott.emp");
cstmt.setInt(2, 3);
cstmt.setInt(3, 5);
cstmt.registerOutParameter(4, OracleTypes.INTEGER);
cstmt.registerOutParameter(5, OracleTypes.CURSOR);
cstmt.executeQuery();
System.out.println("第"+cstmt.getObject(4)+"页");
rs = (ResultSet) cstmt.getObject(5);
while (rs.next()) {
System.out.println(rs.getString(1) + " -- "
+ rs.getString(2) + " -- " + rs.getString(3));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} }}
存储
create or replace package page
as
type mytype is ref cursor;
procedure mypro3(table_name in varchar2,count_num in int,page_num in int,page_num_value out int,mycur in out mytype);
end;
--创建包体--
create or replace package body page
as
procedure mypro3(table_name in varchar2,count_num in int,page_num in int,page_num_value out int,mycur in out mytype)
as
v_sql varchar(100);
v_sql_value varchar(100);
count_sum int;
page_sum int;
maxnum int;
minnum int;
begin
v_sql:='select count(*) from (' || table_name || ')';
execute immediate v_sql into count_sum;
if mod(count_sum,count_num)!=0 then
page_sum:= count_sum/count_num+1;
else
page_sum:= count_sum/count_num;
end if;
if page_num>page_sum then
page_num_value:=page_sum;
else if page_num<1 then
page_num_value:=1;
else
page_num_value:= page_num;
end if;
end if;
maxnum:=count_num*page_num_value;
minnum:=maxnum-count_num+1;
v_sql_value:='SELECT * FROM ( SELECT A.*, ROWNUM RN FROM (SELECT * FROM ' || table_name || ') A WHERE ROWNUM <= ' || maxnum ||')WHERE RN >= ' || minnum;
open mycur for v_sql_value;
end;
end;select count(*) from scott.empSELECT * FROM ( SELECT A.*, ROWNUM RN FROM (SELECT * FROM scott.emp ORDER by empno) A WHERE ROWNUM <= 7)WHERE RN >= 3;调用
package DB;
import java.sql.*;import oracle.jdbc.OracleTypes;
public class test { /**
* @param args
*/
public static void main(String[] args) {
// TODO Auto-generated method stub
Connection conn=DBCon.getCon();
ResultSet rs = null;
CallableStatement cstmt = null;
try {
cstmt = conn.prepareCall("{call page.mypro3(?,?,?,?,?)}");
cstmt.setString(1, "scott.emp");
cstmt.setInt(2, 3);
cstmt.setInt(3, 5);
cstmt.registerOutParameter(4, OracleTypes.INTEGER);
cstmt.registerOutParameter(5, OracleTypes.CURSOR);
cstmt.executeQuery();
System.out.println("第"+cstmt.getObject(4)+"页");
rs = (ResultSet) cstmt.getObject(5);
while (rs.next()) {
System.out.println(rs.getString(1) + " -- "
+ rs.getString(2) + " -- " + rs.getString(3));
}
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} }}
存储
改成 .v_sql_value varchar(1000);试试?