2023.6.15 07.数据库存储过程

07.数据库存储过程

存储过程

MySQL 存储过程是⼀组 预编译的 SQL 语句 ，可以在 MySQL 数据库中 定义和存储 ，并在 需要时执⾏ 。存储过程可

以接受参数、执⾏条件判断、循环、异常处理等操作，使得开发⼈员可以把⼀系列操作组合成⼀个可重复使⽤的单

元，从⽽提⾼代码的复⽤性和可维护性。

存储过程可以在 MySQL 数据库中创建和保存，然后在需要时调⽤。存储过程通常⽤于执⾏复杂的数据操作，例如

数据转换、数据清理、数据分析等。存储过程的执⾏速度通常⽐单个 SQL 语句的执⾏速度更快，因为存储过程是

预编译的，并且可以在多个客户端之间共享。

MySQL 存储过程的语法类似于其他编程语⾔，包括变量定义、条件语句、循环语句、异常处理等。存储过程可以

接受输⼊参数和输出参数，以及返回值。存储过程还可以使⽤游标来处理结果集，并且可以调⽤其他存储过程或函

数。

MySQL 存储过程是⼀种⾮常强⼤的⼯具，可以帮助开发⼈员提⾼代码的复⽤性和可维护性，同时还可以提⾼

数据库的性能。

1、存储过程

存储过程保存在mysql.proc表中

2、创建存储过程：

CREATE PROCEDURE sp_name ([ proc_parameter [,proc_parameter ...]])

其中:proc_parameter : [IN|OUT|INOUT] parameter_name type

其中IN表示输⼊参数，OUT表示输出参数，INOUT表示既可以输⼊也可以输出；param_name表示参数名称；type表示

参数的类型

3、查看存储过程列表：

SHOW PROCEDURE STATUS\G

4、查看存储过程定义

SHOW CREATE PROCEDURE sp_name

mysql> SHOW CREATE PROCEDURE get_customer\G

*************************** 1. row ***************************

Procedure: get_customer

sql_mode:

ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_

BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `get_customer`(IN

customer_id INT)

BEGIN

SELECT * FROM customers WHERE id = customer_id;

END

character_set_client: utf8

collation_connection: utf8_general_ci

Database Collation: latin1_swedish_ci

1 row in set (0.00 sec)

sql_mode 是 MySQL 中的⼀个参数，⽤于控制 MySQL 在执⾏ SQL 语句时的⾏为，包括数据类型的严格

性、⽇期的处理⽅式、空值的处理⽅式、SQL 语句的语法等等。

 

5、调⽤存储过程

CALL sp_name ([ proc_parameter [,proc_parameter ...]])

CALL sp_name

说明:当⽆参时,可以省略"()",当有参数时,不可省略"()"

 

6、存储过程修改

ALTER语句修改存储过程只能修改存储过程的注释等⽆关紧要的东⻄,不能修改存储过程体,所以要修改存储过程,⽅法

就是删除重建

 

7、删除存储过程

DROP PROCEDURE [IF EXISTS] sp_name

 

8、存储过程优势

存储过程把经常使⽤的SQL语句或业务逻辑封装起来,预编译保存在数据库中,当需要时从数据库中直接调⽤,省去了

编译的过程

提⾼了运⾏速度,同时降低⽹络数据传输量,存储过程相当于独⽴命令可以直接调⽤

1. 提⾼性能

2. 提⾼安全性

3. 提⾼可维护性

4. 提⾼可重⽤性

5. ⽀持事务处理 ACID

 

9、存储过程与⾃定义函数的区别

1. 返回值类型不同：⾃定义函数必须返回⼀个值，⽽存储过程可以不返回任何值。

2. 使⽤⽅式不同：⾃定义函数可以在SQL语句中使⽤，如SELECT语句中的函数调⽤，⽽存储过程需要通过CALL

语句来调⽤。

3. 事务处理能⼒不同：存储过程可以包含事务处理代码，⽽⾃定义函数不能。

4. 参数传递⽅式不同：⾃定义函数只能通过参数传递来接收输⼊值，并返回计算结果。⽽存储过程可以通过输

⼊、输出、输⼊输出三种参数类型来传递参数。

5. 可重⽤性不同：⾃定义函数可以在多个查询中使⽤，⽽存储过程需要单独调⽤。

 

10、存储过程示例：

10.1 创建储存过程⽆参数示例

1. 创建⼀个显示当前时间的储存过程⽆参数

mysql> delimiter //

