MySQL存储过程+函数

原创

程序员阿杜

修改于 2021-10-03 13:13:33

4.5K0

文章被收录于专栏：开源君开源君

存储过程和函数

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

变量

系统变量
全局变量
会话变量

查看所有变量

SHOW GLOBAL/SESSION VARIVALES

条件查询

SHOW GLOBAL/SESSION LIKE '%char%'

查看某个变量的值

SELECT @@GLOBAL/SESSION.系统变量名

设置值

SET GLOBAL/SESSION.系统变量名 = 值;

自定义变量
用户变量

- 作用域： 针对于当前会话有效

- 使用

  ```sql

  SET @用户变量名=值  #声明赋值1

  SELECT 字段 INTO @变量名 #声明赋值2

  SELECT @变量名 #查看

```

局部变量

- 作用域：当前begin / end作用域有效

- 使用

  ```mysql

  DECALARE 变量名 类型 ; #声明

  SET 变量名=值;#赋值

  SELECT 变量名; #查看

```

存储过程

概念

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

好处
提高代码的通用性
简化操作
减少了编译次数和连接次数，提高效率
语法
创建

```sql

CREATE PROCEDURE 存储过程名(参数列表)

BEGIN

	SQL语句1;

	SQL语句2;

END

```

- 参数列表
  - 参数模式 IN，OUT，INOUT
  - 参数名
  - 参数类型

使用

```sql

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

```

例子
插入到book中五条记录

```sql

#定义

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

案例演示

无参有返回

 返回公司的员工个数

 ```sql

 #定义

 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 语句;

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相关知识，欢迎访问！

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

数据库

原创声明：本文系作者授权腾讯云开发者社区发表，未经许可，不得转载。

如有侵权，请联系 cloudcommunity@tencent.com 删除。

数据库

登录后参与评论

0 条评论

热度