Question

如何创建MySQL存储过程

Answer 1

MySQL存储过程创建的格式：CREATE PROCEDURE 过程名 ([过程参数[,...]])
[特性 ...] 过程体

这里先举个例子：

mysql> DELIMITER //  
mysql> CREATE PROCEDURE proc1(OUT s int)  
    -> BEGIN 
    -> SELECT COUNT(*) INTO s FROM user;  
    -> END 
    -> //  
mysql> DELIMITER ;

 
注：

（1）这里需要注意的是DELIMITER //和DELIMITER ;

两句，DELIMITER是分割符的意思，因为MySQL默认以";"为分隔符，如果我们没有声明分割符，那么编译器会把存储过程当成SQL语句进行处理，则存储过程的编译过程会报错，所以要事先用DELIMITER关键字申明当前段分隔符，这样MySQL才会将";"当做存储过程中的代码，不会执行这些代码，用完了之后要把分隔符还原。

（2）存储过程根据需要可能会有输入、输出、输入输出参数，这里有一个输出参数s，类型是int型，如果有多个参数用","分割开。

（3）过程体的开始与结束使用BEGIN与END进行标识。

这样，我们的一个MySQL存储过程就完成了。

详细的教程可以百度，有大量的资源可供学习了解。

Answer 2

一、变量的使用
1.用户变量：以”@”开始，形式为”@变量名。”
用户变量跟MySQL客户端是绑定的，设置的变量，只对当前用户使用的客户端生效，使用如下图：
2.全局变量：定义时，以如下两种形式出现，set GLOBAL 变量名 或者 set @@global.变量名。show global variables;
对所有客户端生效。只有具有super权限才可以设置全局变量。
3.会话变量：只对连接的客户端有效。一旦客户端失去连接，变量失效。show session variables;
4.局部变量：作用范围在begin到end语句块之间。在该语句块里设置的变量declare语句专门用于定义局部变量。DECLARE l_numeric number(8,2) DEFAULT 9.95;
全局变量和会话变量的区别：全局变量在MySQL启动的时候由服务器自动将它们初始化为默认值，这些默认值可以通过更改my.ini这个文件来更改。会话变量在每次建立一个新的连接的时候，由MYSQL来初始化。MYSQL会将当前所有全局变量的值复制一份。来做为会话变量。全局变量与会话变量的区别就在于，对全局变量的修改会影响到整个服务器，但是对会话变量的修改，只会影响到当前的会话（也就是当前的数据库连接）。非root用户在修改全局变量时会报没有权限，在修改会话变量时也需要注意，有些变量是不能修改的，只能由root用户进行修改，例如：event_scheduler。
二、存储过程的优点
存储过程增强了SQL语言的功能和灵活性。存储过程可以用流控制语句编写，有很强的灵活性，可以完成复杂的判断和较复杂的运算。
当SQL语句有变动时，可以只修改数据库中的存储过程而不必修改代码。
存储过程能实现较快的执行速度。如果某一操作包含大量的SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的。在首次运行一个存储过程时查询，优化器对其进行分析优化，并且给出最终被存储在系统表中的执行计划。而批处理的SQL语句在每次运行时都要进行编译和优化，速度相对要慢一些。
减少网络传输。在客户端调用一个存储过程当然比执行一串SQL传输的数据量要小。
存储过程可被作为一种安全机制来充分利用。系统管理员通过执行某一存储过程的权限进行限制，能够实现对相应的数据的访问权限的限制，避免了非授权用户对数据的访问，保证了数据的安全。
三、存储过程的参数
MySQL存储过程的参数用在存储过程的定义，共有三种参数类型,IN,OUT,INOUT,形式如：
CREATE PROCEDURE([[IN |OUT |INOUT ] 参数名 数据类型…])
IN 输入参数:表示该参数的值必须在调用存储过程时指定，在存储过程中修改该参数的值不能被返回，为默认值
OUT 输出参数:该值可在存储过程内部被改变，并可返回
INOUT 输入输出参数:调用时指定，并且可被改变和返回
四、存储过程实例
if else end语句
DROP PROCEDURE IF EXISTS `prc_test1`;
CREATE DEFINER = `root`@`localhost` PROCEDURE `prc_test1`(in parameter int)
BEGIN
declare var int;
set var=1;
if var=0 then
set parameter=11;
elseif var=1 then
set parameter=22;
else
set parameter=33;
end if;
select parameter;
END;
case语句
BEGIN
declare var int;
set var = parameter;
case var
when 1 then
set parameter=11;
when 2 then
set parameter=22;
else
set parameter=33;
end case;
select parameter;
END
while do … end while语句
BEGIN
declare var int;
set var = parameter;
while var>0 do
set var = var - 1;
set parameter = parameter -2;
end WHILE;
select parameter;
END;
repeat … until end repeat
BEGIN
declare var int;
set var = parameter;
REPEAT
set var = var - 1;
set parameter = parameter -2;
end REPEAT;
select parameter;
END
loop ··· end loop
BEGIN
declare var int;
set var = parameter;
LOOP_LABLE:loop
set var = var - 1;
set parameter = parameter -2;
if var<0 THEN
LEAVE LOOP_LABLE;
END IF;
end LOOP;
select parameter;
END
标号可以用在begin repeat while 或者loop 语句前，语句标号只能在合法的语句前面使用。可以跳出循环，使运行指令达到复合语句的最后一步。如上面的loop示例。
游标的使用
1、定义游标
2、打开游标
3、使用游标
4、关闭游标
DROP PROCEDURE IF EXISTS `prc_test1`;
CREATE DEFINER = `root`@`localhost` PROCEDURE `prc_test1`()
BEGIN
DECLARE var int;
/**跳出循环标识**/
DECLARE done INT DEFAULT FALSE;
/**声明游标**/
DECLARE cur CURSOR FOR select age from person;
/**循环结束设置跳出标识**/
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
/**打开游标**/
OPEN cur;
LOOP_LABLE:loop
FETCH cur INTO var;
select var;
if done THEN
LEAVE LOOP_LABLE;
END IF;
end LOOP;
/**关闭游标**/
CLOSE cur;
END;
存储过程中变量赋值
语法1：
SET var_name=expr [, var_name=expr]...;
语法2：
SELECT col_name[,...] INTO var_name[,...] from table [WHERE...];