当前位置：极客教程 > SQL > SQL 精品教程 > SQL 存储过程

SQL 存储过程

SQL 存储过程

存储过程 是一组预编译的SQL语句（已准备好的SQL代码），可以重复使用。

他们可以用于执行各种数据库操作，例如插入、更新或删除数据，生成报表和进行复杂的计算。存储过程非常有用，因为它们允许将一组SQL语句封装（捆绑）为单个单元，并以不同的参数重复执行它们，从而便于管理和重用代码。

存储过程的结构与函数类似； 它们接受参数并在我们调用它们时执行操作；但它们之间的区别在于，SQL存储过程编写或创建更简单，而函数具有更为严格的结构，并支持较少的子句和功能。

语法

在SQL中创建存储过程的基本语法如下−

CREATE PROCEDURE procedure_name
   @parameter1 datatype,
   @parameter2 datatype
AS
BEGIN
   -- SQL statements to be executed
END

其中，

  • CREATE PROCEDURE语句用于创建存储过程。在创建存储过程之后，我们可以定义存储过程可能需要的任何输入参数。这些参数以’@’符号开头，后面是它们对应的数据类型。

  • AS关键字用于开始存储过程定义。构成存储过程的SQL语句放置在BEGIN和END关键字之间。

创建存储过程

我们可以使用SQL中的CREATE PROCEDURE语句来创建存储过程。以下是创建存储过程的简单步骤：

  • 为存储过程选择一个名称。

  • 编写存储过程的SQL代码，包括在SQL Server中创建存储过程的代码。

  • 我们可以使用不同的输入参数来执行存储过程的测试。

示例

为了更好地理解，让我们考虑一个包含客户的个人信息（包括姓名、年龄、地址和薪水等）的CUSTOMERS表，如下所示：

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

