PL/SQL中编写Oracle数据库分页的存储过程

其实 Oracle数据库的分页还是比较容易理解的。此文以oracle数据库中的SCOTT用户的EMP表为例,用PL/SQL Developer编写一个分页存储过程,要求是:可以输入表名,每页显示记录数,当前页,返回总记录数,总页数和返回的结果集。

由于需要返回查询出来的结果集,需要在PL/SQL中创建一个package,这个包里面定义一个refcursor类型,用于记录sql语句查询出来的结果集。创建包的代码如下:

create or replace package pagingPackage as
type paging_cursor is ref cursor;
end pagingPackage; 

接下来开始Oracle的分页过程,我们可以用select emp.*,rownum from emp;来显示地表示出每行的行标。然后可以根据行标对内容进行分页,下面这个SQL语句可以作为Oracle分页的模板。

select * from
(select t1.*,rownum rn from (select * from emp) t1 where rownum<=12)
where rn>=8;

有了上面的refcursor类型和分页模板,下面开始编写分页的存储过程,代码如下:

create procedure paging
(tableName in varchar2 ,--表名
pageSizes in number,--每页显示记录数
pageNow in number,--当前页
rowNums out number,--总记录数
pageNum out number,--总页数
paging_cursor out pagingPackage.paging_cursor) is
--定义部分
--定义sql语句,字符串
v_sql varchar2(1000);
--定义两个整数,用于表示每页的开始和结束记录数
v_begin number:=(pageNow-1)*pageSizes+1;
v_end number:=pageNow*pageSizes;
begin
 --执行部分
 v_sql:='select * from (select t1.*,rownum rn from (select * from '||tableName||') t1 where rownum<='||v_end||') where rn>='||v_begin;
 --把游标和sql语句关联
 open paging_cursor for v_sql;
 --计算rowNums和pageNum
 --组织一个sql语句
 v_sql:='select count(*) from '||tableName;
 --执行该sql语句,并赋给rowNums
 execute immediate v_sql into rowNums;
 --计算pageNum
 if mod(rowNums,pageSizes)=0 then
  pageNum := rowNums/pageSizes;
  else
   pageNum := rowNums/pageSizes+1;
   end if;
 end; 

在Java中编写代码测试分页,代码如下:

package com.test.oracletest;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
//测试分页
public class OraclePaging {
  public static void main(String[] args) {
    try {
      // 加载驱动
      Class.forName("oracle.jdbc.driver.OracleDriver");
      // 获取连接
      Connection connection = DriverManager.getConnection(
          "jdbc:oracle:thin:@127.0.0.1:1521:ORCL", "SCOTT", "tiger");
      // 创建CallableStatement,调取数据库的存储过程
      CallableStatement cst = connection
          .prepareCall("{call paging(?,?,?,?,?,?)}");
      // 给?赋值
      cst.setString(1, "emp");
      cst.setInt(2, 6);
      cst.setInt(3, 2);
      // 注册存储过程的输出项
      cst.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER);
      cst.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER);
      cst.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR);
      // 执行
      cst.execute();
      // 获取输出项
      int rowNums = cst.getInt(4);
      int pageNum = cst.getInt(5);
      ResultSet rs = (ResultSet) cst.getObject(6);
      System.out.println("总记录数rowNums = " + rowNums);
      System.out.println("总页数pageNum = " + pageNum);
      System.out.println("EMPNO" + '\t' + "ENAME" + '\t' + "ROWNUM");
      while (rs.next()) {
        System.out.println(rs.getInt("EMPNO") + " " + '\t'
            + rs.getString("ENAME") + '\t' + rs.getInt("RN"));
      }
    } catch (Exception e) {
      e.printStackTrace();
    } finally {
      // 关闭资源
    }
  }
} 

执行的结果如图所示:

我们还可以通过修改模板最内侧的视图来满足其他一些基本的排序要求。Oracle的分页思想就是如此。。#

(0)

