devbox
喵喵爱编程
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

MariaDB 存储过程与函数详解_mariadb function

作者：喵喵爱编程 | 2024-06-30 11:01:55

赞

踩

mariadb function

简单的说,存储过程就是一条或者多条SQL语句的集合,可以理解为脚本,但是起作用不仅限于批处理,下面我们将重点学习如何使用创建存储函数和过程,变量的调用查看等,存储过程是MySQL的一个重点内容.

存储程序可以分为存储过程和函数,MySQL中创建存储过程和函数使用的语句分别是:CREATE PROCEDURE和CREATE FUNCTION.使用CALL语句来调用存储过程,只能用输出变量返回值.函数可以从语句外调用(即通过引用函数名),也能返回标量值,存储过程也可以调用其他存储过程.

创建存储过程

创建存储过程,需要使用create procedure语句,其基本语法格式如下:

  1. create procedure proc_name ([proc_parameter])
  2. 	[characteristics ...] routine_body

如上,create procedure为用来创建存储函数的关键字,proc_name为存储过程名称,proc_parameter为指定存储过程的参数列表,列表的形式如下所示:

[ IN | OUT | INOUT ] param_name type

其中,IN表示输入的参数,OUT表示输出的参数,INOUT表示即可以输入也可以输出,param_name表示参数名称,type表示参数的类型,该类型可以使MySQL数据库中任意的类型.

创建简单存储过程: 创建一个存储过程Proc(),实现查询lyshark数据表的所有字段.

  1. MariaDB [lyshark]> delimiter //
  2. MariaDB [lyshark]> create procedure Proc()
  3.     -> BEGIN
  4.     -> select * from lyshark;
  5.     -> END //
  6. Query OK, 0 rows affected (0.00 sec)
  7.  
  8. MariaDB [lyshark]> delimiter ;
  9. MariaDB [lyshark]> call Proc;

以上存储过程,只是执行了查询语句的操作,delimiter语句的作用是方式结束符与冒号相冲突,当我们写完存储过程时,应该使用相同的闭合语句恢复.

创建带参存储过程: 创建一个存储过程,查询lyshark表中记录,过程名称为CountProc,并使用count(*)计算后把结果放入参数param1中,SQL语句如下:

  1. MariaDB [lyshark]> delimiter //
  2. MariaDB [lyshark]> create procedure CountProc(OUT param1 INT)
  3.     -> BEGIN
  4.     -> select count(*) into param1 from lyshark;
  5.     -> END //
  6. Query OK, 0 rows affected (0.01 sec)
  7.  
  8. MariaDB [lyshark]> delimiter ;

当我们想要调用上面的存储过程是,需要指定一个变量名来接收返回结果,此处指定为temp.

  1. MariaDB [lyshark]> call CountProc(@temp);
  2. Query OK, 1 row affected (0.00 sec)
  3.  
  4. MariaDB [lyshark]> select @temp;
  5. +-------+
  6. | @temp |
  7. +-------+
  8. |    17 |
  9. +-------+
  10. 1 row in set (0.00 sec)

创建带参存储过程: 创建一个存储过程CountProc1,然后调用这个过程,定义SQL语句如下:

  1. MariaDB [lyshark]> delimiter //
  2. MariaDB [lyshark]> create procedure CountProc1(IN id int,OUT temp INT)
  3.     -> BEGIN
  4.     -> select count(*) into temp from lyshark where Gid=id;
  5.     -> END //
  6. Query OK, 0 rows affected (0.00 sec)
  7.  
  8. MariaDB [lyshark]> delimiter ;

接着我们调用CountProc1这个存储过程,并给它传递相应的参数.

  1. MariaDB [lyshark]> select count(*) from lyshark where Gid=101;
  2. +----------+
  3. | count(*) |
  4. +----------+
  5. |        3 |
  6. +----------+
  7. 1 row in set (0.00 sec)
  8.  
  9. MariaDB [lyshark]> call CountProc1(101,@num);
  10. Query OK, 1 row affected (0.00 sec)
  11.  
  12. MariaDB [lyshark]> select @num;
  13. +------+
  14. | @num |
  15. +------+
  16. |    3 |
  17. +------+
  18. 1 row in set (0.00 sec)

创建存储函数

存储函数的使用方法与MySQL内部函数的使用方法是一样的,在MySQL中,用户自己定义的存储函数与MySQL内部函数是一个性质的.区别在于,存储函数是用户自己定义的,而内部函数则是开发者编写的.

