ImQi1云笔记

外观

创建存储过程和存储函数

创建存储过程

创建存储过程的语法如下：

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 //
运行结果
运行结果

然后调用存储过程，最后再查看表的数据。

运行结果
运行结果

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

许可证：署名 4.0 国际 (CC-BY-4.0)

2026 © 棋