SQL必知必会-存储过程

存储过程

迄今为止，我们使用的大多数SQL语句都是针对一个或多个表的单条语句。并非所有操作都这么简单，经常会有一些非常复杂的操作需要多条语句才能完成。
例子：
为了处理订单，必须核对以保证库存中有相应的物品。
如果物品有库存，需要预定，不再出售给别的人，并且减少物品数据以反映正确的库存量。
库存中没有的物品需要订购，这需要与供应商进行某种交互。
关于哪些物品入库（并且可以立即发货）和哪些物品退订，需要通知相应的顾客。
执行这个处理需要针对许多表的多条 SQL 语句。此外，需要执行的具体 SQL 语句及其次序也不是固定的，它们可能会根据物品是否在库存中而变化。
存储过程就是为以后使用而保存的一条或多条 SQL 语句。可将其视为批文件，虽然它们的作用不仅限于批处理。

为什么要使用存储过程

1.通过把处理封装在一个易用的单元中，可以简化复杂的操作（如前面例子所述）。
2.由于不要求反复建立一系列处理步骤，因而保证了数据的一致性。如果所有开发人员和应用程序都使用同一存储过程，则所使用的代码都是相同的。
3.这一点的延伸就是防止错误。需要执行的步骤越多，出错的可能性就越大。防止错误保证了数据的一致性。
4.简化对变动的管理。如果表名、列名或业务逻辑（或别的内容）有变化，那么只需要更改存储过程的代码。使用它的人员甚至不需要知道这些变化。
5.这一点的延伸就是安全性。通过存储过程限制对基础数据的访问，减少了数据讹误（无意识的或别的原因所导致的数据讹误）的机会。
6.因为存储过程通常以编译过的形式存储，所以 DBMS 处理命令的工作较少，提高了性能。
7.存在一些只能用在单个请求中的 SQL 元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。

换句话说，使用存储过程有三个主要的好处，即简单、安全、高性能。显然，它们都很重要。不过，在将 SQL 代码转换为存储过程前，也必须知道它的一些缺陷。

1.不同 DBMS 中的存储过程语法有所不同。事实上，编写真正的可移植存储过程几乎是不可能的。不过，存储过程的自我调用（名字以及数据如何传递）可以相对保持可移植。因此，如果需要移植到别的 DBMS ，至少客户端应用代码不需要变动。
2.一般来说，编写存储过程比编写基本 SQL 语句复杂，需要更高的技能，更丰富的经验。因此，许多数据库管理员把限制存储过程的创建作
为安全措施（主要受上一条缺陷的影响）。

执行存储过程

执行存储过程的 SQL 语句很简单，即EXECUTE。EXECUTE接受存储过程名和需要传递给它的任何参数。请看下面的例子：

EXECUTE AddNewProduct( 'JTS01',
'Stuffed Eiffel Tower',
6.49,
'Plush stuffed toy with the text La->Tour Eiffel in red white and blue' );

这里执行一个名为AddNewProduct的存储过程，将一个新产品添加到Products表中。AddNewProduct有四个参数，分别是：供应商 ID （Vendors表的主键）、产品名、价格和描述。这 4 个参数匹配存储过程中 4 个预期变量（定义为存储过程自身的组成部分）。此存储过程将新行添加到Products表，并将传入的属性赋给相应的列。

我们注意到，在Products表中还有另一个需要值的列prod_id列，它是这个表的主键。为什么这个值不作为属性传递给存储过程？要保证恰当地
生成此 ID ，最好是使生成此 ID 的过程自动化（而不是依赖于最终用户的输入）。这也是这个例子使用存储过程的原因。以下是存储过程所完成
的工作：

1.验证传递的数据，保证所有 4 个参数都有值；
2.生成用作主键的唯一 ID ；
3.将新产品插入Products表，在合适的列中存储生成的主键和传递的数据。

创建存储过程

Oracle 版本：

CREATE PROCEDURE MailingListCount (
ListCount OUT INTEGER
)
IS
v_rows INTEGER;
BEGIN
SELECT COUNT(*) INTO v_rows
FROM Customers
WHERE NOT cust_email IS NULL;
ListCount := v_rows;
END;

这个存储过程有一个名为ListCount的参数。此参数从存储过程返回一个值而不是传递一个值给存储过程。关键字OUT用来指示这种行为。
Oracle 支持IN（传递值给存储过程）、OUT（从存储过程返回值，如这里）、INOUT（既传递值给存储过程也从存储过程传回值）类型的参数。存储过程的代码括在BEGIN和END语句中，这里执行一条简单的SELECT语句，它检索具有邮件地址的顾客。然后用检索出的行数设
置ListCount（要传递的输出参数）。

调用 Oracle 例子可以像下面这样：

var ReturnValue NUMBER
EXEC MailingListCount(:ReturnValue);
SELECT ReturnValue;

这段代码声明了一个变量来保存存储过程返回的任何值，然后执行存储过程，再使用SELECT语句显示返回的值。

MYSQL存储过程的创建和调用

存储过程就是具有名字的一段代码，用来完成一个特定的功能。
创建的存储过程保存在数据库的数据字典中。

CREATE
    [DEFINER = { user | CURRENT_USER }]
　PROCEDURE sp_name ([proc_parameter[,...]])
    [characteristic ...] routine_body
 
proc_parameter:
    [ IN | OUT | INOUT ] param_name type
 
characteristic:
    COMMENT 'string'
  | LANGUAGE SQL
  | [NOT] DETERMINISTIC
  | { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
  | SQL SECURITY { DEFINER | INVOKER }
 
routine_body:
　　Valid SQL routine statement
 
[begin_label:] BEGIN
　　[statement_list]
　　　　……
END [end_label]

MYSQL 存储过程中的关键语法

声明语句结束符，可以自定义:

DELIMITER $$
或
DELIMITER //

声明存储过程:

CREATE PROCEDURE demo_in_parameter(IN p_in int)       

存储过程开始和结束符号:

BEGIN .... END    

变量赋值:

SET @p_in=1  

变量定义:

DECLARE l_int int unsigned default 4000000; 

创建mysql存储过程、存储函数:

create procedure 存储过程名(参数)

存储过程体:

create function 存储函数名(参数)