Oracle学习笔记（二十九）——pl/sql的进阶之编写分页过程

最新推荐文章于 2018-06-03 11:07:47 发布

half1_2_1

最新推荐文章于 2018-06-03 11:07:47 发布

阅读量515

点赞数

分类专栏：数据库文章标签：韩顺平 oracle 数据库存储分页

本文链接：https://blog.csdn.net/shangqing1123/article/details/51208280

版权

数据库专栏收录该内容

39 篇文章 1 订阅

订阅专栏

一、分页

1、无返回值的存储过程

1）案例：现有一张表book，表结构如下：

编写一个过程，可以向book表添加书，要求通过java程序调用该过程。

SQL> create table book(
  2  bookId number,
  3  bookName varchar2(50),
  4  publishHouse varchar2(50));
Table created

SQL> --编写过程
SQL> --in:表示输入参数。缺省时默认为in。
SQL> --out：表示输出参数。
SQL> create or replace procedure sp_pro7
  2  (spBookId in number,spBookName in varchar2,sppublicHouse varchar2) is
  3  begin
  4    insert into book values(spBookId,spBookName,sppublicHouse);
  5  end;
  6  /
Procedure created

//调用一个无返回值的过程

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;

public class Test1 {
      public static void main(String[] args) {
             // TODO Auto-generated method stub
            CallableStatement cs = null;
            Connection ct = null;
             try {
                   // 加载驱动
                  Class. forName("oracle.jdbc.driver.OracleDriver");
                   ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "***" );

                   cs = ct.prepareCall( "{call sp_pro7(?,?,?)}");

                   cs.setInt(1, 10);
                   cs.setString(2, "笑傲江湖");
                   cs.setString(3, "人民出版社" );

                   cs.execute();

            } catch (Exception e) {
                   // TODO: handle exception
                   e.printStackTrace();
            } finally {
                   try {
                         // 关闭资源
                         cs.close();
                         ct.close();
                  } catch (Exception e1) {
                         // TODO Auto-generated catch block
                         e1.printStackTrace();
                  }
            }
      }
}

2、有返回值的存储过程（非列表）

1）案例：编写一个过程，可以输入雇员的编号，返回该雇员的姓名、工资和岗位。

SQL> --有输入和输出的存储过程
SQL> create or replace procedure sp_pro8
  2  (spno in number,spName out varchar2,spSal out number,spJob out varchar2) is
  3  begin
  4    select ename,sal,job into spName,spSal,spJob from emp where empno=spno;
  5  end;
  6  /
Procedure created

//调用有返回值的过程
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;

public class Test2 {
      public static void main(String[] args) {
             // TODO Auto-generated method stub
            CallableStatement cs = null;
            Connection ct = null;
             try {
                   // 加载驱动
                  Class. forName("oracle.jdbc.driver.OracleDriver");
                   ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "***" );

                   cs = ct.prepareCall( "{call sp_pro8(?,?,?,?)}");

                   // 赋值
                   cs.setInt(1, 7788);
                   cs.registerOutParameter(2, oracle.jdbc.OracleTypes.VARCHAR );
                   cs.registerOutParameter(3, oracle.jdbc.OracleTypes.DOUBLE );
                   cs.registerOutParameter(4, oracle.jdbc.OracleTypes.VARCHAR );

                   // 执行
                   cs.execute();
                   // 取出返回值,注意问号的顺序
                  String name = cs.getString(2);
                  String sal = cs.getString(3);
                  String job = cs.getString(4);

                  System. out.println( "7788 的名字是：" + name + "薪水是：" + sal + ",工作是：" + job );
            } catch (Exception e) {
                   // TODO: handle exception
                   e.printStackTrace();
            } finally {
                   try {
                         // 关闭资源
                         cs.close();
                         ct.close();
                  } catch (Exception e1) {
                         // TODO Auto-generated catch block
                         e1.printStackTrace();
                  }
            }
      }
}

3、有返回值的存储过程（列表【结果集】）

案例：编写一个过程，输入部门号，返回该部门所有雇员的信息。

分析：由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外。但由于是集合，所以不能用一般的参数，必须要用package，分为两部分：

建一个包

SQL> --返回结果集的过程
SQL> --建立包，在该包中，定义类型test_cursor，是一个游标
SQL> create or replace package testpackage as
  2  type test_cursor is ref cursor;
  3  end testpackage;
  4  /
Package created

建立存储过程

SQL> --2、创建存储过程
SQL> create or replace procedure sp_pro9
  2  (spNo in number,p_cursor out testpackage.test_cursor) is
  3  begin
  4    open p_cursor for select * from emp where deptno=spNo;
  5  end;
  6  /
