什么是“存储过程”，它们是如何工作的?
存储过程是由什么组成的(每个东西都必须是存储过程)?
一般来说，存储过程是一个“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语句。大多数主流DBMs都支持存储过程;然而，并非所有国家都这样做。您需要查看特定的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个过程!这还是在你没有副本的前提下你可能会有。
这就是使用ORM或其他方法自动生成基本CRUD操作的优点所在。
存储过程是一组预编译的SQL语句，用于执行特殊任务。
示例:如果我有一个Employee表
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 Server上运行此过程:
Execute Employee details
--- (Employee details is a user defined name, give a name as you want)
然后，我将值插入到Employee表中
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)。
存储过程主要用于在数据库上执行某些任务。例如
从数据上的一些业务逻辑获取数据库结果集。 在一次调用中执行多个数据库操作。 用于将数据从一个表迁移到另一个表。 可用于其他编程语言，如Java。
存储过程是一组已创建并存储在数据库中的SQL语句。存储过程将接受输入参数，以便多个使用不同输入数据的客户端可以通过网络使用单个过程。存储过程可以减少网络流量并提高性能。如果我们修改一个存储过程，所有的客户端都将得到更新后的存储过程。
创建存储过程的示例
CREATE PROCEDURE test_display
AS
SELECT FirstName, LastName
FROM tb_test;
EXEC test_display;
使用存储过程的优点
A stored procedure allows modular programming. You can create the procedure once, store it in the database, and call it any number of times in your program. A stored procedure allows faster execution. If the operation requires a large amount of SQL code that is performed repetitively, stored procedures can be faster. They are parsed and optimized when they are first executed, and a compiled version of the stored procedure remains in a memory cache for later use. This means the stored procedure does not need to be reparsed and reoptimized with each use, resulting in much faster execution times. A stored procedure can reduce network traffic. An operation requiring hundreds of lines of Transact-SQL code can be performed through a single statement that executes the code in a procedure, rather than by sending hundreds of lines of code over the network. Stored procedures provide better security to your data Users can be granted permission to execute a stored procedure even if they do not have permission to execute the procedure's statements directly. In SQL Server we have different types of stored procedures: System stored procedures User-defined stored procedures Extended stored Procedures System-stored procedures are stored in the master database and these start with a sp_ prefix. These procedures can be used to perform a variety of tasks to support SQL Server functions for external application calls in the system tables Example: sp_helptext [StoredProcedure_Name] User-defined stored procedures are usually stored in a user database and are typically designed to complete the tasks in the user database. While coding these procedures don’t use the sp_ prefix because if we use the sp_ prefix first, it will check the master database, and then it comes to user defined database. Extended stored procedures are the procedures that call functions from DLL files. Nowadays, extended stored procedures are deprecated for the reason it would be better to avoid using extended stored procedures.
存储过程不过是编译成单个执行计划的一组SQL语句。
创建一次，调用n次 减少网络流量
示例:创建存储过程
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
想想这样的情况，
You have a database with data. There are a number of different applications needed to access that central database, and in the future some new applications too. If you are going to insert the inline database queries to access the central database, inside each application's code individually, then probably you have to duplicate the same query again and again inside different applications' code. In that kind of a situation, you can use stored procedures (SPs). With stored procedures, you are writing number of common queries (procedures) and store them with the central database. Now the duplication of work will never happen as before and the data access and the maintenance will be done centrally.
注意:
In the above situation, you may wonder "Why cannot we introduce a central data access server to interact with all the applications? Yes. That will be a possible alternative. But, The main advantage with SPs over that approach is, unlike your data-access-code with inline queries, SPs are pre-compiled statements, so they will execute faster. And communication costs (over networks) will be at a minimum. Opposite to that, SPs will add some more load to the database server. If that would be a concern according to the situation, a centralized data access server with inline queries will be a better choice.
在DBMS中，存储过程是一组具有指定名称的SQL语句，这些SQL语句以编译后的形式存储在数据库中，这样就可以由许多程序共享。
存储过程的使用在
提供对数据的受控访问(最终用户只能输入或更改数据，但不能编写过程) 确保数据完整性(数据将以一致的方式输入)和 提高工作效率(存储过程的语句只需编写一次)
“什么是存储过程”已经在这里的其他帖子中回答过了。我将要发布的是一种不太为人所知的使用存储过程的方法。它对存储过程进行分组或对存储过程进行编号。
语法参考
； 按此编号
可选整数，用于对同名过程进行分组。可以使用一个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
Use
exec FirstTest 10
exec FirstTest;2 20,30
结果
另一个尝试
CREATE Procedure SecondTest;2
(
@InputA INT,
@InputB INT
)
AS
BEGIN
SELECT 'A' + CONVERT(VARCHAR(10),@InputA)+ CONVERT(VARCHAR(10),@InputB)
END
GO
结果
Msg 2730，级别11，状态1，程序SecondTest，行1[批量启动行3] 无法创建组号为2的过程“SecondTest”，因为数据库中当前不存在同名且组号为1的过程。 必须先执行CREATE PROCEDURE 'SecondTest';
引用:
使用数字语法创建过程 在SQL Server中编号的存储过程 对存储过程进行分组- sqlmag .
谨慎
对过程进行分组后，就不能单独删除它们了。 此功能可能会在Microsoft SQL Server的未来版本中删除。
对于简单的,
存储过程是存储程序，存储在数据库中的程序或函数。
每个存储的程序都包含一个由SQL语句组成的主体。该语句可以是由几个用分号(;)分隔的语句组成的复合语句。
CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
SET @x = 0;
REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END;
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
这是我从essential.com上学到的，非常有用。
存储过程将帮助您在服务器中编写代码。您可以传递参数并查找输出。
create procedure_name (para1 int,para2 decimal)
as
select * from TableName
在存储过程中，语句只被写入一次，从而减少了客户端和服务器之间的网络流量。 我们也可以避免Sql注入攻击。
Incase if you are using a third party program in your application for processing payments, here database should only expose the information it needed and activity that this third party has been authorized, by this we can achieve data confidentiality by setting permissions using Stored Procedures. The updation of table should only done to the table it is targeting but it shouldn't update any other table, by which we can achieve data integrity using transaction processing and error handling. If you want to return one or more items with a data type then it is better to use an output parameter. In Stored Procedures, we use an output parameter for anything that needs to be returned. If you want to return only one item with only an integer data type then better use a return value. Actually the return value is only to inform success or failure of the Stored Procedure.
序言:SQL92标准创建于1992年，并由Firebase DB推广。该标准引入了“存储过程”。
** 透传查询:一个字符串(通常通过编程连接)，计算为语法正确的SQL语句，通常在服务器层生成(在PHP、Python、PERL等语言中)。然后将这些语句传递到数据库。 **
** 触发器:设计用于响应数据库事件(通常是DML事件)而触发的一段代码，通常用于强制数据完整性。 **
解释什么是存储过程的最好方法是解释执行DB逻辑的传统方式(即不使用存储过程)。
创建系统的传统方式是使用“直通查询”，并且可能在DB中有触发器。 几乎所有不使用存储过程的人都会使用一个叫做"直通查询"的东西
随着存储过程的现代约定，触发器和“透传查询”一起成为传统。
存储过程的优点是:
They can be cached as the physical text of the Stored Procedure never changes. They have built in mechanisms against malicious SQL injection. Only the parameters need be checked for malicious SQL injection saving a lot of processor overhead. Most modern database engines actually compile Stored Procedures. They increase the degree of abstraction between tiers. They occur in the same process as the database allowing for greater optimisation and throughput. The entire workflow of the back end can be tested without client side code. (for example the Execute command in Transact SQL or the CALL command in MySQL). They can be used to enhance security because they can be leveraged to disallow the database to be accessed in a way that is inconsistent with how the system is designed to work. This is done through the database user permission mechanism. For example you can give users privileges only to EXECUTE Stored Procedures rather that SELECT, UPDATE etc privileges. No need for the DML layer associated with triggers. ** Using so much as one trigger, opens up a DML layer which is very processor intensive **
总之，在创建一个新的SQL数据库系统时，没有理由使用直通查询。
值得一提的是，在已经使用触发器或直通查询的遗留系统中使用存储过程是完全安全的;这意味着从遗留过程迁移到存储过程是非常容易的，而且这样的迁移不会使系统长时间停机。
create procedure <owner>.<procedure name><param> <datatype>
As
<body>
存储过程是将数据访问集中在一点上的SQL语句组。适用于在一个镜头中执行多个操作。
推荐文章
- 我如何在T-SQL用逗号格式化一个数字?
- LEFT OUTER JOIN如何返回比左表中存在的记录更多的记录?
- 如何用SQL语句计算百分比
- Postgres唯一约束与索引
- SQL Server动态PIVOT查询?
- 如何等待2秒?
- SQL Server: CROSS JOIN和FULL OUTER JOIN的区别是什么?
- MySQL对重复键更新在一个查询中插入多行
- varchar和nvarchar SQL Server数据类型之间的主要性能差异是什么?
- 向现有表添加主键
- 使用电子邮件地址为主键?
- MySQL:如何复制行，但改变几个字段?
- 不能删除或更新父行:外键约束失败
- MongoDB在v4之前不兼容ACID意味着什么?
- SQL WHERE ID IN (id1, id2，…idn)
