ORACLE——存储过程

　　存储过程procedure

　　被内容来自《oracle从入门到精通——明日科技》一书

　　存储过程是一种命名的PL/SQL程序快，存储过程被保存在数据库中，它不可以被SQL语句直接执行或调用，只能通过EXECUT命令执行或在PL/SQL程序快中内部调用。由于存储过程是已经编译好的代码，所以在被调用或引用时，其执行效率非常高。本次所有操作均在Oracle 18c本版。

1.创建存储过程

　　1.1.创建存储过程的语法

　　　　使用关键字create、procedure，关键字后面时过程名字和参数列表，其基本语法如下：　

create [or replace] procedure pro_name [(parameter1 [,parameter2]...)] is|as
begin
    plsql_sentences;
[exception]
    [dowith_sentences;]
end [pro_name];

　　　　1.1.1.pro_name：存储过程的名字。如果数据库存在该名字，可以加上or replace ，这样新的存储过程就会覆盖掉原来已经存在的存储过程。

　　　　1.1.2.parameter1：存储过程的参数。如果输入参数则需要在后面使用in关键字，如果输出参参数则在后面使用out关键字。in和out关键字后面时参数的类型，不能指定参数类型的长度。

　　　　1.1.3.plsql_sentences：PL/SQL语句。它是存储过程实现的主体。

　　　　1.1.4.dowith_sentences：异常处理语句。也是PL/SQL语句，可以没有。

　　　　1.1.5.在创建存储过程中IS关键字也可以用AS代替，效果是一样的。

　　1.2.下面通过一个实际例子来解释如何创建一个存储过程。

　　　　假如有一个表：

create teble t_user(
    t_name varchar2(20) not null,
    t_address varchar2(100) not null     
)tablespace low_data;

　　　　下面创建一个存储过程，向表user插入数据：

create procedure add_data_to_user is
begin
    insert into user (t_name,t_address) values ('tony','shanghai');--插入数据的sql语句，存储过程的实体
    commit;--提交事务
    dbms_output.put_line('新插入数据成功！');--插入数据成功返回的信息提示，可以没有
end add_data_to_user;

　　　　上面的存储过程为：向表user插入一条记录，存储过程的名字是add_data_to_user。如果在当前模式下数据库已经存在名为add_data_to_user的存储过程，则使用or replace关键字创建，如下：

create or replace procedure add_data_to_user is
begin
    insert into user (t_name,t_address) values ('simth','beijing');--插入数据的sql语句，存储过程的实体
    commit;--提交事务
    dbms_output.put_line('新插入数据成功！');--插入数据成功返回的信息提示，可以没有
end add_data_to_user;

　　　　这样就会覆盖之前创建的同名的存储过程。如果不存在则为直接创建名为add_data_to_user的存储过程。

　　　　如果创建的过程中出现的错误，用户可以使用'show error'命令查看错误信息。

　　　　上面的存储过程主体inert语句仅仅是被编译了，并没有执行，下面说一下如何执行。

　　1.3.执行存储过程

　　　　在SQL*Plus环境下，执行存储过程使用关键字execute命令执行存储过程的名字即可，语句如下：

execute add_data_to_user;

　　　　在PL/SQL中执行存储过程语句为：

begin
    add_data_to_user;
end;

2.存储过程的关键字

　　Oralce为了增强存储过程的灵活性，提供向存储过程传入参数的功能。存错过程可以接受多个参数，参数模式包括：IN、OUT、INOUT三种。

　　2.1.IN模式参数

　　　　这是一种输入型参数。参数值由调用方传入，并且只能被存储过程使用读取。这种模式是最长用的，也是默认的。

　　　　用法：parameter_name IN paramter_type，即（参数名称 IN 参数类型）。需要注意的是参数类型不能指定长度。

　　　　下面创建一个IN模式的存储过程：

