百度360必应搜狗淘宝本站头条
mqtt服务器xftp6mybatiscollectionkeyerrorc#var
当前位置：网站首页 > 热门文章 > 正文

存储过程:强大与便捷的完美融合（存储过程的作用和使用方法）

bigegpt 2025-03-25 11:14 7 浏览

对于刚刚接触AI领域，代码编程领域的人们来说，数据库是核心，那么数据库的核心又是什么呢？这就是今天给大家带来的内容：SQL Server之存储过程。

“存储过程”又名数据库引擎，用了它会有哪些好处，又会给我们带来什么方面的便捷，我们具体展开来讲解一下；

SQL Server 中的存储过程是由一个或多个 Transact-SQL 语句或对 Microsoft .NET Framework 公共语言运行时 (CLR) 方法的引用构成的一个组。 过程与其他编程语言中的构造相似，这是因为它们都可以：

  • 接受输入参数并以输出参数的格式向调用程序返回多个值。
  • 包含用于在数据库中执行操作的编程语句。 这包括调用其他过程。
  • 向调用程序返回状态值，以指明成功或失败（以及失败的原因）。

总结来说SQL 存储过程 是一组预编译的 SQL 语句，存储在数据库中，可以通过调用来执行。存储过程可以提高代码复用性、性能优化和安全性

一、使用存储过程的好处

减少了服务器/客户端网络流量

过程中的命令作为代码的单个批处理执行。 这可以显著减少服务器和客户端之间的网络流量，因为只有对执行过程的调用才会跨网络发送。 如果没有过程提供的代码封装，每个单独的代码行都不得不跨网络发送。

代码的重复使用

任何重复的数据库操作的代码都非常适合于在过程中进行封装。 这消除了不必要地重复编写相同的代码、降低了代码不一致性，并且允许拥有所需权限的任何用户或应用程序访问和执行代码。

更容易维护

在客户端应用程序调用过程并且将数据库操作保持在数据层中时，对于基础数据库中的任何更改，只有过程是必须更新的。 应用程序层保持独立，并且不必知道对数据库布局、关系或进程的任何更改的情况。

提升了性能

默认情况下，在首次执行过程时将编译过程，并且创建一个执行计划，供以后的执行重复使用。 因为查询处理器不必创建新计划，所以，它通常用更少的时间来处理过程。

如果过程引用的表或数据有显著变化，则预编译的计划可能实际上会导致过程的执行速度减慢。 在此情况下，重新编译过程和强制新的执行计划可提高性能。

此图来自网络，仅供参考

二、存储过程的类型

用户定义

用户定义的过程可在用户定义的数据库中创建，或者在除了 Resource 数据库之外的所有系统数据库中创建。 该过程可在 Transact-SQL 中开发，或者作为对 Microsoft .NET Framework 公共语言运行时 (CLR) 方法的引用开发。

临时

