什么是存储过程?它们是如何工作的?存储过程的构成是什么(每一个必须是存储过程的东西)?
存储过程是一批可以以几种方式执行的SQL语句。大多数主要的DBM都支持存储过程;但是,并不是所有的都支持。你需要向你的特定DBMS帮助文档核实具体细节。由于我对SQL Server最为熟悉,我将使用它作为我的样本。
要创建一个存储过程,语法是相当简单的。
CREATE PROCEDURE <owner>.<procedure name>
<param> <datatype>
AS
<Body>
因此,举例来说。
CREATE PROCEDURE Users_GetUserInfo
@login nvarchar(30)=null
AS
SELECT * from [Users]
WHERE ISNULL(@login,login)=login
存储过程的一个好处是,你可以将数据访问逻辑集中到一个地方,然后便于DBA优化。存储过程还有一个安全方面的好处,即你可以授予存储过程执行权限,但用户不需要对底层表有读/写权限。这是防止SQL注入的良好第一步。
存储过程确实有缺点,基本上是与你的基本CRUD操作相关的维护。假设每个表都有插入、更新、删除和至少一个基于主键的选择,这意味着每个表将有4个过程。现在,在一个有400个表的大型数据库中,你将有1600个过程!这还是在假设有400个表的情况下。这是假设你没有重复的程序,但你可能会有。
这就是使用ORM或其他方法来自动生成基本CRUD操作的好处。
存储过程是一组预编译的SQL语句,用于执行一项特殊任务。
例子。如果我有一个`雇员'表
Employee ID Name Age Mobile
---------------------------------------
001 Sidheswar 25 9938885469
002 Pritish 32 9178542436
首先,我正在检索`Employee'表。
Create Procedure Employee details
As
Begin
Select * from Employee
End
在SQL服务器上运行该程序。
Execute Employee details
--- (Employee details is a user defined name, give a name as you want)
其次,我将该值插入雇员表中
Create Procedure employee_insert
(@EmployeeID int, @Name Varchar(30), @Age int, @Mobile int)
As
Begin
Insert Into Employee
Values (@EmployeeID, @Name, @Age, @Mobile)
End
要在SQL Server上运行参数化过程。
Execute employee_insert 003,’xyz’,27,1234567890
--(Parameter size must be same as declared column size)
例如:@Name Varchar(30)
。
在Employee
表中,Name
列的大小必须是varchar(30)
。
存储过程是一组已经创建并存储在数据库中的SQL语句。 一个存储过程将接受输入参数,这样一个存储过程就可以被多个客户机使用不同的输入数据在网络上使用。 一个存储过程将减少网络流量,提高性能。 如果我们修改一个存储过程,所有的客户端都会得到更新的存储过程。
创建存储过程的例子
CREATE PROCEDURE test_display
AS
SELECT FirstName, LastName
FROM tb_test;
EXEC test_display;
使用存储过程的优势
你可以只创建一次存储过程,将其存储在数据库中,然后在程序中任意调用。
如果操作需要大量重复执行的SQL代码,存储过程可以更快地执行。 当它们第一次执行时,它们会被解析和优化,并且存储过程的编译版本会留在内存缓存中供以后使用。 这意味着存储过程在每次使用时都不需要重新解析和优化,因此执行时间更快。
一个需要数百行Transact-SQL代码的操作,可以通过执行存储过程中的代码的单一语句来执行,而不是通过网络发送数百行代码。
即使用户没有直接执行存储过程'语句的权限,也可以被授予执行存储过程的权限。
在SQL Server中,存储过程有不同的类型。
系统存储过程
用户定义的存储程序
扩展存储程序
系统存储程序存储在主数据库中,这些程序以sp_
为前缀。
这些程序可用于执行各种任务,以支持系统表中外部应用程序调用的SQL Server功能。
例子: sp_helptext [StoredProcedure_Name]。
用户定义的存储程序通常存储在用户数据库中,通常是为了完成用户数据库中的任务。
在对这些存储过程进行编码的时候,不要使用的sp_
前缀,因为如果我们先使用sp_
前缀,就会检查主数据库,然后再到用户定义数据库。
一般来说,存储过程是一个"SQL函数。"它们有。
-- a name
CREATE PROCEDURE spGetPerson
-- parameters
CREATE PROCEDURE spGetPerson(@PersonID int)
-- a body
CREATE PROCEDURE spGetPerson(@PersonID int)
AS
SELECT FirstName, LastName ....
FROM People
WHERE PersonID = @PersonID
这是一个侧重于T-SQL的例子。存储过程可以执行大多数SQL语句,返回标量和基于表的值,并被认为更安全,因为它们可以防止SQL注入攻击。
想想这样的情况。
注意
存储过程无非是将一组SQL语句编译成一个执行计划。
它减少了网络流量
例子: 创建一个存储过程
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE GetEmployee
@EmployeeID int = 0
AS
BEGIN
SET NOCOUNT ON;
SELECT FirstName, LastName, BirthDate, City, Country
FROM Employees
WHERE EmployeeID = @EmployeeID
END
GO
更改或修改一个存储过程。
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE GetEmployee
@EmployeeID int = 0
AS
BEGIN
SET NOCOUNT ON;
SELECT FirstName, LastName, BirthDate, City, Country
FROM Employees
WHERE EmployeeID = @EmployeeID
END
GO
删除一个存储过程。
DROP PROCEDURE GetEmployee
"什么是存储过程"。 在这里的其他帖子中已经回答了。 我要发的是一种不为人知的使用存储过程的方法。 它就是 "分组存储过程 "或 "给存储过程编号"。
语法参考
[![在此输入图像描述][1]][1]
`; 根据this
一个可选的整数,用于对同名的程序进行分组。 通过使用一个DROP PROCEDURE语句,可以将这些分组后的程序一起删除。
实例
CREATE Procedure FirstTest
(
@InputA INT
)
AS
BEGIN
SELECT 'A' + CONVERT(VARCHAR(10),@InputA)
END
GO
CREATE Procedure FirstTest;2
(
@InputA INT,
@InputB INT
)
AS
BEGIN
SELECT 'A' + CONVERT(VARCHAR(10),@InputA)+ CONVERT(VARCHAR(10),@InputB)
END
GO
使用
exec FirstTest 10
exec FirstTest;2 20,30
结果
[![在此输入图像描述][2]][2]
另一个尝试
CREATE Procedure SecondTest;2
(
@InputA INT,
@InputB INT
)
AS
BEGIN
SELECT 'A' + CONVERT(VARCHAR(10),@InputA)+ CONVERT(VARCHAR(10),@InputB)
END
GO
结果
Msg 2730, Level 11, State 1, Procedure SecondTest, Line 1 [Batch Start Line 3] 不能创建存储过程'SecondTest' 组号为2的存储过程,因为数据库中当前不存在一个具有相同名称和组号为1的存储过程。 必须先执行CREATE PROCEDURE 'SecondTest';1。
参考文献。
小心
2.在今后的Microsoft SQL Server版本中,可能会删除该功能。
[1]: https://i.stack.imgur.com/W3U2d.png [2]: https://i.stack.imgur.com/BLVh9.png
在存储过程中,语句只写一次,减少了客户端和服务器之间的网络流量。 我们也可以避免Sql注入攻击。
SQL Server中的存储过程可以接受输入参数,并返回输出参数的多个值。 在SQL Server中,存储过程通过编程语句在数据库中执行操作,并向调用过程或批处理返回一个状态值。
在SQL Server中使用存储过程的好处
它们允许模块化编程。 它们允许更快的执行。 它们可以减少网络流量。 它们可以作为一种安全机制。
下面是一个存储过程的例子,它接受一个参数,执行一个查询并返回一个结果。 具体来说,存储过程接受BusinessEntityID作为参数,并使用它来匹配HumanResources.Employee表的主键,从而返回所请求的雇员。
> create procedure HumanResources.uspFindEmployee `*<<<---Store procedure name`*
@businessEntityID `<<<----parameter`
as
begin
SET NOCOUNT ON;
Select businessEntityId, <<<----select statement to return one employee row
NationalIdNumber,
LoginID,
JobTitle,
HireData,
From HumanResources.Employee
where businessEntityId =@businessEntityId <<<---parameter used as criteria
end
我是从必要网学来的......非常有用。