存储过程

一、存储过程简介

　　存储过程是Oracle开发者在数据转换或查询报表时经常使用的方式之一。它就是想编程语言一样一旦运行成功，就可以被用户随时调用，这种方式极大的节省了用户的时间，也提高了程序的执行效率。存储过程在数据库开发中使用比较频繁，它有着普通SQL语句不可替代的作用。所谓存储过程，就是一段存储在数据库中执行某种功能的程序。其中包含一条或多条SQL语句，但是它的定义方式和PL/SQL中的块、包等有所区别。存储过程可以通俗地理解为是存储在数据库服务器中的封装了一段或多段SQL语句的PL/SQL代码块。在数据库中有一些是系统默认的存储过程，那么可以直接通过存储过程的名称进行调用。另外，存储过程还可以在编程语言中调用，如Java、C#等。

 

二、存储过程优点

1. 简化复杂的操作。存储过程可以把需要执行的多条SQL语句封装到一个独立单元中，用户只需调用这个单元就能达到目的。这样就实现了一人编写多人调用。
2. 增加数据独立性。与视图的效果相似，利用存储过程可以把数据库基础数据和程序（或用户）隔离开来，当基础数据的结构发生变化时，可以修改存储过程，这样对程序来说基础数据的变化是不可见的，也就不需要修改程序代码了。
3. 提高安全性。使用存储过程有效降低了错误出现的几率。如果不使用存储过程要实现某项操作可能需要执行多条单独的SQL语句，而过多的执行步骤很可能造成更高的出现错误几率。
4. 提高性能。完成一项复杂的功能可能需要多条SQL语句，同时SQL每次执行都需要编译，而存储过程可以包含多条SQL语句，而且创建后只需要编译一次，以后就可以直接调用。

 

三、基本语法

　　

CREATE [OR REPLACE] procedure pro_name    [(parameter1[,parameter2]...] is|as
begin
    plsql_sentences;
[exception]
    [dowith_sentences;]
end [pro_name];

1. 存储过程名定义：包括存储过程名和参数列表。参数名和参数类型。参数名不能重复。参数的数据类型只需要指明类型名即可，不需要指定宽度。 参数的宽度由外部调用者决定。 存储过程可以有参数，也可以没有参数。
2. 变量声明块：紧跟着的as (is )关键字，可以理解为pl/sql的declare关键字，用于声明变量。 变量声明块用于声明该存储过程需要用到的变量，它的作用域为该存储过程。另外这里声明的变量必须指定宽度。
3. 过程语句块：从begin 关键字开始为过程的语句块。存储过程的具体逻辑在这里来实现。
4. 异常处理块：关键字为exception ，为处理语句产生的异常。该部分为可选 。
5. 结束块：由end关键字结束。

 存储过程的参数传递方式 ：
　　存储过程的参数传递有三种方式:IN,OUT,IN OUT . 
　　IN 按值传递，并且它不允许在存储过程中被重新赋值。如果存储过程的参数没有指定存参数传递类型，默认为IN

 

 四、无参存储过程

　　    1、先创建一个表

　　　　

create table salary
(
sncode varchar2(4) ,
sname varchar2(20), 
birth varchar2(20),
salary number(8,2)
);

comment on table salary is
'薪资表';
comment on  column salary.sncode is
'员工号';
comment on column salary.sname is
'姓名';
comment on  column salary.birth is
'出生时间';
comment on column salary.salary is
'薪水';

Insert into salary(sncode,sname,birth,salary) Values('C001','张三','1986.01.10',1100);  
Insert into salary(sncode,sname,birth,salary) Values('C002','李四','1980.10.10',3000);  
Insert into salary(sncode,sname,birth,salary) Values('C003','王五','1996.12.10',800);  
commit;  

 

  　　2、创建存储过程

　　

create or replace procedure salary_proc is
begin
       Insert into salary(sncode,sname,birth,salary) Values('C004','王六','1996.12.10',800);  
       commit;
       dbms_output.put_line('插入成功')   ;
end salary_proc;

　　这时候select * from salary

　　这时还没有插入数据，我们只是创建了存储过程而并没有执行,若想要执行的话,使用Execute关键字来执行存储过程；也可以简写”EXEC“

　　

　　3、调用存储过程

　　

SQL> exec salary_proc;
PL/SQL procedure successfully completed

　　

五、带in参数存储过程

　　输入类型参数,参数由调用者传入,只能被存储过程读取,是默认的参数模式,也是最常用的

　　1、创建带in参数的存储过程

create or replace procedure salary_proc(sncode in varchar2,sname in varchar2,birth in varchar2,salary_mon in number)
is
begin
  insert into     salary values(sncode,sname,birth,salary_mon);
  commit;
end salary_proc;

　　创建一个存储过程,并定义3个IN模式的变量,然后将这3个变量的值插入到salary 表中

 　　

　　2、 调用带参的存储过程

　　　　调用带参的存储过程（传参数）的方式有三种

 　　　　1)指定名称传递-->参数名称在左,参数值在右,中间使用赋值符号"=>"连接:

　　　　

SQL> exec salary_proc(sname =>'张一山',birth =>'2000.10.01',salary_mon =>100000,sncode => 'c008');
PL/SQL procedure successfully completed

　　　　2)按位置传递(这种方式不用写字段名称,所以赋值顺序必须与字段标准顺序一致)

　　　　

