存储过程和函数相对于 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 调用 (和系统函数一致)
来源: http://www.jianshu.com/p/6e411e849502