OraclePL/SQL的存储过程
OraclePL/SQL的存储过程。oracle pl/sql存储过程是很重要的知识，通过熟练编写，可以很好的理解oracle pl/sql
/**
存储过程
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;