SQL> exec salary_proc('c008','杨紫','2001.10.01',200000);
PL/SQL procedure successfully completed

　　　　

　　　  3）混合方式传递(顾名思义:这是将前两者结合使用的)

SQL> exec salary_proc('c009','李连杰',salary_mon =>300000,birth =>'1988.10.01');
PL/SQL procedure successfully completed

 　　　

　　3、IN参数的默认值(IN类型是可以设定默认值的

create or replace procedure salary_proc(
sncode in varchar2,
sname in varchar2,
birth in varchar2,
salary_mon in number default 10000)
is
begin
  insert into     salary values(sncode,sname,birth,salary_mon);
  commit;
end salary_proc;

　　调用

SQL> exec salary_proc('c011','成龙','19800101')
PL/SQL procedure successfully completed

 

 

 　　特别注意:因为在中间使用了"指名方式"传值,所以后面的参数都要使用指名方式;因为指名方式可能已经破坏了参数原始的定义顺序了.

 

六、带out参数存储过程

　　输出类型参数,表示这个参数在存储过程中已经被赋值,并且参数值可以传递到当前存储过程以外的环境中

     1、创建

　　创建存储过程，要求定义两个OUT模式的字符类型的参数，然后在salary表中检索到的姓名和薪水存储到这两个参数中

create or replace procedure salary_select(in_sncode in varchar2,
out_name out salary.sname%type,
out_salary_mon out salary.salary_mon%type)
is
begin
  select sname,salary_mon 
  into out_name,out_salary_mon
  from salary
  where sncode=in_sncode;
exception
  when no_data_found then
    dbms_output.put_line('没有该员工');
  
end salary_select;

 

　　2、调用带out参数的存储过程

　　当调用或者执行带out参数的存储过程时,都需要定义变量来保存这两个out参数，下面对OUT模式如何调用或执行分别举例子说明：

 

　　1）在PL/SQL块中调用OUT模式的存储过程:在PL/SQL块的DECLARE部分定义与存储过程中out参数兼容的若干变量

　　首先在PL/SQL块中声明若干变量，然后调用select_dept_out存储过程,并将定义的变量传入该存储过程,以便接收out参数的返回值

SQL> declare
  2  out_name salary.sname%type;
  3  out_salary_mon salary.salary_mon%type;
  4  begin
  5  salary_select('c011',out_name,out_salary_mon);
  6  dbms_output.put_line('姓名：'||out_name||'薪水：'||out_salary_mon);
  7  end;
  8  /

姓名成龙薪水10000
PL/SQL procedure successfully completed

　　具体过程：执行上述代码时，声明的两个变量会被传入到存储过程中，但存储过程执行时，其中的out参数会被赋值，存储过程执行完毕后，OUT参数的值会在调用处（begin）返回，之后定义的两个变量(declare)就能得到传回来的值，就可以在存储过程之外任意使用了。

 

　　2）使用Exec执行OUT模式的存储过程:使用Exec命令需要在SQL*Plus环境中使用variable关键字声明两个变量,用来存储out参数的返回值

　　使用variable关键字声明两个变量,分别用来存储姓名和薪水,然后使用exec命令执行存储过程,并传入声明的两个变量来接收out参数的返回值

SQL> variable sname varchar2(10);
SQL> variable salary_mon number;
SQL> exec salary_select('c009',:sname,:salary_mon);
PL/SQL procedure successfully completed
sname
---------
李连杰
salary_mon
---------
300000

 

七、带in、out参数的存储过程

开始之前咱们现总结一下IN和OUT的特性：

在执行存储过程时,

IN参数只能根据调用者传入的值去执行存储过程,不能被修改;

OUT参数只能等待存储过程执行完毕为其赋值再供外界使用,不能像IN一样为存储过程提供数据;

 到這里,大家想一想:如果我要是想【计算一个数的平方或者平方根】,这种存储过程怎么写呢？

岂不是要是用IN传入一个数,再用OUT定义一个变量来接收了？不过大家仔细想一下，我们想要计算的值传进去后，就没用了，如果再原路将计算结果返还回来，那该多好，就不用单独定义OUT参数了，结果就有了IN OUT模式参数

IN OUT就是解决这个问题的;兼顾了IN和OUT的参数特性调用存储过程时，上面的分析如果看懂了，这里就不详细解释定义了。就是给定一个参数，在存储过程执行过程中，发生了改变，之后再将该参数原路返还给调用者；

create or replace procedure pro_square(
    num in out number,
    flag in boolean) is
    i int:=2;                --表示计算平方
begin
    if flag then             --if语句,如果为true
      num:=power(num,i);     --计算平方
    else                     --否则
      num:=sqrt(num);        --计算平方根
      end if;
end pro_square;

 

调用 

 

SQL> declare
  2  var_number number;
  3  var_temp number;
  4  boo_flag boolean;
  5  begin
  6  var_temp :=5;  --:=表示赋值
  7  var_number:=var_temp;
  8  boo_flag:=false;
  9  pro_square(var_number,boo_flag);
 10  if boo_flag then
 11  dbms_output.put_line(var_temp||'平方'||var_number);
 12  else
 13  dbms_output.put_line(var_temp||'平方根'||var_number);
 14  end if;
 15  end;
 16  /

5平方根2.23606797749978969640917366873127623544
PL/SQL procedure successfully completed