【SQL】存储过程（Stored Procedure）

今天给大家介绍下数据库中的存储过程（Stored Procedure）。

首先，什么是存储过程？

存储过程是一组预先定义并编译好的 SQL 语句的集合，存储在数据库中，可以通过指定存储过程的名字并给出参数（如果有的话）来调用执行。存储过程可以包含复杂的逻辑控制语句，如条件判断（IF）、循环（WHILE）等，能够处理复杂的业务逻辑。一句话来说就是封装 SQL 语句，以便后续使用。

不同的数据库在存储过程的语法上有点差异，SQL Server中存储过程使用的频率还是挺高的，像我目前的工作就是天天和存储过程打交道，比如在后端程序中将需要的参数传入，在代码中执行后就会立即获得数据库返回的结果。

我分别用MySQL和SQL Server演示一个实例：在用户登录的时候，需要把用户信息存储下来，但是我们先得判断该用户的信息是否已经存在User表中，如果不存在，应该insert，如果已经存在，那么应该执行update。我们就可以把这个逻辑整合一下写在存储过程里。

如何创建储存过程：

 在SQL Server中，在界面上通过点击可编程性---存储过程---新建---存储过程来让系统自动生成一个存储过程的模版，然后基于这个模版来写SQL语句：

从模版中我们也可以看出创建存储过程的语法如下：

CREATE PROCEDURE 存储过程名称
    [参数列表]
AS
BEGIN
    -- SQL语句
END

其中“存储过程名称”就是你给存储过程起的名字，后面要使用的时候直接执行它就可以了。

• “参数列表”：参数列表是可选的，用于向存储过程中传递数据。参数可以有输入参数和输出参数。
• “SQL语句”：是存储过程的核心部分，包含了要执行的 SQL 语句。

例如，创建一个名为 spSetUserInfo 的存储过程，用于记录用户的登录信息：

CREATE PROCEDURE spSetUserInfo
    @ID VARCHAR(50),
    @Nickname VARCHAR(20)
AS
BEGIN
    -- 先判断该用户ID是否存在，
    -- 不存在则Insert新增数据, 已存在则Update
    IF NOT EXISTS (SELECT 1 FROM dbo.Users WHERE Id = @ID)
    BEGIN
        INSERT INTO dbo.Users
        (
            Id,
            Nickname,
            RegisterTime,
            RecentLoginTime
        )
        VALUES
        (@ID, @Nickname, GETDATE(), GETDATE());
    END;
    ELSE
        UPDATE dbo.Users SET RecentLoginTime = GETDATE() WHERE Id = @ID;
END;

执行上面的代码后数据库中就创建了一个spSetUserInfo的存储过程，存储过程最好以spXX命名，这样别人一看就知道这是一个存储过程。存储过程创建好后也可以更改里面的代码，在图形界面上右击该存储过程---修改

修改存储过程的关键字是Alter，改完后点击执行即会应用该更改（注意不是点保存）。

后面要调用这个存储过程的话，用到的关键词是EXECUTE或者EXEC，例如：

EXECUTE dbo.spSetUserInfo @ID = '123abcd',    
                          @Nickname = '自由行'

填入对应的参数后执行，我们再查看User表中就会发现，该数据被成功添加到表中（因为不存在该ID，则新增）。如果我们再执行一次的话（已经存在，则update），就会发现RecentLoginTime被更新。

如果你想删除该存储过程的话，可以在界面上右击该存储过程，找到删除选项，也可以执行drop语句删除：

DROP PROCEDURE dbo.spSetUserInfo

上面只是一个简单的示例，你可以用存储过程做很多事，在里面写复杂的SQL。比如你在一家制造业公司，需要查看每个环节物料的消耗和产出情况，你就可以将每个环节对应取数据的SQL写在一个存储过程中，后面每天只要执行这个存储过程，传入日期就可以得到当天各环节物料的消耗和产出情况。

再看下MySQL中存储过程的相关语法，有细微的差别。比如创建存储过程的语法是：

DELIMITER //
CREATE PROCEDURE 存储过程名称 (参数列表)
BEGIN
    -- 存储过程的 SQL 语句
END //
DELIMITER ;

 

DELIMITER //
CREATE PROCEDURE spSetUserInfo(IN ID VARCHAR(50), IN Nickname VARCHAR(20))
BEGIN
    -- 检查用户是否存在
    IF NOT EXISTS (SELECT 1 FROM Users WHERE Id = ID) THEN
        -- 如果用户不存在，插入新用户
        INSERT INTO Users (Id, Nickname, RegisterTime, RecentLoginTime)
        VALUES (ID, Nickname, NOW(), NOW());
    ELSE
        -- 如果用户存在，更新最近登录时间
        UPDATE Users 
        SET RecentLoginTime = NOW() 
        WHERE Id = ID;
    END IF;
END //
DELIMITER ;

 

解释一下：

1. DELIMITER

• 存储过程可能包含多个 SQL 语句，而 MySQL 默认的语句分隔符是分号（;）。为了区分存储过程内部的分号和存储过程定义的结束，需要使用 DELIMITER 关键字来改变分隔符。

• 例如，将分隔符改为 //，这样存储过程定义的结束就用 // 表示，而存储过程内部的 SQL 语句仍然可以使用分号。

2. CREATE PROCEDURE

• 用于定义存储过程的名称。存储过程名称必须是唯一的，不能与数据库中已有的存储过程名称重复。

3. 参数列表

• 参数列表是可选的，用于传递输入值或返回输出值。

• 参数的格式为：[IN | OUT | INOUT] 参数名 数据类型。

  • IN：表示输入参数，用于将值传递给存储过程。

  • OUT：表示输出参数，用于存储过程执行后返回值。

  • INOUT：表示既可以作为输入参数，也可以作为输出参数。

• 执行则用到的关键字是CALL，例如：

CALL spSetUserInfo('123abcd' ,'自由行')

存储过程是数据库中非常重要的编程工具，适用于复杂的业务逻辑处理，可以实现增删改查的需要，可以返回多个值或结果集，能大大提高工作的效率和代码的可维护性。下篇会介绍函数，它和存储过程有很多相似的地方。

希望这篇文章能帮助你了解和使用存储过程。如果你在学习或实际工作中遇到相关问题，欢迎随时交流探讨！

---

打个广告，哈哈哈，求关注我的公众号：数据码头。里面全是知识干货和经验分享。