MySQL：存储过程和函数

变量

系统变量

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

• • 全局变量　　
  • 会话变量

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

# 查看全局变量
SHOW GLOBAL VARIABLES;
SHOW GLOBAL VARIABLES LIKE "%char%";

# 查看会话变量
SHOW SESSION VARIABLES;  # 默认是会话变量
SHOW SESSION VARIABLES LIKE "%char%";

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

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

自定义变量

　　用户自定义的变量

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

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

# 赋值或更新用户变量的值
SET @用户变量名=值；
SET @用户变量名：=值；
SELECT @用户变量名：=值；

SELECT 字段 INTO 变量名 FROM 表
SELECT COUNT(*) INTO @count1 FROM  student;

# 查看用户变量名
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 函数名；

---