SQL 存储过程

一、SQL 存储过程 介绍

SQL语言中的存储过程是存储在数据库中用于执行特定任务的一组逻辑语句。

它是一个由名称、参数列表和 Transact-SQL 语句组成的子程序。

任何用户都可以将存储过程作为命名对象存储在 SQL 数据库中，并可以使用触发器、其他过程和其他编程应用程序（如 Java、PHP、R、C#、Python 等）来调用它。

SQL 数据库在第一次调用存储过程时创建一个执行计划并将其存储在缓存中。该计划被 SQL Server 重用，它以可靠的性能快速执行存储过程。

二、SQL 存储过程的类型

以下是 SQL 中的两种存储过程：

• 用户定义的存储过程
• 系统存储过程

三、用户定义的存储过程

用户定义的存储过程由数据库开发人员和管理员创建并存储在当前数据库中。

这种类型的存储过程提供了一个或多个 SQL 语句，用于从数据库表中检索、更新和删除值。

用户定义的存储过程进一步分为以下两种类型：

1. T-SQL 存储过程
2. CLR 存储过程

T-SQL 存储过程

Transact-SQL 过程接受参数并返回它们。此存储过程管理带或不带参数的 INSERT、UPDATE 和 DELETE 语句，并在输出中提供行数据。

CLR 存储过程

CLR 存储过程是由 CLR（公共语言运行时）和另一个用 C# 和 VB.NET 等基于 CLR 的语言编写的存储过程组合创建的存储过程。

CLR 过程是 .Net Framework 的对象，它们在 SQL 数据库服务器的内存中执行。

四、系统存储过程

SQL 数据库服务器为管理活动创建和执行系统存储过程。SQL 数据库服务器不允许开发人员干预系统存储过程。

SQL中存储过程的语法：

以下语法用于在SQL语言中创建简单存储过程：

CREATE PROCEDURE Procedure_Name  
AS  
/*    SQL Statements */  
GO;  

以下语法用于在SQL语言中执行存储过程：

EXEC Procedure_Name ;  

五、SQL 存储过程 的示例

首先，我们必须在 SQL 中创建表并将数据插入到表中。

以下查询使用 CREATE TABLE 语句创建Student_Stored_Procedure表：

CREATE TABLE Student_Stored_Procedure  
(  
Student_ID INT NOT NULL,   
Student_Name varchar(100),  
Student_Course varchar(50),  
Student_Age INT,   
Student_Marks INT  
);   

以下 SQL 语句 使用 INSERT INTO 语句将学生记录插入到上表中

INSERT INTO Student_Stored_Procedure VALUES (101, Anuj, B.tech, 20, 88);  
INSERT INTO Student_Stored_Procedure VALUES (102, Raman, MCA, 24, 98);  
INSERT INTO Student_Stored_Procedure VALUES (104, Shyam, BBA, 19, 92);  
INSERT INTO Student_Stored_Procedure VALUES (107, Vikash, B.tech, 20, 78);  
INSERT INTO Student_Stored_Procedure VALUES (111, Monu, MBA, 21, 65);  
INSERT INTO Student_Stored_Procedure VALUES (114, Jones, B.tech, 18, 93);  
INSERT INTO Student_Stored_Procedure VALUES (121, Parul, BCA, 20, 97);  
INSERT INTO Student_Stored_Procedure VALUES (123, Divya, B.tech, 21, 89);  
INSERT INTO Student_Stored_Procedure VALUES (128, Hemant, MBA, 23, 90);  
INSERT INTO Student_Stored_Procedure VALUES (130, Nidhi, BBA, 20, 88);  
INSERT INTO Student_Stored_Procedure VALUES (132, Priya, MBA, 22, 99);  
INSERT INTO Student_Stored_Procedure VALUES (138, Mohit, MCA, 21, 92); 

让我们使用以下 SELECT 语句查看上表的记录

SELECT * FROM Student_Stored_Procedure;  

结果为：