mysql> CREATE PROCEDURE showTime()

-> BEGIN

-> SELECT now();

-> END//

Query OK, 0 rows affected (0.00 sec)

mysql> delimiter ;

mysql> CALL showTime;

+---------------------+

| now() |

+---------------------+

| 2023-05-25 15:17:44 |

+---------------------+

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

 

2. 查看存储过程：

mysql> show procedure status like 'showTime'\G

*************************** 1. row ***************************

Db: wing

Name: showTime

Type: PROCEDURE

Definer: root@localhost

Modified: 2023-05-25 15:17:07

Created: 2023-05-25 15:17:07

Security_type: DEFINER

Comment:

character_set_client: utf8

collation_connection: utf8_general_ci

Database Collation: latin1_swedish_ci

1 row in set (0.00 sec)

 

3. 跨数据库是否能执⾏：

mysql> create database testdb;

Query OK, 1 row affected (0.00 sec)

mysql> use testdb;

Database changed

mysql> call showTime();

ERROR 1305 (42000): PROCEDURE testdb.showTime does not exist

mysql> call wing.showTime();

+---------------------+

| now() |

+---------------------+

| 2023-05-25 15:20:12 |

+---------------------+

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

 

4. 创建含参存储过程：只有⼀个IN参数

mysql> delimiter ;

mysql> call selectById(2);

ERROR 1305 (42000): PROCEDURE testdb.selectById does not exist

mysql> call selectById(3);

ERROR 1305 (42000): PROCEDURE testdb.selectById does not exist

mysql> use wing;

Database changed

mysql> delimiter //

mysql> CREATE PROCEDURE selectById(IN uid SMALLINT UNSIGNED)

-> BEGIN

-> SELECT * FROM students WHERE stuid = uid;

-> END//

Query OK, 0 rows affected (0.00 sec)

10.2 创建储存过程有参数示例

1. 创建⼀个员⼯表

 

use wing;

CREATE TABLE employees (

id INT PRIMARY KEY,

name VARCHAR(50),

age INT,

department VARCHAR(50)

);

INSERT INTO employees VALUES (1, 'Wing', 25, 'Devops');

INSERT INTO employees VALUES (2, 'Jane Smith', 30, 'Marketing');

INSERT INTO employees VALUES (3, 'Bob Johnson', 35, 'Engineering');

mysql> select * from employees;

+----+-------------+------+-------------+

| id | name | age | department |

+----+-------------+------+-------------+

| 1 | Wing | 25 | Devops |

| 2 | Jane Smith | 30 | Marketing |

| 3 | Bob Johnson | 35 | Engineering |

+----+-------------+------+-------------+

3 rows in set (0.00 sec)

 

2. 创建储存过程

DELIMITER //

CREATE PROCEDURE `get_employee`(IN emp_id INT)

BEGIN

SELECT * FROM employees WHERE id = emp_id;

END //

DELIMITER ;

这个存储过程名为 get_employee ，它接受⼀个整数类型的参数 emp_id ，⽤于查找员⼯表中ID为 emp_id 的

员⼯记录。在存储过程中，使⽤ IN 关键字来指定参数类型。

 

3. 执⾏这个存储过程

mysql> CALL get_employee(1);

+----+------+------+------------+

| id | name | age | department |

+----+------+------+------------+

| 1 | Wing | 25 | Devops |

+----+------+------+------------+

1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

 

10.3计算的存储过程示例

# @i 变量

# repeat 重复

# @i = @i + 1 相当于shell脚本⾥⾯的i++

delimiter //

CREATE PROCEDURE dorepeat(n INT)

BEGIN

SET @i = 0;

SET @sum = 0;

REPEAT SET @sum = @sum+@i; SET @i = @i + 1;

UNTIL @i > n END REPEAT;

END//

delimiter ;

CALL dorepeat(100);

SELECT @sum;

存储过程名为 dorepeat ，它有⼀个参数 n ，并使⽤ REPEAT 循环计算从0到n的总和。最后，存储过程将结果存储

在MySQL⽤户变量 @sum 中。

使⽤ delimiter 命令来指定存储过程的结束符。这是必要的，因为存储过程中包含了多个分号。在存储过程结束

后，使⽤ delimiter 命令将结束符改回分号。

使⽤ CALL 语句调⽤存储过程，并使⽤ SELECT 语句来检索存储在 @sum 中的结果

 

mysql> SELECT @sum;

+------+

| @sum |

+------+

| 5050 |

+------+

1 row in set (0.00 sec)