相关推荐

  • oracle 虚拟专用数据库详细介绍

    所谓虚拟专用数据库(VPD)指的是,通过在数据库里进行配置,从而让不同的用户只能查看某个表里的部分数据.VPD分为以下两个级别. 行级别:在该级别下,可以控制某些用户只能查看到某些数据行.比如,对于销售数据表sales 来说,每个销售人员只能检索出他自己的销售数据,不能查询其他销售人员的销售数据. 列级别:在该级别下,可以控制某些用户不能检索某个表的某个列的值.比如用户HR 下的 employees 表中,含有工资(salary)列,由于该列比较敏感,因此不让其他用户查询该列的值. 其他用户检索

  • Python编程实战之Oracle数据库操作示例

    本文实例讲述了Python编程实战之Oracle数据库操作.分享给大家供大家参考,具体如下: 1. 要想使Python可以操作Oracle数据库,首先需要安装cx_Oracle包,可以通过下面的地址来获取安装包 http://cx-oracle.sourceforge.net/ 2. 另外还需要oracle的一些类库,此时需要在运行python的机器上安装Oracle Instant Client软件包,可以通过下面地址获得 http://www.oracle.com/technetwork/d

  • oracle、mysql数据库停止与启动的批处理文件

    1.关机,取消关机 ::关机 echo 30秒之后关机 %systemroot%\system32\shutdown -s -t 30 ::取消关机 echo 取消关机 %systemroot%\system32\shutdown -a 2.oralce,或者mysql滴服务启动与关闭 ::oralce service start net start OracleVssWriterORCL net start OracleDBConsoleorcl net start OracleOraDb10

  • windows下python连接oracle数据库

    python连接oracle数据库的方法,具体如下 1.首先安装cx_Oracle包 2.解压instantclient-basic-windows.x64-11.2.0.4.0.zip到c:\oracle 3.拷贝instantclient_11_2下所有.dll文件到c:\python34\Lib\site-packages\下(根据自己的python版本拷贝到相应的site-packages文件夹下) python连接示例代码: # -*- coding: utf-8 -*- import

  • PL/SQL中编写Oracle数据库分页的存储过程

    其实 Oracle数据库的分页还是比较容易理解的.此文以oracle数据库中的SCOTT用户的EMP表为例,用PL/SQL Developer编写一个分页存储过程,要求是:可以输入表名,每页显示记录数,当前页,返回总记录数,总页数和返回的结果集. 由于需要返回查询出来的结果集,需要在PL/SQL中创建一个package,这个包里面定义一个refcursor类型,用于记录sql语句查询出来的结果集.创建包的代码如下: create or replace package pagingPackage

  • 使用PL/SQL Developer连接Oracle数据库的方法图解

    之前因为项目的原因需要使用Oracle数据库,由于时间有限没办法从基础开始学习,而且oracle操作的命令界面又太不友好,于是就找到了PL/SQL Developer这个很好用的软件来间接使用数据库. 下面简单介绍一下如何用这个软件连接Oracle数据库. 第一步 安装Oracle Database和PL/SQL Developer 这一步网上有很多的教程就不详细介绍.Oracle可以直接到官网去下载,在安装中注意几个问题就行: 1.一定要记住自己全局数据库名字和口令,这个在后面连接的时候很重要

  • 解析PL/SQL Developer导入导出数据库的方法以及说明

    PL/SQL Developer是Oracle数据库中用于导入或导出数据库的主要工具,本文主要介绍了利用PL/SQL Developer导入和导出数据库的过程,并对导入或导出时的一些注意事项进行了说明,接下来我们就一一介绍. 导出步骤:    1 tools ->export user object 选择选项,导出.sql文件.    2 tools ->export tables-> Oracle Export 选择选项导出.dmp文件.导入步骤:    注:导入之前最好把以前的表删除

  • Oracle PL/SQL中异常高级特性示例解析

    PL/SQL(Procedural Language/SQL,过程语言/SQL)是结合了Oracel过程语言和结构化查询语言(SQL)的一种扩展语言. 优点: (1)PL/SQL具有编程语言的特点,它能把一组SQL语句放到一个模块中,使其更具模块化种序的特点. (2)PL/SQL可以采用过程性语言控制程序的结构. (3)PL/SQL有自动处理的异常处理机制. (4)PL/SQL程序块具有更好的可移植性,可移植到另一个Oracle数据库中. (5)PL/SQL程序减少了网络的交互,有助于提高程序性

  • Oracle在PL/SQL中使用存储过程

    目录 一.概述 二.存储过程详解 1.创建过程语法: 2.创建存储过程 3.调用存储过程 4.C# 调用: 三.存储过程返回记录集SYS_REFCURSOR 1.返回单行语法 2.返回多行语法 四.维护存储过程 1.删除过程 2.显示过程代码 3.查看过程状态 4.重新编译过程 五. 过程与函数比较 1.相同点: 2.不同点: 六. 与过程相关数据字典 一.概述 过程和函数统称为PL/SQL子程序,他们是被命名的PL/SQL块,均存储于数据库中. 并通过输入.输出和输入输出参数与其调用者交换信息

  • Oracle在PL/SQL中嵌入SQL语句

    PL/SQL块中只能直接嵌入SELECT.DML(INSERT,UPDATE,DELETE)以及事务控制语句(COMMIT,ROLLBACK,SAVEPOINT), 而不能直接嵌入DDL语句(CREATE,ALTER,DROP)和DCL语句(GRANT,REVOKE) 1.嵌入SELECT语句 使用SELECT INTO语句时,必须要返回一条数据,并且只能返回一条数据. v_ename emp.ename%type; v_sal emp.sal%type; select ename,sal in

  • Oracle在PL/SQL中使用子查询

    目录 一.概述 1.单行子查询(子查询只返回一行) 2.多行单列子查询(子查询返回多行) 3.多列子查询 二.在DDL语句中使用子查询 1.create table 2.create View 3.create materialized view 建立实体化视图 三.在DML语句中使用子查询 1.INSERT 2.UPDATE 3.DELETE 四.WITH语句 五.相关子查询. 一.概述 在一个SQL语句中嵌套另一个SQL语句成为子查询.包括单行子查询,多行子查询,多列子查询. 注意,当在DD

  • PL/SQL Dev连接Oracle弹出空白提示框的解决方法分享

    没办法,只能自己研究,经过大概一天时间吧,还是搞好了,写个总结. 出现这种问题,解决方法大概有这几种: 1.权限不够,导致弹出空吧提示框.(直接上链接) http://jingyan.baidu.com/article/066074d6760959c3c21cb0d6.html 就PL/SQL图标上点右键---属性---兼容性--管理员身份运行此程序的勾打上,即可 2.环境变量没设对. ①在安装oracle服务器的机器上搜索下列文件,oci.dllocijdbc10.dll(其中10代表orac

  • Oracle数据库分页的集中方法(三种方法)

    在 做项目中用到了分页,下面说一下oracle分页的方法; 采用伪列 rownum 查询前10条记录 [sql] select * from t_user t where ROWNUM <10; 按照学生ID排名,抓取前三条记录 -- 不能对ROWNUM使用>(大于1的数值).>=(大于或等于1的数值).=(大于或等于1的数值),否则无结果 -- 所以直接用只能从1开始 -- rownum >10 没有记录,因为第一条不满足去掉的话,第二条的rownum又成了1,所以永远没有满足条

  • pl/sql连接远程oracle服务器的配置教程

    本文为大家分享了pl/sql连接远程oracle服务器的配置教程,具体内容如下 方法1: 打开所有程序->找到oracle-oradb11g-home1->Net Configuration Assistant 本地网络服务名配置,点击下一步 选择添加,点击下一步 填写服务名(注数据库名),点击下一步 选择通信协议:TCP,下一步 填写数据库所在的主机名(也可以是IP地址),选择端口号(根据数据库所在的主机的端口号进行选择,一般是标准端口号),点击下一步,选择测试,点击下一步 ok,到此结束.

随机推荐