序言: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数据库系统时，没有理由使用直通查询。

值得一提的是，在已经使用触发器或直通查询的遗留系统中使用存储过程是完全安全的;这意味着从遗留过程迁移到存储过程是非常容易的，而且这样的迁移不会使系统长时间停机。

对于简单的,

存储过程是存储程序，存储在数据库中的程序或函数。

每个存储的程序都包含一个由SQL语句组成的主体。该语句可以是由几个用分号(;)分隔的语句组成的复合语句。

CREATE PROCEDURE dorepeat(p1 INT)
BEGIN
  SET @x = 0;
  REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
END;

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

从数据上的一些业务逻辑获取数据库结果集。 在一次调用中执行多个数据库操作。 用于将数据从一个表迁移到另一个表。 可用于其他编程语言，如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语句和过程逻辑的命名集合，即编译、验证并存储在服务器数据库中。存储过程通常被视为其他数据库对象，并通过服务器安全机制进行控制。

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