PL/SQL编程（五）

八、pl/sql进阶—存储过程分类

分页是任何一个网站（bbs、e-shop、blog）都会使用到的技术，因此学习pl/sql编程开发就一定要掌握该技术。

1.无返回值的存储过程

--案例

--现有一张book表，变结构为书号、书名和出版社。

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

create table(bookId number, bookName varchar2(50), publishHouse varchar2(50));

--编写过程

create or replace procedure test_pro(bookId in number, bookName in varchar2, publishHouse varchar) is

--in表示输入参数，不写的话默认为in

--out表示输出参数

begin

--执行部分

insert into book(bookId,bookName,publishHouse) values(bookId,bookName,publishHouse);

end;

 

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

--案例

--输入员工编号，返回员工的姓名

create or replace procedure test_pro(num in varchar2,out_name out number) is

--

begin

select ename into out_name from emp where empno=num;

end;

 

--案例扩展：输入员工编号，返回姓名、工资和岗位

create or replace procedure test_pro(num in varchar2,out_name out number,out_sal out number, out_job out varchar2) is

begin

select ename,sal,job into out_name,out_sal,out_job from emp where empno=num;

end;

 

3. 有返回值的存储过程（列表[结果集]）

由于Oracle的储存过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，但由于返回结果是集合，所以不能用一般的参数，用游标。

--案例

--编写一个过程，输入部门号，返回该部门所有雇员信息

--分2步，1建包

create or replace package test_package as

type test_cursor is ref cursor;

 

end test_package;

--2建立存储过程

create or replace procedure test_pro(num in number, out_cursor out test_package.test_cursor) is

begin

open out_cursor for select * from emp where empno=num;

end;

 

九、pl/sql分页存储过程

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

1.基础准备

--oracle分页，取6-10条结果

--1.将整个结果作为一个虚表

select * from emp t1

--2.列出rownum列号

select t1.*,ROWNUM rn from (select * from emp) t1

--3.先去掉大于10的列

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

--4.再去掉大于6的列 (模板)

select * from (select t1.*,ROWNUM rn from (select * from emp) t1 where ROWNUM<=10) where rn>=6;

2.分页过程

--开始编写分页过程

--输入3个变量(表名、当前页，每页记录数)，返回3个变量(总记录数、总页数、结果集)

create or replace procedure SpiltPage(tableName in varchar2,

pageNow in number,

pageSize in number,

totalRecords out number,

totalPages out number,

out_cursor out test_package.test_cursor

) is

--定义变量

--定义sql语句，字符串拼接用

v_sql varchar2(1000);

--定义2个整数

v_begin number:=(pageNow-1)*pageSize+1;

v_end number:=pageNow*pageSize;

begin

--执行部分

v_sql:='select * from (select t1.*,ROWNUM rn from (select * from '|| tableName ||' order by sal) t1 where ROWNUM<='|| v_end ||') where rn>='||v_begin;

--把游标和sql语句关联起来

open out_cursor for v_sql;

 

--计算totalRecords和totalPages

--组织一个sql语句

v_sql:='select count(*) from '|| tableName;

--执行sql语句，并把返回值赋值给totalRecords

execute immediate v_sql into totalRecords;

--计算totalPages

if mod(totalRecords,pageSize)=0 then

totalPages:=totalRecords/pageSize;

else

totalPages:=totalRecords/pageSize+1;

end if;

--关闭游标

close out_cursor;

end;