Procedure created

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
public class Test3 {
      public static void main(String[] args) {
             // TODO Auto-generated method stub
            CallableStatement cs = null;
            Connection ct = null;
             try {
                   // 加载驱动
                  Class. forName("oracle.jdbc.driver.OracleDriver");
                   ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "***" );

                   cs = ct.prepareCall( "{call sp_pro9(?,?)}");

                   // 赋值
                   cs. setInt(1, 10);
                   cs.registerOutParameter(2, oracle.jdbc.OracleTypes.CURSOR );

                   // 执行
                   cs.execute();
                   // 得到结果集
                  ResultSet rs = (ResultSet) cs.getObject(2);
                  System. out.println( "10号部门：");
                   while ( rs.next()) {
                        System. out.println( "empno：" + rs.getInt(1) + "empName：" + rs .getString(2));
                  }

            } catch (Exception e) {
                   // TODO: handle exception
                   e.printStackTrace();
            } finally {
                   try {
                         // 关闭资源
                         cs.close();
                         ct.close();
                  } catch (Exception e1) {
                         // TODO Auto-generated catch block
                         e1.printStackTrace();
                  }
            }
      }
}

4、编写分页过程

案例：编写一个存储过程，要求可以输入表名、每页显示记录数、当前页,返回总记录数、总页数和返回的结果集

分页查询
- select * from emp;
- select t1.*,rownum rn from (select * from emp) t1;
- select t1.*,rownum rn from (select * from emp) t1 where rownum<=10;
- select * from (select t1.*,rownum rn from (select * from emp) t1 where rownum<=10) where rownum>=6;

SQL> --新建包
SQL> create or replace package testpackage as
  2  type test_cursor is ref cursor;
  3  end testpackage;
  4  /
Package created

SQL> --编写分页的存储过程
SQL> create or replace procedure fenye
  2  (tableName in varchar2,
  3  myPageSize in number,
  4  pageNow in number,
  5  myRows out number,--总记录数
  6  myPageCount out number,--总页数
  7  p_cursor out testpackage.test_cursor--返回的结果集
  8  ) is
  9  --定义部分
 10  --定义sql语句 字符串
 11  v_sql varchar2(1000);
 12  --定义两个整数
 13  v_begin number:=(pageNow-1)*myPageSize+1;
 14  v_end number:=pageNow*myPageSize;
 15  begin
 16    v_sql:='select * from (select t1.*,rownum rn from (select * from '||tableName||
 17    ') t1 where rownum<='||v_end||') where rn>='||v_begin;
 18    --把游标和sql语句关联
 19    open p_cursor for v_sql;
 20    --计算myRows
 21    v_sql:='select count(*) from '||tableName;
 22    execute immediate v_sql into myRows;
 23    --计算myPageCount
 24    if mod(myRows,myPageSize)=0 then
 25      myPageCount:=myRows/myPageSize;
 26      else
 27        myPageCount:=myRows/myPageSize+1;
 28    end if;
 29    --关闭游标
 30    close p_cursor;
 31  end;
 32  /
Procedure created

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;

public class Test4 {
      public static void main(String[] args) {
             // TODO Auto-generated method stub
            Connection ct = null;
            CallableStatement cs = null;
             try {
                  Class. forName("oracle.jdbc.driver.OracleDriver");
                   ct = DriverManager.getConnection("jdbc:oracle:thin:@127.0.0.1:1521:orcl", "scott", "***" );

                   cs = ct.prepareCall( "{call fenye(?,?,?,?,?,?)}");

                   // 赋值
                   cs.setString(1, "emp");
                   cs.setInt(2, 5);
                   cs.setInt(3, 1);

                   // 注册总记录数
                   cs.registerOutParameter(4, oracle.jdbc.OracleTypes.INTEGER );
                   // 注册总页数
                   cs.registerOutParameter(5, oracle.jdbc.OracleTypes.INTEGER );
                   // 注册返回的结果集
                   cs.registerOutParameter(6, oracle.jdbc.OracleTypes.CURSOR );

                   cs.execute();
                   // 取出总记录数
                   int rowNum = cs.getInt(4);
                   // 取出总页数
                   int pageCount = cs.getInt(5);
                  ResultSet rs = (ResultSet) cs.getObject(6);

                   // 显示
                  System. out.println( "rowNum=" + rowNum);
                  System. out.println( "pageCount=" + pageCount);

                   while ( rs.next()) {
                        System. out.println( "编号：" + rs .getInt(1) + ",姓名：" + rs .getString(2));
                  }
            } catch (Exception e) {
                   // TODO Auto-generated catch block
                   e.printStackTrace();
            } finally {
                   try {
                         // 关闭资源
                         cs.close();
                         ct.close();
                  } catch (Exception e1) {
                         // TODO Auto-generated catch block
                         e1.printStackTrace();
                  }
            }
     }
}

half1_2_1

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle学习笔记（二十九）——pl/sql的进阶之编写分页过程

一、分页1、无返回值的存储过程 1）案例：现有一张表book，表结构如下：编写一个过程，可以向book表添加书，要求通过java程序调用该过程。SQL> create table book( 2 bookId number, 3 bookName varchar2(50), 4 publishHouse varchar2
复制链接

扫一扫

专栏目录