SQL 什么是存储过程？

Question

提问by George Stocker

What is a "stored procedure"and how do they work?

什么是“存储过程”，它们是如何工作的？

What is the make-up of a stored procedure (things each musthave to be a stored procedure)?

存储过程的组成是什么（每个都必须是存储过程）？

Answer 1

采纳答案by JoshBerke

Stored procedures are a batch of SQL statements that can be executed in a couple of ways. Most major DBMs support stored procedures; however, not all do. You will need to verify with your particular DBMS help documentation for specifics. As I am most familiar with SQL Server I will use that as my samples.

存储过程是一组可以通过多种方式执行的 SQL 语句。大多数主要的 DBM 都支持存储过程；然而，并非所有人都这样做。您需要使用特定的 DBMS 帮助文档来验证细节。由于我最熟悉 SQL Server，因此我将使用它作为我的示例。

To create a stored procedure the syntax is fairly simple:

创建存储过程的语法相当简单：

CREATE PROCEDURE <owner>.<procedure name>

     <Param> <datatype>

AS

     <Body>

So for example:

例如：

CREATE PROCEDURE Users_GetUserInfo

    @login nvarchar(30)=null

AS

    SELECT * from [Users]
    WHERE ISNULL(@login,login)=login

A benefit of stored procedures is that you can centralize data access logic into a single place that is then easy for DBA's to optimize. Stored procedures also have a security benefit in that you can grant execute rights to a stored procedure but the user will not need to have read/write permissions on the underlying tables. This is a good first step against SQL injection.

存储过程的一个好处是您可以将数据访问逻辑集中到一个地方，然后 DBA 可以轻松地进行优化。存储过程还具有安全优势，因为您可以向存储过程授予执行权限，但用户不需要对基础表具有读/写权限。这是对抗 SQL 注入的良好第一步。

Stored procedures do come with downsides, basically the maintenance associated with your basic CRUDoperation. Let's say for each table you have an Insert, Update, Delete and at least one select based on the primary key, that means each table will have 4 procedures. Now take a decent size database of 400 tables, and you have 1600 procedures! And that's assuming you don't have duplicates which you probably will.

存储过程确实有缺点，基本上是与基本CRUD操作相关的维护。假设每个表都有一个插入、更新、删除和至少一个基于主键的选择，这意味着每个表将有 4 个过程。现在使用一个包含 400 个表的体面大小的数据库，并且您有 1600 个程序！那是假设您没有可能会有的重复项。

This is where using an ORMor some other method to auto generate your basic CRUD operations has a ton of merit.

这就是使用ORM或其他一些方法来自动生成基本 CRUD 操作的优点。

Answer 2

回答by sidhewsar

A stored procedure is a set of precompiled SQL statements that are used to perform a special task.

存储过程是一组用于执行特殊任务的预编译 SQL 语句。

Example: If I have an Employeetable

示例：如果我有一张Employee桌子

Employee ID  Name       Age  Mobile
---------------------------------------
001          Sidheswar  25   9938885469
002          Pritish    32   9178542436

First I am retrieving the Employeetable:

首先我检索Employee表：

Create Procedure Employee details
As
Begin
    Select * from Employee
End

To run the procedure on SQL Server:

要在 SQL Server 上运行该过程：

Execute   Employee details

--- (Employee details is a user defined name, give a name as you want)

Then second, I am inserting the value into the Employee Table

其次，我将值插入到员工表中

Create Procedure employee_insert
    (@EmployeeID int, @Name Varchar(30), @Age int, @Mobile int)
As
Begin
    Insert Into Employee
    Values (@EmployeeID, @Name, @Age, @Mobile)
End

To run the parametrized procedure on SQL Server:

要在 SQL Server 上运行参数化过程：

Execute employee_insert 003,'xyz',27,1234567890

  --(Parameter size must be same as declared column size)

Example: @Name Varchar(30)

例子： @Name Varchar(30)

In the Employeetable the Namecolumn's size must be varchar(30).

在Employee表中，Name列的大小必须是varchar(30)。

Answer 3

回答by Kanwar Singh

A stored procedure is a group of SQL statements that has been created and stored in the database. A stored procedure will accept input parameters so that a single procedure can be used over the network by several clients using different input data. A stored procedures will reduce network traffic and increase the performance. If we modify a stored procedure all the clients will get the updated stored procedure.

