Oracle 高效分页存储过程实例含javademo

最新推荐文章于 2017-01-17 09:28:00 发布

穹劲天空

最新推荐文章于 2017-01-17 09:28:00 发布

阅读量1.4k

点赞数

文章标签： oracle 数据库存储 plsql

关于分页，想必是每一程序猿都会遇到的问题，解决办法有很多，接下来就让我们尝试下 oracle 存储过程实现，直接在数据库层面实现，重点在于高效

1首先创建存储过程用的包，以及声明存储过程

[sql] view plain copy pr int ?

--创建包
-- 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接下来创建包体，和存储过程的实现

[sql] view plain copy print ?

--创建包体
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;

3现在我们的 sql脚本已经完成，接下来可以现在 pl/sql中测试一遍我们写的存储过程是否正确，pl/sql 集成了测试工具，我们直接使用即可

我们可是用系统自带的 emp表去测试，我的测试结果如下：

4 测试成功后，我们接下来将存储过程应用到 java工程当中去

[java] view plain copy print ?

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 初学者理解存储过程，非常具有实用效果，若有疑问，欢迎拍砖~~

穹劲天空

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle 高效分页存储过程实例含javademo

关于分页，想必是每一程序猿都会遇到的问题，解决办法有很多，接下来就让我们尝试下 oracle 存储过程实现，直接在数据库层面实现，重点在于高效 1首先创建存储过程用的包，以及声明存储过程[sql] view plaincopypr int?--创建包 -- Author : Sugar -- Created : 2014-07-04 -- P
复制链接

扫一扫