OraclePL/SQL的存储过程

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;