1.创建表
SQL> create table t1( id number);
表已创建。
SQL> commit;
提交完成。
2.插入数据
insert into t1 values(1)
...
insert into t1 values(10)
3.创建程序包
create or replace package pkg_query as
type cur_query is ref cursor;
end pkg_query;
4.创建存储过程
create OR REPLACE PROCEDURE prc_query(
p_tableName in varchar2,--表名
p_strwhere in varchar2,--查询条件
p_orderColumn in varchar2,--排序的列
p_orderStyle in varchar2,--排序方式
p_curPage in out Number,--当前页
p_pageSize in out Number,--每页显示记录条数
p_totalRecords out Number,--总记录数
p_totalPages out Number,--总页数
v_cur out pkg_query.cur_query)--返回的结果集
IS
v_sql VARchar2(1000) := '';--sql语句
v_startRecord Number(4);--开始显示的记录条数
v_endRecord Number(4);--结束显示的记录条数
BEGIN
--记录中总记录条数
v_sql := ' select TO_NUMBER(count(*)) FROM ' || p_tableName || ' where 1=1 ';
IF p_strwhere IS NOT NULL or p_strwhere <> '' THEN
v_sql := v_sql || p_strwhere;
END IF;
execute IMMEDIATE v_sql INTO p_totalRecords;
--验证页面记录大小
IF p_pageSize < 0 THEN
p_pageSize := 0;
END IF;
--根据页大小计算总页数
IF MOD(p_totalRecords,p_pageSize) = 0 THEN
p_totalPages := p_totalRecords / p_pageSize;
ELSE
p_totalPages := p_totalRecords / p_pageSize + 1;
END IF;
--验证页号
IF p_curPage < 1 THEN
p_curPage := 1;
END IF;
IF p_curPage > p_totalPages THEN
p_curPage := p_totalPages;
END IF;
--实现分页查询
v_startRecord := (p_curPage - 1) * p_pageSize+1;
v_endRecord := p_curPage * p_pageSize;
v_sql := ' select * FROM (select A.*, rownum r FROM ' || ' (select * FROM ' || p_tableName;
IF p_strwhere IS NOT NULL or p_strwhere <> '' THEN
v_sql := v_sql || ' where 1=1 ' || p_strwhere;
END IF;
IF p_orderColumn IS NOT NULL or p_orderColumn <> '' THEN
v_sql := v_sql || ' ORDER BY ' || p_orderColumn || ' ' || p_orderStyle;
END IF;
v_sql := v_sql || ' ) A where rownum <= ' || v_endRecord || ' ) B where r >= ' || v_startRecord;
OPEN v_cur FOR v_sql;
END prc_query;
/
5.创建Java-JDBC
package page_return_list;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import oracle.jdbc.OracleTypes;
public class Search {
/**
* author david
* @param args
*/
public static void main(String[] args) {
String driver = "oracle.jdbc.driver.OracleDriver";
String strUrl = "jdbc:oracle:thin:@127.0.0.1:1521:myorcl";
Statement stmt = null;
ResultSet rs = null;
Connection conn = null;
String p_tableName = "t1";
String p_strwhere = "";
String p_orderColumn = "id";
String p_orderStyle = "asc";
Integer p_curPage = 10; //负数时能正确返回首页数据, 但超过总页数时, 却不能返回未页数据, 有待修正pl/sql语句
Integer p_pageSize = 3;
Integer p_totalRecords = 0;
Integer p_totalPages = 0;
try {
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, "scott", "tiger");
CallableStatement proc = null;
String sql = "{ call prc_query(?,?,?,?,?,?,?,?,?) }";
proc = conn.prepareCall(sql);
proc.setString(1, p_tableName);
proc.setString(2, p_strwhere);
proc.setString(3, p_orderColumn);
proc.setString(4, p_orderStyle);
proc.setInt(5, p_curPage);
proc.setInt(6, p_pageSize);
proc.registerOutParameter(5, java.sql.Types.INTEGER);
proc.registerOutParameter(6, java.sql.Types.INTEGER);
proc.registerOutParameter(7, java.sql.Types.INTEGER);
proc.registerOutParameter(8, java.sql.Types.INTEGER);
proc.registerOutParameter(9, OracleTypes.CURSOR);
proc.execute();
p_curPage = proc.getInt(5);
p_pageSize = proc.getInt(6);
p_totalRecords = proc.getInt(7);
p_totalPages = proc.getInt(8);
System.out.println("p_totalRecords: " + p_totalRecords + " , "
+ "p_totalPages: " + p_totalPages + ", p_curPage: "
+ p_curPage + ", p_pageSize: " + p_pageSize);
//获得游标
proc.registerOutParameter(9, OracleTypes.CURSOR);
rs = (ResultSet) proc.getObject(9);
while (rs.next()) {
System.out.println(rs.getInt(1));
}
} catch (SQLException ex2) {
ex2.printStackTrace();
} catch (Exception ex2) {
ex2.printStackTrace();
} finally {
try {
if (rs != null) {
rs.close();
if (stmt != null) {
stmt.close();
}
if (conn != null) {
conn.close();
}
}
} catch (SQLException ex1) {
}
}
}
}