MYSQL中的储存过程和存储函数

目录

MySQL中的存储过程

 存储过程的基本语法

1. 创建存储过程

2. 调用存储过程

3.修改存储过程

4. 删除存储过程

存储过程的优点

存储过程的缺点

总结

MySQL中的存储函数

函数的基本语法

存储函数的特点

存储函数的优点

存储函数的缺点

存储过程体

(1)局部变量：declare var_name[，...] type [default value]

(2)SET语句：为变量赋值：set var_name = expr

(3)select...into语句:把选定列的值直接存储到局部变量，只能返回一行数据

(4)流程控制语句

(5)游标：

---

MySQL中的存储过程

（Stored Procedure）是一组预编译的SQL语句集，存储在数据库中，可以通过调用来执行。存储过程可以接受参数、执行复杂的逻辑操作，并返回结果。它们通常用于封装常用的业务逻辑，提高代码的复用性和执行效率。

 存储过程的基本语法

1. 创建存储过程

DELIMITER //

CREATE PROCEDURE procedure_name ([IN | OUT | INOUT] parameter_name parameter_type, ...)
[characteristic...]
BEGIN
    -- SQL语句
END //

DELIMITER ;

- DELIMITER ：改变默认的语句结束符，以便在存储过程中使用分号 `;`。
- CREATE PROCEDURE：创建存储过程。
- procedure_name：存储过程的名称。
- parameter_name：参数名称。
- parameter_type：参数的数据类型。
- IN：输入参数（默认）。
- OUT：输出参数。
- INOUT：既是输入参数也是输出参数。
- BEGIN ... END：存储过程的主体部分，包含要执行的SQL语句。

