第十二节：存储过程

存储过程简介

  1. 存储过程：是一组为了完成特定功能的SQL 语句集。数据库中的一个重要对象。它存储在数据库中，一次编译后永久有效
  2. 存储过程的作用：是将常用或复杂的工作预先用 SQL 语句写好并用一个指定名称存储起来，这个过程经编译和优化后存储在数据库服务器中，因此称为存储过程。
  3. 存储过程通常有如下优点：
    1. 封装性：对SQL语句进行封装，可以实现多次调用
    2. 增强功能和灵活性：可以用流程控制语句增加其灵活性，可以完成复杂的判断和较复杂的运算
    3. 减少网络流量：存储过程是存储在服务器端中的，客户端调用存储过程时，网络中传送的只是该调用语句而不是整个SQL语句
    4. 高性能：存储过程执行一次后，产生的二进制代码就驻留在缓冲区，在以后的调用中，只需要从缓冲区中执行二进制代码即可
    5. 提高数据库的安全性和数据的完整性：使用存储过程可以完成所有数据库操作，且可以通过编程的方式控制数据库信息访问的权限

存储过程的创建和调用

  1. 语法格式：

    CREATE
    [DEFINER = { user | CURRENT_USER }]
    　PROCEDURE sp_name ([proc_parameter1,...,proc_parametern])
    [characteristic ...]
    [begin_label:] BEGIN
    　　SQL语句
    END [end_label]

  2. 语法说明：

    1. [DEFINER = { user | CURRENT_USER }]：指定存储过程的定义者
      1. user ：用户的名称，表示user用户创建存储过程
      2. CURRENT_USER：表示当前用户创建存储过程(指定CURRENT_USER和不使用[DEFINER = { user | CURRENT_USER }]这行的效果是一样的)
    2. sp_name：存储过程的名称，默认在当前数据库中创建。若需要在特定数据库中创建存储过程，则要在名称前面加上数据库的名称。(如:db_name.sp_name)
    3. proc_parameter：存储过程的参数；即使存储过程没有参数，存储过程的名称后仍需加上括号
      1. 格式：IN/OUT/INOUT 参数名称 参数的数据类型 
      2. 参数的数据类型：可以是任何有效的 MySQL 
      3. 参数类型：存储过程支持三种类型的参数,即输入参数、输出参数和输入/输出参数
    4. characteristic:指定存储的特性，如果没有指定特征，会使用默认的特征值来创建存储过程.特征如下:
      1. COMMENT 'string'：存储过程的注释性信息写在COMMENT里面
      2. LANGUAGE SQL：存储过程使用的语言，说明存储过程中使用的是sql语言编写的，默认是sql
      3. [NOT] DETERMINISTIC：当确定每次的存储过程的输入和输出都是相同的内容时，可以使用DETERMINISTIC，默认为NOT DETERMINISTIC
      4. CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA ：提供子程序使用数据的内在信息，这些特征值目前提供给服务器，但是目前mysql中定义该特征没有任何效果
        1. CONTAINS SQL表示子程序不包含读或者写数据的语句
        2. NO SQL 表示子程序不包含sql
        3. READS SQL DATA 表示子程序包含读数据的语句，但是不包含写数据的语句
        4. MODIFIES SQL DATA 表示子程序包含写数据的语句
    5.  SQL SECURITY DEFINER | INVOKER ：用来指定存储过程由定义者来执行，还是由执行者来执行，默认值是DEFINER
      1. DEFINER 表示只有定义者自己才能够执行
      2. INVOKER 表示调用者可以执行
    6. 过程体：也称为存储过程体。以关键字 BEGIN 开始，以关键字 END 结束，包含在过程调用时执行的SQL语句(dml、ddl语句，if-then-else和while-do语句、声明变量的declare语句等)。若存储过程体中只有一条SQL 语句，可以省略 BEGIN-END
      1. 格式：以begin开始，以end结束(可嵌套) 
        BEGIN
　　    BEGIN
　　　　    BEGIN
　　　　　　    statements; 
　　　　    END
　　END
END
      2. 注意：每个嵌套块及其中的每条语句，必须以分号结束，表示过程体结束的begin-end块(又叫做复合语句)，则不需要分号
      3. 复合语句贴标签:
        1. 格式： 
          begin_label: begin
　　sql
end end_label
        2. 标签有作用：增强代码的可读性；在某些语句(例如:leave和iterate语句)，需要用到标签
        3. 实例： 
          label1: BEGIN
　　label2: BEGIN
　　　　label3: BEGIN
　　　　　　statements; 
　　　　END label3 ;
　　END label2;
END label1
  3. 调用存储过程：call sp_name[(传参)]
  4. delimiter：声明语句结束符
    1. 作用：修改SQL语句的结束符
    2. 语法格式：DELIMITER 字符
    3. 对存储过程作用：在MySQL中默认是以分号作为SQL语句的结束标志。而在创建存储过程时，存储过程体可能包含有多条 SQL 语句，这些SQL语句是以分号作为语句结束符，MySQL在处理时会以遇到的第一条 SQL 语句结尾处的分号作为整个程序的结束符，而不再去处理存储过程体中后面的 SQL 语句。若在定义过程时，使用 DELIMITER $ 命令将语句的结束符号从分号临时改为$，使得过程体中使用的分号被直接传递到服务器，而不会被客户端解释为程序的结束
  5. 实例： 
    delimiter ,　　