现在使用下面的INSERT语句将值插入到这个表中：

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (1, 'Ramesh', 32, 'Ahmedabad', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (2, 'Khilan', 25, 'Delhi', 1500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (3, 'kaushik', 23, 'Kota', 2000.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (4, 'Chaitali', 25, 'Mumbai', 6500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (5, 'Hardik', 27, 'Bhopal', 8500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (6, 'Komal', 22, 'MP', 4500.00 );

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7, 'Muffy', 24, 'Indore', 10000.00 );

表将被创建为 —

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  1 | Ramesh   |  32 | Ahmedabad |  2000.00 |
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  3 | kaushik  |  23 | Kota      |  2000.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
|  5 | Hardik   |  27 | Bhopal    |  8500.00 |
|  6 | Komal    |  22 | MP        |  4500.00 |
|  7 | Muffy    |  24 | Indore    | 10000.00 |
+----+----------+-----+-----------+----------+

现在，让我们来看一个简单的示例，创建一个存储过程，它接受一个输入参数并返回一个结果集。

在下面的查询中，我们首先尝试创建名为’GetCustomerInfo’的存储过程。然后，我们为其提供一个名为@CutomerAge的单个输入参数。存储过程然后从” CUSTOMERS “表中选择所有记录，其中CutomerAge与输入参数匹配。

CREATE PROCEDURE GetCustomerInfo
   @CutomerAge INT
AS
BEGIN
   SELECT * FROM CUSTOMERS
   WHERE AGE = @CutomerAge
END

输出

这将产生以下结果−

Commands completed successfully.

验证

为了验证更改，一旦我们创建了存储过程，我们可以通过使用不同的输入参数执行它来进行测试，如下面的查询所示：

EXEC GetCustomerInfo @CutomerAge = 25

这将返回CUSTOMERS表中所有列，其中顾客的年龄是25岁。

+----+----------+-----+-----------+----------+
| ID | NAME     | AGE | ADDRESS   | SALARY   |
+----+----------+-----+-----------+----------+
|  2 | Khilan   |  25 | Delhi     |  1500.00 |
|  4 | Chaitali |  25 | Mumbai    |  6500.00 |
+----+----------+-----+-----------+----------+

存储过程参数类型

数据库系统中的存储过程可以具有不同类型的参数，这些参数是在执行存储过程时传递的值的占位符。以下是SQL中不同类型的存储过程参数：

序号 参数与描述
1 输入参数 这些参数用于将值从调用程序或用户传递给存储过程。
2 输出参数 这些参数用于将值从存储过程返回给调用程序或用户。
3 输入/输出参数 这些参数允许存储过程接受输入值并返回输出值。
4 表值参数 这些参数用于将表变量作为参数传递给存储过程。
5 默认参数 这些参数用于指定如果没有为参数传递值，则使用的默认值。
6 游标参数 这些参数用于将光标传递给存储过程。
7 输出XML参数 这些参数用于从存储过程返回XML数据。

现在，让我们来看一下SQL中最常见的存储过程参数类型之一 –

带有IN参数的过程

IN参数是默认参数，它会从程序中接收输入值。

当执行存储过程时，我们可以将值作为参数传递。这些值是只读的，也就是说它们不能由存储过程修改。

示例

在下面的查询中，我们试图创建一个存储过程，它以客户的ID作为输入参数，并返回该客户的工资。

过程体只需要执行一个SELECT语句，从”CUSTOMERS”表中检索”Salary”列，其中”CustomerID”与输入参数匹配。

CREATE PROCEDURE GetCustomerSalary
   @CustomerID INT
AS
BEGIN
   SELECT SALARY FROM CUSTOMERS WHERE ID = @CustomerID
END

输出

这将产生以下结果 –

Commands completed successfully.

验证

为了执行存储过程并传递一个值给“@CustomerID”参数，我们将使用如下的EXEC命令：

EXEC GetCustomerSalary @CustomerID = 6

这将返回ID为6的客户的薪资，假设在”Customers”表中有对应的行 –

+----------+
| SALARY   |
+----------+
|  4500.00 |
+----------+

OUT参数的使用

OUT参数用于向程序发送输出值。它允许我们将一个或一组值返回给调用程序。

需要注意的是，当使用OUT参数时，在将其传递给存储过程时，我们必须在参数名后面指定关键字”OUT”。这告诉SQL Server该参数是一个输出参数，并且应由存储过程分配一个值。

示例

在下面的查询中，我们试图创建一个存储过程，该存储过程将客户的ID作为输入参数，并使用输出参数”@Salary”返回该客户的薪水，该输出参数保存存储过程返回的薪水值。

存储过程体执行一个SELECT语句，从”CUSTOMERS”表中获取”Salary”列，其中”ID”与输入参数匹配。我们使用赋值运算符”=”将返回的值赋给我们的输出参数”@Salary”。

CREATE PROCEDURE GetCustomerSalary
   @CustomerID INT,
   @Salary DECIMAL(18,2) OUT
AS
BEGIN
   SELECT @Salary = SALARY FROM CUSTOMERS WHERE ID = @CustomerID
END

输出

这将产生如下结果-

Commands completed successfully.

验证

在这里，我们首先声明一个与输出参数”@Salary”相同数据类型的变量”@CustSalary”。然后使用EXEC命令执行存储过程，传递”@CustomerID”参数和”@Salary”参数作为输出参数。最后，我们SELECT “@CustSalary”变量的值，以在结果集中显示客户的工资。

DECLARE @CustSalary DECIMAL(18, 2)
EXEC GetCustomerSalary @CustomerID = 4, @Salary = @CustSalary OUT
SELECT @CustSalary AS 'Customer Salary'

这将返回如下所示的输出：

+-------------------+
| Customer Salary   |
+-------------------+
|  6500.00          |
+-------------------+

带有INOUT参数的过程

INOUT参数是IN参数和OUT参数的组合，它允许我们在存储过程中同时传递数据进去和从中获得数据。

要在存储过程中声明一个INOUT参数，我们需要在参数名之前使用 OUTPUT 关键字。

示例

在下面的查询中，我们为存储过程提供了两个INOUT参数：cust_id和salary。@salary参数被指定为一个OUTPUT参数，它可以用作输入和输出参数。

存储过程首先使用cust_id参数从数据库中检索客户的当前工资。然后将工资增加10%，并使用cust_id参数更新数据库中客户的工资。

CREATE PROCEDURE increaseSalary
   @cust_id INT,
   @salary DECIMAL(18, 2) OUTPUT
AS
BEGIN
   -- get the current salary of the customer
   SELECT @salary = SALARY FROM CUSTOMERS WHERE ID = @cust_id;

   -- increase the salary by 10%
   SET @salary = @salary * 1.1;

   -- update the cutomer's salary in the database
   UPDATE CUSTOMERS SET SALARY = @salary WHERE ID = @cust_id;
END;

输出

这将产生以下结果-

Commands completed successfully.

验证

在这里，我们声明并初始化@cust_id和@salary变量，其值为我们要更新的客户ID和薪水。然后，我们调用’increaseSalary’存储过程，将这些变量作为参数传入。@salary参数被指定为 OUTPUT 参数，这允许在存储过程中更新它。最后，我们使用SELECT语句检索更新后的@salary变量的值。

DECLARE @cust_id INT;
DECLARE @salary DECIMAL(18, 2);

SET @cust_id = 7;
SET @salary = 50000.00;

EXEC increaseSalary @cust_id, @salary OUTPUT;

SELECT @salary AS 'Customer Salary'

这将返回如下所示的输出 –

+-------------------+
| Customer Salary   |
+-------------------+
|  11000.00         |
+-------------------+

如何重命名存储过程

要在SQL中重命名存储过程，我们使用内置的系统存储过程sp_rename。

语法

以下是在SQL中重命名存储过程的基本语法：

sp_rename 'old_procedure_name', 'new_procedure_name';

示例

在下面的查询中，我们试图将现有的存储过程“GetCustomerSalary”重命名为“newProcedure”-

sp_rename 'GetCustomerSalary', 'newProcedure';

输出

执行上述查询后，’GetCustomerSalary’ 存储过程将被重命名为‘newProcedure’。

Caution: Changing any part of an object name could break scripts and stored procedures.

注意： 请注意，如果存储过程被其他对象（如视图或其他存储过程）引用，我们需要手动更新这些引用，以反映存储过程的新名称。此外，重命名存储过程可能会影响依赖于旧名称的任何脚本或应用程序，因此在将其部署到生产环境之前，我们最好彻底测试变更。

如何修改存储过程

要在SQL中修改现有的存储过程，我们使用ALTER PROCEDURE语句。我们可以使用ALTER PROCEDURE语句添加或删除参数。

请注意，当我们修改存储过程时，任何依赖于它的相关对象或代码也需要相应地进行修改。

语法

以下是在SQL中修改存储过程的基本语法：

ALTER PROCEDURE procedure_name
AS
BEGIN
    -- New procedure code goes here
END

示例

假设我们有一个名为‘GetCustomerSalary’的存储过程，它接受一个名为@Customer_ID的参数，并返回具有该ID的客户的详细信息，如下所示−

-- Original stored procedure
CREATE PROCEDURE GetCustomerSalary
   @CustomerID INT
AS
BEGIN
   SELECT * FROM CUSTOMERS WHERE ID = @CustomerID
END

以下是上述查询的输出 –

Commands completed successfully.

现在，我们正在尝试通过添加一个新的参数 @CustomerName，类型为 VARCHAR(20)，来修改‘GetCustomerSalary’存储过程。然后，我们修改SELECT语句以同时过滤‘ID’和‘NAME’。

ALTER PROCEDURE GetCustomerSalary
   @CustomerID INT,
   @CustomerName VARCHAR(20)
AS
BEGIN
   SELECT * FROM CUSTOMERS WHERE ID = @CustomerID AND NAME = @CustomerName
END

输出

生成的结果如下所示−

Commands completed successfully.

要在SQL中删除存储过程，我们使用DROP PROCEDURE语句。

注意，删除存储过程将永久删除它，所以在执行DROP PROCEDURE语句之前，请确保我们确实想要删除它。此外，删除存储过程可能会影响依赖于存储过程的任何脚本或应用程序，所以在将更改部署到生产环境之前，最好进行彻底的测试。

语法

以下是在SQL中删除存储过程的基本语法

DROP PROCEDURE [IF EXISTS] procedure_name;

示例

DROP PROCEDURE IF EXISTS GetCustomerSalary;

输出

生成的结果如下所示 –

Commands completed successfully.

存储过程的优势

以下是存储过程的优势：

  • 提高性能 - 存储过程是预编译并存储在服务器上，因此它们比从客户端应用程序发送的SQL语句执行更快。

  • 代码重用 - 存储过程可以从不同的客户端应用程序调用，这意味着相同的代码可以在不同的应用程序之间重用。这减少了开发时间和维护成本。

  • 减少网络流量 - 因为存储过程在服务器上执行，只有结果返回给客户端，这减少了网络流量并提高了应用程序的性能。

  • 更好的安全性 - 存储过程可以用于强制执行安全规则并防止未经授权访问敏感数据。它们还可以限制用户可以执行的操作，从而更容易维护数据的完整性和一致性。

  • 简化维护 - 通过将SQL代码存储在一个位置，更容易维护和更新代码。这使得修复错误、添加新功能和优化性能更加容易。

存储过程的缺点

以下是存储过程的缺点：

  • 增加开销 - 存储过程可能比简单的SQL语句消耗更多的服务器资源，特别是当它们经常被使用或用于复杂操作时。

  • 有限的可移植性 - 存储过程通常特定于某个特定的数据库管理系统(DBMS)，这意味着它们可能不易于在其他DBMS中移植。

  • 调试挑战 - 调试存储过程可能比调试简单的SQL语句更具挑战性，特别是当涉及多层代码时。

  • 安全风险 - 如果存储过程编写不正确，它们可能构成安全风险，特别是如果它们用于访问敏感数据或执行可能危及数据库完整性的操作。

Python教程

Python 教程

Python 教程
Tkinter 教程

Tkinter 教程
Pandas 教程

Pandas 教程
NumPy 教程

NumPy 教程
Flask 教程

Flask 教程
Django 教程

Django 教程
PySpark 教程

PySpark 教程
wxPython 教程

wxPython 教程
SymPy 教程

SymPy 教程
Seaborn 教程

Seaborn 教程
SciPy 教程

SciPy 教程
RxPY 教程

RxPY 教程
Pycharm 教程

Pycharm 教程
Pygame 教程

Pygame 教程
PyGTK 教程

PyGTK 教程
PyQt 教程

PyQt 教程
PyQt5 教程

PyQt5 教程
PyTorch 教程

PyTorch 教程
Matplotlib 教程

Matplotlib 教程
Web2py 教程

Web2py 教程
BeautifulSoup 教程

BeautifulSoup 教程

Java教程

Java 教程

Java 教程

Web教程

HTML 教程

HTML 教程
CSS 教程

CSS 教程
CSS3 教程

CSS3 教程
jQuery 教程

jQuery 教程
Ajax 教程

Ajax 教程
AngularJS 教程

AngularJS 教程
TypeScript 教程

TypeScript 教程
WordPress 教程

WordPress 教程
Laravel 教程

Laravel 教程
Next.js 教程

Next.js 教程
PhantomJS 教程

PhantomJS 教程
Three.js 教程

Three.js 教程
Underscore.JS 教程

Underscore.JS 教程
WebGL 教程

WebGL 教程
WebRTC 教程

WebRTC 教程
VueJS 教程

VueJS 教程

数据库教程

SQL 教程

SQL 教程
MySQL 教程

MySQL 教程
MongoDB 教程

MongoDB 教程
PostgreSQL 教程

PostgreSQL 教程
SQLite 教程

SQLite 教程
Redis 教程

Redis 教程
MariaDB 教程

MariaDB 教程

图形图像教程

Vulkan 教程

Vulkan 教程
OpenCV 教程

OpenCV 教程

大数据教程

R语言 教程

R语言 教程

开发工具教程

Git 教程

Git 教程
VSCode 教程

VSCode 教程
Docker 教程

Docker 教程
Gerrit 教程

Gerrit 教程
Excel 教程

Excel 教程

计算机教程

Go语言 教程

Go语言 教程
C++ 教程

C++ 教程

SQL 精品教程