存储过程和函数相对于java的函数的是非常类似的，把语句组合到一起，使用的时候，直接调用就可以了。
存储过程
语法
CREATE PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
routine_body是主语部分，填写带代码，可以使一条语句，也可以是复合语句。复合语句必须加上begin end，代码写在begin end中。
参数类型
参数类型，分为三种 IN，OUT，INOUT；
- IN 默认模式。表示参数必须传递给存储过程，且是不能修改的，只读；
- OUT表示参数可以改变，并将其更改后新值传递会调用程序。请注意：存储过程在启动时无法访问OUT参数的初始值；
- INOUT是IN和OUT的组合。调用程序可以传递参数，并且存储过程可以修改INOUT参数并将新值传递回调用程序
create procedure(IN a INT, OUT b INT, INOUT c INT)
....
定义变量
DECLARE variable_name datatype(size) DEFAULT default_value;
- DECLARE 关键字后面跟变量名。
- 声明变量之后，它的初始值为NULL,可以设置默认值
可以同时声明多个变量
DECLARE x, y INT DEFAULT 0;
声明了两个整数变量x和y，默认值为0
分配变量值
有两种方式可以修改变量值，一种是set语法
一种是select ... into ...语法
DECLARE X INT DEFAULT 0;
SET X = 1;
SELECT COUNT(*) INTO X FROM E;
上面的语句，先用的set语法将X的值改为1，然后将E表中的记录条数赋值给X。
注意：SET X = 1和 SET X := 1是一样的效果；select into 必须只能查出一行结果，多个会报错。
流程控制
IF
IF expression THEN
statements;
ELSEIF expression THEN
statements;
ELSE statements;
END IF;
和高级语言中的if结构非常类似
CASE
简单的case语句
CASE expression
WHEN expression1 THEN commands
WHEN expression2 THEN commands
...
ELSE commands
END
这种的简单的case语句可以用于任何地方。
如果作为控制语句的话，格式如下：
CASE expression
WHEN expression1 THEN statements;
WHEN expression2 THEN statements;
...
ELSE statements;
END CASE;
这种case语句只能用于begin end中（注意两者之间的差别）
循环
mysql中有三种循环语句 WHILE, REPEAT和LOOP
while
[name:] WHILE expression DO
statments;
END WHILE
expression 为真，则执行循环;name是该循环的名称，是可选的。
repeat和loop
[name:]REPEAT
statements;
UNTIL expression
END REPEAT
[name:LOOP
statements;
END REPEAT
loop没有退出条件。
最好和LEAVE和ITERATE语句一起使用
LEAVE语句和break作用一样，直接跳出循环。
ITERATE和continue作用一样，结束本次循环。
LEAVE和ITERATE使用，循环必须要名字。
如：
[name:] WHILE expression DO
statments;
IF expression2 THEN LEAVEN name;
END IF;
END WHILE;
异常处理
发生异常时，我们可以声明一个处理程序
DECLARE action HANDLER FOR condition_value statement;
action 接受以下值之一：
- CONTINUE:继续执行封闭代码块（begin end）
- EXIT:封闭代码块的执行终止
condition_value指定一个特定条件或一类激活处理程序的条件。condition_value接受以下值： - 一个mysql错误代码
- 标准的sqlstate值或者它可以是 SQL WARNING, NOT FOUND或SQL EXCEPTION条件。NOT FOUND 用于游标或select into variable_list语句。
游标
要处理存储过程中的结果集，就需要使用游标了。游标可以迭代查询返回的一组行。
游标是只读的。
游标使用的步骤如下：
声明游标
DECLAERE cursor_name CURSOR FOR SELECT_statements;
游标必须声明在变量声明之后。否则会报错。游标必须始终与select语句相关联。
打开游标
open cursor_name;
取数据
FETCHC cursor_name INTO variables list;
将游标中的值取出来，并赋值给variables list。
关闭游标
close cursor_name;
示例：
delimiter //
create procedure mypr()
begin
declare i int default 0;
declare is_finished int default 0;
declare cur cursor for select id from a;
declare continue handler for not found set is_finished = 1;
open cur;
while is_finished = 0 do
fetch cur into i;
if is_finished = 0 then select i;
end if;
end while;
close cur;
end //
delimiter ;
上述示例没有什么业务场景，就是一个简单的打印a表中所有的id值。
函数
函数和存储过程十分的类似。
其创建的语句为：
CREATE FUNCTION function_name(param1,param2,…)
RETURNS datatype
[NOT] DETERMINISTIC
statements
- 创建函数关键字是FUNCTION,参数不能IN,OUT,INOUT修饰。
- RETURNS语句中必须指定返回值的数据类型。
- 对于相同的输入参数，如果函数返回相同的结果，这样则被认为是确定性的，否则不是确定性的。必须决定一个存储函数是否是确定性的。如果声明不正确，可能产生意想不到的结果。
- 将代码写入主体中。可以使单个语句也可以是复合语句。在主语部分中，必须至少指定一个RETURN语句，return返回一个值给调用者。
示例：
delimiter //
create function myfu(a int) return varchar(20)
begin
declare v varchar(20) default '';
case a
when 1 then set v = 'hello';
when 2 then set v = 'world';
else v = 'nihao';
end case;
return v;
end //
delimiter ;
区别
-
存储过程参数有IN,OUT,INOUT三种，而函数中参数没有类型修饰，均为IN参数
-
存储过程没有返回值(可以通过OUT或INOUT参数返回值)，而函数有返回值
-
调用方式不同，存储过程用call调用，而函数使用select调用(和系统函数一致)
网友评论