-characteristic的格式：1.comment 'comment'

                                    2. language SQL :指定语言

                                    3.{contains SQL|NO SQL|Reads SQL data|modifies SQL data}

                                    读写|不包括SQL|只读|只写

                                   4.{SQL security {definer |invoker}  {使用者许可|创建者许可}

2. 调用存储过程

CALL procedure_name([parameter_value, ...]);

3.修改存储过程

alter procedure sp_name[characteristic]

4. 删除存储过程

DROP PROCEDURE IF EXISTS procedure_name;

示例

示例 1：简单的存储过程

DELIMITER //

CREATE PROCEDURE GetEmployeeCount()
BEGIN
    SELECT COUNT(*) AS EmployeeCount FROM employees;
END //

DELIMITER ;

调用存储过程：

CALL GetEmployeeCount();

示例 2：带输入参数的存储过程

DELIMITER //

CREATE PROCEDURE GetEmployeeByDepartment(IN dept_name VARCHAR(50))
BEGIN
    SELECT * FROM employees WHERE department = dept_name;
END //

DELIMITER ;

调用存储过程：

CALL GetEmployeeByDepartment('Sales');

示例 3：带输出参数的存储过程

DELIMITER //

CREATE PROCEDURE GetEmployeeCountByDepartment(IN dept_name VARCHAR(50), OUT emp_count INT)
BEGIN
    SELECT COUNT(*) INTO emp_count FROM employees WHERE department = dept_name;
END //

DELIMITER ;

调用存储过程：

CALL GetEmployeeCountByDepartment('Sales', @count);
SELECT @count AS EmployeeCount;

示例 4：带INOUT参数的存储过程

DELIMITER //

CREATE PROCEDURE IncrementCounter(INOUT counter INT, IN increment INT)
BEGIN
    SET counter = counter + increment;
END //

DELIMITER ;

调用存储过程：

SET @counter = 10;
CALL IncrementCounter(@counter, 5);
SELECT @counter;  -- 输出 15

存储过程的优点

1. 代码复用：存储过程可以在多个地方调用，减少代码重复。
2. 性能优化：存储过程在首次执行时会被编译和优化，后续调用时直接执行编译后的代码，提高执行效率。
3. 安全性：可以通过存储过程控制对数据的访问权限，避免直接暴露表结构。
4. 简化复杂操作：可以将复杂的业务逻辑封装在存储过程中，简化应用程序代码。

存储过程的缺点

1. 调试困难：存储过程的调试通常比应用程序代码困难。
2. 移植性差：存储过程通常与特定的数据库系统绑定，迁移到其他数据库系统时可能需要重写。
3. 版本控制：存储过程的版本控制不如应用程序代码方便。

总结

存储过程是MySQL中非常强大的功能，适合用于封装复杂的业务逻辑、提高性能和安全性。然而，在使用存储过程时也需要注意其缺点，合理权衡使用场景。

MySQL中的存储函数

（Stored Function）与存储过程类似，但它是一个可以返回单个值的子程序。存储函数通常用于封装计算逻辑，并在SQL语句中像内置函数一样调用。存储函数可以接受参数并返回一个标量值（如整数、字符串、日期等）。

---

函数的基本语法

1. 创建存储函数

DELIMITER //

CREATE FUNCTION function_name ([parameter_name parameter_type, ...])
RETURNS return_type
[DETERMINISTIC | NOT DETERMINISTIC]
[SQL DATA ACCESS {CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA}]
BEGIN
    -- 函数逻辑
    RETURN value;
END //

DELIMITER ;

- `function_name`：函数的名称。
- `parameter_name`：参数名称。
- `parameter_type`：参数的数据类型。
- `RETURNS return_type`：指定函数返回值的数据类型。
- `DETERMINISTIC`：表示函数是否是确定性的（即相同的输入是否总是返回相同的输出）。
- `SQL DATA ACCESS`：指定函数对数据的访问方式（如是否读取或修改数据）。
- `BEGIN ... END`：函数的主体部分，包含要执行的逻辑。
- `RETURN value`：函数返回的值。

例：

delimiter $$
create function fu_search(cid int)
return char(20)
deterministic
begin
    declare sex char(2)
    select cust_sex into sex from customers where cust_id=cid;
if sex is null then
    return(select'没有该用户')
else if sex='F' then
    return(select '女')
else
    return(select'男')
end if;
end $$

2. 调用存储函数

存储函数可以像内置函数一样在SQL语句中调用：

SELECT function_name(parameter_value, ...);

3. 删除存储函数

DROP FUNCTION IF EXISTS function_name;

4.查看存储函数：show function status;或者show create function sp_name;

---

存储函数的特点

1. 返回值：存储函数必须返回一个值。
2. 调用方式：存储函数可以在SQL语句中直接调用（如 `SELECT`、`WHERE` 等）。
3. 参数：存储函数可以接受输入参数，但不能有输出参数（`OUT`）或输入输出参数（`INOUT`）。
4. 确定性：如果函数是确定性的（`DETERMINISTIC`），MySQL可以对其进行优化。

---

示例

示例 1：简单的存储函数

创建一个函数，计算两个数的和：

DELIMITER //

CREATE FUNCTION AddNumbers(a INT, b INT)
RETURNS INT
DETERMINISTIC
BEGIN
    RETURN a + b;
END //

DELIMITER ;

调用函数：

SELECT AddNumbers(10, 20);  -- 输出 30

---

特性	存储函数 (Stored Function)	存储过程 (Stored Procedure)
返回值	必须返回一个值	可以没有返回值，或通过 `OUT` 参数返回
调用方式	在SQL语句中直接调用（如 `SELECT`）	使用 `CALL` 语句调用
参数	只有输入参数	可以有输入、输出、输入输出参数
用途	封装计算逻辑，返回单个值	封装复杂业务逻辑，执行多个操作
返回值类型	标量值（如整数、字符串等）	可以返回结果集或通过参数返回值

存储函数的优点

1. 代码复用：将常用逻辑封装为函数，减少重复代码。
2. 简化SQL：在SQL语句中直接调用函数，使查询更简洁。
3. 提高性能：函数在首次执行时会被编译和优化，后续调用时直接执行编译后的代码。

---

存储函数的缺点

1. 调试困难：函数的调试通常比应用程序代码困难。
2. 移植性差：函数通常与特定的数据库系统绑定，迁移到其他数据库系统时可能需要重写。
3. 复杂性限制：函数通常用于简单的计算逻辑，复杂逻辑更适合使用存储过程。

---

总结

存储函数是MySQL中非常有用的工具，适合用于封装计算逻辑并在SQL语句中调用。它们可以提高代码的复用性和可读性，但在使用时需要注意其局限性和适用场景。

存储过程体

        存储过程体可以使用各种SQL语句与过程式语句的组合，来封装数据库应用中复杂的业务逻辑和处理规则。

(1)局部变量：declare var_name[，...] type [default value]

局部变量只能在存储过程体的Begin和End语句中声明

局部变量必须在过程体开头处声明

局部变量的作用范围为Begin...End语句块

局部变量不同于用户变量，不能用@

(2)SET语句：为变量赋值：set var_name = expr

(3)select...into语句:把选定列的值直接存储到局部变量，只能返回一行数据

select col_name [,...] into var_name[,...] table_expr

(4)流程控制语句

a条件控制语句 b循环语句

a：if-then-else：

IF search_condition THEN statement_list
[ELSEIF...]
[ELSE...]
END IF

CASE语句：

case case_value
    when when_value then statement_list
    [...]
    [ELSE statement_list]
END CASE
//或者
case search_condition then statement_list

b循环语句：

1.while语句

[begin_label:] while search_condition do
statement_list
end while[end_label]

2.repeat语句：

[begin_label:] repeat
statement_list
until search_condition
end repeat[end_label]

3.loop语句:

[begin_label:]loop

statement_list

end loop[end_label]

(5)游标：

使用select语句查询数据时会返回一组称为结果集的数据·行，该结果集可能拥有多行数据，这些数据无法直接一行一行的进行处理

1.声明游标·：declare cursor_name cursor for select_statement

2.打开：        open cursor_name

3,读取            fetch cursor_name into var_name[,var_name]...

4,关闭            close cursor_name