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)