- MySQL - 存储过程 Stored Procedure

文章目录

- - 存储过程是什么
  - 存储过程的优点
  - 存储过程的缺点
  - 存储过程分类
  - 存储过程的查询语句
  - 存储过程的创建和调用
  - - 语法
    - 实例 - IN、OUT、INOUT
    - 条件语句
    - 循环语句
    - 迭代

存储过程是什么

存储过程是一组为了完成特定功能的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_bodycharacteristic: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);
输出1， 2
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);输出1，2 
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 ; 
casewhen var=0 theninsert into t values(30);when var>0 thenwhen var<0 thenelse
end case

循环语句

1、while ···· end while：

while 条件 do--循环体
endwhilemysql > 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 ;