14.1.1 创建存储过程-MySQL从入门到精通(四)

当前位置:　首页>> 技术小册>> MySQL从入门到精通(四)

### 14.1.1 创建存储过程

在MySQL数据库中，存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集，它经编译后存储在数据库中，用户通过指定存储过程的名字并给定参数（如果有）来调用执行它。存储过程的使用可以极大地提高数据库操作的灵活性和效率，特别是在处理复杂逻辑和大量数据操作时。本章将详细介绍如何在MySQL中创建存储过程，包括基本语法、参数使用、流程控制语句以及存储过程的调用与管理。

#### 14.1.1.1 存储过程的基本语法

MySQL中创建存储过程的基本语法如下：

```sql
CREATE PROCEDURE procedure_name ([proc_parameter[,...]])
[characteristic ...] routine_body
```

- **procedure_name**：存储过程的名称，在数据库中必须唯一。
- **proc_parameter**：存储过程的参数列表，格式为`[IN | OUT | INOUT] param_name type`。其中，`IN`表示输入参数，`OUT`表示输出参数，`INOUT`表示既可以输入也可以输出的参数。`param_name`是参数名，`type`是参数类型。
- **characteristic**：存储过程的特性，如`LANGUAGE SQL`（指定存储过程的语言为SQL）、`DETERMINISTIC`（表示存储过程对同样的输入总是产生相同的结果）、`NOT DETERMINISTIC`（表示存储过程的结果可能因其他因素而变化）等。
- **routine_body**：存储过程的主体，包含了一系列的SQL语句，用于实现特定的功能。

#### 14.1.1.2 创建简单的存储过程

下面是一个简单的存储过程示例，该过程不接受任何参数，用于查询并返回`employees`表中所有员工的名字和部门ID：

```sql
DELIMITER //

CREATE PROCEDURE GetAllEmployees()
BEGIN
    SELECT name, department_id FROM employees;
END //

DELIMITER ;
```

注意，在创建存储过程之前，我们使用`DELIMITER //`更改了命令的结束符，以便在存储过程内部可以使用分号（`;`）作为SQL语句的结束符，而不会导致整个`CREATE PROCEDURE`语句提前结束。存储过程定义完成后，我们再通过`DELIMITER ;`将命令结束符改回分号。

#### 14.1.1.3 带参数的存储过程

接下来，我们创建一个带有输入参数的存储过程，用于查询指定部门ID下的所有员工信息：

```sql
DELIMITER //

CREATE PROCEDURE GetEmployeesByDepartment(IN dept_id INT)
BEGIN
    SELECT name, position, salary FROM employees WHERE department_id = dept_id;
END //

DELIMITER ;
```

在这个例子中，`dept_id`是一个输入参数，用于指定要查询的部门ID。

#### 14.1.1.4 使用输出参数的存储过程

输出参数允许存储过程将值返回给调用者。以下是一个示例，该存储过程计算并返回指定部门中员工的平均工资：

```sql
DELIMITER //

CREATE PROCEDURE GetAverageSalaryByDepartment(IN dept_id INT, OUT avg_salary DECIMAL(10,2))
BEGIN
    SELECT AVG(salary) INTO avg_salary FROM employees WHERE department_id = dept_id;
END //

DELIMITER ;
```

调用此存储过程并获取结果的方式略有不同，因为它需要处理输出参数：

```sql
CALL GetAverageSalaryByDepartment(5, @salary);
SELECT @salary;
```

这里，`@salary`是一个用户定义的变量，用于接收存储过程的输出参数`avg_salary`的值。

#### 14.1.1.5 流程控制语句

存储过程中可以使用多种流程控制语句，如IF、CASE、LOOP、REPEAT、WHILE等，以实现复杂的逻辑处理。以下是一个使用IF语句的存储过程示例，它根据员工的薪资水平给出薪资评价：

```sql
DELIMITER //

CREATE PROCEDURE EvaluateSalary(IN emp_salary DECIMAL(10,2), OUT salary_grade VARCHAR(50))
BEGIN
    IF emp_salary < 3000 THEN
        SET salary_grade = 'Low';
    ELSEIF emp_salary BETWEEN 3000 AND 7000 THEN
        SET salary_grade = 'Medium';
    ELSE
        SET salary_grade = 'High';
    END IF;
END //

DELIMITER ;
```

#### 14.1.1.6 存储过程的调用与管理

存储过程创建后，可以通过`CALL`语句进行调用。除了之前示例中的调用方式外，还可以传递变量作为参数。此外，MySQL还提供了多种管理和维护存储过程的命令，如`SHOW PROCEDURE STATUS`用于查看存储过程的状态信息，`DROP PROCEDURE`用于删除存储过程等。

#### 总结

通过本章的学习，您应该已经掌握了MySQL中存储过程的基本创建方法，包括如何定义参数、编写存储过程体、使用流程控制语句以及存储过程的调用与管理。存储过程是数据库编程中非常重要的一个概念，它能够提高数据库操作的效率，简化复杂逻辑的处理。在实际应用中，合理设计和使用存储过程将大大提高数据库应用的性能和可维护性。希望本章的内容能为您的MySQL学习和应用之路提供帮助。

该分类下的相关小册推荐：

MySQL8.0入门与实践

MySQL从入门到精通(三)

MySQL必会核心问题

MySQL 实战 45 讲

细说MySQL(零基础到高级应用)

MySQL从入门到精通(一)

MySQL从入门到精通(五)

MySQL从入门到精通(二)

SQL零基础到熟练应用(增删改查)