存储过程

介绍：

• SQL语句需要先编译然后执行，而存储过程是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数(如果该存储过程带有参数)来调用执行它。

• 存储过程是可编程的函数，在数据库中创建并保存，可以由SQL语句和控制结构组成，当想要在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟，它允许控制数据的访问方式。

优点：

1. 增强SQL语言的功能和灵活性：存储过程可以用控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。

2. 标准组件式编程：存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。

3. 较快的执行速度：如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行编译和优化，速度相对要慢一些。

4. 减少网路流量：针对同一个数据库对象的操作(如查询、修改)，如果这一操作所涉及的Transaction-SQL语句被组织进存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大减少网络流量并降低了网络负载。

5. 作为一种安全机制来充分利用：通过对执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证的数据的安全。

存储过程的创建：

• 语法：Create producedure 过程名（[[in|out|inout]参数名 参数类型，[in|out|inout]参数名 数据类型...]])

  delimiter //
    create procedure myproc(out s in)
      begin 
        select count(*) into s from students;
      end
      //
  delimiter ;

• 分隔符：

  Mysql默认以“;”为分隔符，如果没有什么分隔符，则编译器会把存储过程当成SQL语句进行处理，因此编译过程会报错，所以要事先用“DELIMITER //声明当前段分隔符，让编译器把两个“//”之间的内容当做存储过程的代码，不会执行这些代码；"DELIMITER；"的意为把分隔符还原。

• 参数：

  存储过程根据需要可能会有输入、输出、输入输出参数，如果有多个参数用“，”分隔开。MySQL存储过程的参数用在存储过程的定义，共有三种参数类型，IN,OUT,INOUT：

  • In参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值。

  • OUT：该值可在存储过程内部被改变，并可返回

  • InOUT:调用时指定，并且可被改变和返回

• 过程体

  过程体的开始与结束使用begin和end进行标识。

  in参数例子

delimiter //
  create procedure in_param(in p_in int)
    begin
    select p_in;
    set p_in =2;
    select p_in;
    end;
    //
 delimiter;
 #调用
 set @p_in=1;
 call in_param(@p_in);
 select @p_in;
 #执行结果：
   p_in
 1 1
   p_in
 1 2
   @p_in
 1 1
 * 以上可以看出，p_in虽然在存储过程中被修改，但并不影响@p_id的值

out参数例子

#存储过程out参数
delimiter //
  create procedure out_param(out p_out int)
    begin
      select p_out;
      set p_out=2;
      select p_out;
    end;
    //
delimiter;
#调用
set @p_out=1;
call out_param(@p_out);
select @p_out;
#执行结果:
   p_out
1  <null>
   p_out
1  2
   @p_out
1  2

inout参数例子

#存储过程inout参数
delimiter //
  create procedure inout_param(inout p_inout int)
    begin
      select p_inout;
      set p_inout=2;
      select p_inout;
    end;
    //
delimiter;
#调用
set @p_inout=1;
call inout_param(@p_inout);
select @p_inout;
#执行结果：
    p_inout
 1  1
    p_inout
 1  2
    @p_inout
 1  2

• 变量

  语法：declare 变量名1[,变量名2...] 数据类型[默认值]；

  数据类型为MySQL的数据类型

• 变量赋值

  语法：set 变量名 = 变量值[,变量名 = 变量值...]

• 用户变量

  用户变量一般以@开头

• #在MySQL客户端使用用户变量

select 'hello world' into @x;
select @x;
set @y='Goodbye Cruel World';
select @y;
set @z = 1+2+3;
select @z;
#执行结果
    @x
 1  Hello World
    @y
 1  Goodbye Cruel World
    @z
 1  6

• #在存储过程中使用用户变量

  create procedure GreetWorld() select concat(@greeting,'World');
  set @greeting = 'Hello';
  call GreetWorld();
  #执行结果
     concat(@greeting,'world')
  1  Hello World

• #在存储过程间传递全局范围的用户变量

  CREATE PROCEDURE p1() SET @last_proc='p1';
  CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_proc);
  CALL p1();
  CALL p2();
  #执行结果：
     concat('last procedure was',@last_proc)
  1  Last procedure was p1

  MySQL存储过程的修改

  alter procedure 更改用create procedure建立的预先指定的存储过程，其不会影响相关存储过程或存储功能。

alter {procedure | function} sp_name [characteristic ...] characteristic;
{contains sql | no sql | reads sql data |modifies sql data}
| sql security {definer | invoker} | comment 'string'

• sp_name：参数表示存储过程或函数的名称；

• characteristic参数指定存储函数的特性；

• contains sql：表示子程序包含sql语句，但不包含读或写数据的语句；

• no sql：表示子程序不包含sql语句；

• reads sql data：表示子程序中包含读数据的语句；

• modifies sql data：表示子程序中包含写数据的语句；

• sql security{definer | invoker}指明谁有权限来执行，definer表示只有定义者自己才能够执行；invoker表示调研者可以执行。

• comment 'string'：注释信息

  实例

#将读写权限改为modifies sql data，并指明调用者可以执行
alter procedure num_from_employee
  modifies sql data
  sql security invoker ;
#将读写权限改为reads sql data，并加上注释信息'find name'。
alter procedure name_from_employee
  reads sql data
  comment 'find name';

MySQL存储过程的删除

DROP PROCEDURE [过程1[,过程2..]]

从MySQL的表格中删除一个或多个存储过程。

MySQL存储过程的查询

#查询存储过程
select name from mysql.proc where db = '数据库名'；
select routine_name from information_schema.routines where routine_schema='数据库名';
show procedure status where db = '数据库名';
#查看存储过程详细信息
show create procedure 数据库，存储过程名；

MySQL存储过程的控制语句

变量作用域

• 内部变量在其作用域范围内享有更高的优先权，当执行到end时，内部变量消失，不可再见了，在存储过程外再也找不到这个内部变量，但是可以通过out参数或者将其值指派给会话变量来保存其值。