开发: C++知识库 Java知识库 JavaScript Python PHP知识库人工智能区块链大数据移动开发嵌入式开发工具数据结构与算法开发测试游戏开发网络协议系统运维
教程: HTML教程 CSS教程 JavaScript教程 Go语言教程 JQuery教程 VUE教程 VUE3教程 Bootstrap教程 SQL数据库教程 C语言教程 C++教程 Java教程 Python教程 Python3教程 C#教程
数码: 电脑笔记本显卡显示器固态硬盘硬盘耳机手机 iphone vivo oppo 小米华为单反装机图拉丁

-> 大数据 -> MySQL存储过程+函数 -> 正文阅读

[大数据]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 函数名(参数列表)；

案例演示

无参有返回

返回公司的员工个数

#定义
CREATE FUNCTION countEmp() RETURNS INT
BEGIN
    DECLARE result INT;
    SELECT COUNT(*) INTO result
    FROM employees;
    RETURN result;
END;
#调用
SELECT countEmp();

有参有返回

根据员工名返回工资

#定义
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 函数名;
```

流程控制结构

分支结构

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

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);