什么是存储过程? [英] What is a stored procedure?
问题描述
什么是存储过程"，它们是如何工作的?
What is a "stored procedure" and how do they work?
存储过程的组成是什么(每个必须必须是存储过程)?
What is the make-up of a stored procedure (things each must have to be a stored procedure)?
推荐答案
存储过程是一组可以通过多种方式执行的 SQL 语句.大多数主要的 DBM 都支持存储过程；然而，并非所有人都这样做.您需要使用特定的 DBMS 帮助文档来验证细节.由于我最熟悉 SQL Server，因此我将使用它作为我的示例.
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.
创建存储过程的语法相当简单:
To create a stored procedure the syntax is fairly simple:
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 注入的良好开端.
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.
存储过程确实有缺点，基本上是与基本CRUD 操作.假设每个表都有一个插入、更新、删除和至少一个基于主键的选择，这意味着每个表将有 4 个过程.现在使用一个包含 400 个表的体面大小的数据库，并且您有 1600 个程序！那是假设您没有重复项，而您可能会这样做.
Stored procedures do come with downsides, basically the maintenance associated with your basic CRUD operation. 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.
这是使用 ORM 或其他一些自动生成基本 CRUD 操作的方法有很多优点.
This is where using an ORM or some other method to auto generate your basic CRUD operations has a ton of merit.
这篇关于什么是存储过程?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
