- MySQL - 存储过程 Stored Procedure


存储过程是什么

存储过程是一组为了完成特定功能的SQL语句集,存储在数据库中,一次编译多次使用,用户通过指定存储过程的名字并传入必要的参数来调用执行它;

存储过程就是数据库语言层面的代码封装与重用;


存储过程的优点

  • 将重复性很高、复杂的一些操作,封装到一个存储过程中,简化了对这些SQL的调用;
  • 可以重复使用,减少数据库开发人员的工作量;
  • 一次编译多次调用执行,可以提高数据库执行速度;
    存储过程只在创建的时候进行编译,以后每次调用的时候不需要再编译,一般的SQL语句每次执行都要先编译;
  • 可以使用SQL+循环进行批处理;
  • 存储过程存放在服务器上,调用的时候只需要传递存储过程的名称和必要的参数就可以了,降低了网络传输的数据量;
  • 参数化的存储过程可以防止SQL注入,也可以给存储过程设置权限,确保了数据的安全;

存储过程的缺点

  • 可移植性差:不同数据库的存储过程的语法不同,切换数据库时需要重新编写存储过程;
  • 每个数据库的存储过程语法几乎都不一样,十分难以维护(不通用);
  • 调试麻烦;

存储过程分类

1、系统存储过程:以sp_开头,用来进行系统的各项设定、取得信息、相关管理工作;

2、本地存储过程:一般说的存储过程就是本地存储过程,是由用户创建的,用来完成某一特定功能;

3、临时存储过程:(sql server)
本地临时存储过程:以#作为名称的开头;只有创建的用户自己能使用;
全局临时存储过程:以##作为名称的开头;连接到数据库服务器的用户都能调用;

4、远程存储过程:(sql server)

5、扩展存储过程:用户可以使用外部程序语言编写的存储过程,名字通常以xp开头;


存储过程的查询语句

查看某个数据库下面的存储过程:
select pName from mysql.proc where db='数据库名';
select routine_name from information_schema.routines where routine_schema='数据库名';
show procedure status where db='数据库名';

查看存储过程的详细信息:show create procedure dbName.pName;

修改存储过程:alter procedure procedureName;

删除存储过程:drop procedure procedureName;


存储过程的创建和调用

https://www.runoob.com/w3cnote/mysql-stored-procedure.html

语法
delimiter $$

create  [definer = { user | current_user}] 
    procedure  sp_name (in/out/inout  参数名  参数类型)
    [characteristic ...] 
    routine_body
  
characteristic:
   	  comment  'string'
	  | LANGUAGE SQL
	  | [NOT] DETERMINISTIC
	  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
	  | SQL SECURITY { DEFINER | INVOKER }
 
routine_body:
  Valid SQL routine statement
 
[begin_label:] BEGIN
  [statement_list]
    ……
END [end_label]

delimiter ;

1、delimiter $$:修改语句结束符;
语句结束符表示语句已经结束,MySQL可以执行语句了;

在定义存储过程前,将语句结束符临时修改为$$(符号随意定),使得存储过程的过程体中使用的;被直接传递到服务器,而不会被客户端解释;
存储过程结束后,要将语句结束符改为默认的;delimiter ;


2、create procedure p_name(IN/OUT/INOUT p_in int, ...):声明存储过程;
默认情况下,存储过程和默认数据库相关联,如果想指定存储过程创建在某个特定的数据库下,那么在过程名前面加数据库名做前缀;

如果过程没有参数,也必须在过程名后面写上小括号;

  • IN: 输入参数,表示调用者向过程传入值;
    用来接收调用者调用存储过程时传入的参数;可以传入字面值、或变量;
  • OUT:输出参数,表示调用者向过程传入值;
    用来向调用者返回值,传出值只能是变量;
  • INOUT:输入输出参数,既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量);尽量少使用inout;

3、begin ... end$$:定义存储过程体;

  • 存储过程体包含了在过程调用时必须执行的语句,例如:dml、ddl语句,if-then-else和while-do语句、声明变量的declare语句等;
  • 过程体格式:以begin开始,以end结束(可嵌套);
  • 可以为语句块帖标签:增强代码的可读性,在某些语句(例如:leave和iterate语句),需要用到标签;
    在这里插入图片描述

4、set @p_in=1;:变量赋值;


5、declare id int unsigned default 100;:声明变量;
过程体中声明的是局部变量,直接使用sql语句声明的是全局变量;使用set为变量赋值时,在过程体中修改变量的值,不影响全局变量的值;


6、call pName(参数列表);:调用存储过程;


7、变量的作用域:
内部的变量在其作用域范围内享有更高的优先权,当执行到 end 变量时,内部变量消失,此时已经在其作用域外,变量不再可见了,应为在存储过程外再也不能找到这个申明的变量,但是可以通过 out 参数或者将其值指派给会话变量来保存其值;


实例 - IN、OUT、INOUT

1、删除给定球员参加的所有比赛:

mysql> delimiter $$  #将语句的结束符号从分号;临时改为两个$$(可以是自定义)
mysql> CREATE PROCEDURE delete_matches(IN p_playerno INTEGER)
    -> BEGIN
    ->   DELETE FROM MATCHES  WHERE playerno = p_playerno;
    -> END$$
