外观
创建存储过程和存储函数
创建存储过程
创建存储过程的语法如下:
CREATE PROCEDURE proc_name([proc_parameter[...]])
[characteristic] routine_body其中,proc_name代表存储过程的名称;proc_charameter代表存储过程的参数列表;characteristic代表存储过程的特性;routine_body代表SQL代码的内容。proc_parameter参数由三部分组成,分别为输入或输出类型、参数名称和参数类型。其形式为[IN|OUT|INOUT]param_name type。其中IN表示输入参数;OUT表示输出参数;INOUT表示既可作为输入函数又可作为输出函数;param_type表示存储过程参数名称;type参数指存储过程的参数类型,该类型可以为MySQL中的任意数据类型。一个存储过程包含名称、参数列表、还可以包含很多SQL语句集。
注:characteristic部分的语法如下。
{CONTAINS SQL|NO SQL|READS SQL DATA|MODIFIES SQL DATA}
|SQL SECURITY {DEFINER|INVOKER}
|COMMENT 'string'| 参数 | 说明 |
|---|---|
| sp_name | 存储过程或存储函数的名称 |
| characteristic | 指定存储函数的特性 |
| CONTRAINS SQL | 表示子程序包含SQL语句,但不包含读写数据的语句 |
| NO SQL | 表示子程序不包含SQL语句 |
| READS SQL DATA | 表示子程序中包含读数据的语句 |
| MODIFIES SQL DATA | 表示子程序中包含写数据的语句 |
| SQL SECURITY | 指明权限执行。DEFINER表示只有定义者才能执行;INVOKER表示调用者才能执行 |
| COMMENT ‘string’ | 注释信息 |
输入参数、输出参数、输入输出参数:
in 输入参数,意思说你的参数要传到存过过程的过程里面去,在存储过程中修改该参数的值不能被返回
out 输出参数:该值可在存储过程内部被改变,并向外输出,一般在调用时使用全局变量,即在变量名前加一个“@”符号,此时这个变量就存储起来了
inout 输入输出参数,既能输入一个值又能传出来一个值
在MySQL中存储过程体以CREATE PROCECURE开始,后面紧跟存储过程的名称和参数。MySQL中的存储过程名称不区分大小写,且存储过程不能与MySQL中的内置函数同名。存储过程体的语句块以begin开始,以end结束,语句体中可以包含变量的声明、控制语句、SQL查询语句等。由于存储过程体内部要以分号结束,所以在定义存储过程体之前都要新定义一个结束符号,并且应降低该字符在语句块中的出现概率。
下面我们先创建一个数据表,该数据表中只包含一个字段用于存储编程语言名称的字段,创建存储过程的语句如下。
delimiter //
create procedure proc_name(in parameter int)
begin
declare variable varchar(50);
if parameter = 1 then
set variable = 'MySQL';
elseif parameter = 2 then
set variable = 'Java';
else set variable = 'Python';
end if;
insert into tb values (variable);
end
//在此例中,我们将parameter作为输入量,然后通过判断parameter的值来改变变量的值,最后使用INSERT INTO语句实现快速插入数据的操作。

MySQL中默认的语句结束符为分号,存储过程中的SQL语句需要使用分号结束.为了避免冲突,首先用“delimiter //”将MySQL的结束符设置为”//“,再用“delimiter ;”来将结束符设置成分号。
创建存储函数
创建存储函数的语法和创建存储过程的语法类似,语法如下:
CREATE FUNCTION sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body其中sp_name为存储函数的名称,func_parameter为存储函数的参数列表,RETURNS type指定返回值的类型,characteristic指定存储函数的特性,routine_body代表SQL代码内容。
下面仅演示如何创建存储函数。
delimiter //
create function func(i int)
returns varchar(50)
begin
if i=1 then return 'Java';
elseif i=2 then return 'Python';
else return 'MySQL';
end if;
end;
//若MySQL提示:This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable) 需要在MySQL的配置文件中找到[mysqld]位置,在该行下面设置log-bin-trust-function-creators=1。

变量的应用
局部变量
局部变量以关键字DECLARE声明,语法如下:
DECLARE variable_name type [DEFAULT value]其中variable_name表示局部变量名,type表示类型,DEFAULT value可选,表示为局部变量设置默认值。
如:
delimiter //
create procedure p1()
begin
declare i varchar(10) default 'outer';
begin
declare i varchar(10) default 'inner';
select i;
end;
select i;
end;
//
全局变量
全局变量比较于局部变量,只需要在前面加上一个“@”符号。
delimiter //
create procedure p2()
begin
set @a=1;
begin
set @a=2;
select @a;
end;
select @a;
end;
//
为变量赋值
MySQL中可以使用关键字DECLARE来声明变量,可以使用关键字SET为变量赋值,可以同时为多个变量赋值,各个变量的赋值语句用“,”隔开。
另外,MySQL还支持使用下面的语句结构为变量赋值:
SELECT col_name[,...] INTO var_name[,...] FROM table_name where condition;其中col_name代表查询的字段名称;var_name代表变量的名称;table_name代表表的名称,如从stu表中查询name为‘张三’的记录,并将记录中的tel赋值为局部变量i,那么代码如下:
select tel into i from stu where name = '张三';光标的应用
在MySQL中查询记录,其结果可能位多条记录。在存储过程和函数中使用光标可以实现逐条读取结果集中的记录。
光标的使用包括以下步骤:
声明光标
光标只能在存储过程中定义。声明光标使用关键字DECLARE,语法如下。
DECLARE cursor_name CURSOR FOR select_statement;其中,cursor_name为光标的名称,光标使用和表是同样的规则;select_statement是一个SELECT语句,返回一行或多行数据,该语句也可以在存储过程中定义多个光标,但是必须保证光标的名称不能相同。
打开光标
在声明光标之后,若要使用光标需要先打开光标。打开光标的语法如下。
OPEN cursor_name;使用光标
顺利打开光标后,可以使用FETCH...INTO语句读取数据,语法如下。
FETCH cursor_name INTO var_name[,var_name]...关闭光标
使用CLOSE关键字关闭光标,语法如下。
CLOSE cursor_name;现在举一个实例——更新表中信息:在员工表中将name字段由‘姓名’改成‘id-姓名’:
首先查看表中数据。

然后我们创建如下的存储过程。
delimiter //
create procedure proc()
begin
declare e_id int; # 存储字段id
declare e_name varchar(50); # 存储字段name
declare isDone int default 0; # 存储是否结束的标志
declare cur_proc cursor for select id as e_id,name as e_id from employees; # 声明光标
declare continue handler for not found set isDone = 1;
# 若光标没有成功查找到数据就将结束表示设为1
open cur_proc; # 开启光标
while not isDone do # 循环,若结束标志为0则继续循环
fetch cur_proc into e_id,e_name; # 使用光标,赋值
update employees set name = concat(e_id,'-',e_name) where id = e_id; # 连接
end while;
close cur_proc; # 关闭光标
end //
然后调用存储过程,最后再查看表的数据。

可以看到表的数据被改动了,说明达到目的了。