创建存储函数: 创建储存函数CountProc2,然后调用这个函数,SQL语句如下:

  1. MariaDB [lyshark]> delimiter //
  2. MariaDB [lyshark]> create function CountProc2(myid INT)
  3.     -> returns int
  4.     -> BEGIN
  5.     -> return(select count(*) from lyshark where Gid=myid);
  6.     -> END //
  7. Query OK, 0 rows affected (0.02 sec)
  8.  
  9. MariaDB [lyshark]> delimiter ;

接着我们来调用这个存储函数,SQL语句如下:

  1. MariaDB [lyshark]> select CountProc2(101);
  2. +-----------------+
  3. | CountProc2(101) |
  4. +-----------------+
  5. |               3 |
  6. +-----------------+
  7. 1 row in set (0.00 sec)

以上可以看出,返回结果,虽然存储函数和存储过程的定义稍有不同,但是可以实现相同功能.

创建存储函数: 创建名称为NameZip,该函数返回select语句查询结果,数值类型为字符串,SQL语句如下:

  1. MariaDB [lyshark]> delimiter //
  2. MariaDB [lyshark]> create function NameZip()
  3.     -> returns char(50)
  4.     -> return (select s_name from suppliers where s_call='4521');
  5.     -> //
  6. Query OK, 0 rows affected (0.00 sec)
  7.  
  8. MariaDB [lyshark]> delimiter ;

调用一下看看效果,SQL语句如下.

  1. MariaDB [lyshark]> select s_name from suppliers where s_call ='4521';
  2. +------------+
  3. | s_name     |
  4. +------------+
  5. | FastFruit. |
  6. +------------+
  7. 1 row in set (0.00 sec)
  8.  
  9. MariaDB [lyshark]> select NameZip();
  10. +------------+
  11. | NameZip()  |
  12. +------------+
  13. | FastFruit. |
  14. +------------+
  15. 1 row in set (0.00 sec)

如果在存储函数中的RETURN语句返回一个类型不同于函数的型的值,返回值将被强制为恰当的类型.

提示:指定参数为IN,OUT或者INOUT只对PROCEDURE是合法的.FUNCTION中总是默认为IN参数.RETURNS子句只能对FUNCTION做指定,对函数而言这是强制的.它用来指定函数的返回类型,而且函数体必须包含一个RETURN value语句.

声明使用变量

变量可以在子程序中声明并使用,这些变量的作用范围实在BEGIN...END程序中,本小姐将介绍定义和赋值一个变量,定义变量的语句如下:

DECLARE var_name[,varname]...date_type [DEFAULT value];

如上,var_name为局部变量名称,DEFAULT value子句给变量提供一个默认值,值除了可以被声明为一个常数之外,还可以被指定为一个表达式,如果没有DEFAULT子句,初始值为NULL.

定义变量: 定义名称为myparam的变量,类型为INT,默认值设置为100,只能在过程中使用.

  1. DECLARE myparam INT DEFAULT 100;
  2. DECLARE charsss char DEFAULT 'hello';

变量赋值: 定义3个变量,分别为var1,var2,var3,数据类型为INT,使用SET为变量赋值,代码如下:

  1. DECLARE var1,var2,var3 INT;
  2.  
  3. MariaDB [lyshark]> set @var1=10,@var2=30;
  4. Query OK, 0 rows affected (0.01 sec)
  5.  
  6. MariaDB [lyshark]> set @var3=@var1+@var2;
  7. Query OK, 0 rows affected (0.00 sec)
  8.  
  9. MariaDB [lyshark]> select @var3;
  10. +-------+
  11. | @var3 |
  12. +-------+
  13. |    40 |
  14. +-------+
  15. 1 row in set (0.00 sec)

使用数据光标

查询语句可能返回多条记录,如果数据量非常大,需要在存储过程和函数中使用光标来逐条读取查询结果集中的记录,光标必须在声明处理程序之前被声明,并且变量和条件还必须在声明光标或处理程序之前被声明.

声明光标: 声明名称为cursor_lyshark的光标,SQL代码如下:

declare cursor_lyshark cursor for select Name,Price from lyshark;

打开光标: 打开名称为cursor_lyshark的光标,SQL代码如下:

open cursor_lyshark

使用光标: 使用名称为cursor_lyshark的光标,将查询出的数据存入lyshark_name和lyshark_price这两个变量中.

fetch cursor_lyshark into lyshark_name,lyshark_price;

关闭光标: 关闭名称为cursor_lyshark的光标文件.

close cursor_lyshark

使用流程控制

流程控制语句用来根据条件控制语句的执行,MySQL中用来构造控制流程的语句有:IF,CASE,LOOP,LEAVE,ITERATE,REPEAT,WHILE等,每个流程中可能包含一个单独的语句,或者是使用BEGIN...END构造的复合语句,构造可以被嵌套.