Student_ID	Student_Name	Student_Course	Student_Age	Student_Marks
101	Anuj	B.tech	20	88
102	Raman	MCA	24	98
104	Shyam	BBA	19	92
107	Vikash	B.tech	20	78
111	Monu	MBA	21	65
114	Jones	B.tech	18	93
121	Parul	BCA	20	97
123	Divya	B.tech	21	89
128	Hemant	MBA	23	90
130	Nidhi	BBA	20	88
132	Priya	MBA	22	99
138	Mohit	MCA	21	92

以下查询创建了从上述 Student_Stored_Procedure 表中选择所有记录的存储过程

CREATE PROCEDURE Show_All_Students  
AS  
SELECT * FROM Student_Stored_Procedure  
GO ;  

 现在，使用以下SQL执行存储过程以查看其输出

EXEC Show_All_Students;  

结果为：

Student_ID	Student_Name	Student_Course	Student_Age	Student_Marks
101	Anuj	B.tech	20	88
102	Raman	MCA	24	98
104	Shyam	BBA	19	92
107	Vikash	B.tech	20	78
111	Monu	MBA	21	65
114	Jones	B.tech	18	93
121	Parul	BCA	20	97
123	Divya	B.tech	21	89
128	Hemant	MBA	23	90
130	Nidhi	BBA	20	88
132	Priya	MBA	22	99
138	Mohit	MCA	21	92

六、一个参数的存储过程 

使用一个参数创建存储过程的语法如下：

CREATE PROCEDURE Procedure_Name @Parameter_Name Datatype (size of the Parameter)  
AS   
/*   SQL Statement * /  
GO;  

使用一个参数执行存储过程的语法如下

EXEC Procedure_Name @Parameter_Name = Value;  

以下SQL语句创建存储过程，显示上表中特定课程的学生

CREATE PROCEDURE Show_Particular_Course_Student @Student_Course nvarchar(50)  
AS   
SELECT * FROM Student_Stored_Procedure WHERE Student_Course = @Student_Course  
GO;  

以下SQL语句执行上述存储过程并在输出中显示 B.tech 学生的记录

EXEC Show_Particular_Course_Student @Student_Course = 'B.tech';  

输出结果为：

Student_ID	Student_Name	Student_Course	Student_Age	Student_Marks
101	Anuj	B.tech	20	88
107	Vikash	B.tech	20	78
114	Jones	B.tech	18	93
123	Divya	B.tech	21	89

七、多参数的存储过程 

下面给出了创建具有多个参数的存储过程的语法：

CREATE PROCEDURE Procedure_Name @Parameter_Name_1 Datatype (size of the Parameter_1),  @Parameter_Name_2 Datatype (size of the Parameter_2), .....,  @Parameter_Name_N Datatype (size of the Parameter_N)  
AS   
/*   SQL Statement * /  
GO;  

执行带有多个参数的存储过程的语法如下

EXEC Procedure_Name @Parameter_Name1 = Value, @Parameter_Name2 = Value, ....., @Parameter_NameN = Value; 

以下SQL语句创建存储过程，显示上表中具有特定年龄的特定课程的学生

CREATE PROCEDURE Show_Particular_Course_Age_Student @Student_Course nvarchar(50), @Student_AgeINT  
AS   
SELECT * FROM Student_Stored_Procedure WHERE Student_Course = @Student_Course AND Student_Age =@Student_Age  
GO;  

以下SQL语句执行存储过程并在输出中显示课程为 B.tech 且年龄为 20 的学生的记录

EXEC Show_Particular_Course_Student @Student_Course = 'B.tech', @Student_Age = 20;  

八、SQL中存储过程的优点

以下是SQL语言中存储过程的重要好处或优势：

• 减少网络流量：存储过程最大限度地减少了应用程序和数据库服务器之间的网络流量，因为应用程序不需要发送多行 SQL 代码，只需将存储过程的名称和参数传递给数据库服务器。
• 更强的安全性：存储过程提供了很高的安全性，因为它限制了用户直接访问表中的数据。
• 可重用：存储过程可以被多个用户使用，无需重复编写相同的 SQL 代码。代码的可重用性最大限度地缩短了开发时间。
• 易于修改：任何用户都可以在 ALTER TABLE 命令的帮助下轻松修改存储过程。
• 提高性能： SQL 中的存储过程最大限度地提高了应用程序的性能。