189 8069 5689
这篇文章主要讲解了“MySQL存储过程怎么理解”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“mysql存储过程怎么理解”吧！
目前创新互联公司已为上千家的企业提供了网站建设、域名、虚拟空间、网站托管运营、企业网站设计、古田网站维护等服务，公司将坚持客户导向、应用为本的策略，正道将秉承"和谐、参与、激情"的文化，与客户和合作伙伴齐心协力一起成长，共同发展。
1、存储过程的简介：
我们常用的操作数据库语言SQL语句在执行的时候需要要先编译，然后执行，而存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果该存储过程带有参数）来调用执行它。
一个存储过程是一个可编程的函数，它在数据库中创建并保存。它可以有SQL语句和一些特殊的控制结构组成。当希望在不同的应用程序或平台上执行相同的函数，或者封装特定功能时，存储过程是非常有用的。数据库中的存储过程可以看做是对编程中面向对象方法的模拟。它允许控制数据的访问方式。
2、存储过程的有点：
(1).存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
(2).存储过程允许标准组件是编程。存储过程被创建后，可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。而且数据库专业人员可以随时对存储过程进行修改，对应用程序源代码毫无影响。
(3).存储过程能实现较快的执行速度。如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。
(4).存储过程能过减少网络流量。针对同一个数据库对象的操作（如查询、修改），如果这一操作所涉及的Transaction-SQL语句被组织程存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大增加了网络流量并降低了网络负载。
(5).存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。
3、关于mysql的存储过程：
存储过程是数据库存储的一个重要的功能，但是MySQL在5.0以前并不支持存储过程，这使得MySQL在应用上大打折扣。好在MySQL 5.0终于开始已经支持存储过程，这样即可以大大提高数据库的处理速度，同时也可以提高数据库编程的灵活性。
同时，要在mysql5.1以上版本创建子程序，必须具有CREATE ROUTINE权限，并且ALTER ROUTINE和EXECUTE权限被自动授予它的创建者；
4、存储过程的创建：
（1）语法：
CREATE PROCEDURE sp_name ([ proc_parameter ]) [ characteristics..] routine_body
proc_parameter指定存储过程的参数列表，列表形式如下：[IN|OUT|INOUT] param_name type
其中in表示输入参数，out表示输出参数，inout表示既可以输入也可以输出；param_name表示参数名称；type表示参数的类型该类型可以是MYSQL数据库中的任意类型有以下取值：
characteristic:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
routine_body:
Valid SQL procedure statement or statements
LANGUAGE SQL ：说明routine_body部分是由SQL语句组成的，当前系统支持的语言为SQL，SQL是LANGUAGE特性的唯一值
[NOT] DETERMINISTIC ：指明存储过程执行的结果是否正确。DETERMINISTIC 表示结果是确定的。每次执行存储过程时，相同的输入会得到相同的输出。[NOT] DETERMINISTIC 表示结果是不确定的，相同的输入可能得到不同的输出。如果没有指定任意一个值，默认为[NOT] DETERMINISTIC
CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA：指明子程序使用SQL语句的限制。
CONTAINS SQL表明子程序包含SQL语句，但是不包含读写数据的语句；
NO SQL表明子程序不包含SQL语句；
READS SQL DATA：说明子程序包含读数据的语句；
MODIFIES SQL DATA表明子程序包含写数据的语句。
默认情况下，系统会指定为CONTAINS SQL
SQL SECURITY { DEFINER | INVOKER } ：指明谁有权限来执行。DEFINER 表示只有定义者才能执行
INVOKER 表示拥有权限的调用者可以执行。默认情况下，系统指定为DEFINER
COMMENT 'string' ：注释信息，可以用来描述存储过程或函数
routine_body是SQL代码的内容，可以用BEGIN...END来表示SQL代码的开始和结束
（2） 格式
MySQL存储过程创建的格式：CREATE PROCEDURE 过程名 ([过程参数[,...]])
[特性 ...] 过程体
EG：
DELIMITER //
DROP PROCEDURE IF EXISTS simpleproc;
CREATE PROCEDURE simpleproc (OUT param1 INT)
BEGIN
SELECT COUNT(*) INTO param1 FROM t_user;
END //
DELIMITER ;
我这是在使用Navicat for MySQL 上敲的，就不打算进入mysql客户端去敲了，个人觉得这个更方便快捷一点；如果熟悉linux的童鞋估计就不这么认为啦；
好了，废话不多说，先解释一下：（1）DELIMITER这个东西的作用就是告诉mysql，我的存储过程的结束符号是//，所以在你的存储过程中使用;不会被认为是结束符号；注：当使用delimiter命令时，你应该避免使用反斜杠(‘\’)字符，因为那是MySQL的 转义字符（2）drop procedure if exists的意思跟我们建表时的语句是一样的，如果存在这个存储过程则删除；（3）create procedure simpleproc (out param1 int) 的意思也很明确，就是创建这个过程，而且带有一个输出参数，注：在function中可以有return 语句，但是procedure是没有的，但是也可以做到将结果传回去，如上；（4） begin .... end 这中间包裹着的就是procedure的主程序，也就是主体部分；简而言之，把你要放在一起工作的sql都丢在这里就好；（5）DELIMITER；这个语句作为结束语句。目的是告诉mysql我现在不要使用//作为结束字符了，要换回；了；注：这个不止可是//，还可以是其他符号；
5、变量
DECLARE语句被用来把不同项目局域到一个 子程序：局部变量，条件和 处理程序 及光标；DECLARE仅被用在BEGIN ... END复合语句里，并且必须在复合语句的开头，在任何其它语句之前。光标必须在声明处理程序之前被声明，并且变量和条件必须在声明光标或处理程序之前被声明。
declare 声明局部变量：DECLARE var_name[,...] type [DEFAULT value]；这个语句被用来声明局部变量。要给变量提供一个默认值，请包含一个DEFAULT子句。值可以被指定为一个表达式，不需要为一个常数。如果没有DEFAULT子句，初始值为NULL。
set 变量：SET var_name = expr [, var_name = expr] ...； 在存储程序中的SET语句是一般SET语句的扩展版本。被参考变量可能是子程序内声明的变量，或者是全局服务器变量。在存储程序中的SET语句作为预先存在的SET语法的一部分来实现。这允许SET a=x, b=y, ...这样的扩展语法。其中不同的变量类型（局域 声明变量及全局和集体变量）可以被混合起来。这也允许把局部变量和一些只对系统变量有意义的选项合并起来。在那种情况下，此选项被识别，但是被忽略了。
6、定义处理程序
有时候程序是会出错的，但是你希望你的程序在出错的情况下继续执行，declare就可以帮助我们解决这样的问题；具体定义如下：特定条件需要特定处理。这些条件可以联系到错误，以及子程序中的一般流程控制。定义条件是事先定义程序执行过程中遇到的问题，处理程序定义了在遇到这些问题时候应当采取的处理方式，并且保证存储过程或函数在遇到警告或错误时能继续执行。这样可以增强存储程序处理问题的能力，避免程序异常停止运行
1、定义条件
DECLARE condition_name CONDITION FOR[condition_type]
[condition_type]:
SQLSTATE[VALUE] sqlstate_value |mysql_error_code
condition_name：表示条件名称
condition_type：表示条件的类型
sqlstate_value和mysql_error_code都可以表示mysql错误
sqlstate_value为长度5的字符串错误代码
mysql_error_code为数值类型错误代码，例如：ERROR1142(42000)中，sqlstate_value的值是42000，
mysql_error_code的值是1142
//方法一：使用sqlstate_value DECLARE command_not_allowed CONDITION FOR SQLSTATE '42000' //方法二：使用mysql_error_code DECLARE command_not_allowed CONDITION FOR SQLSTATE 1148
这个语句指定需要特殊处理条件。他将一个名字和指定的错误条件关联起来。
这个名字随后被用在定义处理程序的DECLARE HANDLER语句中
2、定义处理程序
DECLARE handler_type HANDLER FOR condition_value[,...] sp_statement
handler_type:
|CONTINUE：对一个CONTINUE处理程序，当前子程序的执行在执行 处理程序语句之后继续。
| EXIT :对于EXIT处理程序，当 前BEGIN...END复合语句的执行被终止。
| UNDO :UNDO 处理程序类型语句还不被支持。 ·
condition_value:
SQLSTATE [VALUE] sqlstate_value
| condition_name
| SQLWARNING：SQLWARNING是对所有以01开头的SQLSTATE代码的速记
| NOT FOUND：NOT FOUND是对所有以02开头的SQLSTATE代码的速记
| SQLEXCEPTION：SQLEXCEPTION是对所有没有被SQLWARNING或NOT FOUND捕获的SQLSTATE代码的速记
| mysql_error_code
这个语句指定每个可以处理一个或多个条件的处理程序。如果产生一个或多个条件，指定的语句被执行。
//方法一：捕获sqlstate_value DECLARE CONTINUE HANDLER FOR SQLSTATE '42000' SET @info='CAN NOT FIND'; //方法二：捕获mysql_error_code DECLARE CONTINUE HANDLER FOR 1148SET @info='CAN NOT FIND'; //方法三：先定义条件，然后调用 DECLARE can_not_find CONDITION FOR 1146 ; DECLARE CONTINUE HANDLER FOR can_not_find SET set @info='CAN NOT FIND'; //方法四：使用SQLWARNING DECLARE EXIT HANDLER FOR SQLWARNING SET @info='ERROR'; //方法五：使用NOT FOUND DECLARE EXIT HANDLER FOR NOT FOUND SET @info='CAN NOT FIND'; //方法六：使用SQLEXCEPTION DECLARE EXIT HANDLER FOR SQLEXCEPTION SET @info='ERROR'; eg: delimiter // DROP PROCEDURE IF EXISTS sp2; CREATE PROCEDURE sp2() BEGIN DECLARE CONTINUE HANDLER for SQLSTATE '23000' set @info = 23000; SET @info = 1; INSERT INTO test.t_user VALUES (1,'test','test','test'); SET @info = 2; INSERT INTO test.t_user VALUES (1,'test','test','test'); SET @info = 3; END // delimiter ; -- 执行以下sp2 call sp2(); -- 查询以下@info SELECT @info; --结果分析：如果程序没有出错，按照执行目的，结果@info = 3; 如果被捕获了 '23000' 则结果是@23000; 使用continue作为执行handler_type的话，捕获异常就继续执行；所以结果为3; 使用exit作为执行handler_type的话，捕获异常后直接结束，所以结果为23000;
7、光标
简单光标在存储程序和函数内被支持。语法如同在嵌入的SQL中。光标当前是不敏感的，只读的及不滚动的。 不敏感意为服务器可以活不可以复制它的结果表。 光标必须在声明处理程序之前被声明，并且变量和条件必须在声明光标或处理程序之前被声明。
1、声明光标
DECLARE cursor_name CURSOR FOR select_statement
这个语句声明一个光标。也可以在子程序中定义多个光标，但是一个块中的每一个光标必须有唯一的名字。 SELECT语句不能有INTO子句。
2、打开光标
OPEN cursor_name 这个语句打开先前声明的光标。
3、使用光标 FETCH
FETCH cursor_name INTO var_name [, var_name] ... 这个语句用指定的打开光标读取下一行（如果有下一行的话），并且前进光标指针
4、关闭光标
CLOSE cursor_name 这个语句关闭先前打开的光标。 如果未被明确地关闭，光标在它被声明的复合语句的末尾被关闭。
2、流程控制构造
1．IF语句
IF语句用来进行条件判断。根据是否满足条件，将执行不同的语句。其语法的基本形式如下：
IF search_condition THEN statement_list [ELSEIF search_condition THEN statement_list] ... [ELSE statement_list] END IF
其中，search_condition参数表示条件判断语句；statement_list参数表示不同条件的执行语句。
注意：MYSQL还有一个IF()函数，他不同于这里描述的IF语句
下面是一个IF语句的示例。代码如下：
delimiter // DROP PROCEDURE IF EXISTS sp3; CREATE PROCEDURE sp3() BEGIN DECLARE age int DEFAULT 19; set @age1 = 0; SET @age2 = 0; set @age3 = 0; IF age>20 THEN SET @age1=age; ELSEIF age=20 THEN SET @age2=age; ELSE SET @age3=-1; END IF; END // delimiter ; call sp3();
该示例根据age与20的大小关系来执行不同的SET语句。
如果age值大于20，那么将count1的值加1；如果age值等于20，那么将count2的值加1；
其他情况将count3的值加1。IF语句都需要使用END IF来结束。
2．CASE语句
CASE语句也用来进行条件判断，其可以实现比IF语句更复杂的条件判断。CASE语句的基本形式如下：
CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list] ... [ELSE statement_list] END CASE
其中，case_value参数表示条件判断的变量；
when_value参数表示变量的取值；
statement_list参数表示不同when_value值的执行语句。
CASE语句还有另一种形式。该形式的语法如下：
CASE WHEN search_condition THEN statement_list [WHEN search_condition THEN statement_list] ... [ELSE statement_list] END CASE
其中，search_condition参数表示条件判断语句；
statement_list参数表示不同条件的执行语句。
下面是一个CASE语句的示例。代码如下：
delimiter // DROP PROCEDURE IF EXISTS sp4; CREATE PROCEDURE sp4() BEGIN DECLARE age int DEFAULT 19; set @age1 = 0; CASE age WHEN age > 20 THEN SET @age1 = age; WHEN age = 20 THEN SET @age1 = age-1; ELSE SET @age1 = -1; END CASE; END // delimiter ; call sp4(); select @age1;
注意：这里的CASE语句和“控制流程函数”里描述的SQL CASE表达式的CASE语句有轻微不同。这里的CASE语句不能有ELSE NULL子句
并且用END CASE替代END来终止！！
3．LOOP语句
LOOP语句可以使某些特定的语句重复执行，实现一个简单的循环。
但是LOOP语句本身没有停止循环的语句，必须是遇到LEAVE语句等才能停止循环。
LOOP语句的语法的基本形式如下：
[begin_label:] LOOP statement_list END LOOP [end_label]
其中，begin_label参数和end_label参数分别表示循环开始和结束的标志，这两个标志必须相同，而且都可以省略；(例子与leave结合一起写)
4．LEAVE语句
LEAVE语句主要用于跳出循环控制。其语法形式如下：
LEAVE label
其中，label参数表示循环的标志。
下面是一个LEAVE语句的示例。代码如下：
delimiter // DROP PROCEDURE IF EXISTS sp5; CREATE PROCEDURE sp5() BEGIN set @age = 0; add_age:LOOP SET @age = @age + 1; if @age>1000 THEN LEAVE add_age; end IF; END LOOP add_age; END // delimiter ; -- 调用 call sp5(); -- 查看结果 select @age;
该示例循环执行count加1的操作。当count的值等于100时，则LEAVE语句跳出循环。
5．ITERATE语句
ITERATE语句也是用来跳出循环的语句。但是，ITERATE语句是跳出本次循环，然后直接进入下一次循环。
ITERATE语句只可以出现在LOOP、REPEAT、WHILE语句内。
ITERATE语句的基本语法形式如下：
ITERATE label
其中，label参数表示循环的标志。
下面是一个ITERATE语句的示例。代码如下：
delimiter // DROP PROCEDURE IF EXISTS sp6; CREATE PROCEDURE sp6() BEGIN DECLARE age int DEFAULT 0; DECLARE EXIT HANDLER FOR SQLSTATE '23000' SET age = 23000; add_fun:LOOP SET age = age + 1; IF age > 10 THEN INSERT into t_user value(1, 'lennon', 'lennon', 'lennon'); ELSEIF age > 5 && age < 10 THEN ITERATE add_fun; ELSE select age; END IF; END LOOP add_fun; END // delimiter ; call sp6();
说明：LEAVE语句和ITERATE语句都用来跳出循环语句，但两者的功能是不一样的。
LEAVE语句是跳出整个循环，然后执行循环后面的程序。而ITERATE语句是跳出本次循环，然后进入下一次循环。
使用这两个语句时一定要区分清楚。
6．REPEAT语句
REPEAT语句是有条件控制的循环语句。当满足特定条件时，就会跳出循环语句。REPEAT语句的基本语法形式如下：
[begin_label:] REPEAT statement_list UNTIL search_condition END REPEAT [end_label]
其中，statement_list参数表示循环的执行语句；search_condition参数表示结束循环的条件，满足该条件时循环结束。
下面是一个REPEAT语句的示例。代码如下：
delimiter // DROP PROCEDURE IF EXISTS sp7; CREATE PROCEDURE sp7() BEGIN set @age = 0; add_count:REPEAT set @age = @age + 1 ; UNTIL @age>100 END REPEAT add_count; END // delimiter ; call sp7(); select @age;
该示例循环执行count加1的操作，count值为100时结束循环。
REPEAT循环都用END REPEAT结束。
7．WHILE语句
WHILE语句也是有条件控制的循环语句。但WHILE语句和REPEAT语句是不一样的。
WHILE语句是当满足条件时，执行循环内的语句。
WHILE语句的基本语法形式如下：
[begin_label:] WHILE search_condition DO statement_list END WHILE [end_label]
其中，search_condition参数表示循环执行的条件，满足该条件时循环执行；
statement_list参数表示循环的执行语句。
下面是一个ITERATE语句的示例。代码如下：
delimiter // DROP PROCEDURE IF EXISTS sp8; CREATE PROCEDURE sp8() BEGIN set @age = 0; WHILE @age < 100 DO set @age = @age + 1 ; END WHILE; END // delimiter ; call sp8(); select @age;
该示例循环执行count加1的操作，count值小于100时执行循环。
如果count值等于100了，则跳出循环。WHILE循环需要使用END WHILE来结束。
8、查看存储过程
SHOW { PROCEDURE | FUNCTION } STATUS [ LIKE ' pattern ' ] ;SHOW CREATE { PROCEDURE | FUNCTION } sp_name ;SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME=' sp_name ' ;
eg:
show PROCEDURE STATUS like 'sp6'; show CREATE PROCEDURE sp6; SELECT * FROM information_schema.Routines WHERE ROUTINE_NAME='sp6' ;
感谢各位的阅读，以上就是“mysql存储过程怎么理解”的内容了，经过本文的学习后，相信大家对mysql存储过程怎么理解这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是创新互联，小编将为大家推送更多相关知识点的文章，欢迎关注！