存储过程是一组已创建并存储在数据库中的 SQL 语句。存储过程将接受输入参数，以便多个客户端使用不同的输入数据通过网络使用单个过程。一个存储过程将减少网络流量并提高性能。如果我们修改一个存储过程，所有客户端都会得到更新的存储过程。

Sample of creating a stored procedure

创建存储过程的示例

CREATE PROCEDURE test_display
AS
    SELECT FirstName, LastName
    FROM tb_test;

EXEC test_display;

Advantages of using stored procedures

使用存储过程的优点

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-definedstored 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 usethe sp_prefix because if we use the sp_prefix first, it will check the master database, and then it comes to user defined database.
Extendedstored 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 代码，则存储过程可以更快。它们在第一次执行时被解析和优化，并且存储过程的编译版本保留在内存缓存中供以后使用。这意味着存储过程不需要在每次使用时重新解析和重新优化，从而大大缩短了执行时间。
存储过程可以减少网络流量。
需要数百行 Transact-SQL 代码的操作可以通过在过程中执行代码的单个语句来执行，而不是通过网络发送数百行代码。
存储过程为您的数据提供更好的安全性
用户可以被授予执行存储过程的权限，即使他们没有直接执行过程语句的权限。
在 SQL Server 中，我们有不同类型的存储过程：
- 系统存储过程
- 用户定义的存储过程
- 扩展存储过程
系统存储过程存储在 master 数据库中，这些过程以sp_前缀开头。这些过程可用于执行各种任务，以支持系统表中外部应用程序调用的 SQL Server 函数
示例：sp_helptext [StoredProcedure_Name]
用户定义的存储过程通常存储在用户数据库中，通常旨在完成用户数据库中的任务。尽管编码这些程序不使用的sp_前缀，因为如果我们使用的sp_第一个前缀，它会检查主数据库，然后它涉及到用户定义的数据库。
扩展存储过程是从 DLL 文件调用函数的过程。如今，不推荐使用扩展存储过程，因为最好避免使用扩展存储过程。

Answer 4

回答by Dave Swersky

Generally, a stored procedure is a "SQL Function." They have:

通常，存储过程是“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

This is a T-SQL focused example. Stored procedures can execute most SQL statements, return scalar and table-based values, and are considered to be more secure because they prevent SQL injection attacks.

这是一个以 T-SQL 为重点的示例。存储过程可以执行大多数 SQL 语句，返回标量和基于表的值，并且被认为更安全，因为它们可以防止 SQL 注入攻击。

Answer 5

回答by Supun Wijerathne

Think of a situation like this,

想想这样的情况，

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.

您有一个包含数据的数据库。
访问该中央数据库需要许多不同的应用程序，将来也会有一些新的应用程序。
如果您打算在每个应用程序的代码中单独插入内联数据库查询以访问中央数据库，那么您可能必须在不同应用程序的代码中一次又一次地复制相同的查询。
在这种情况下，您可以使用存储过程 (SP)。使用存储过程，您可以编写一些常见查询（过程）并将它们存储在中央数据库中。
现在不再像以前那样重复工作，数据访问和维护将集中进行。

NOTE:

笔记：

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.

在上述情况下，您可能想知道“为什么我们不能引入一个中央数据访问服务器来与所有应用程序交互？是的。这将是一个可能的替代方案。但是，
与这种方法相比，SP 的主要优势在于，与带有内联查询的数据访问代码不同，SP 是预编译的语句，因此它们的执行速度更快。并且通信成本（通过网络）将降至最低。
与此相反，SP 会给数据库服务器增加一些负载。如果根据情况这会是一个问题，那么具有内联查询的集中式数据访问服务器将是更好的选择。

Answer 6

回答by MADHAIYAN M

A stored procedure is mainly used to perform certain tasks on a database. For example

存储过程主要用于对数据库执行某些任务。例如

Get database result sets from some business logic on data.
Execute multiple database operations in a single call.
Used to migrate data from one table to another table.
Can be called for other programming languages, like Java.

从数据的一些业务逻辑中获取数据库结果集。
在一次调用中执行多个数据库操作。
用于将数据从一张表迁移到另一张表。
可以被其他编程语言调用，比如 Java。