IF-THEN-ELSE条件语句: IF语句用来判断条件分支

首先传递一个数值,接收到parameter变量里,然后自增+1,并判断,如果var=0则返回系统时间,var=1则返回系统日期.

  1. CREATE PROCEDURE proc_1(IN parameter int)
  2. BEGIN
  3.     DECLARE var int;
  4.     SET var=parameter+1;
  5. 		
  6. 		IF var=0
  7. 		THEN
  8. 				select CURRENT_TIME();
  9. 		ELSEIF var=1
  10. 		THEN
  11. 				select CURRENT_DATE();
  12. 		END IF;
  13.   END

接着我们编译这段过程,并测试依次传入temp=-1和temp=1.

  1. MariaDB [lyshark]> set @temp=0;
  2. Query OK, 0 rows affected (0.00 sec)
  3.  
  4. MariaDB [lyshark]> call proc_1(@temp);
  5. +----------------+
  6. | CURRENT_DATE() |
  7. +----------------+
  8. | 2018-12-28     |
  9. +----------------+
  10. 1 row in set (0.00 sec)
  11.  
  12. Query OK, 0 rows affected (0.00 sec)
  13.  
  14. MariaDB [lyshark]> set @temp=-1;
  15. Query OK, 0 rows affected (0.00 sec)
  16.  
  17. MariaDB [lyshark]> call proc_1(@temp);
  18. +----------------+
  19. | CURRENT_TIME() |
  20. +----------------+
  21. | 07:23:04       |
  22. +----------------+
  23. 1 row in set (0.00 sec)
  24.  
  25. Query OK, 0 rows affected (0.00 sec)

CASE-WHEN-THEN-ELSE语句: 另一个进行条件判断的语句,条件判断分支结构

  1. CREATE PROCEDURE proc_2 (IN parameter INT)
  2.     BEGIN
  3.       DECLARE var INT;
  4.       SET var=parameter+1;
  5.       CASE var
  6.         WHEN 0 THEN
  7.           select "这个数值是0呀";
  8.         WHEN 1 THEN
  9.           select "这个数值是1呀";
  10.         ELSE
  11.           select "这个数值是其他呀";
  12.       END CASE ;
  13.     END ;
  1. MariaDB [lyshark]> set @temp=0;
  2. Query OK, 0 rows affected (0.00 sec)
  3.  
  4. MariaDB [lyshark]> call proc_2(@temp);
  5. +---------------------+
  6. | 这个数值是1呀       |
  7. +---------------------+
  8. | 这个数值是1呀       |
  9. +---------------------+
  10. 1 row in set (0.00 sec)
  11.  
  12. Query OK, 0 rows affected (0.00 sec)
  13.  
  14. MariaDB [lyshark]> set @temp=-1;
  15. Query OK, 0 rows affected (0.00 sec)
  16.  
  17. MariaDB [lyshark]> call proc_2(@temp);
  18. +---------------------+
  19. | 这个数值是0呀       |
  20. +---------------------+
  21. | 这个数值是0呀       |
  22. +---------------------+
  23. 1 row in set (0.00 sec)
  24.  
  25. Query OK, 0 rows affected (0.00 sec)
  26.  
  27. MariaDB [lyshark]> set @temp=-9999;
  28. Query OK, 0 rows affected (0.00 sec)
  29.  
  30. MariaDB [lyshark]> call proc_2(@temp);
  31. +--------------------------+
  32. | 这个数值是其他呀         |
  33. +--------------------------+
  34. | 这个数值是其他呀         |
  35. +--------------------------+
  36. 1 row in set (0.00 sec)
  37.  
  38. Query OK, 0 rows affected (0.00 sec)

LOOP语句: 用于循环执行重复语句,LOOP只会创建一个循环过程,并不会判断.

首先使用LOOP语句进行循环操作,id值小于等于5之前,将重复执行循环过程,代码如下:

  1. CREATE PROCEDURE proc_4()
  2. 	BEGIN
  3.       DECLARE id INT;
  4.       SET id=0;
  5.       LOOP_LABLE:LOOP
  6. 		SELECT "这里开始计数";
  7.         SET id=id+1;
  8.         IF id >=5 THEN
  9.           LEAVE LOOP_LABLE;  #退出循环
  10.         END IF;
  11.       END LOOP;
  12. 	END;

WHWHILE-DO…END-WHILE语句: 流程循环语句.

  1. CREATE PROCEDURE proc_4()
  2.     BEGIN
  3.       DECLARE var INT;
  4.       SET var=0;
  5.       WHILE var<6 DO
  6.         select "循环判断";
  7.         SET var=var+1;
  8.       END WHILE ;
  9.     END;

