022.PL-SQL进阶—分页过程

课 程 推 荐

我 的 个 人 主 页：👉👉 失心疯的个人主页 👈👈

入 门 教 程 推 荐 ：👉👉 Python零基础入门教程合集 👈👈

虚 拟 环 境 搭 建 ：👉👉 Python项目虚拟环境(超详细讲解) 👈👈

PyQt5 系 列 教 程：👉👉 Python GUI(PyQt5)文章合集 👈👈

Oracle数据库教程：👉👉 Oracle数据库文章合集 👈👈

优 质 资 源 下 载 ：👉👉 资源下载合集 👈👈

优 质 教 程 推 荐：👉👉 Python爬虫从入门到入狱系列 合集 👈👈

.

分页

• 分页是任何一个网站（bbs、网上商城、blog）都会使用到的技术，因此学习PL/SQL编程开发就一定要掌握分页技术
• 

存储过程

1. 无返回值的存储过程
2. 有返回值的存储过程

无返回值的存储过程

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

• 第一步：创建表

  create table book(bookID number(4),bookName varchar2(50),publishHouse varchar2(50));
  

  • 

• 第二步：按要求编写过程

  create or replace procedure sp_insert_book(bookid number,bookname varchar2,publishing varchar2) is
  begin
    insert into book values(bookid,bookname,publishing);
  end;
  

  • 

有返回值的存储过程

• 存储过程的参数中in表示输入参数，out表示输出参数
• 案例：编写一个过程，输入员工编号，返回该员工的姓名

  create or replace procedure sp_select_emp(spno in number,spname out varchar2) is
  begin
    select ename into spname from emp where empno=spno;
  end;
  

返回值为列表(结果集)的存储过程

• 案例：编写一个过程，输入部门号，返回该布恩所有员工的信息
• 分析：由于oracle存储过程没有返回值，所以过程的所有返回值都是通过out参数来替代的。列表也不例外，但由于是集合，所以不能用一般的参数，必须用pagkage.所以这个案例必须分两步

1. 创建一个包

   create or replace package testpackage as 
     type test_cursor is ref cursor;
   end testpackage;
   

2. 创建过程

   create or replace procedure sp_select_emp(spNo in number,p_cursor out testpackage.test_cursor) is
   begin
     open p_cursor for select * from emp where deptno=spno;
   end;
   

3. 在程序中调用过程

---

编写分页过程

• oracle分页课程
• 案例：编写一个存储过程，输入表名、每页显示记录数、显示第几页。返回总记录数，总页数和返回的结果集
• Oracle的分页步骤

1. 先用select语句查询所有数据，当做视图(临时表)来用

   select * from emp;
   

   • 

2. 给该视图取别名为t1，然后查询该表所有内容，并添加编号(行号)

   select t1.*,rownum rn from (select * from emp) t1;
   

   • 

3. 通过编号(行号)来对数据进行筛选

   select t1.*,rownum rn from (select * from emp) t1 where rownum<10;
   

   • 

4. 如果还有筛选要求，则将该筛选结果作为视图(临时表)再次查询，并加上where条件

   select * from (select t1.*,rownum rn from (select * from emp) t1 where rownum<10) where rn>4;
   

   • 

5. 开始编写分页的存储过程

   1. 创建一个包，定义类型为游标

      -- 创建一个游标类，用以接收查询记录
      /*创建一个包my_package,该包里面定义一个游标类型test_cursor*/
      create or replace package my_package as
        type test_cursor is ref cursor;
      end my_package;
      

   2. 编写分页过程

      create or replace procedure sp_fenye
        (sp_tablename varchar2; -- 输入表名
        sp_pagesize in number; -- 输入每页显示记录数
        sp_pagerow in number; -- 输入显示第几页
        sp_rows out number; -- 输出总记录数
        sp_pagecount out number; -- 输出总页数
        sp_cursor out tespackage.test_cursor -- 输出结果集，类型为自定义的游标类型
        ) is
        -- 定义变量
        v_sql varchar2(1000);--定义一个字符串，用来存储sql语句
        v_begin number:=(sp_pagerow-1)*sp_pagesize+1; --定义一个变量，存储当前页开始的记录数
        v_end number:=sp_pagerow*sp_pagesize;--定义一个变量，存储当前页最后一条记录数
      begin
        v_sql:='select * from (select t1.*,rownum rn from (select * from '||sp_tablename||') t1 where rownum<='||v_end||') where rn>='||v_begin||';'
      end;