临时过程是用户定义过程的一种形式。 临时过程与永久过程类似，唯一的不同点在于它们存储在 tempdb 中。 临时过程有两种类型：本地过程和全局过程。 它们在名称、可见性以及可用性上有区别。 本地临时过程的名称以单个数字符号 (#) 开头；它们仅对当前的用户连接是可见的；当用户关闭连接时被删除。 全局临时过程的名称以两个数字符号 (##) 开头，创建后对任何用户都是可见的，并且在使用该过程的最后一个会话结束时被删除。

系统

系统过程包含数据库引擎。 它们物理上存储在内部隐藏的 Resource 数据库中，但逻辑上出现在每个系统定义和用户定义数据库的 sys 架构中。 此外，msdb 数据库还在 dbo 架构中包含用于计划警报和作业的系统存储过程。 因为系统过程以前缀 sp_ 开头，所以，我们建议你在命名用户定义过程时不要使用此前缀。

SQL Server 支持在 SQL Server 和外部程序之间提供一个接口以实现各种维护活动的系统过程。 这些扩展过程使用 xp_ 前缀。

简单介绍完使用存储过程的好处，以及类型，接下来教大家如何使用 SQL Server Management Studio 和 Transact-SQL CREATE PROCEDURE 语句来创建 SQL Server 存储过程。

一、存储过程的基本语句

1. 创建存储过程

CREATE PROCEDURE ProcedureName
    @Parameter1 DataType,
    @Parameter2 DataType
AS
BEGIN
    -- SQL 语句
    SELECT * FROM TableName WHERE ColumnName = @Parameter1;
END;

2. 调用存储过程

EXEC ProcedureName @Parameter1 = Value1, @Parameter2 = Value2;

3. 删除存储过程

DROP PROCEDURE ProcedureName;

二、存储过程的示例

示例 1：简单查询

CREATE PROCEDURE GetEmployeeByID
    @EmployeeID INT
AS
BEGIN
    SELECT * FROM Employees WHERE EmployeeID = @EmployeeID;
END;

-- 调用
EXEC GetEmployeeByID @EmployeeID = 1;

示例 2：带输出参数

CREATE PROCEDURE GetEmployeeCount
    @DepartmentID INT,
    @EmployeeCount INT OUTPUT
AS
BEGIN
    SELECT @EmployeeCount = COUNT(*) 
    FROM Employees 
    WHERE DepartmentID = @DepartmentID;
END;

-- 调用
DECLARE @Count INT;
EXEC GetEmployeeCount @DepartmentID = 2, @EmployeeCount = @Count OUTPUT;
PRINT @Count;

示例 3：插入数据并返回新记录的 ID

CREATE PROCEDURE AddEmployee
    @FirstName NVARCHAR(50),
    @LastName NVARCHAR(50),
    @HireDate DATE,
    @NewEmployeeID INT OUTPUT
AS
BEGIN
    INSERT INTO Employees (FirstName, LastName, HireDate)
    VALUES (@FirstName, @LastName, @HireDate);

    SET @NewEmployeeID = SCOPE_IDENTITY(); -- 获取新插入的 ID
END;
-- 调用
DECLARE @NewID INT;
EXEC AddEmployee @FirstName = 'Jane', @LastName = 'Doe', @HireDate = '2023-10-01', @NewEmployeeID = @NewID OUTPUT;
PRINT 'New Employee ID: ' + CAST(@NewID AS NVARCHAR);

三、存储过程的优点

  1. 性能优化
  • 存储过程在首次执行时会被编译并缓存，后续调用速度更快。
  1. 代码复用
  • 将常用的 SQL 逻辑封装在存储过程中，减少重复代码。
  1. 安全性
  • 可以通过存储过程限制用户对底层表的直接访问，只允许通过存储过程操作数据。
  1. 事务管理
  • 可以在存储过程中实现复杂的事务逻辑。

四、存储过程的高级用法

1. 条件逻辑

CREATE PROCEDURE CheckSalary
    @EmployeeID INT
AS
BEGIN
    DECLARE @Salary DECIMAL(10,2);
    SELECT @Salary = Salary FROM Employees WHERE EmployeeID = @EmployeeID;

    IF @Salary > 50000
    BEGIN
        PRINT 'High Salary';
    END
    ELSE
    BEGIN
        PRINT 'Low Salary';
    END
END;
-- 调用
EXEC CheckSalary @EmployeeID = 1;

2. 循环操作

CREATE PROCEDURE UpdateSalaries
    @Percentage DECIMAL(5,2)
AS
BEGIN
    DECLARE @EmployeeID INT;
    DECLARE EmployeeCursor CURSOR FOR
    SELECT EmployeeID FROM Employees;

    OPEN EmployeeCursor;
    FETCH NEXT FROM EmployeeCursor INTO @EmployeeID;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        UPDATE Employees 
        SET Salary = Salary * (1 + @Percentage / 100)
        WHERE EmployeeID = @EmployeeID;

        FETCH NEXT FROM EmployeeCursor INTO @EmployeeID;
    END;
    CLOSE EmployeeCursor;
    DEALLOCATE EmployeeCursor;
END;

-- 调用
EXEC UpdateSalaries @Percentage = 10; -- 给所有员工加薪 10%

3. 错误处理

CREATE PROCEDURE SafeInsert
    @FirstName NVARCHAR(50),
    @LastName NVARCHAR(50)
AS
BEGIN
    BEGIN TRY
        INSERT INTO Employees (FirstName, LastName)
        VALUES (@FirstName, @LastName);
    END TRY
    BEGIN CATCH
        PRINT 'Error: ' + ERROR_MESSAGE();
    END CATCH
END;
-- 调用
EXEC SafeInsert @FirstName = 'John', @LastName = 'Doe';

五、存储过程的最佳实践

  1. 命名规范
  • 使用有意义的名称，如 sp_GetEmployeeByID 或 usp_UpdateSalary。
  1. 避免过度使用游标
  • 游标性能较差，尽量使用集合操作（如 UPDATE、DELETE）。
  1. 参数验证
  • 在存储过程中对输入参数进行验证，避免 SQL 注入或无效数据。
  1. 注释与文档
  • 为存储过程添加注释，说明其功能、参数和返回值。

实践练习

尝试编写存储过程实现以下功能：

  1. 分页查询
  2. 数据导入/导出
  3. 复杂业务逻辑（如订单处理）

通过掌握存储过程，你可以显著提升 SQL Server 的开发效率和性能！






相关推荐

【机器学习】数据挖掘神器LightGBM详解(附代码)

来源：机器学习初学者本文约11000字，建议阅读20分钟本文为你介绍数据挖掘神器LightGBM。LightGBM是微软开发的boosting集成模型，和XGBoost一样是对GBDT...

3分钟，用DeepSeek全自动生成语音计算器，还带括号表达式!

最近，大家慢慢了解到了DeepSeek的强大功能，特别是它在编程领域也同样强大。编程零基础小白，一行代码不用写，也能全自动生成一个完整的、可运行的软件来！很多程序员一直不相信小白不写代码也能编软件！下...

python学习笔记 3.表达式

在Python中，表达式是由值、变量和运算符组成的组合。以下是一些常见的Python表达式：算术表达式：由数值和算术运算符组成的表达式，如加减乘除等。例如：5+3、7*2、10/3等。字符...

5.7 VS 8.x，为什么用户不升级MySql

一般来说为了更好的功能和性能，都需要将软件升级到最新的版本，然而在开源软件中，由于一些开发商变化或其他的问题（开源授权变化），致使人们不愿使用最新的版本，一个最典型的问题就是CentOS操作系统。还有...

大厂高频:讲一下MySQL主从复制

大家经常听说主从复制，那么主从复制的意义？能解决的问题有哪些？主从复制能解决的问题就是在我们平时开发的程序中操作数据库的时候，大多数的情况查询的操作大大超过了写的操作，也就说对数据库读取数据的压力比较...

MYSQL数据库的五大安全防护措施

以技术为基础的企业里最有价值的资产莫过于是客户或者其数据库中的产品信息了。因此，在这样的企业中，保证数据库免受外界攻击是数据库管理的重要环节。很多数据库管理员并没有实施什么数据库保护措施，只是因为觉得...

docker安装mysql

准备工作已安装Docker环境（官方安装文档）终端/命令行工具（Linux/macOS/WSL）步骤1：拉取MySQL镜像打开终端执行以下命令，拉取官方MySQL镜像（默认最新版本）：d...

Zabbix监控系统系列之六:监控 mysql

zabbix监控mysql1、监控规划在创建监控项之前要尽量考虑清楚要监控什么，怎么监控，监控数据如何存储，监控数据如何展现，如何处理报警等。要进行监控的系统规划需要对Zabbix很了解，这里只是...

详解MySQL的配置文件及优化

#头条创作挑战赛#在Windows系统中，MySQL服务器启动时最先读取的是my.ini这个配置文件。在Linux系统中，配置文件为my.cnf，其路径一般为/etc/my.cnf或/etc/mysq...

Mysql 几个批处理执行脚本

学习mysql过程中，需要创建测试数据，并让多人每人一个数据库连接并进行作业检查。整合部分批处理创建数据批量创建数据库DELIMITER$CREATEPROCEDURECreateDatab...

MySQL学到什么程度?才有可以在简历上写精通

前言如今互联网行业用的最多就是MySQL，然而对于高级Web面试者，尤其对于寻找30k下工作的求职者，很多MySQL相关知识点基本都会涉及，如果面试中，你的相关知识答的模糊和不切要点，基...

mysql 主、从服务器配置“Slave_IO_Running: Connecting” 问题分析

#在进行mysql主、从服务器配置时，”SHOWSLAVESTATUS;“查看从库状态Slave_IO_Runing，出现错误：“Slave_IO_Running:Connectin...

MYSQL数据同步

java开发工程师在实际的开发经常会需要实现两台不同机器上的MySQL数据库的数据同步，要解决这个问题不难，无非就是mysql数据库的数据同步问题。但要看你是一次性的数据同步需求，还是定时数据同步，亦...

「MySQL 8」MySQL 5.7都即将停只维护了，是时候学习一波MySQL 8了

MySQL8新特性选择MySQL8的背景：MySQL5.6已经停止版本更新了，对于MySQL5.7版本，其将于2023年10月31日停止支持。后续官方将不再进行后续的代码维护。另外，...

Prometheus监控mysql

通过Prometheus监控Mysql，我们需要在Mysql端安装一个mysql-exporter，然后Prometheus通过mysql-exporter暴露的端口抓取数据。1.安装一个MYSQL配...

一周热门
最近发表
标签列表