细谈Mysql的存储过程和存储函数

[IN|OUT|INOUT]parame_name type

1."p_name"用于指定存储过程的名称。

2."proc_parameter"用于指定存储过程中的参数列表。其中，语法项"parame_name"为参数名，"type"为参数的类型（类型可以是Mysql中任意的有效数据类型）。Mysql的存储过程支持三种类型的参数，即输入参数IN，输出参数OUT,输入输出参数INOUT。输入参数是使数据可以传递给一个存储过程；输出参数是用于存储过程需要返回的一个操作结果；输入输出参数既可以充当输入参数也可以充当输出结果。
参数的取名不要和表中的列名相同，否则尽管不会返回出错信息，但储存过程中的sql语句会将参数名当做列名，从而引发不可预知的错误。

3.语法项"rountine_body"表示存储过程的主体部分，也成为存储过程体，其包含了需要执行的sql。过程体以关键字BEGIN开始，以关键字END结束。若只有一条sql可以忽略BEGIN....END标志。

1.3.3 局部变量

在存储过程体中可以声明局部变量，用来存储过程体中的临时结果。在Mysql中使用DECLARE语句来声明局部变量。

DECLARE var_name type [DEFAULT value]

SET var_name = expr[,var_name2 = expr]....

1.3.6 SELECT....INTO语句

在Mysql中，可以使用SELECT...INTO语句把选定的列的值存储到局部变量中。格式是：

SELECT col_name[,..] INTO var_name[,....] table_expr

其中"col_name"用于指定列名；"var_name"用于指定要赋值的变量名；"table_expr"表示SELECT语句中FROM后面的部分。

注意：SELECT...INTO语句返回的结果集只能有一行数据。

1.3.7 流程控制语句

条件判断语句

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 ;

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 ;

循环语句
while ・・・・ end while:

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 ;

repeat・・・・ end repea：

它在执行操作后检查结果，而 while 则是执行前进行检查。

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 ;

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

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 ;

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 ;

1.3.8 游标

MySQL中的游标可以理解成一个可迭代对象(类比Python中的列表、字典等可迭代对象)，它可以用来存储select 语句查询到的结果集，这个结果集可以包含多行数据，从而使我们可以使用迭代的方法从游标中依次取出每行数据。

MySQL游标的特点：
1.只读：无法通过光标更新基础表中的数据。
2.不可滚动：只能按照select语句确定的顺序获取行。不能以相反的顺序获取行。 此外，不能跳过行或跳转到结果集中的特定行。
3.敏感：有两种游标：敏感游标和不敏感游标。敏感游标指向实际数据，不敏感游标使用数据的临时副本。敏感游标比一个不敏感的游标执行得更快，因为它不需要临时拷贝数据。MySQL游标是敏感的。

1.声明游标

游标声明必须在变量声明之后。如果在变量声明之前声明游标，MySQL将会发出一个错误。游标必须始终与select语句相关联。

declare cursor_name cursor for select_statement;

2.打开游标

使用open语句打开游标，只有先打开游标才能读取数据。

open cursor_name;

3.读取游标

使用fetch语句来检索游标指向的一行数据，并将游标移动到结果集中的下一行。

fetch cursor_name into var_name;

4.关闭游标

使用close语句关闭游标。

close cursor_name;

当游标不再使用时，应该关闭它。   当使用MySQL游标时，还必须声明一个notfound处理程序来处理当游标找不到任何行时的情况。 因为每次调用fetch语句时，游标会尝试依次读取结果集中的每一行数据。 当游标到达结果集的末尾时，它将无法获得数据，并且会产生一个条件。 处理程序用于处理这种情况。

declare continue handler for not found set type = 1;

type是一个变量，示游标到达结果集的结尾。

delimiter $$
create PROCEDURE phoneDeal()
BEGIN
 DECLARE id varchar(64); -- id
 DECLARE phone1 varchar(16); -- phone
 DECLARE password1 varchar(32); -- 密码
 DECLARE name1 varchar(64); -- id
 -- 遍历数据结束标志
 DECLARE done INT DEFAULT FALSE;
 -- 游标
 DECLARE cur_account CURSOR FOR select phone,password,name from account_temp;
 -- 将结束标志绑定到游标
 DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
 
 -- 打开游标
 OPEN cur_account; 
 -- 遍历
 read_loop: LOOP
 -- 取值 取多个字段
 FETCH NEXT from cur_account INTO phone1,password1,name1;
 IF done THEN
 LEAVE read_loop;
 END IF;
 
 -- 你自己想做的操作
 insert into account(id,phone,password,name) value(UUID(),phone1,password1,CONCAT(name1,'的家长'));
 END LOOP;
 
 -- 关闭游标
 CLOSE cur_account;
END $$

1.3.7 调用存储过程

使用call语句调用存储过程

call sp_name[(传参)];

1.3.8 删除存储过程

使用drop语句删除存储过程

DROP PROCEDURE sp_name

2 存储函数

2.1 什么是存储函数

存储函数和存储过程一样，都是sql和语句组成的代码块。
存储函数不能有输入参数，并且可以直接调用，不需要call语句，且必须有一条包含RETURN语句。

2.2 创建存储函数

在Mysql中使用CREATE FUNCTION语句创建：

CREATE FUNCTION fun_name (par_name type[,...])
RETURNS type
[characteristics] 
fun_body

其中，fun_name为函数名，并且名字唯一，不能与存储过程重名。par_name是指定的参数，type为参数类型；RETURNS字句用来声明返回值和返回值类型。fun_body是函数体，所有存储过程中的sql在存储函数中同样可以使用。但是存储函数体中必须包含一个RETURN 语句。
characteristics指定存储过程的特性，有以下取值：

• 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'：注释信息，用来描述存储过程或函数。

delimiter $$
create function getAnimalName(animalId int) RETURNS VARCHAR(50)
DETERMINISTIC
begin
 declare name VARCHAR(50);
 set name=(select name from animal where id=animalId);
 return (name);
end$$
delimiter;

-- 调用
select getAnimalName(4)