存储过程就是一条或者多条SQL语句的集合。
1 创建存储过程和函数
存储程序可以分为存储过程和存储函数,MySQL中创建存储过程和函数使用的语句分别是:CREATE PROCEDURE和CREATE FUNCTION。使用CALL语句来调用存储过程,只能用输出变量返回值,函数可以从语句外调用(即通过引用函数名),也能返回标量值。存储过程也可以调用其它存储过程。
1.1 创建存储过程
- 创建存储过程,需要使用CREATE PROCEDURE语句,基本语法格式如下:
create procedure sp_name ([proc_parameter])
BEGIN
routine_body
END
- proc_parameter:为指定存储过程的参数列表,列表的形式为
[IN|OUT|INOUT] param_name type
- 如果需要为输出参数赋值,使用INTO关键字,
select column into para where condition;
1.2 创建存储函数
- 创建存储函数,需要使用CREATE FUNCTION语句,基本语法格式如下:
create function func_name ([proc_parameter])
returns type
routine_body
- 注意:指定参数为IN、OUT或INOUT只对PROCEDURE是合法的,FUNCTION中总是默认IN参数。RETURNS子句只能对FUNCTION做指定,对函数而言这是强制的。它用来指定函数的返回类型,而且函数体必须包含一个RETURN value语句。
1.3 变量
感谢:本部分的资料来源于博客
1.3.1 局部变量
- 局部变量一般用在sql语句块中,比如存储过程的begin/end。其作用域仅限于该语句块,在该语句块执行完毕后,局部变量就消失了。
局部变量一般用declare来声明,可以使用default来说明默认值。
定义变量:在存储过程中使用DECLARE语句定义变量,语法格式为
declare var_name,... data_type [default value];
- 变量赋值:MySQL使用SET语句为变量赋值,语法格式为
set var_name = expr,...;
- MySQL中还可以通过SELECT…INTO为一个或多个变量赋值,语法格式为
select col_name,... into var_name,... table_expr;
- MySQL中还可以通过SELECT…INTO为一个或多个变量赋值,语法格式为
1.3.2 用户变量
- 用户变量的作用域要比局部变量要广。用户变量可以作用于当前整个连接,但是当当前连接断开后,其所定义的用户变量都会消失。
- 用户变量使用如下(这里我们无须使用declare关键字进行定义,可以直接这样使用):
select @变量名
- 对用户变量赋值有两种方式,一种是直接用”=”号,另一种是用”:=”号。其区别在于使用set命令对用户变量进行赋值时,两种方式都可以使用;当使用select语句对用户变量进行赋值时,只能使用”:=”方式,因为在select语句中,”=”号被看作是比较操作符。
1.3.3 会话变量
- 服务器为每个连接的客户端维护一系列会话变量。在客户端连接时,使用相应全局变量的当前值对客户端的会话变量进行初始化。设置会话变量不需要特殊权限,但客户端只能更改自己的会话变量,而不能更改其它客户端的会话变量。会话变量的作用域与用户变量一样,仅限于当前连接。当当前连接断开后,其设置的所有会话变量均失效。
- 设置会话变量有如下三种方式:
- set session var_name = value;
- set @@session.var_name = value;
- set var_name = value;
- 查看一个会话变量也有如下三种方式:
- select @@var_name;
- select @@session.var_name;
1.4 光标
查询语句可能返回多条记录,如果数据量非常大,需要在存储过程中和存储函数中使用光标来逐条读取查询结果集中的记录,应用程序可以根据需要滚动或浏览其中的数据,需要注意的是,光标必须在声明处理程序之前被声明,并且变量和条件还必须在声明光标或处理程序之前被声明。
- 声明光标:MySQL中使用DECLARE关键字来声明光标,其语法格式为
declare cursor_name cursor for select_statement;
- 打开光标:
open cursor_name;
- 使用光标:
fetch cuesor_name into val_name [,...];
关闭光标:
close cursor_name;
注意: MySQL中光标只能在存储过程和函数中使用
1.5 流程控制
- IF语句:
if expr_condition then
statement_list;
elseif expr_condition then
statement_list;
else
statement_list;
end if;
- CASE语句:
# 第一种
case case_expr
when when_value then
statement_list;
...
else
statement_list;
end case;
# 第二种
case
when expr_condition then
statement_list;
...
else
statement_list;
end case;
- LOOP语句:
[loop_label:] loop
statement_list;
end loop [loop_label];
- LEAVE语句:类似break,用于跳出循环
leave label;
- ITERATE语句,类似jump,跳转到语句开头
iterate label;
- REPEAT语句:类似do while
[repeat_label:] repeat
statement_list;
until expr_condition
end repeat [repeat_lable];
- WHILE语句:
[while_label:] while expr_condition do
statement_list;
end while [while_lable];
2 调用存储过程和函数
存储过程必须使用CALL语句调用,并且存储过程和数据库相关,如果要执行其他数据库中的存储过程,需要指定数据库名称,例如CALL dbname.procname。存储函数的调用与MySQL中预定义的函数的调用方式相同。
3 查看存储过程和函数
- 使用SHOW STATUS语句查看存储过程和函数的状态:
show {procedure|function} status [like 'pattern'];
- 使用SHOW CREATE语句查看存储过程和函数的定义:
show create {procedure|function} sp_name;
- 从information_schema.Routines表中查看存储过程和函数的信息。
4 删除存储过程和函数
- 删除使用DROP语句:
drop {procedure|function} [if exists] sp_name;