Mysql中的存储程序——存储函数

存储程序按照调用方式的不同，可分为：存储例程、触发器、事件。存储例程又可以分为存储函数和存储过程。在这里，主要讲存储例程，因为触发器、事件都是由Mysql自动调用的，存储例程是人工写的。

以前的SQL都是单挑语句的执行，顶多语句复杂一点，现在从更高维度来看待SQL，多条SQL将封装成一个程序。那么就需要有程序最基本的规则。

设置变量

不管什么语言，都有“变量”的定义，SQL中，用户操作的变量叫用户变量，系统自带的变量即系统变量。

新建用户变量。变量类型可以是不同的，变量可以赋值给变量，原变量的值是不变的。（注意必须不管新建还是查询，用户变量必须带@符号）

mysql> set @yz=12306;
Query OK, 0 rows affected (0.00 sec)

变量赋值

赋值方式有三种

新建时直接使用set赋值
通过SQL查询出来的结果，使用set赋值，但SQL查出的结果必须是一行一列，否则就会报错
通过SQL哈讯出来的结果，在末尾使用into赋值。这种方式允许SQL结果集是一行一列，也可以是一行多列，但变量数必须和列数相等，否则也会有错误

mysql> select key1 from st1 where id=10;
+-------------------------+
| key1                    |
+-------------------------+
| 6h2eatxd1wslfyendpvflw4 |
+-------------------------+
1 row in set (0.00 sec)

mysql> set @key1=(select key1 from st1 where id=10);
Query OK, 0 rows affected (0.00 sec)
mysql> select @key1;
+-------------------------+
| @key1                   |
+-------------------------+
| 6h2eatxd1wslfyendpvflw4 |
+-------------------------+
1 row in set (0.00 sec)
------------------------------------------------------------第二种赋值方式---------------------------------------------

mysql> select key2 from st1 where id=11 into @key2;

Query OK, 1 row affected (0.00 sec)

mysql> select @key2;
+-----------+
| @key2 |
+-----------+
| 282671284 |
+-----------+
1 row in set (0.00 sec)

mysql> select key2 from st1 where id=11;
+-----------+
| key2 |
+-----------+
| 282671284 |
+-----------+
1 row in set (0.00 sec)

存储函数

创建存储函数

注意：

使用delimiter 可以暂时将SQL分隔符替换成其他自定义符号，这样才能使得begin中的SQL语句不会因为分号而终止运行。
存储函数的函数体的最后，一定包含 return 语句

mysql> delimiter $
mysql> create function get_key1(ids int(10)) 　　 //参数有0个或多个；参数名不可与列名、变量名冲突；参数无默认值
    -> returns varchar(100)　　　　　　　　　　　　　//返回的类型，定义是需要非常注意，这里的类型不能乱填，需要返回真实返回的数据的类型
    -> begin　　　　　　　　　　　　　　　　　　　　　　//开始执行
    -> return (select avg(key1) from st1 where id=ids);  //return结果是select语句的结果集
    -> end $　　　　　　　　　　　　　　　　　　　　　　//结束函数
Query OK, 0 rows affected (0.10 sec)

查看、使用、操作存储函数

//和表一样，存储函数也可以查看它的定义信息
mysql> show create function get_key1\G
*************************** 1. row ***************************
            Function: get_key1
            sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
     Create Function: CREATE DEFINER=`root`@`localhost` FUNCTION `get_key1`(ids int(10)) RETURNS varchar(100) CHARSET utf8 COLLATE utf8_unicode_ci
begin
return (select avg(key1) from st1 where id=ids);
end
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_unicode_ci
1 row in set (0.00 sec)
----------------------------------------------------------------------------------------------

//和普通的函数一样使用即可

mysql> select get_key1(2)$
+-------------+
| get_key1(2) |
+-------------+
| kyp |
+-------------+
1 row in set (0.00 sec)

-----------------------------------------------------------------------------------------------

//删除存储函数

mysql> drop function get_key1$
Query OK, 0 rows affected (0.00 sec)

//注意修改了分隔符的需要改回来

mysql> delimiter ;
mysql>

函数体中操作局部变量

create function get_jb()
returns int
begin
declare c int;   //使用declare定义局部变量，且局部变量是不带@符号的。并且可使用default关键字设置默认值
set c=100;
return c;
end #
Query OK, 0 rows affected (0.00 sec)

mysql> select get_jb()#
+----------+
| get_jb() |
+----------+
|      100 |
+----------+
1 row in set (0.00 sec)

函数体中操作用户变量

函数体中可以使用并且修改函数外部用户定义的变量

mysql> delimiter #
mysql> create function get_demo()
    -> returns int
    -> begin
    -> set @qwe=10;
    -> return @qwe;
    -> end #
Query OK, 0 rows affected (0.00 sec)

mysql> select get_demo()#
+------------+
| get_demo() |
+------------+
|         10 |
+------------+
1 row in set (0.00 sec)

函数体中的分支语法

if else-选择

关注语法，正常调用即可

mysql> delimiter #
mysql> create function get_choice(c int)   //参数传入时，参数名在前，类型在后
    -> returns varchar(10)
    -> begin
    -> declare result varchar(10);
    -> if c = 1 then
    -> set result='结果是1';
    -> elseif c = 2 then
    -> set result='结果是2';
    -> elseif c = 3 then
    -> set result='结果是3';
    -> else 
    -> set result='非法参数';
    -> end if;
    -> return result;
    -> end #
Query OK, 0 rows affected (0.00 sec)

循环语句

while

即传统语言的while，先判断条件，再决定是否执行

create function get_while(c int unsigned)
returns int
begin
    declare result int default 0;
    declare i int default 1;
    while i <= c do              //while 条件 do 循环体 end while
    set result = result +i;
    set i = i + 1;
    end while;
    return result;
end #

repeat

create function get_repeat(c int unsigned)
returns int
begin
    declare result int default 0;
    declare i int default 1;
    repeat 　　　　　　　　　　　　//repaet 循环体 until 条件 end repeat。最少会循环一次，类似do-while
    set result = result +i;
    set i = i + 1;
    until i > c end repeat;
    return result;
end #

loop

lopp没有设置自己的终止循环条件。想让loop循环停止只有两种方法：

1. 直接搭配条件语句，使用return语句使函数终止，这样函数中的循环也就停止了

create function get_loop(c int unsigned)
returns int
begin
    declare result int default 0;
    declare i int default 1;
    loop 
    if i > c then
    return result;
    end if;
    set result = result +i;
    set i = i + 1;
    end loop;
end #

2. 使用leave语句，但相对麻烦，需要添加很多标记。这里的标记应该是函数的一个关键字，函数中很多地方都可以使用它来结束某个语句或程序。以我的理解，这种方式相对容易使用的，因为它可以使用其他条件来结束循环，而不是依赖于步进的 i ，甚至可以不用定义循环中的 i

create function get_leave(c int unsigned)
returns int
begin
    declare result int default 0;
    declare i int default 1;
    flag:loop                 //1、在使用lopp前加上一个标记
    if i > c then
    leave flag;　　　　　　　　　//2、执行leave时，就会把名为flag所标记的循环语句给停掉
    end if;
    set result = result +i;
    set i = i + 1;
    end loop flag;　　　　　　//3、结束时也需添加标记
    return result;
end #

posted @ 2021-12-21 22:30 我永远喜欢石原里美阅读(283) 评论(0) 收藏举报

刷新页面返回顶部

我永远喜欢石原里美