Query OK, 0 rows affected (0.01 sec) 
mysql> delimiter;  #将语句的结束符号恢复为分号

2、IN 输入参数:

mysql> delimiter $$
mysql> create procedure in_param(in p_in int)
    -> begin
    ->   select p_in;
    ->   set p_in=2;
    ->    select P_in;
    -> end$$
mysql> delimiter ; 
mysql> set @p_in=1; 
mysql> call in_param(@p_in);
输出12
mysql> select @p_in;
输出1

以上可以看出,p_in 在存储过程中被修改,但并不影响 @p_id 的值,因为前者为局部变量、后者为全局变量;

3、OUT 输出参数:

mysql> delimiter //
mysql> create procedure out_param(out p_out int)
    ->   begin
    ->     select p_out;
    ->     set p_out=2;
    ->     select p_out;
    ->   end //
mysql> delimiter ;
mysql> set @p_out=1; 
mysql> call out_param(@p_out);
	先输出NULL,因为out是向调用者输出参数,不接收输入的参数,所以存储过程里的p_out为null
	再输出2,因为过程体中使用set为输出参数赋值为2; 
mysql> select @p_out;
	输出2;调用了out_param存储过程,输出参数,改变了p_out变量的值;

4、INOUT 输入输出参数:

mysql> delimiter $$
mysql> create procedure inout_param(inout p_inout int)
    ->   begin
    ->     select p_inout;
    ->     set p_inout=2;
    ->     select p_inout;
    ->   end
    -> $$
mysql> delimiter ; 
mysql> set @p_inout=1; 
mysql> call inout_param(@p_inout);
	输出12 
mysql> select @p_inout;
	输出2
#调用了inout_param存储过程,接受了输入的参数,也输出参数,改变了变量

5、变量定义与赋值:

  • 在客户端使用变量:
    在这里插入图片描述
  • 在存储过程中使用用户变量:
    在这里插入图片描述
  • 在存储过程间传递全局范围的用户变量:
    在这里插入图片描述
条件语句

1、if-then-else 语句:

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc2(IN parameter int)  
     -> begin 
     -> declare var int;  
     -> set var=parameter+1;  
     -> if var=0 then 
     -> insert into t values(17);  
     -> end if;  
     -> if parameter=0 then 
     -> update t set s1=s1+1;  
     -> else 
     -> update t set s1=s1+2;  
     -> end if;  
     -> end;  
     -> //  
mysql > DELIMITER ;

2、case语句:

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc3 (in parameter int)  
     -> begin 
     -> declare var int;  
     -> set var=parameter+1;  
     -> case var  
     -> when 0 then   
     -> insert into t values(17);  
     -> when 1 then   
     -> insert into t values(18);  
     -> else   
     -> insert into t values(19);  
     -> end case;  
     -> end;  
     -> //  
mysql > DELIMITER ; 
case
    when var=0 then
        insert into t values(30);
    when var>0 then
    when var<0 then
    else
end case
循环语句

1、while ···· end while

while 条件 do
    --循环体
endwhile

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc4()  
     -> begin 
     -> declare var int;  
     -> set var=0;  
     -> while var<6 do  
     -> insert into t values(var);  
     -> set var=var+1;  
     -> end while;  
     -> end;  
     -> //  
mysql > DELIMITER ;

2、repeat···· end repea:在执行操作后检查结果,而 while 则是执行前进行检查;

repeat
    --循环体
until 循环条件  
end repeat;

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc5 ()  
     -> begin   
     -> declare v int;  
     -> set v=0;  
     -> repeat  
     -> insert into t values(v);  
     -> set v=v+1;  
     -> until v>=5  
     -> end repeat;  
     -> end;  
     -> //  
mysql > DELIMITER ;

3、loop ·····endloop:loop 循环不需要初始条件,这点和 while 循环相似,同时和 repeat 循环一样不需要结束条件, leave 语句的意义是离开循环;

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc6 ()  
     -> begin 
     -> declare v int;  
     -> set v=0;  
     -> LOOP_LABLE:loop  
     -> insert into t values(v);  
     -> set v=v+1;  
     -> if v >=5 then 
     -> leave LOOP_LABLE;  
     -> end if;  
     -> end loop;  
     -> end;  
     -> //  
mysql > DELIMITER ;

4、LABLES 标号:标号可以用在 begin repeat while 或者 loop 语句前,语句标号只能在合法的语句前面使用;可以跳出循环,使运行指令达到复合语句的最后一步;

迭代

ITERATE 通过引用复合语句的标号,来从新开始复合语句:

mysql > DELIMITER //  
mysql > CREATE PROCEDURE proc10 ()  
     -> begin 
     -> declare v int;  
     -> set v=0;  
     -> LOOP_LABLE:loop  
     -> if v=3 then   
     -> set v=v+1;  
     -> ITERATE LOOP_LABLE;  
     -> end if;  
     -> insert into t values(v);  
     -> set v=v+1;  
     -> if v>=5 then 
     -> leave LOOP_LABLE;  
     -> end if;  
     -> end loop;  
     -> end;  
     -> //  
mysql > DELIMITER ;

MySQL存储过程学习使用之创建、修改、删除、调用

  • 0
    点赞
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值