PLSqL入门与精通（第18章：存储过程具体例子）

这次解说一下存储过程的具体例子。

存储过程是描述一系列处理的命名的PL/SQL块。可以用在各种场合。

本次简介一个具体例子：

例如，在某个公司的业务中，管理着产品的库存。因此，有“库存”的表。

SQL CREATE TABLE库存（
2 产品名称 VARCHAR2（20）PRIMARY KEY，
3 库存数 NUMBER）；

表创建成功。

数据登记：电视的库存数为10，收音机的库存数为5。

SQL>INSERT INTO 库存 VALUES（'电视’，10）；
创建了一行。
SQL>INSERT INTO 库存 VALUES（‘收音机’，5）； ‘哈哈，大家不用收音机了，暴露年龄了’
创建了一行。
SQL> COMMIT;

提交完成。

SQL>SELECT FROM 库存；
产品名 库存数
'------------------------------
电视 10
收音机 5

订单表：

订单尽可能简单地做成了以下的：

SQL CREATE TABLE 订单
2（订单ID NUMBER PRIMARY KEY，
3 产品名称 VARCHAR2（20），
4 订购数量 NUMBER）；

表创建成功。

这家公司接受订单后，会在订单上登记数据，同时将库存表中该产品的库存减少对应的订货量。

但是，订单数量比库存数量多的情况下，会出现库存不足的错误。

吧这一系列的订单受理处理交给 存储过程：store Project。

程序名称为“PROC订单受理”。
需要交给这个程序的信息，从表的定义是“产品名”和“订购数”。
因为“订单ID”是主关键字，所以我想从顺序对象中获取值。
创建排序对象。名字是以“SEQ订购ID”为名制作的。

SQL CREATE SEQUEENCE SEQ 订单ID；
创建了顺序。
那么，马上制作程序「PROC订单受理」。
尽量简单：

1 CREATE OR REPLACE PROCEDURE PROC接受订单
2（P产品名 IN 订单.产品名%TYPE，P-订单数 IN 订单.订单数%TYPE）
3 IS
4 V库存数 库存.库存数%TYPE；
5 BEGIN
6 SELECT 库存数 INTO V库存数 FROM 库存 WHERE 产品名=P产品名 FOR UPATE；
7 IF P订购数量>V库存数 THEN
8 RAISE APPLICATION ERROR（-2000，“库存不足错误：’|P产品名称）；
9 END IF;
10 UPDATE 库存
11 SET 库存数=库存数-P订单数
12 WHERE 产品名称＝P产品名称；
13 INSERT INTO订单（订单ID、产品名称、订单数量）
14 VALES（SEQ订单ID.NEXTVAL，P产品名称，P订购数量）；
15 COMMIT;
16 EXCEPTION
17 WHEN NO_DATA_FOUND THEN
18 RAISE APPLICATION ERROR（-2001，“错误产品名称”）；
19 END;

SQL> /

程序已创建。

在上述例子中，在第6行中将该产品的库存数作为变量取得。

在这个SELECT文上加上FOR UPDATE句是为了防止多个交易同时处理同一产品。

通过在SELECT上加上FOR UPDATE，对针对的对象记录进行枷锁。
从而可以防止多个请求同时处理，保证交易的整合性。

在第7～9行中，将取得的库存数和订单数进行比较，如果订单数量多的话，将发生库存不足的错误。
因此，下面第10行以后是库存不足时的处理。
在第10～12行中，库存表中该产品的库存数量减少了下单数量。

第13～14行，在订单表上登记了订单内容。
产品名和订单数是从这个过程的参数中获取的，并且订单号码是从顺序“SEQ订单ID”中获取的值。

第15行的“COMMIT”并不是在这个过程中更新的确定，而是交易的确定。
因此，如果在调用过程的阶段发生了交易（数据更新等），也包含在内是确定的。

因此，也有不在这个程序中进行COMMIT，而是在调用源进行的想法。

在执行部（第6～15行）中，第6行的SELECT INTO语句为0个时有可能发生系统错误。
也就是说，参数指定的产品名称是错误的产品名称。
为此，在第17～18行中准备了NO DATA FOUND例外处理程序，SELECT INTO文中0件时出现了“是错误的产品名称”的错误。

那么我们试试。

我们点3台电视吧。电视的库存是10台，所以不会库存不足。

SQL> BEGIN
2 PROC 接受订单（'电视’，3）；
3 END;
4 /

PL/SQL过程成功完成。
根据这个，电视的订购被登记。

SQL>SELECT FROM 订单；

订购ID 产品名 订购数
‘’-----------------------------------------
1 电视 3

另外，电视的库存数是7。

SQL>SELECT FROM 库存；
产品名 库存数
'------------------------------
电视 7
收音机 5

收音机的库存数是5台，如果订购8台收音机，库存不足。

SQL> BEGIN
2 PROC 接受订单（'收音机’，8）；
3 END;
4 /

BEGIN
行1发生错误。:
ORA-20000：库存不足错误：收音机
ORA-06512: "SCOTT.PROC_受理订货”，行8
ORA-06612:行2
正如上面的结果一样，是库存不足的错误。

那么订购四台就可以了。
1 BEGIN
2 PROC 接受订单（‘收音机’，4）；
3 END;

SQL> /
PL/SQL过程成功完成。

根据这个订单，有4台收音机的订单。

SQL>SELECT FROM 订单；

订单ID 产品名 订购数
'----------------------------------------
1 台电视 3
2 收音机 4

而且，收音机的库存数量减少了一台。

SQL>SELECT FROM 库存；

产品名 库存数
'------------------------------

电视 7
广播 1

这次作为具体的例子介绍了像过程一样的处理。

本次到此为止，谢谢。