mysql 过程和函数_MySQL：存储过程和函数

变量

系统变量

变量由系统提供，不是用户自定义的，属于服务器层面

全局变量

会话变量

# 如果是全局级别，则需要加global，如果是会话级别，则需要加session，如果不写，则默认是会话

# 查看全局变量

SHOW GLOBAL VARIABLES;

SHOW GLOBAL VARIABLESLIKE "%char%";

# 查看会话变量

SHOW SESSION VARIABLES; # 默认是会话变量

SHOW SESSION VARIABLESLIKE "%char%";

# 查看指定的某个系统变量的值SELECT @@系统变量名； # 默认查看的是会话变量SELECT @@GLOBAL.系统变量名；

# 为系统变量赋值SET global|[session] 系统变量名=值; # 默认是会话变量SET @@global|[session].系统变量名=值; # 默认是会话变量

自定义变量

用户自定义的变量

用户变量：针对于当前会话连接有效，同于会话变量的作用域

# 申明并初始化SET @用户变量名=值；SET @用户变量名：=值；SELECT @用户变量名：=值；

# 赋值或更新用户变量的值SET @用户变量名=值；SET @用户变量名：=值；SELECT @用户变量名：=值；SELECT 字段 INTO 变量名 FROM表SELECT COUNT(*) INTO @count1 FROMstudent;

# 查看用户变量名SELECT @用户变量名；

局部变量：仅仅在begin end中有效，应用在begin end的第一句话

# 声明DECLARE变量名 类型；DECLARE 变量名 类型 DEFAULT值；

# 赋值SET 局部变量名=值SET 局部变量名：=值SELECT @局部变量名：=值SELECT 字段 INTO @局部变量名 FROM表；

#查看SELECT 局部变量名

局部变量和用户变量

用户变量：  当前会话               会话的任何地方定义和使用　                　必须加@符号，不用限定类型

局部变量：  BEGIN  END中     只能在BEGIN END中，且为第一句话       一般不加@符号，需要限定类型

存储过程

介绍

一组预先编译好的SQL语句的集合，理解成批处理语句

提高代码的重用性

简化操作

减少了编译次数，并且减少了和数据库服务器的连接次数，提高了效率

创建

CREATE PROCEDURE存储过程名(参数列表)BEGIN存储过程体(一组合法的SQL语句)END

注意：

参数列表包含三个部分 参数模式  参数名  参数类型

如果存储过程体仅仅只有一条语句，BEGIN END可以省略

存储过程体中的每条SQL语句的结尾必须要加分号

存储过程的结尾可以使用DELIMITER重新设置

参数模式：

IN： 该参数可以作为输入，也就是该参数需要调用方法传入值

OUT： 该参数可以作为输出，也就是该参数可以作为返回值

INOUT： 既可以作为输入也可以作为输出，既要传入值，也可以返回值

调用

CALL 存储过程名(实参列表)；

案例

/*1. 空参列表

案例1： 插入到admin表中五条记录

DELIMITER $

CREATE PROCEDURE myp1()

BEGIN

INSERT INTO admin(name, `password`)

VALUES('jhon1', "000"),('jhon2',"001");

END $

CALL myp1()$

SELECT * FROM admin$

2. IN模式参数, 默认是IN

案例： 创建存储过程实现 根据girl name 查询对应的boy信息

CREATE PROCEDURE myp2(IN g_name VARCHAR(20) )

BEGIN

SELECT b.* FROM boys b RIGHT JOIN girls g ON b.id=g.boy_id WHERE g.name=g_name;

END $

CALL myp2('Mary')$

案例： 创建存储过程实现，用户是否登入成功

CREATE PROCEDURE myp3(IN name VARCHAR(20), in PASSWORD VARCHAR(20))

BEGIN

DECLARE res INT DEFAULT '';

select count(*) into res from admin where admin.name=name and admin.password=PASSWORD;

SELECT IF(res>0,'successfule','failed');

END $

CALL myp3('root','123')$

3. OUT模式的存储过程

案例： 根据girl name ，返回boy name

CREATE PROCEDURE myp5(IN girl_nam VARCHAR(20), OUT boy_name VARCHAR(20), OUT age INT)

BEGIN

SELECT b.name,b.age INTO boy_name,age FROM boys b INNER JOIN girls g ON b.id=g.boy_id WHERE g.name=girl_name;

END $

CALL myp5('Mary', @bname, @age)$

SELECT @banme,@age$

4. INOUT 模式参数

案例： 传入a和b两个值。最终a和b都翻倍并返回

CREATE PROCEDURE myp6(INOUT a INT, INOUT b INT)

BEGIN

SET a=a*2;

SET b=b*2;

END $

SET @a=20$

SET @b=20$

CALL myp6(@a,@b)$

SELECT @a,@b$

*

删除存储过程

DROP PROCEDURE 存储过程名

查看存储过程

SHOW CREATE PROCEDURE 存储过程名

函数

介绍

一组预先编译好的SQL语句的集合，理解成批处理语句

提高代码的重用性

简化操作

减少了编译次数，并且减少了和数据库服务器的连接次数，提高了效率

对比存储

存储过程： 可以有0或多个返回， 适合做批量插入、批量更新

函数       ：  有且仅有一个返回，适合做处理数据后返回一个结果

创建

CREATE FUNCTION函数名(参数列表) RETURNS返回类型BEGIN函数体END

注意：

参数列表包含两部分： 参数名  参数类型

函数体必须有return语句：  return 值

函数体仅有一句话时，可以省略begin end

使用delimiter语句设置结束标记

调用

SELECT 函数名(参数列表)

案例

/*1. 无参数返回

案例： 返回员工个数

DELIMITER $

CREATE FUNCTION myf1 RETURNS INT

BEGIN

DECLARE c INT DEFAULT 0;

SELECT COUNT(*) INTO c FROM employees;

RETURN c;

END $

SELECT myf1()$

2. 有参数返回

案例： 根据员工名，返回他的工资

CREATE FUNCTION(name VARCHAR(20)) RETURNS DOUBLE

BEGIN

SET @sal=0;

SELECT salary INTO @sal FROM employees e WHERE e.name =name;

RETURN @sal;

END

SELECT myf2('jhon')$*/

查看函数

SHOW CREATE FUNCTION 函数名

删除函数

DROP FUNCTION 函数名；