19 使用存储过程

1.存储过程

存储过程是为以后使用而保存的一条或多条SQL语句

可以单独编写每条SQL语句，并根据结果有条件地执行其他语句。

2.为什么要使用存储过程

(1)通过把处理封装在一个易用的单元中，可以简化复杂的操作；

(2)由于不要求反复建立在一系列处理步骤，因而保证了数据的一致性；

(3)简化对变动的管理；

(4)因为存储过程通常以编译过的形式存储，所以DBMS处理命令所需的工作量少，提高了性能。

(5)存储在一些只能用在单个请求的SQL元素和特性，存储过程可以使用它们来编写功能更强更灵活的代码。

缺点：

不同DBMS中的存储过程语法有所不同；

一般编写存储过程比编写基本SQL语句复杂，需要更高的近，更丰富的经验。

3.执行存储过程

使用EXECUTE执行存储过程的SQL语句。

输入：

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的主键）、产品名、价格和描述。此存储过程将新行添加到Products表中，并将传入的属性赋给相应的列。

Products表的主键prod_id列不作为属性传递给存储过程。要保证恰当地生成ID，且ID的生成过程自动化。

存储过程所完成的工作：

²  验证传递的数据，保证所有4个参数都有值；

²  生成用作主键的唯一ID；

²  将新产品插入products表，在合适的列中存储生成的主键和传递的数据。

对于DBMS可能包括以下的执行选择：

u  参数可选，具有不提供参数的默认值；

u  不按次序给出参数，以“参数=值”的方式给出参数值；

u  输出参数，允许存储过程正在执行的应用程序中更新所用的参数；

u  用SELECT语句检索数据；

u  返回代码，允许存储过程返回一个值到正在执行的应用程序。

4.创建存储过程

一个简单的存储过程例子，对邮件发送清单中具有邮件地址的顾客进行计数。

——仅适用于SQL Server版本

输入：

CREATE PROCEDURE MailingListCount

AS

DECLARE @cnt INTEGER

SELECT @cnt = COUNT(*)

FROM Customers

WHERE NOT cust_email IS NULL;

RETURN @cnt;

调用：

DECLARE @ReturnValue INT

EXECUTE @ReturnValue=MailingListCount;

SELECT @ReturnValue;

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

CREATE PROCEDURE NewOrder @cust_id CHAR(10)

AS

-- Declare variable for order number

DECLARE @order_num INTEGER

-- Get current highest order number

SELECT @order_num = MAX(order_num)

FROM Orders

-- Determine next order number

SELECT @order_num = @order_num + 1

-- Insert new order

INSERT INTO Orders(order_num, order_date, cust_id)

VALUES(@order_num, GETDATE(), @cust_id)

-- Return order number

RETURN @order_num;

此存储过程在Orders表中创建一个新订单，它只有一个参数，即下订单的顾客ID。订单号和订单日期这两列在存储过程中自动生成。

代码首先声明一个局部变量来存储订单号

检索当前最大的订单号并增加1

用INSERT语句插入由新生成的订单号、当前系统日期和传递的顾客ID组成的订单。

最后，用RETURN @order_num返回订单号。

 

转载于:https://www.cnblogs.com/Sumomo0516/p/6131599.html