Oracle 存储过程、包、包体如何创建？

关注【郑大钱呀】【_公】【_众】【_号】，我们一起交流，一起学习。

Oracle Live SQL

在学习存储过程之前，我们首先需要有一个Oracle的数据库，但是Oracle数据库比较大，有时候电脑稍差点就带不动，且安装还是比较繁琐的。

Oracle公司提供的一项免费的云服务(Live SQL)，它为开发人员和DBA提供了一个在线开发平台，用于测试和共享SQL、PL/SQL脚本、教程和最佳实践等内容。

我们打开浏览器，输入Oracle Lice SQL，第一个就是，如下图：

点击 start coding now 进入登录页面：

登录成功后，就可以写SQL代码了

存储过程

存储过程我们可以简单的理解成就是用于执行特定操作的pl/sql语句块，主要用于处理复杂的业务逻辑。

我们再看一下正式的定义：存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，因为事先经过预编译，所以在处理复杂的业务逻辑的时候，存储过程要比普通的SQL要快，除此之外存储过程，还有如下的特点：

1. 存储过程的代码直接存放在数据库中，一般直接通过存储过程的名称调用，减少了网络流量，加快了系统执行效率
2. 可复用：存储过程可以重复使用，从而可以减少数据库开发人员的工作量
3. 提高系统安全性,参数化的存储过程可以防止SQL注入式攻击

如何创建一个存储过程

我们先来看一下语法：

CREATE [OR REPLACE] PROCEDURE 存储过程名称 [(参数名[IN|OUT] 参数类型, ...)]
IS[AS]
BEGIN
-- SQL代码
END [存储过程名称];

• CREATE PROCEDURE 表示创建一个存储过程，[OR REPLACE ]是一个可选项，表示如果存储过程已经存在则替换
• [(参数名[IN|OUT] 参数类型, ...)],此部分为参数部分，首先我们需要指定参数的名称，[IN|OUT|IN OUT]，则表示该参数是入参，还是出参，最后面再接着参数类型
• BEGIN...END;为具体存储过程的具体实现

这里我们先以一个简单的示例感受一下：

/*创建存储一个存储，输出当前时间*/
create or replace procedure print_Time
IS
BEGIN
    DBMS_OUTPUT.PUT_LINE(to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss'));
END print_Time;

执行完成后，如下图：

存储过程已经成功创建。

查询存储过程

存储过程创建成功了，我们怎么查询已经创建的存储过程呢，下面的语句就可以查询出已经创建的存储过程：

select distinct name from user_source where type = 'PROCEDURE'

结果如下：

调用存储过程

方式一

我们存储过程创建成功了，该如何调用呢，我们只需把存储过程放到BEGIN...END代码块里就可以了，示例如下：

方式二

我们也可以call来调用存储过程，如下图：

方式三

我们也可以使用exec来调用，但是exec是sqlplus命令，只能在sqlplus中使用，与call不同的是：

1. 存储过程没有参数时,exec可以直接跟过程名（可以省略()），但call则必须带上().
2. exec是plsql语言，call是sql语言
3. exec执行会捕获并输出某些异常，而call会忽略，所以写存储调用时建议用exec

exec pro_name(参数1..);

删除存储过程

当我们创建了一个存储过程后，想删除的时候，我们可以使用下面的语法将其删掉

DROP PROCEDURE 存储过程名称

示例如下：

创建一个有入参的存储过程

create or replace procedure print_info(
    name in varchar2,
    age int,
    sex in varchar2
)
as
begin
    dbms_output.put_line('姓名'||name||',年龄'||age||',性别'||sex);
end;

这里我们定义了三个参数，用以接受姓名、年龄、性别，关于入参的定义我们需要值得注意是：

1. 入参的关键字使用 IN
2. 参数的数据类型，只需要指定类型，不需要指定大小，比如，字符串类型就是指定varchar2就可以，不需要指定的varchar2(100)

调用结果如下：

创建一个有返回值的存储过程

create or replace procedure print_info(
    name in varchar2,
    age int,
    sex in varchar2,
    info out varchar2
)
as
begin
    info:= '姓名'||name||',年龄'||age||',性别'||sex;
end;

这里我们新增了一个出参info，在代码块中我们将拼接好的字符串赋值给info参数，info:= 值为oracle的赋值语法，我们也可以用另一种写法，如下：

create or replace procedure print_info(
    name in varchar2,
    age int,
    sex in varchar2,
    info out varchar2
)
as
begin
    --该写法等同于 info:= 值
    select '姓名'||name||',年龄'||age||',性别'||sex into info from dual;
end;

调用结果如下：

创建IN OUT的存储过程

所谓的IN OUT的存储过程，即存储过程的参数既可以出参又可以入参，我们先看一个示例感受一下：

create or replace procedure print_info(
    str in out varchar2
)
as
begin
    dbms_output.put_line(str);
    str:='str作为出参';
end print_info;

调用过程如下：

这块有个疑问，in 参数 、out参数可以合用，但是in参数和in out参数合用的时候，创建存储过程不会报错，但是调用总是会失败。

在存储过程内部定义参数

我们除了想存储过程传入参数，有些时候，我们也会在存储过程中定义一些内部的参数，比如当我们判断传入时间为空的时候，则将当前使用当前时间，示例代码如下：

create or replace procedure get_curr_time(
    v_time varchar2
)
as
    --存储过程中定义参数
    curr_time varchar(50):=to_char(SYSDATE,'yyyy-mm-dd hh24:mi:ss');
begin
    if v_time is null then
        dbms_output.put_line(curr_time);
    else
        dbms_output.put_line(v_time);
    end if;
end;

调用函数结果如下：

Oracle 包(Package)

Oracle包的概念类似于面向对象语言中的类，主要为了方便管理存储过程或函数，主要用于管理存储过程或者存储函数，它由两部分组成，包头和包体，包头定义各个存储过程或函数的规范，包体是存储过程或函数的具体实现。

创建包头语法

create or replace package 包名称
is|as
[存储过程]
[函数]
end [包名];

创建包体

create or replace package body 包名
is|as
实现包头中定义的过程或函数：
end [包名];

示例如下

首先创建一个包

create or replace package package_study is
    -- 求和
    procedure add_num(
        num1 number,
        num2 number);
end package_study;

创建包体

create or replace package body package_study as
    procedure add_num(
            num1 number,
            num2 number)as
    begin
        dbms_output.put_line(num1+num2);
    end add_num;
end package_study;

调用存储过程
调用包中的存储过程需要使用包名.存储过程名的方式调用

查询包语句

SELECT distinct name FROM user_source where type='PACKAGE';

结尾

本期的内容就到这里了，如有不足之处还请大家多多指正，欢迎大家留言、关注、转发、收藏，谢谢。