create or replace procedure insert_data_to_user(
       d_name in varchar2,--参数一
       d_address in varchar2--参数二
) is 
begin 
  insert into t_user(t_name,t_address) values(d_name,d_address);--存储过程的实体，向表中插入数据
  commit;--提交事务
end;

　　　　 向存储过程传递参数有三种方式：

　　　　2.1.1.制定名称传递

　　　　　　制定名称传递是指在向存储过程传递参数时需要指定参数的名称，即参数名称在左，中间时赋值符号“=>”，右侧是参数值。其语法如下：

pro_name(parameter1=>value1[,parameter2=>value2...])　　　　　　

　　　　　　parameter1：参数名称。在传递参数时，这个参数名称的顺序和存储过程定义的顺序无关。
　　　　　　value2：参数值。参数值的类型要和存储过程中定义的类型相兼容。
　　　　　　=>：是一种新的赋值符号。
　　　　　　下面是按照制定名称方式执行我们刚才创建的存储过程：

--第一种方式
SQL> begin
  2  insert_data_to_user(d_name=>'张三',d_address => '上海');
  3  end;
  4  /

PL/SQL procedure successfully completed
--第二种方式
SQL> begin
  2  insert_data_to_user(d_address=>'北京',d_name => '李四');
  3  end;
  4  /

PL/SQL procedure successfully completed

　　　　　　我们可以看到第一种方式是按照存储过程中定义的顺序传递参数的，第二种方式没有按照存储过程中定义的顺序传递参数，但都执行成功了。可以看到表中现在存在了这两个记录：

SQL> select * from t_user;

T_NAME               T_ADDRESS
-------------------- --------------------------------------------------------------------------------
张三                 上海
李四                 北京

SQL> 

　　　　2.1.2.按位置传递

　　　　　　按指定名称传递参数时虽然直观易读，但也有缺点，就是如果参数过多时，代码就会冗余。因此我们可以采取按位置传递。但需要注意的是，采取这种方式我们要保证传递的参数值要和存储过程中定义的顺序一致。接下来我们还以

insert_data_to_user存储过程为例向中传递参数：

SQL> begin
  2  insert_data_to_user('博客园 ','位置不详 ');
  3  end;
  4  /

PL/SQL procedure successfully completed

　　　　　　注意：我上面插入数据时参数值后面有个空格，我这是自己的字符集出现问题了，所以需要这样做，真实项目中不用这样做。我们查询表可以看到刚插入的数据：

SQL> select * from t_user;

T_NAME               T_ADDRESS
-------------------- --------------------------------------------------------------------------------
张三                 上海
李四                 北京
博客园               位置不详

SQL> 

　　　　　　有的时候参数过多时，我们记不住参数的类型和顺序，这时我们可以使用desc命令查看存储过程中的参数定义信息，如：

SQL> desc insert_data_to_user;
Parameter Type     Mode Default? 
--------- -------- ---- -------- 
D_NAME    VARCHAR2 IN            
D_ADDRESS VARCHAR2 IN     

　　　　2.1.3.混合方式传递

　　　　　　混合方式就是将前面两种方式结合到一起使用。为了方便测试我们重新新建一个存储过程，具有多个参数的存储过程：

--创建表t_product
create table t_product(
       p_id number not null,
       p_name varchar2(50) not null,
       p_count number,
       p_price number,
       p_area varchar2(200)
)tablespace WCH_DATA;
--添加表、字段注释
comment on table t_product is'产品/商品表 ';
comment on column t_product.p_id is '主键 ';
comment on column t_product.p_name is '产品名称 ';
comment on column t_product.p_count is '产品数量 ';
comment on column t_product.p_price is '产品价格 ';
comment on column t_product.p_area is '产品生产地区 ';
--创建Sequence
create sequence seq_product_id
minvalue 1
maxvalue 99999999999
start with 1
increment by 1
cache 20;
--创建存储过程
create or replace procedure  insert_data_to_product(
       d_name in varchar2,
       d_count in number,
       d_price in number,
       d_area in varchar2
) is 
begin
  insert into t_product values(seq_product_id.nextval,d_name,d_count,d_price,d_area);
  commit;