Answer 7

回答by venkat

A stored procedure is used to retrieve data, modify data, and delete data in database table. You don't need to write a whole SQL command each time you want to insert, update or delete data in an SQL database.

存储过程用于在数据库表中检索数据、修改数据和删除数据。每次要在 SQL 数据库中插入、更新或删除数据时，您都不需要编写完整的 SQL 命令。

Answer 8

回答by user5723819

A stored procedure is nothing but a group of SQL statements compiled into a single execution plan.

存储过程只不过是一组编译成单个执行计划的 SQL 语句。

Create once time and call it n number of times
It reduces the network traffic

创建一次并调用它 n 次
它减少了网络流量

Example: creating a stored procedure

示例：创建存储过程

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

Alter or modify a stored procedure:

更改或修改存储过程：

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 or delete a stored procedure:

删除或删除存储过程：

DROP PROCEDURE GetEmployee

Answer 9

回答by Kedarnath M S

A stored procedure is a precompiled set of one or more SQL statements which perform some specific task.
A stored procedure should be executed stand alone using EXEC
A stored procedure can return multiple parameters
A stored procedure can be used to implement transact

存储过程是一组预编译的一个或多个执行某些特定任务的 SQL 语句。
存储过程应该单独执行 EXEC
一个存储过程可以返回多个参数
可以使用存储过程来实现事务

Answer 10

回答by LCJ

"What is a stored procedure" is already answered in other posts here. What I will post is one less known way of using stored procedure. It is grouping stored proceduresor numbering stored procedures.

“什么是存储过程”已在此处的其他帖子中得到解答。我将发布的是一种鲜为人知的使用存储过程的方法。它是grouping stored procedures或numbering stored procedures。

Syntax Reference

语法参考

; numberas per this

; number按照这个

An optional integer that is used to group procedures of the same name. These grouped procedures can be dropped together by using one DROP PROCEDURE statement

用于对同名过程进行分组的可选整数。可以使用一个 DROP PROCEDURE 语句将这些分组的过程删除在一起

Example

例子

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

Result

结果

Another Attempt

另一种尝试

CREATE Procedure SecondTest;2
(
     @InputA INT,
    @InputB INT
)
AS 
BEGIN
    SELECT 'A' + CONVERT(VARCHAR(10),@InputA)+ CONVERT(VARCHAR(10),@InputB)
END
GO

Result

结果

Msg 2730, Level 11, State 1, Procedure SecondTest, Line 1 [Batch Start Line 3] Cannot create procedure 'SecondTest' with a group number of 2 because a procedure with the same name and a group number of 1 does not currently exist in the database. Must execute CREATE PROCEDURE 'SecondTest';1 first.

消息 2730，级别 11，状态 1，过程 SecondTest，第 1 行 [批处理开始行 3] 无法创建组号为 2 的过程“SecondTest”，因为当前不存在具有相同名称且组号为 1 的过程数据库。必须先执行 CREATE PROCEDURE 'SecondTest';1。

References:

参考资料：

CAUTION

警告

After you group the procedures, you can't drop them individually.
This feature may be removed in a future version of Microsoft SQL Server.

将过程分组后，您不能单独删除它们。
此功能可能会在 Microsoft SQL Server 的未来版本中删除。

SQL 什么是存储过程？

提问by George Stocker

采纳答案by JoshBerke

回答by sidhewsar

回答by Kanwar Singh

回答by Dave Swersky

回答by Supun Wijerathne

回答by MADHAIYAN M

回答by venkat

回答by user5723819

回答by Kedarnath M S

回答by LCJ

相关推荐

最近更新

标签

SQL 什么是存储过程？

提问by George Stocker

采纳答案by JoshBerke

回答by sidhewsar

回答by Kanwar Singh

回答by Dave Swersky

回答by Supun Wijerathne

回答by MADHAIYAN M

回答by venkat

回答by user5723819

回答by Kedarnath M S

回答by LCJ

相关推荐

SQL sql统计io扫描计数说明

SQL 如何使用“as”关键字为 Oracle 中的表设置别名？

如何在 SQL Server Management Studio 中将列值设置为 NULL？

SQL ORA-01843 不是有效月份 - 比较日期

相关推荐

最近更新

标签