动态 分页存储过程 oracle环境(转)
来源:互联网 发布:网络计算器在线使用 编辑:程序博客网 时间:2024/04/30 20:25
原文:http://www.cnblogs.com/zakaz/articles/2319576.html
存以备用!
PL/SQL 存储过程
--分页查询
create or replace package testpackage as
type testcursor is ref cursor;
end testpackage;
/
--select * from (select rownum rn,a1.* from (select * from emp)a1 where rownum<=10) where rn>=6;
create or replace procedure zk_pro7(
zk_table in varchar2,
zk_pagesize in number,
zk_pagenow in number,
zk_rownum out number,
zk_rowcount out number,
zk_cursor out testpackage.testcursor)
is
v_sql varchar2(1000);
v_begin number:=(zk_pagenow-1)*zk_pagesize;
v_end number:=zk_pagenow*zk_pagesize;
begin
v_sql:='select * from (select rownum rn,a1.* from (select * from '||zk_table||')a1 where rownum<='||v_end||') where rn>='||v_begin;
open zk_cursor for v_sql;
v_sql:='select connt(*) from '||zk_table;
execute immediate v_sql into zk_rownum;
if mod(zk_rownum,zk_pagesize)=0 then
zk_rowcount := zk_rownum/zk_pagesize;
else
zk_rowcount:=zk_rownum/zk_pagesize+1;
end if;
end;
/
=================
java代码段
package com.gentle;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
public class Test1 {
private static Connection connection = null;
private static CallableStatement statement = null;
private static ResultSet resultSet = null;
public static void main(String[] args) {
conn();
}
static void conn() {
try {
Class.forName("oralce.jdbc.driver.OracleDriver");
connection = DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:ORACLE","scott","tiger");
statement = connection.prepareCall("{call zk_pro7(?,?,?,?,?,?)}");
statement.setString(1, "emp");
statement.setInt(2, 3);
statement.setInt(3, 1);
statement.registerOutParameter(4, oracle.jdbc.OracleTypes.NUMBER);
statement.registerOutParameter(5, oracle.jdbc.OracleTypes.NUMBER);
statement.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
statement.execute();
int rownum = statement.getInt(4);
int rowcount = statement.getInt(5);
System.out.println("rownum:" + rownum);
System.out.println("rowcount:" + rowcount);
resultSet = (ResultSet) statement.getObject(6);
while(resultSet.next()) {
resultSet.getString("ename");
resultSet.getInt("sal");
resultSet.getDate("hiredate");
//....
}
} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
} finally {
closeDB();
}
}
static void closeDB() {
if(connection!=null) {
try {
connection.close();
connection = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
if(statement!=null) {
try {
statement.close();
statement = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
if(resultSet!=null) {
try {
resultSet.close();
resultSet = null;
} catch (SQLException e) {
e.printStackTrace();
}
}
}
}
- 动态 分页存储过程 oracle环境(转)
- oracle存储过程分页
- oracle存储过程分页
- Oracle分页存储过程
- Oracle 存储过程分页
- ORACLE分页存储过程
- oracle 分页存储过程
- Oracle分页存储过程
- Oracle分页存储过程
- Oracle存储过程分页
- oracle 分页存储过程
- Oracle存储过程分页
- Oracle分页存储过程
- oracle 分页 存储过程
- Oracle存储过程分页
- oracle分页存储过程
- Oracle分页存储过程
- oracle存储过程分页
- C#几个经常用到的字符串的截取、替换
- C# ASP.NET MVC HtmlHelper用法大全
- mfc的命令函数
- 亿android开发一周年记!
- 深入浅出谈cuda
- 动态 分页存储过程 oracle环境(转)
- mysql主从同步配置(windows环境)
- [Unity3D]无缝场景切换解决方案(1) - 简单场景切换
- linux自带硬盘检测命令smartctl
- 第7周项目2-求两数正差值
- [Unity3D]场景间切换与数据传递(以及物体删除技巧)
- 我的第一份c++
- CSDN问答频道“华章杯”9月份排行榜出炉,请上榜用户提交相关邮寄信息
- jstl 遍历 List,Set,Map