MySQL语法拾遗-存储程序

---

title: mysql语法拾遗-存储程序
date: 2019-11-11 12:23:49
categories:

• MySQL
• MySQL语法相关
  tags:
• MySQL
• 语法指南
  description: MySQL中的存储程序介绍（存储例程，触发器，事件）

---

MySQL中的存储程序

自定义变量与语句结束符

自定义变量 set

使用set语句，自定义变量,变量名前要加’@'符号。可以将常量，某一搜索结果值，赋值给变量

-- 将1赋值给变量a
set @a = 1
-- 将搜索结果赋值给多个变量
SELECT m1, n1 FROM t1 LIMIT 1 INTO @a, @b
-- 查看变量
select @a

语句结束符 delimiter

delimiter 设置语句的语句结束分隔符，默认为’;’

delimiter $
SELECT * FROM t1 LIMIT 1;
SELECT * FROM t2 LIMIT 1;
SELECT * FROM t3 LIMIT 1;
 $

存储函数与存储过程

存储函数

将MySQL语句封装到某一函数中。

创建存储函数

语句形式：

CREATE FUNCTION 函数名(`[参数列表]`)
RETURNS 返回值类型
BEGIN
    函数体（以';'分隔语句）
END

e.g.

delimiter $
CREATE FUNCTION avg_score(s VARCHAR(100))
RETURNS DOUBLE
BEGIN
    RETURN (SELECT AVG(score) FROM student_score WHERE subject = s);
END $
delimiter $

调用函数

select avg_score(param1,param2...)

查看与删除存储函数

批量查询函数：show function status like [函数名]

查询具体函数内容：show create function [函数名]

删除函数：drop function [函数名]

存储函数的函数体

定义局部变量 declare

• 函数体中的局部变量，必须先定义再使用，局部变量无需加前缀@。
• 若局部变量未被赋值，可以使用定义时的default值。
• 局部变量只在函数体内有效！
  定义方法：declare param1,param2,... [数据类型] [default 默认值]

e.g.

delimiter $
create function var_demo()
returns int
begin
    declare c int;
    set c = 4;
    return c;
end $
delimiter $

自定义（全局）变量 @

在函数体内可以使用自定义变量，无需使用delcare定义变量。自定义变量在函数体外仍然有效，类似全局变量。

判断语句

IF语句格式：

if 表达式 then
    语句列表
elseif 表达式 then
    语句列表
else
    语句列表
end if;

e.g.

delimiter $
create function condition_demo(i int)
returns varchar(10)
begin
    declare result varchar(10);
    if i=1 then
        set result='结果是1';
    ELSEIF i = 2 THEN
         SET result = '结果是2';
    ELSEIF i = 3 THEN
         SET result = '结果是3';
    ELSE
         SET result = '非法参数';
    END IF;
    RETURN result;
 END $
 delimiter $

循环语句

• while循环语句

先判断表达式，则执行语句列表，否则退出循环

语句格式：

while 表达式 do
    语句列表
end while;

e.g.

-- 计算n以内的累计和
mysql> delimiter $
mysql> CREATE FUNCTION sum_them(n INT UNSIGNED)
-> RETURNS INT
-> BEGIN
->     DECLARE result INT DEFAULT 0;
->     DECLARE i INT DEFAULT 1;
->     WHILE i <= n DO
->         SET result = result + i;
->         SET i = i + 1;
->     END WHILE;
->     RETURN result;
-> END $

• repeat循环语句

先执行语句，再判断表达式，成立则退出循环。与while类似，形式略不同：

repeat
    语句列表
until 表达式 end repeat;

• loop循环语句

语句形式：

loop
    语句列表
end loop;

在语句列表中，可以添加return语句来跳出循环，并返回值。或者使用leave语句直接跳出循环，不返回值。使用leave时，要先标记循环体

CREATE FUNCTION sum_all(n INT UNSIGNED)
RETURNS INT
BEGIN
    DECLARE result INT DEFAULT 0;
    DECLARE i INT DEFAULT 1;
    flag:LOOP  -- flag:  标记循环体
        IF i > n THEN
            LEAVE flag;
        END IF;
        SET result = result + i;
        SET i = i + 1;
    END LOOP flag;
    RETURN result;
END

存储过程

创建存储过程

存储函数与存储过程类似，都是对SQL语句封装。而，存储函数侧重于返回值，存储过程侧重于单纯的执行语句。

无需声明返回值类型，创建格式：

create procedure 存储过程名称（`[参数名称]`）
begin
    语句列表
end

调用存储过程

call 存储过程

查看于删除存储过程

语句与存储函数类似，只需将function改为procedure.

• 查看当前数据库中创建的存储过程都有哪些的语句：

SHOW PROCEDURE STATUS [LIKE 需要匹配的存储过程名称]

• 查看某个存储过程具体是怎么定义的语句：

SHOW CREATE PROCEDURE 存储过程名称

• 删除存储过程的语句：

DROP PROCEDURE 存储过程名称

存储过程的参数前缀

存储过程在定义参数时可以添加前缀,默认外in：

参数类型(IN | OUT | INOUT) 参数名 数据类型

前缀	实际参数是否必须是变量	描述
IN	否	仅用于向存储过程传递数据，如果IN参数在过程中被修改，调用者不可见。
OUT	是	传递的数据将无效，仅用于将过程产生的数据赋值给OUT参数，存储过程执行结束后，调用者可以访问到OUT参数。
INOUT	是	综合IN和OUT的特点，既可以用于调用者向存储过程传递数据，也可以用于存放存储过程中产生的数据以供调用者使用。

存储过程和存储函数的不同点

存储函数在定义时需要显式用RETURNS语句标明返回的数据类型，而且在函数体中必须使用RETURN语句来显式指定返回的值，存储过程不需要。

存储函数只支持IN参数，而存储过程支持IN参数、OUT参数、和INOUT参数。

存储函数只能返回一个值，而存储过程可以通过设置多个OUT参数或者INOUT参数来返回多个结果。

存储函数直接在表达式中调用，而存储过程只能通过CALL语句来显式调用。