end;

　　　　　　我们创建了一个名为：insert_data_to_product的存储过程，参数有d_name，d_count，d_price，d_area。其中表的主键id通过我们创建的序列seq_product_id产生。下面我们来通过混合方式传递参数执行这个存储过程：

SQL> begin
  2  insert_data_to_product('apple',100,d_price => 5,d_area => 'china');
  3  end;
  4  /

PL/SQL procedure successfully completed

　　　　　　需要注意的是：在某个位置使用了按名称传递方式后，其后面的参数也要使用按名称传递。因为按名称传递的方式有可能已经破坏了参数原始的定义顺序。

　　2.2.OUT模式

　　　　本次我们需要用到表t_product，里面的数据为：

SQL> select * from t_product;

      P_ID P_NAME                                                P_COUNT    P_PRICE P_AREA
---------- -------------------------------------------------- ---------- ---------- --------------------------------------------------------------------------------
         1 apple                                                     100          5 china
         2 汽车                                                       20     100000 美国
         3 飞机                                                       10   65900000 美国
         4 大豆                                                   300000        2.2 巴西
         5 手机                                                   300000       3200 中国

　　　　OUT模式是一种输出类型的参数。表示这个参数在存储过程中已经被赋值，并且可以将这个参数传递到存储过程之外的环境中去。关键字位于参数名称之后，和IN 模式相似。下面定义一个OUT模式的存储过程：

create or replace procedure get_data_from_product (
       g_id in number,       --声明一个输入变量参数
       g_name out varchar2,  --声明一个输出变量
       g_count out varchar2  --声明一个输出变量
) is
begin
  select p_name,p_count into g_name,g_count from t_product where p_id=g_id;--存储过程主体，根据传入的id查询产品的名字和数量
end;

　　　　在上面的存储过程中定义了两个OUT类型的参数，因为OUT模式需要输出参数，所以定义两个参数用来接受返回值。

　　　　2.2.1.在PL/SQL中调用OUT模式的存储过程

　　　　　　这种方式需要在PL/SQL块中通过declare定义与存储过程中OUT参数相兼容的变量，如：

SQL> set serveroutput on 
SQL> declare
  2  var_name t_product.p_name%type;
  3  var_count t_product.p_count%type;
  4  begin
  5  get_data_from_product(2,var_name,var_count);
  6  dbms_output.put_line(var_name);
  7  dbms_output.put_line(var_count);
  8  end;
  9  /

汽车
20

PL/SQL procedure successfully completed

 　　　　　　在这段代码中我们首先声明了两个变量var_name和var_count，变量类型要和存储过程中定义的OUT变量相兼容。然后把两个变量传入到存储过程中，当存储过程被执行时，其中out参数会被赋值，当存储过程执行完之后，OUT参数的值会在会在调用处返回。这样定义的两个变量就可以得到OUT参数被赋予的值了。

　　　　2.2.2.使用exec命令执行OUT模式的存储过程

　　　　　　使用exec命令需要在SQL*Plus环境中使用variable关键字声明两个变量，将声明的了两个变量接受OUT参数的返回值。如：

SQL> variable v_name varchar2(50);
SQL> variable v_count number;
SQL> exec get_data_from_product(3,:v_name,:v_count);

PL/SQL procedure successfully completed

v_name
---------
飞机
v_count
---------
10

　　　　　　我们还可以使用print或者select命令来查看变量的值如：

SQL> print v_name;
v_name
---------
飞机

SQL> 
SQL> print v_count;
v_count
---------
10


SQL> select :v_name,:v_count from dual;

:V_NAME                                                                            :V_COUNT
-------------------------------------------------------------------------------- ----------
飞机                                                                                     10
v_name
---------
飞机
v_count
---------
10