自定义函数、存储过程(解释in、out和inout)和mysql中的定义变量

最新推荐文章于 2025-10-16 16:44:01 发布

原创最新推荐文章于 2025-10-16 16:44:01 发布 · 1.8k 阅读

7 ·

CC 4.0 BY-SA版权

文章标签：

#自定义函数 #存储过程 #in、out、inout的使用 #mysql定义变量

数据库专栏收录该内容

15 篇文章

订阅专栏

本文深入探讨了SQL函数和存储过程的创建与使用，详细解释了两者的语法、优点及局限性，并通过实例演示了如何在MySQL中定义和调用自定义函数与存储过程。

自定义函数

函数：是一系列sql语句的集合，他的注重点主要是返回值，可以作为查询语句的一个部分来调用，针对性强
优点：提供sql语句的重用性，共享性，可移植性，执行速度快
缺点：只能返回一个值，功能复杂，写起来更复杂，查看起来也困难

语法：

create function 函数名([参数列表]) returns 数据类型      #注意returns 要加s
begin
 sql语句;    #也可以写逻辑语句
 return 值;
end;

直接演示带参函数

mysql> delimiter $
mysql> create function stu_fun(name varchar(20)) returns int
    -> begin
    -> declare id int;      #定义变量，局部变量
    -> select id from stu where name=name into id;   #可以说用select into赋值变量，或者set c=1,或者set @c=1 一般set不用做select查询赋值设
    -> return id;
    -> end;
    -> $

函数调用

mysql> select stu_fun('lyf');
+----------------+
| stu_fun('lyf') |
+----------------+
|              1 |
+----------------+
mysql> select * from stu where id=stu_fun("lyf");
+----+------+------+------+
| id | name | age  | sex  |
+----+------+------+------+
|  1 | lyf  |   18 | 男   |
+----+------+------+------+

查看函数

mysql> show create function stu_fun(函数名);

查看所有函数

mysql> show function status;  #这里会显示所有函数包含系统的一般配合like做模糊查询

删除函数

mysql> drop function stu_fun(函数名);

注意:函数只返回一个值，如果查询到多条值赋给id就会报错
ERROR 1172 (42000): Result consisted of more than one row

存储过程

存储过程：一系列sql语句的集合，与函数不同的是他没有返回值。可以作为一个独立的部分来执行，他比函数更强大，他可以修改一系列表。能批量操作
优点：可重用性，对开发者透明，减少sql冗余，也可以安全封装，只显示应该显示的
缺点：开发、维护、调试是困难，复杂业务难以构造，过多的存储过程会加大内存

语法;

create procedure 存储过程名([参数列表])     #参数中in表示输入、out表示输出、inout表示可以输入可以输出
begin 
    sql语句或者逻辑语句
end;

例子,一次性举例三种参数传递

mysql> create procedure stu_proce(in name varchar(20),out ages int,inout sex varchar(2))
    -> begin
    -> select * from stu where name=name and sex=sex;
    -> select age into ages from stu where name=name and sex=sex;
    -> end;
    -> $
Query OK, 0 rows affected (0.00 sec)

使用存储过程并设置参数：

mysql> delimiter ;
mysql> set @sex='男';      #一个@表示用户变量，两个@表示系统会话变量(最后会说明)，inout需要先设置值之后在传参
Query OK, 0 rows affected (0.00 sec)

#out输出变量也需要用用户变量传出来
mysql> call stu_proce('lyf',@ages,@sex);
+----+------+------+------+
| id | name | age  | sex  |
+----+------+------+------+
|  1 | lyf  |   18 | 男   |
+----+------+------+------+
1 row in set (0.00 sec)
Query OK, 1 row affected (0.01 sec)
#这里输出了那条select查询语句


mysql> select @ages,@sex;
+------+------+
| @ages| @sex |
+------+------+
|  18  | 男   |
+------+------+
1 row in set (0.00 sec)     
#注意这里要用ages不能age是因为如果用age就会同名，输出结果会是Null，
#多个赋值可以用select age,name into ages,names from stu where id=1;

查看存储过程

mysql> show create procedure stu_proce(存储过程名);

查看所有存储过程

mysql> show procedure status;  #这里会显示所有函数包含系统的一般配合like做模糊查询

删除存储过程

mysql> drop procedure stu_proce;

对于in、out、inout作用范围：

mysql> delimiter $
mysql> create procedure proce(in p_in int,out p_out int,inout p_inout int)
    -> begin
    -> select p_in,p_out,p_inout;
    -> set p_in=2,p_out=2,p_inout=2;
    -> select p_in,p_out,p_inout;
    -> end;
    -> $
Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;
mysql> set @p_in=1,@p_out=1,@p_inout=1;
Query OK, 0 rows affected (0.00 sec)

mysql> call proce(@p_in,@p_out,@p_inout);
+------+-------+---------+
| p_in | p_out | p_inout |
+------+-------+---------+
|    1 |  NULL |       1 |
+------+-------+---------+
1 row in set (0.00 sec)

+------+-------+---------+
| p_in | p_out | p_inout |
+------+-------+---------+
|    2 |     2 |       2 |
+------+-------+---------+
1 row in set (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

mysql> select @p_in,@p_out,@p_inout;
+-------+-------+----------+
| @p_in | @p_out| @p_inout |
+-------+-------+----------+
|     1 |     2 |        2 |
+-------+-------+----------+
1 row in set (0.00 sec)

p_in 在存储过程中被修改，但并不影响 @p_id 的值
p_out为null因为out是向调用者输出参数，不接收输入的参数。调用后，输出参数，改变了p_out变量的值
p_inout,在调用了存储过程后，接受了输入的参数，也输出参数，改变了变量
这一部分开始我也不懂，我看了下w3的文档然后实践操作了一下

mysql中的变量定义

    用户自定义变量：
            declare id int default 局部变量定义在begin ... end中
            set @id=1  用户变量  只要写上@id(写id不算)他在这个窗口都有效，都可以使用（包括在函数、触发器中）
    系统变量：用于改变系统参数的
            set @@id=1 会话变量             #这个修改只会改变这个窗口，其他窗口会改变
            set globe @@id=1 全局变量   #这个则只改变这次服务的开启，重启服务后又会重置
            永久改变系统参数配置，需要修改配置文件
   查询全局变量：show global variables;
   查询会话变量：show session variables;或show variables；