什么是存储过程?

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

存储过程是由什么组成的(每个东西都必须是存储过程)?

当前回答

想想这样的情况，

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.

2016-05-22 06:13:18

其他回答

一般来说，存储过程是一个“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注入攻击。

2009-01-19 22:45:29

存储过程将帮助您在服务器中编写代码。您可以传递参数并查找输出。

create procedure_name (para1 int,para2 decimal)
as
select * from TableName

2018-02-07 10:41:05

存储过程是可以以多种方式执行的一批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操作的优点所在。

2009-01-19 22:57:15

create procedure <owner>.<procedure name><param> <datatype>
As
    <body>

存储过程是将数据访问集中在一点上的SQL语句组。适用于在一个镜头中执行多个操作。

2023-01-23 02:50:04

在存储过程中，语句只被写入一次，从而减少了客户端和服务器之间的网络流量。我们也可以避免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.

2018-11-28 12:04:07

什么是存储过程?

推荐文章

最新文章

标签