阅读 68

MySQL存储过程+函数详解!

存储过程和函数

文章已同步至GitHub开源项目: Java超神之路

变量

  • 系统变量

    • 全局变量

    • 会话变量

      查看所有变量

    SHOW [GLOBAL/SESSION] VARIVALES
    复制代码

    ​ 条件查询

    SHOW [GLOBAL/SESSION] LIKE '%char%'
    复制代码

    查看某个变量的值

    SELECT @@[GLOBAL/SESSION].系统变量名
    复制代码

    设置值

    SET [GLOBAL/SESSION].系统变量名 = 值;
    复制代码
  • 自定义变量

    • 用户变量

      • 作用域: 针对于当前会话有效

      • 使用

        SET @用户变量名=值  #声明赋值1
        SELECT 字段 INTO @变量名 #声明赋值2
        SELECT @变量名 #查看
        复制代码
    • 局部变量

      • 作用域:当前begin / end作用域有效

      • 使用

        DECALARE 变量名 类型 ; #声明
        SET 变量名=值;#赋值
        SELECT 变量名; #查看
        复制代码

存储过程

  • 概念

    一组预先编译好的SQL语句集合。

  • 好处

    • 提高代码的通用性
    • 简化操作
    • 减少了编译次数和连接次数,提高效率
  • 语法

    • 创建

      CREATE PROCEDURE 存储过程名(参数列表)
      BEGIN
         SQL语句1;
         SQL语句2;
      END
      复制代码
      • 参数列表
        • 参数模式 IN,OUT,INOUT
        • 参数名
        • 参数类型
    • 使用

      CALL 存储过程名(实参列表);
      复制代码
  • 例子

    • 插入到book中五条记录

      #定义
      CREATE PROCEDURE INSERT5()
      BEGIN
          INSERT INTO book values (1,'计算机网络',45.9,'罗贯中',NOW());
          INSERT INTO book values (2,'计算机网络',45.9,'罗贯中',NOW());
          INSERT INTO book values (3,'计算机网络',45.9,'罗贯中',NOW());
          INSERT INTO book values (4,'计算机网络',45.9,'罗贯中',NOW());
          INSERT INTO book values (5,'计算机网络',45.9,'罗贯中',NOW());
      END;
      
      #调用
      CALL INSERT5();
      复制代码
  • 根据工资查询员工

    #根据工资查询员工
    CREATE PROCEDURE getBySalary(IN s double)
    BEGIN
        SELECT *
            FROM employees
                WHERE salary = s;
    END;
    
    #调用
    CALL getBySAlary();
    复制代码
  • 根据工资返回员工名

    #定义
    CREATE PROCEDURE getNameBySalary(IN salary double,OUT name VARCHAR(20))
    BEGIN
        SELECT last_name INTO name
            FROM employees
                WHERE employees.salary = salary;
    END;
    #调用
    SET @result;
    CALL getNameBySalary(24000,@result);
    SELECT @result;
    复制代码

函数

​ 有且仅有一个返回

  • 创建

    CREATE FUNCATION 函数名(参数名 参数类型,参数名 参数类型) RETURNS 返回类型
    BEGIN
     函数体
    END
    复制代码
  • 调用

    SELECT 函数名(参数列表);
    复制代码

    案例演示

    1. 无参有返回

      返回公司的员工个数

      #定义
      CREATE FUNCTION countEmp() RETURNS INT
      BEGIN
          DECLARE result INT;
          SELECT COUNT(*) INTO result
          FROM employees;
          RETURN result;
      END;
      #调用
      SELECT countEmp();
      复制代码
  1. 有参有返回

    根据员工名返回工资

    #定义
    CREATE FUNCTION getSalaryByName(name VARCHAR(20)) RETURNS DOUBLE
    BEGIN
        DECLARE salary DOUBLE;
        SELECT e.salary INTO salary
        FROM employees e
        WHERE e.last_name = name;
        RETURN salary;
    END;
    #调用
    SELECT getSalaryByName('K_ing');
    复制代码
  • 查看函数定义语句

    SHOW CREATE FUNCTION 函数名;
    复制代码

流程控制结构

分支结构

  1. IF(表达式 1,表达式2,表达式3) 如果表达式1成立,返回2,否则返回3

  2. CASE结构 如果ELSE省略 当都不匹配时 返回NULL

    #等值判断
    CASE 表达式//字段
    WHEN 常量1 THEN 语句1;
    WHEN 常量2 THEN 语句2;
    ELSE 默认语句;
    END;
    
    #区间判断
    CASE 
    WHEN 条件 THEN 语句;
    WHEN 条件 THEN 语句;
    ELSE 默认语句;
    END;
    复制代码

    案例: 创建函数,根据传入成绩,显示等级

    #定义
    CREATE FUNCTION wage_scale(score DOUBLE) RETURNS varchar(10)
    BEGIN
        CASE
            WHEN score>=90 THEN RETURN '优秀';
            WHEN score>=80 THEN RETURN '良好';
            WHEN score>=70 THEN RETURN '普通';
            WHEN score>=60 THEN RETURN '及格';
            ELSE RETURN '不及格';
            END CASE;
    END;
    #调用
    SELECT wage_scale(90);
    复制代码

循环结构

WHILE

  • 语法

    [标签名]WHILE 循环条件 DO
    循环体;
    END WHILE[标签名];
    复制代码

案例:根据次数批量插入admin表数据

#定义存储过程
CREATE PROCEDURE pro_while(IN count INT)
BEGIN
DECLARE i INT DEFAULT 1;
WHILE i <= count
  DO
        INSERT INTO admin (username, password) VALUES (count, count);
        SET i = i + 1;
  END WHILE;
end;

#调用
CALL pro_while(10);
#结果 admin表中插入10条数据
复制代码

LOOP 可以用来模拟简单的死循环

  • 语法

    [标签名]LOOP
    循环体;
    END LOOP [标签名]
    复制代码

REPEAT

  • 语法

    [标签名]REPEAT
    循环体;
    UNTIL 结束循环条件
    END REPEAT [标签名]
    
    复制代码

案例

已知表content

字段说明
id自增主键
content随机字符

创建存储过程,插入指定数量的随机字符。

#建表
CREATE TABLE content(
    id INT PRIMARY KEY AUTO_INCREMENT,
    content VARCHAR(100)
);

#创建存储过程
CREATE PROCEDURE random_content(IN count INT)
BEGIN
    #定义变量
    DECLARE i INT DEFAULT 1;
    WHILE i <= count DO
        #循环体
        INSERT INTO content VALUES (null,random_bytes(100));
        SET i = i+1;
        END WHILE;
END;
#d
CALL random_content(100);
复制代码

文章已同步至GitHub开源项目: Java超神之路 更多Java相关知识,欢迎访问!

文章分类
后端
文章标签