create procedure select1(in name varchar(20)) select * from commodity where c_name =name,
create definer='weiking'@'%' procedure select2(in name varchar(20)) select * from commodity where c_name =name,
create definer='weiking'@'%' procedure select3(in name varchar(20)) language sql not deterministic contains sql sql security definer comment 'this a stored procedure' begin select * from commodity where c_name =name; end

delimiter ;
　　
call cel('iphonex');

删除存储过程

  1. 语法格式如下：drop procedure  [ if exists ] 过程名
  2. 语法说明如下
    1. 过程名:指定要删除的存储过程的名称。存储过程名称后面没有参数列表，也没有括号
    2. if exists:用于防止因删除不存在的存储过程而引发的错误
  3. 实例：drop procedure showSomeCommodity;

修改存储过程

  1. 语法格式：alter procedure  sp_name characteristic  语句只能改变存储过程的特征，不能修改过程的参数以及过程体
  2. 如要修改存储过程的参数、过程体和名称，可以先删除该存储过程，再重新创建

查询存储过程

  1. 查看某个数据库下面的存储过程：
    1. select name from mysql.proc where db= '数据库名';
    2. select routine_name from information_schema.routines where routine_schema='数据库名';
    3. show procedure status where db='数据库名';
  2. 查看存储过程的创建过程：show create procedure 数据库.存储过程名;

存储过程的参数

  1. 存储过程的参数共有三种类型：IN、OUT、INOUT
    1. IN 输入参数：表示调用者向过程传入值（传入值可以常量或变量）
    2. OUT 输出参数：表示过程向调用者传出值(可以返回多个值)（传出值只能是变量）
    3. INOUT 输入输出参数：既表示调用者向过程传入值，又表示过程向调用者传出值（值只能是变量）
  2. 建议：
    1. 输入值使用in参数
    2. 返回值使用out参数
    3. inout参数就尽量的少用（该参数就是将输入的参数值进行转换后输出）
  3. 实例：
    1. in输入参数 
      delimiter ,
create procedure int_para(in id int)begin select id ;set id = 3;select id; end,
set @ip =12,
delimiter ;
call in_param(@ip);  输出的结果分别是12和3
select @ip;输出的结果是12,只是将@ip的值赋值给id，改变id不影响@Ip的值
    2. out输出参数 
      delimiter ,
create procedure out_para(out id int)begin select id ;set id = 3;select id; end,
set @ip= 11,
delimiter ;
call out_param(@ip); 输出的结果分别为null和3,因为out是向调用者输出参数，不接收输入的参数
select @ip;输出的结果为3 ,调用了out_para存储过程，输出参数，改变了@ip变量的值
    3. inout输入参数 
      delimiter ,
create procedure inout_para(inout id int)begin select id ;set id = 3;select id; end,
set @ip=12,
delimiter ;
call inout_para(@ip);输出的结果为 12和3
select @ip;输出的结果为3 ，调用了inout_param存储过程，接受了输入的参数，也输出参数，改变了变量

 

存储过程中的变量

用户变量

  1. 用户变量：mysql系统的全局变量，该变量随着用户连接断开而消失，如果不定义用户变量直接查询返回null
  2. 用户变量名以@开头
  3. 定义用户变量有两种方式：
    1. set @变量名=值/表达式(set @id=1+3*5;)
    2. select 值/表达式 into @变量名(select 123 into @id;)
  4. 查询用户变量：select @变量名 (select @id) 
  5. 实例： 
    set @name = 'world';
delimiter ?
create procedure sayHello() begin select concat('hello',@name);end?
call sayHello()?

局部变量

  1. 存储过程体的变量：属于局部变量，在存储过程体的头部进行声明，只对该过程体可见
  2. 定义变量：declare 变量名,...,变量名n 变量类型 [default 默认值] 。实例如下：
    1. declare id int unsigned default 4000000;  
    2. declare to_date date default '1999-12-31'; 
  3. 赋值变量：set 变量名1=变量值1/表达式,...,变量名2=变量值2/表达式
  4. 查询变量：select 变量
  5. 实例： 
    create procedure sayHello1() begin declare say varchar(11);set say = 'hello';select concat(say,@name);end?
call sayhello1()?

  6. 拓展：整型的每一种都有无符号（unsigned）和有符号（signed）两种类型，在默认情况下声明的整型变量都是有符号的类型，如果需声明无符号类型的话就需要在类型前加上unsigned。无符号版本和有符号版本的区别就是有符号类型需要使用一个bit来表示数字的正负 

变量的作用域

  1. 用户变量：在mysql系统中全局有效
  2. 局部变量：只在其作用域内有效，享有更高的优先权，当执行到end时，已经不属于存储体的作用域，该存储体中的变量将不可见
  3. 如果是多层begin...end 嵌套，则内部的begin...end中的变量对于外部 begi...end而言不可见
  4. 实例 
    create procedure proName() -- 输出结果为inner和outer
begin
declare x varchar(25) default 'outer';
begin
declare x varchar(25) default 'inner';
select x;
end;
select x;
end ?
posted @ 2019-12-17 17:03  WeiKing  阅读(369)  评论(0)    收藏  举报