关于分页,想必是每一程序猿都会遇到的问题,解决办法有很多, 接下来就让我们尝试下 oracle 存储过程实现,直接在数据库层面实现,重点在于高效
2接下来创建包体,和存储过程的实现
1首先创建 存储过程用的包,以及声明存储过程
- --创建包
- -- Author : Sugar
- -- Created : 2014-07-04
- -- Purpose : 分页过程
- create or replace package p_page is
- TYPE type_cur IS REF CURSOR; -- 定义游标变量用于返回记录集
- PROCEDURE Pagination(
- Pindex in number, -- 分页索引
- Psql in varchar2, -- 产生 dataset 的 sql 语句
- Psize in number, -- 页面大小
- Pcount out number, -- 返回分页总数
- v_cur out type_cur -- 返回当前页数据记录
- );
- procedure PageRecordsCount(
- Psqlcount in varchar2, -- 产生 dataset 的 sql 语句
- Prcount out number -- 返回记录总数
- );
- end p_page;
--创建包
-- Author : Sugar
-- Created : 2014-07-04
-- Purpose : 分页过程
create or replace package p_page is
TYPE type_cur IS REF CURSOR; -- 定义游标变量用于返回记录集
PROCEDURE Pagination(
Pindex in number, -- 分页索引
Psql in varchar2, -- 产生 dataset 的 sql 语句
Psize in number, -- 页面大小
Pcount out number, -- 返回分页总数
v_cur out type_cur -- 返回当前页数据记录
);
procedure PageRecordsCount(
Psqlcount in varchar2, -- 产生 dataset 的 sql 语句
Prcount out number -- 返回记录总数
);
end p_page;
2接下来创建包体,和存储过程的实现
- --创建包体
- create or replace package body p_page is
- PROCEDURE Pagination(
- Pindex in number,
- Psql in varchar2,
- Psize in number,
- Pcount out number,
- v_cur out type_cur
- )
- AS
- v_sql VARCHAR2(1000);
- v_count number;
- v_Plow number;
- v_Phei number;
- Begin
- ------------------------------------------------------------ 取分页总数
- v_sql := 'select count(*) from (' || Psql || ')';
- execute immediate v_sql into v_count;
- Pcount := ceil(v_count/Psize);
- ------------------------------------------------------------ 显示任意页内容
- v_Phei := Pindex * Psize + Psize;--得到记录上限
- v_Plow := v_Phei - Psize + 1; --得到记录下限
- --例如: Psql := 'select rownum rn,t.* from emp t' ; -- 要求必须包含 rownum 字段
- v_sql := 'select * from (' || Psql || ') where rn between ' || v_Plow || ' and ' || v_Phei ;
- open v_cur for v_sql;
- End Pagination;
- procedure PageRecordsCount(
- Psqlcount in varchar2,
- Prcount out number
- )
- as
- v_sql varchar2(1000);
- v_prcount number;
- begin
- v_sql := 'select count(*) from (' || Psqlcount || ')';
- execute immediate v_sql into v_prcount;
- Prcount := v_prcount; -- 返回记录总数
- end PageRecordsCount;
- end p_page;
--创建包体
create or replace package body p_page is
PROCEDURE Pagination(
Pindex in number,
Psql in varchar2,
Psize in number,
Pcount out number,
v_cur out type_cur
)
AS
v_sql VARCHAR2(1000);
v_count number;
v_Plow number;
v_Phei number;
Begin
------------------------------------------------------------ 取分页总数
v_sql := 'select count(*) from (' || Psql || ')';
execute immediate v_sql into v_count;
Pcount := ceil(v_count/Psize);
------------------------------------------------------------ 显示任意页内容
v_Phei := Pindex * Psize + Psize;--得到记录上限
v_Plow := v_Phei - Psize + 1; --得到记录下限
--例如: Psql := 'select rownum rn,t.* from emp t' ; -- 要求必须包含 rownum 字段
v_sql := 'select * from (' || Psql || ') where rn between ' || v_Plow || ' and ' || v_Phei ;
open v_cur for v_sql;
End Pagination;
procedure PageRecordsCount(
Psqlcount in varchar2,
Prcount out number
)
as
v_sql varchar2(1000);
v_prcount number;
begin
v_sql := 'select count(*) from (' || Psqlcount || ')';
execute immediate v_sql into v_prcount;
Prcount := v_prcount; -- 返回记录总数
end PageRecordsCount;
end p_page;
我们可是用系统自带的 emp表去测试 ,我的测试结果如下:
4 测试成功后 ,我们接下来 将存储过程应用到 java工程当中去
- import java.sql.CallableStatement;
- import java.sql.Connection;
- import java.sql.DriverManager;
- import java.sql.ResultSet;
- import java.sql.SQLException;
- import java.sql.Statement;
- public class TestProcedureDemo3 {
- public static void main(String[] args) {
- String driver = "oracle.jdbc.driver.OracleDriver";
- String strUrl = "jdbc:oracle:thin:@192.168.1.100:1521:orcl"; //本机IP不能用localhost取代
- Statement stmt = null; //创建statement
- ResultSet rs = null; //创建结果集
- Connection conn = null; //构造数据库 会话
- CallableStatement proc = null; // 创建 CallableStatement 对象 用于调用存储过程
- try {
- Class.forName(driver);
- conn = DriverManager.getConnection(strUrl, "scott", "tiger");
- //调用存储过程 PAGINATION
- proc = conn.prepareCall("{ call P_PAGE.PAGINATION(?,?,?,?,?) }");
- //设置输入参数
- proc.setInt(1, 0);
- proc.setString(2, "select rownum rn,t.* from emp t");
- proc.setInt(3, 3);
- //绑定输出参数类型
- proc.registerOutParameter(4,oracle.jdbc.OracleTypes.NUMBER);
- proc.registerOutParameter(5,oracle.jdbc.OracleTypes.CURSOR);
- proc.execute();
- int number = proc.getInt(4);
- System.out.println("当前分页总数"+ number);
- rs = (ResultSet) proc.getObject(5);
- while (rs.next()) {
- System.out.println("<tr><td>" + rs.getString(1) +
- "</td><td>"
- + rs.getString(2)
- + "</td></tr>"+ rs.getString(3) +
- "</td></tr>"+ rs.getString(4) + "</td></tr>");
- }
- //调用存储过程 PAGERECORDSCOUNT
- proc = conn.prepareCall("{ call P_PAGE.PAGERECORDSCOUNT(?,?) }");
- proc.setString(1, " emp ");
- proc.registerOutParameter(2,oracle.jdbc.OracleTypes.NUMBER);
- proc.execute();
- int recordCount = proc.getInt(2);
- System.out.println("当前记录总数"+ recordCount);
- } 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) {
- }
- }
- }
- }
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestProcedureDemo3 {
public static void main(String[] args) {
String driver = "oracle.jdbc.driver.OracleDriver";
String strUrl = "jdbc:oracle:thin:@192.168.1.100:1521:orcl"; //本机IP不能用localhost取代
Statement stmt = null; //创建statement
ResultSet rs = null; //创建结果集
Connection conn = null; //构造数据库 会话
CallableStatement proc = null; // 创建 CallableStatement 对象 用于调用存储过程
try {
Class.forName(driver);
conn = DriverManager.getConnection(strUrl, "scott", "tiger");
//调用存储过程 PAGINATION
proc = conn.prepareCall("{ call P_PAGE.PAGINATION(?,?,?,?,?) }");
//设置输入参数
proc.setInt(1, 0);
proc.setString(2, "select rownum rn,t.* from emp t");
proc.setInt(3, 3);
//绑定输出参数类型
proc.registerOutParameter(4,oracle.jdbc.OracleTypes.NUMBER);
proc.registerOutParameter(5,oracle.jdbc.OracleTypes.CURSOR);
proc.execute();
int number = proc.getInt(4);
System.out.println("当前分页总数"+ number);
rs = (ResultSet) proc.getObject(5);
while (rs.next()) {
System.out.println("<tr><td>" + rs.getString(1) +
"</td><td>"
+ rs.getString(2)
+ "</td></tr>"+ rs.getString(3) +
"</td></tr>"+ rs.getString(4) + "</td></tr>");
}
//调用存储过程 PAGERECORDSCOUNT
proc = conn.prepareCall("{ call P_PAGE.PAGERECORDSCOUNT(?,?) }");
proc.setString(1, " emp ");
proc.registerOutParameter(2,oracle.jdbc.OracleTypes.NUMBER);
proc.execute();
int recordCount = proc.getInt(2);
System.out.println("当前记录总数"+ recordCount);
} 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) {
}
}
}
}
5 备注,java工程中需要导入 oracle 驱动包(odbc14.jar) 运行即可出现如下效果 ,应用成功
以上即是本人的学习笔记,虽然不多,但是非常适合oracle 初学者理解存储过程 ,非常具有实用效果 ,若有疑问,欢迎拍砖~~