一、初识存储过程
1、什么是存储过程
存储过程是在大型数据库系统中一组为了完成特定功能的SQL语句集,存储在数据库中。存储过程经过第一次编译后,再次调用不需要编译,用户可以通过指定的存储过程名和给出一些存储过程定义的参数来使用它。一般用的较少,和脚本有类似之处。
Java,Python,PHP等应用程序可以调用存储过程。自MySQL 5.0版本以来,存储过程,存储函数,触发器和事件这些功能才被添加到MySQL数据库引擎
2、为什么要用存储过程
程序分两种,一种是基于web,一种是基于桌面,他们都和数据库进行交互来完成数据的存取工作。假设现在有一种应用程序包含了这两种,现在要修改其中的一个查询sql语句,那么我们可能要同时修改他们中对应的查询sql语句,当我们的应用程序很庞大很复杂的时候问题就出现这,不易维护!另外把sql查询语句放在我们的web程序或桌面中很容易遭到sql注入的破坏。而存储过程正好可以帮我们解决这些问题。
3、存储过程优缺点
优点
- 增强SQL语言的功能和灵活性:存储过程可以用控制语句编写,有很强的灵活性,可以完成复杂的判断和较复杂的运算。
- 标准组件式编程:存储过程被创建后,可以在程序中被多次调用,而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改,对应用程序源代码毫无影响。
- 较快的执行速度:如果某一操作包含大量的Transaction-SQL代码或分别被多次执行,那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询,优化器对其进行分析优化,并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化,速度相对要慢一些。
- 减少网络流量:针对同一个数据库对象的操作(如查询、修改),如果这一操作所涉及的Transaction-SQL语句被组织进存储过程,那么当在客户计算机上调用该存储过程时,网络中传送的只是该调用语句,从而大大减少网络流量并降低了网络负载。
- 作为一种安全机制来充分利用:通过对执行某一存储过程的权限进行限制,能够实现对相应的数据的访问权限的限制,避免了非授权用户对数据的访问,保证了数据的安全。
缺点
- 可移植性差
- 对于简单的SQL语句,存储过程没什么优势,不一定会减少网络传输
- 如果只有一个用户使用数据库,那么存储过程对安全也没什么影响
- 团队开发时需要先统一标准,否则后期维护成本大
- 在大并发量访问的情况下,不宜写过多涉及运算的存储过程
- 业务逻辑复杂时,特别是涉及到对很大的表进行操作的时候,不如在前端先简化业务逻辑
- 如果使用大量存储过程,那么使用这些存储过程的每个连接的内存使用量将会大大增加。此外,如果您在存储过程中过度使用大量逻辑操作,则CPU使用率也会增加,因为数据库服务器的设计不当于逻辑运算。
- 存储过程的构造使得开发具有复杂业务逻辑的存储过程变得更加困难。
- 很难调试存储过程。只有少数数据库管理系统允许您调试存储过程。不幸的是,MySQL不提供调试存储过程的功能。
- 开发和维护存储过程并不容易。开发和维护存储过程通常需要一个不是所有应用程序开发人员拥有的专业技能。这可能会导致应用程序开发和维护阶段的问题。
存储过程能不用尽量不用。原则是:业务逻辑不要封装在数据库里面(数据库去进行逻辑判断业务)。把业务逻辑要交给应用程序处理。这样可以减少数据库资源消耗。人员也难以招聘,因为既懂存储过程,又懂业务的人少。使用困难。大量业务逻辑封装在存储过程中,造成后面根本就不能动了。动a影响b。以后业务逻辑很难剥离出来。增加以后维护困难
4、存储过程和函数
相同点
- 存储过程和函数都是为了可重复执行操作数据库的 sql 语句的集合
- 存储过程和函数都是一次编译,后续执行
不同点
- 标识符不同,函数是 function,过程是 procedure
- 函数中有返回值,过程没有返回值
- 函数中不能使用 select 语句,而过程可以使用
- 函数最后可以通过 select 语句使用,过程通过 call 语句使用
二、存储过程的创建
创建存储过程
注:创建相同名字的存储过程不会成功,即不能覆盖一个已经存在的存储过程。可以先删除然后再创建。
CREATE
[DEFINER = { user | CURRENT_USER }]
PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
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]
MYSQL 存储过程中的关键语法
注:如果在命令行模式下进行存储过程创建,需要修改语句结束符,避免冲突。使用工具可以不修改语句结束符,示例如下
DELIMITER $$
或
DELIMITER //
声明存储过程:
CREATE PROCEDURE demo_in_parameter(IN p_in int)
存储过程开始和结束符号:
BEGIN .... END
变量赋值:
SET @p_in=1
变量定义:
DECLARE l_int int unsigned default 4000000;
下面是存储过程的例子,删除给定球员参加的所有比赛:
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; # 将语句的结束符号恢复为分号
解析:默认情况下,存储过程和默认数据库相关联,如果想指定存储过程创建在某个特定的数据库下,那么在过程名前面加数据库名做前缀。 在定义过程时,使用 DELIMITER $$ 命令将语句的结束符号从分号 ; 临时改为两个 $$,使得过程体中使用的分号被直接传递到服务器,而不会被客户端(如mysql)解释。
存储过程体
存储过程体包含了在过程调用时必须执行的语句,例如:dml、ddl语句,if-then-else和while-do语句、声明变量的declare语句等
过程体格式:以begin开始,以end结束(可嵌套)
BEGIN
BEGIN
BEGIN
statements;
END
END
END
注意:每个嵌套块及其中的每条语句,必须以分号结束,表示过程体结束的begin-end块(又叫做复合语句compound statement),则不需要分号。
如果过程体中只有一条指令,则可以省略 begin 和 end,存储过程体中的每条sql语句的结尾要求必须加分号。
为语句块贴标签
[begin_label:] BEGIN
[statement_list]
END [end_label]
例如:
label1: BEGIN
label2: BEGIN
label3: BEGIN
statements;
END label3 ;
END label2;
END label1
标签有两个作用:
- 增强代码的可读性
- 在某些语句(例如:leave和iterate语句),需要用到标签
捕获异常
declare continue handler for SQLEXCEPTION set e=1;
三、存储过程的参数
MySQL存储过程的参数用在存储过程的定义,共有三种参数类型,IN,OUT,INOUT,形式如:
CREATE PROCEDURE 存储过程名([[IN |OUT |INOUT ] 参数名 数据类形...])
IN 输入参数:表示调用者向过程传入值(传入值可以是字面量或变量)
OUT 输出参数:表示过程向调用者传出值(可以返回多个值)(传出值只能是变量)
INOUT 输入输出参数:既表示调用者向过程传入值,又表示过程向调用者传出值(值只能是变量)
1、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);
+------+
| p_in |
+------+
| 1 |
+------+
+------+
| P_in |
+------+
| 2 |
+------+
mysql> select @p_in;
+-------+
| @p_in |
+-------+
| 1 |
+-------+
# 以上可以看出,p_in 在存储过程中被修改,但并不影响 @p_id 的值,因为前者为局部变量、后者为全局变量。
2、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);
+-------+
| p_out |
+-------+
| NULL |
+-------+
# 因为out是向调用者输出参数,不接收输入的参数,所以存储过程里的p_out为null
+-------+
| p_out |
+-------+
| 2 |
+-------+
mysql> select @p_out;
+--------+
| @p_out |
+--------+
| 2 |
+--------+
# 调用了out_param存储过程,输出参数,改变了p_out变量的值
3、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);
+---------+
| p_inout |
+---------+
| 1 |
+---------+
+---------+
| p_inout |
+---------+
| 2 |
+---------+
mysql> select @p_inout;
+----------+
| @p_inout |
+----------+
| 2 |
+----------+
# 调用了inout_param存储过程,接受了输入的参数,也输出参数,改变了变量
注意:
1、如果过程没有参数,也必须在过程名后面写上小括号 例:
CREATE PROCEDURE sp_name ([proc_parameter[,...]]) ……
2、确保参数的名字不等于列的名字,否则在过程体中,参数名被当做列名来处理
四、变量
1. 变量定义
局部变量声明一定要放在存储过程体的开始:
DECLARE variable_name [,variable_name...] datatype [DEFAULT value];
其中,datatype 为 MySQL 的数据类型,如: int, float, date,varchar(length),例如:
DECLARE l_int int unsigned default 4000000;
DECLARE l_numeric number(8,2) DEFAULT 9.95;
DECLARE l_date date DEFAULT '1999-12-31';
DECLARE l_datetime datetime DEFAULT '1999-12-31 23:59:59';
DECLARE l_varchar varchar(255) DEFAULT 'This will not be padded';
2. 变量赋值
SET 变量名 = 表达式值 [,variable_name = expression ...]
3.使用SELECT …INTO语句为变量赋值
在MySQL存储过程中,可以使用SELECT …INTO语句对变量进行赋值,该语句在数据库中进行查询,并将得到的结果赋值给变量。SELECT …INTO语句的语法格式如下:
SELECT col_name[,...] INTO var_name[,...] table_expr
col_name:要从数据库中查询的列字段名;
var_name:变量名,列字段名按照在列清单和变量清单中的位置对应,将查询得到的值赋给对应位置的变量;
table_expr:SELECT语句中的其余部分,包括可选的FROM子句和WHERE子句。
需要注意的是,在使用SELECT …INTO语句时,变量名不能和数据表中的字段名相同,否则会出错。范例语句:
create procedure getMsg ()
Begin
declare v_title varchar(30);
declare v_content varchar(100);
select title,content into v_title,v_content from news where artId=333;
End
将变量值返回给调用者
在存储过程中定义的变量,经过一系列的处理之后,结果值可能需要返回给存储过程调用者。那么如何返回呢?方便的做法是使用SELECT语句将变量作为结果集返回,因此,在上面一段代码的基础上,加上一句:
create procedure getMsg ()
Begin
declare v_title varchar(30);
declare v_content varchar(100);
select title,content into v_title,v_content from news where artId=333;
select v_title,v_content;
End
4. 用户变量
在MySQL客户端使用用户变量
mysql > SELECT 'Hello World' into @x;
mysql > SELECT @x;
+-------------+
| @x |
+-------------+
| Hello World |
+-------------+
mysql > SET @y='Goodbye Cruel World';
mysql > SELECT @y;
+---------------------+
| @y |
+---------------------+
| Goodbye Cruel World |
+---------------------+
mysql > SET @z=1+2+3;
mysql > SELECT @z;
+------+
| @z |
+------+
| 6 |
+------+
在存储过程中使用用户变量
mysql > CREATE PROCEDURE GreetWorld( ) SELECT CONCAT(@greeting,' World');
mysql > SET @greeting='Hello';
mysql > CALL GreetWorld( );
+----------------------------+
| CONCAT(@greeting,' World') |
+----------------------------+
| Hello World |
+----------------------------+
在存储过程间传递全局范围的用户变量
mysql> CREATE PROCEDURE p1() SET @last_procedure='p1';
mysql> CREATE PROCEDURE p2() SELECT CONCAT('Last procedure was ',@last_procedure);
mysql> CALL p1( );
mysql> CALL p2( );
+-----------------------------------------------+
| CONCAT('Last procedure was ',@last_proc |
+-----------------------------------------------+
| Last procedure was p1 |
+-----------------------------------------------+
注意:用户变量名一般以@开头,滥用用户变量会导致程序难以理解及管理
五、注释
MySQL 存储过程可使用两种风格的注释
- 两个横杆--:该风格一般用于单行注释。
- c 风格: 一般用于多行注释。
六、MySQL存储过程的调用
用call和你过程名以及一个括号,括号里面根据需要,加入参数,参数包括输入参数、输出参数、输入输出参数。调用存储过程示例:
call sp_name[(传参)];
七、MySQL存储过程的查询
我们像知道一个数据库下面有那些表,我们一般采用 showtables; 进行查看。那么我们要查看某个数据库下面的存储过程,是否也可以采用呢?答案是,我们可以查看某个数据库下面的存储过程,但是是另一种方式。我们可以用以下语句进行查询:
# 查看所有的存储过程
select name from mysql.proc where type='PROCEDURE';
# 当然也可以指定数据库名来缩小范围
select name from mysql.proc where type='PROCEDURE' and db='数据库名';
#
select routine_name from information_schema.routines where routine_schema='数据库名';
# 显示数据库中所有存储的存储过程基本信息,包括所属数据库,存储过程名称,创建时间等
show procedure status [where db='数据库名'];
如果我们想知道,某个存储过程的详细,那我们又该怎么做呢?是不是也可以像操作表一样用describe 表名进行查看呢?答案是:我们可以查看存储过程的详细,但是需要用另一种方法:
SHOW CREATE PROCEDURE 数据库.存储过程名;
就可以查看当前存储过程的详细。
八、MySQL存储过程的修改
ALTER PROCEDURE
修改存储过程只能修改那些选项(这里不讲解那些具体选项,想了解的可以自行百度),并不能修改传入传出参数或者sql语句
更改用 CREATE PROCEDURE 建立的预先指定的存储过程,其不会影响相关存储过程或存储功能。
九、MySQL存储过程的删除
删除一个存储过程比较简单,和删除表一样:
语法:drop procedure 存储过程名
#示例
DROP PROCEDURE p1;
#错误演示,不支持批量删除
DROP PROCEDURE p2,p3;
十、MySQL存储过程的控制语句
(1). 变量作用域
内部的变量在其作用域范围内享有更高的优先权,当执行到 end 变量时,内部变量消失,此时已经在其作用域外,变量不再可见了,应为在存储过程外再也不能找到这个申明的变量,但是你可以通过 out 参数或者将其值指派给会话变量来保存其值。
(2). 条件语句
1. if-then-else 语句
if 条件 then
语句;
else
语句;
end if;
if 条件 then
语句;
elseif 条件 then
语句;
.....
else
语句;
end if;
示例:
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语句:
case [变量名]
when [值] then
[执行内容]
when [值] then
[执行内容]
...
else
[执行内容]
end 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
(3). 循环语句
1. while ···· end while
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 语句的意义是离开循环。
loop语句,运行直到遇到leave
[标签名]:loop
[执行内容]
leave [标签名]
[执行内容]
end loop;
示例
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 语句前,语句标号只能在合法的语句前面使用。可以跳出循环,使运行指令达到复合语句的最后一步。
(4). ITERATE迭代,相当于continue,LEAVE 结束,相当于break
ITERATE 通过引用复合语句的标号,来从新开始复合语句:
LEAVE 结束循环
mysql > DELIMITER //
mysql > CREATE PROCEDURE proc10 ()
-> begin
-> declare v int;
-> set v=0;
-> LOOP_LABLE1:loop
-> if v=3 then
-> set v=v+1;
-> ITERATE LOOP_LABLE1; # 进行下一次循环
-> end if;
-> insert into t values(v);
-> set v=v+1;
-> if v>=5 then
-> leave LOOP_LABLE1; # 结束循环
-> end if;
-> end loop;
-> end;
-> //
mysql > DELIMITER ;
十一、mysql 在存储过程中输出日志信息
1、直接用select 打印输出
SELECT 'Comment';
2、用concat连接变量输出
declare myvar INT default 0;
SET myvar = 5;
SELECT concat('myvar is ', myvar);
输出: myvar is 5
3、额外创建一个有一列文本列的表,然后往里面塞信息
declare myvar INT default 0;
SET myvar = 5;
insert into tmptable select concat('myvar is ', myvar);
将上面的sql语句封装成一个存储过程log,以后要用的话就直接调用下面的语句就可以了
CALL log(concat('the value is', myvar));
直接输出到一个文本里面
select "penguin" as log into outfile '/tmp/result.txt';
这个命令会有严格的限制,只能将输出文本放在本地,然后给予其创建和写的权限
一旦输出了一个文本,无法重写,这样可以阻止恶意执行sql注入
十一、DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE
在MySQL的存储过程中经常会看到这句话:DECLARE CONTINUE HANDLER FOR NOT FOUND。
它的含义是:若没有数据返回,程序继续,并将变量 done 设为TRUE ,这种情况是出现在select XX into XXX from tablename的时候发生的。
我们在使用储存过程中使用游标遍历数据的时候的基本写法如下:
create procedure proc_test() sql security invoker
begin
declare p_id varchar(32);
declare done tinyint default false;
declare c_cur cursor for select id from user;
declare continue handler for not found set done = true;
open c_cur;
fetch c_cur into p_id;
while !done do
... #程序逻辑
fetch c_cur into p_id;
end while;
close c_cur;
end;
正常情况这么写是没问题的,可是如果你在while里面的要是有select语句的话就有问题了。如果说你的处理逻辑是这样的:
while !done do
select * from user_role r where r.user_id = p_id;
fetch c_cur into p_id;
end while;
那么当你的select * from user_role r where r.user_id = p_id;找不到数据的时候,declare continue handler for not found set done = true;这句就会执行,有done = true,所以循环体会提前跳出。通过测试得出,declare continue handler for not found set done = true 是对全局的select有效的,只要有一条select语句返回空,那么就是触发该语句。
解决方法就是确保while里面的select永远不会返回空
select * from user_role r where r.user_id = p_id;
#改成下面这样
select col1, col2, ... from
(select col1, col2, ... from user_role r where r.user_id = p_id
union all
select '' col1, '' col2, ...) t
这样的话就可以保证select肯定不是空集合。