Oracle PL/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;