ITERATE语句: 通过引用复合语句的标号,来从新开始复合语句

  1. CREATE PROCEDURE proc_5()
  2.   BEGIN
  3.     DECLARE v INT;
  4.     SET v=0;
  5.     LOOP_LABLE:LOOP
  6.       IF v=3 THEN
  7.         SET v=v+1;
  8.         ITERATE LOOP_LABLE;
  9.       END IF;
  10.       INSERT INTO t VALUES(v);
  11.       SET v=v+1;
  12.       IF v>=5 THEN
  13.         LEAVE LOOP_LABLE;
  14.       END IF;
  15.     END LOOP;
  16.   END;

REPEAT语句: 此语句的特点是执行操作后检查结果

  1. CREATE PROCEDURE proc_6 ()
  2.     BEGIN
  3.       DECLARE v INT;
  4.       SET v=0;
  5.       REPEAT
  6.         INSERT INTO t VALUES(v);
  7.         SET v=v+1;
  8.         UNTIL v>=5
  9.       END REPEAT;
  10.     END;

查看删除过程

MySQL中,用户可以使用SHOW STATUS语句或SHOW CREATE语句来查看存储过程和函数,也可以直接从系统的information_schema数据库中查询

show status查看存储过程:

  1. MariaDB [lyshark]> show procedure status like 'p%' \G
  2.  
  3. *************************** 1. row ************************
  4.                   Db: lyshark
  5.                 Name: proc_1
  6.                 Type: PROCEDURE
  7.              Definer: lyshark@%
  8.             Modified: 2018-12-28 21:16:26
  9.              Created: 2018-12-28 21:16:26
  10.        Security_type: DEFINER
  11.              Comment:
  12. character_set_client: utf8mb4
  13. collation_connection: utf8mb4_general_ci
  14.   Database Collation: latin1_swedish_ci
  15. 12 rows in set (0.00 sec)

show create查看过程与函数:

  1. MariaDB [lyshark]> show create function lyshark.NameZip;
  2. +----------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
  3. | Function | sql_mode | Create Function                                                                                                                                  | character_set_client | collation_connection | Database Collation |
  4. +----------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+
  5. | NameZip  |          | CREATE DEFINER=`root`@`localhost` FUNCTION `NameZip`() RETURNS char(50) CHARSET latin1
  6. return (select s_name from suppliers where s_call='4521') | utf8                 | utf8_general_ci      | latin1_swedish_ci  |
  7. +----------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------+----------------------+----------------------+--------------------+

select 查询存储过程:

  1. MariaDB [lyshark]> select name,created from mysql.proc where db="lyshark" and type="PROCEDURE";
  2. +------------+---------------------+
  3. | name       | created             |
  4. +------------+---------------------+
  5. | CountProc  | 2018-12-28 04:19:58 |
  6. | CountProc1 | 2018-12-28 04:31:10 |
  7. | Proc       | 2018-12-28 03:47:30 |
  8. | proc11111  | 2018-12-28 21:24:19 |
  9. | proc3      | 2018-12-28 05:34:18 |
  10. | proc4      | 2018-12-28 05:37:10 |
  11. | proc8      | 2018-12-28 21:27:12 |
  12. | proc_1     | 2018-12-28 07:20:08 |
  13. | proc_111   | 2018-12-28 21:14:01 |
  14. | proc_1111  | 2018-12-28 21:14:16 |
  15. | proc_2     | 2018-12-28 07:27:32 |
  16. | proc_3     | 2018-12-28 07:35:11 |
  17. | proc_4     | 2018-12-28 21:15:40 |
  18. | proc_444   | 2018-12-28 21:16:26 |
  19. | wang       | 2018-12-28 04:19:07 |
  20. | wang1      | 2018-12-28 04:25:30 |
  21. +------------+---------------------+
  22. 16 rows in set (0.00 sec)

select 查询存储函数:

  1. MariaDB [lyshark]> select name,created from mysql.proc where db="lyshark" and type="FUNCTION";
  2. +------------+---------------------+
  3. | name       | created             |
  4. +------------+---------------------+
  5. | CountProc2 | 2018-12-28 05:04:28 |
  6. | NameZip    | 2018-12-28 05:16:35 |
  7. +------------+---------------------+
  8. 2 rows in set (0.00 sec)

show语句的其他用法:

  1. MariaDB [lyshark]> show procedure status;
  2. MariaDB [lyshark]> show function status;

删除过程与函数:

  1. drop procedure lyshark;
  2. drop function countproc;
本文内容由网友自发贡献，转载请注明出处：https://www.wpsshop.cn/w/喵喵爱编程/article/detail/772459
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号