OraclePL/SQL的存储过程。oracle pl/sql存储过程是很重要的知识，通过熟练编写，可以很好的理解oracle pl/sql
/**
存储过程
编写、编译之后存储在数据库管理系统中的plsql块
create [or replace] procedure 存储过程名[(参数名 [参数模式] 参数类型)]
is[as]
定义变量或者常量
begin
end 存储过程名;
因为存储过程不能直接在过程体中返回值，所以如果在开发时需要使用存储过程的返回值，是以参数的形式
供外部使用的
参数模式：
in 输入参数 也就是传递给存储过程的参数
out 输出参数 也就是存储过程返回给外部的值
in out 输入输出参数 既可以作为为输入参数 也可以为输出参数
**/
create or replace procedure mypro1
is
begin
dbms_output.put_line( to_char(sysdate,’yyyy-mm-dd’) );
end mypro1;
— 执行存储过程
execute mypro1;
begin
mypro1();
end;
create or replace procedure mypro2(newsal in number)
is
begin
update emp set sal = newsal where sal <= 800;
commit;
end mypro2;
— 执行带有一个输入参数的存储过程
execute mypro2(5000);
/**
创建带有输入参数和输出参数的存储过程
**/
— 根据条件统计记录数，记录数据返回
create or replace procedure mypro3(psal in number,totalRows out number)
as
begin
select count(*) into totalRows from emp where sal > psal;
end mypro3;
— 调用具有输入参数和输出参数的存储过程
declare
total number;
begin
mypro3(1000,total);
dbms_output.put_line(total);
end;
–程序包：将对同一个模式进行操作的那些程序组织一个包中，编译开发人员或者管理人员对象这些程序进行管理
— 包定义部分 ：声明当前包中要定义组织那些程序(存储过程和函数、以及动态游标等)
create or replace package mypkg
as
–定义游标类型
type pagination_cursor_type is ref cursor;
–定义存储过程 在包声明部分仅仅定义并没有创建
procedure pagination(
tname in varchar2, –表示表名的输入参数
whereStmt in varchar2, –表示where子句的输入参数
orderStmt in varchar2, –表示order by子句的输入参数
pageNumber in number, –表示页码的输入参数
pageSize in number, –表示一页记录数的输入参数
totalRows out number, –表示总记录的输出参数
resultset out pagination_cursor_type –表示分页查询结果的游标输出参数
);
end mypkg;
— 包体部分 ： 用于将包定义部分定义哪些存储过程、函数进行创建
create or replace package body mypkg
as
— 创建在包中声明的存储过程
procedure pagination(
tname in varchar2, –表示表名的输入参数
whereStmt in varchar2, –表示where子句的输入参数
orderStmt in varchar2, –表示order by子句的输入参数
pageNumber in number, –表示页码的输入参数
pageSize in number, –表示一页记录数的输入参数
totalRows out number, –表示总记录的输出参数
resultset out pagination_cursor_type –表示分页查询结果的游标输出参数
)
as
— 定义存储体中需要使用的变量
vsql varchar2(255); — 用于存储拼接的sql语句的变量
beginIndex number; –用于表示查询开始索引的变量
endIndex number; –用于表示查询结束索引的变量
v_pageNumber number; –用于临时存储页码的变量
totalPages number; –用于临时存储总页数的变量
begin
— 拼接获取总记录数的sql语句
vsql := ‘select count(*) from ‘||tname;
— 判断是否输入where子句
if whereStmt is not null then
vsql := vsql||’ ‘||whereStmt;
end if;
dbms_output.put_line(vsql);
— 执行动态sql语句，该sql语句用于获取表中总记录数，将执行的结果赋值给totalRows
execute immediate vsql into totalRows;
— 计算总页数： ceil函数向上取整
totalPages := ceil(totalRows/pageSize);
— 判断传入的输入参数页码pageNumber是否符合业务逻辑
v_pageNumber := pageNumber;
if pageNumber<1 then
v_pageNumber := 1;
elsif pageNumber>totalPages then
v_pageNumber := totalPages;
end if;
— 计算其实索引
beginIndex := (v_pageNumber-1)*pageSize;
— 计算结束的索引
endIndex := v_pageNumber*pageSize;
— 拼接查询的sql语句 首先拼接分页查询的子查询部分。
vsql := ‘select rownum rn, x.* from ‘||tname||’ x’;
— 判断输入参数的where子句是不是为null，如果不为null，拼接where子句
if whereStmt is not null then
vsql := vsql||’ ‘||whereStmt||’ and rownum<=’||endIndex;
else
vsql := vsql||’ where rownum<=’||endIndex;
end if;
— 拼接出一个完整的分页查询语句
vsql := ‘select * from (‘||vsql||’) where rn>’||beginIndex;
dbms_output.put_line(vsql);
–执行分页查询语句，查询结果存储在动态游标中
open resultset for vsql;
end pagination;
end mypkg;
— 调用存储过程
declare
totalRow number;
resultset mypkg.pagination_cursor_type;
begin
mypkg.pagination(’emp’,’where sal>1000′,”,1,10,totalRow,resultset);
loop
end;