PLSQL入门与精通（第20章：复杂的连续编号问题）

这次，使用顺序表来制作编号的处理过程。

这个过程是一个过程，通过发号单位呼出，则返回发号单位的下一个编号。

因此，模式具有IN的参数（发号单位）和OUT的参数（生成的顺序号码）。

程序名作为「PROC_顺序号码」，程序如下：

1 CREATE OR REPLACE PROCEDURE PROC_顺序编号
2（P发号单位IN VARCHAR2，P编号OUT NUMBER）–P编号为OUT的参数
3 IS
4 V_已编号 顺序.编号%TYPE；
5 BEGIN
6 -将指定的发号单位获取至1足组件OUT参数
7 UPDATE 顺序
8 SET 编号 = 编号 + 1
9 WHERE 发号单位＝ P_发号单位
10 RETURNING 编号 INTO P_编号；
11 --更新行数为0行的话是新的发号单位，所以先发1号，然后在顺序表中新登记发号单位
12 IF SQL%ROWCOUNT = 0 THEN
13 P_编号：=1；
14 INSERT INTO 顺序（发号单位，编号）VALUES（P发号单位，1）；
15 END IF;
16* END;
SQL> /

程序已创建。

在第7-10行中，将UPDATE指定的发号单位的号码加上“+1”。
并且，在第10行的RETURNING句中，将加法后的编号列代入OUT的变量P_编号中。
顺便说一下，这个RETURNING句：
一般来说，如果不知道UPDATE后的值是什么样的值，就再次使用SELECT INTO取得值，但是使用RETURNING句的话，就可以直接在UPDAATE之后直接取得更新后的值，非常方便。
另外，执行的SQL的数量也不需要SELECT，性能也会提高。

12-15行UPDATE行数为0行时，表示为新的发号单位。（原来没有记录的意思）
所以先发一号，再在顺序表上登记新的发号单位。

需要注意的是，这个编号的逻辑还有其他可能，但是自己组装逻辑时候，即使在多个交易中同时执行，也就是多个事物同时执行的时候，我们要注意不要出现重复编号。
上边例子中的逻辑是发号处理的最初的执行文是UPDATE文，因为默认要触发行排他的锁，所以同时进行多个同一发号单位的发号处理的时候，不可能发出同一个号码。

还有一种实现方式：
首先发行SELECT INTO文，将相应的发号单位的编号获取为变量，然后将加上“+1”，之后把顺序表更新为新号码。
但是，这种情况下，如果确保不出现重复的号，需要在SELECT INTO文中加上FOR UPDATE句进行行锁，阻断其他交易。
如果使用SELECT INTO而没有用FOR UPDATE的话，根据时间的不同，有可能会在多个交易中编号相同，请注意。

像上边的例子那样，如果一开始在UPDATE上进行加算处理的话，因为在执行的时候会加锁，所以不用担心多个交易会取得相同的号码。

让我们马上去验证一下吧：

调用具有OUT参数的过程时，必须要有接受该值的变量。
这里，V_编号变量中存储已编号的值。

SQL> DECLARE
2 V_编号 顺序.编号%TYPE；
3 BEGIN
4 PROC_顺序编号（'订购ID’，V_编号）；
5 DBMS_OUTPUT.PUT_LINE（‘订购ID=’ || V_号码）；
6 PROC_顺序编号（'顾客ID’，V编号）；
7 DBMS_OUTPUT.PUT_LINE（‘客户ID=’ || V_编号）；
8 END;
9 /

订单ID=3
顾客ID=101

PL/SQL过程成功完成。

顺序表上的订购ID本来是2号，所以3号被返回。
同样，顺序表上的顾客ID原本是100号，所以返回了101号。

因为在这个过程中没有进行COMMIT处理，所以如果ROLLBACK的话整个交易都会被取消，结果编号会被返回也不会缺失。

如下：
SQL> ROLLBACK;
回滚完成。

SQL> DECLARE
2 V_编号 顺序.编号%TYPE；
3 BEGIN
4 PROC_顺序编号（'订购ID’，V_编号）；
5 DBMS_OUTPUT.PUT_LINE（‘订购ID=’ || V_号码）；
6 PROC_顺序编号（'顾客ID’，V编号）；
7 DBMS_OUTPUT.PUT_LINE（‘客户ID=’ || V_编号）；
8 END;
9 /

订单ID=3
顾客ID=101
PL/SQL过程成功完成。

因为ROLLBACK了，所以顺序表的更新也被取消，结果显示的值和以前取得值是一样的。

也就是说，可以保证序列号不会断。

因为是简单的测试，为了保证后续能够继续验证，我们把值进行返回。

SQL> ROLLBACK;
回滚完成。

接下来，把上一章（第19章）的例子：「PROC_订单受理程序」内的处理中，变更为从这次的「PROC_顺序号码」程序取得在订单表中INSERT时的订购ID的值。

1 CREATE OR REPLACE PROCEDURE PROC_接受订单
2（P_产品名 IN 订单.产品名%TYPE，P_订单数 IN 订单.订单数%TYPE）
3 IS
4 V_库存数 库存.库存数%TYPE；
5 V_订单ID 订单.订单ID%TYPE；–<<添加>>
6 BEGIN
7 --获取指定产品的库存数
8 SELECT 库存数 INTO V_库存数 FROM 库存 WHERE 产品名 = P_产品名 FOR UPATE；

9 --订单数量超过库存数时出错
10 IF P_订购数量 > V_库存数 THEN
11 RAISE APPLICATION ERROR（-2000，“库存不足错误：’ || P_产品名称）；
12 END IF;

13 --库存足的时候，将库存数量减少订单数量
14 UPDATE 库存
15 SET 库存数 = 库存数 - P_订单数量
16 WERE 产品名称＝P_产品名称；

17 --进一步登记订单表
18 --获取订单ID的值
19 PROC_顺序号（'订购ID’，V_订购ID）；–<<追加>>
20 INSERT INTO 订单（订单ID, 产品名称, 订单数量）
21 VALUES（V_订单ID，P_产品名称，P_订单数量）；–<修改>
22 COMMIT;
23 EXCEPTION
24 WHEN NO_DATA_FOUND THEN
25 RAISE APPLICATION ERROR（-2001，“错误产品名称”）；
26* END;
SQL> /

程序已创建。

这样做的话，通过PROC订单受理程序进行订单处理时，可以保证订单表的主键（订单ID列）的值一定是连续号码。

那么，我们马上登记订单吧。现在订单和库存表如下。
SQL>SELECT * FROM 订单；

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

※这两行是上次订购的内容。

SQL＞SELECT * FROM 库存
2 ;

产品名 库存数
'------------------------------
电视 7
收音机 1

电视机的库存是7台，我来处理订购2台电视的订单。

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

SQL＞SELECT * FROM 订购 ORDER BY 订购ID；
订购ID 产品
定购 数量
'----------------------------------------
1 电视 3
2 收音机 4
3 电视 2 ←这次的订单

SQL>SELECT * FROM 库存；
产品名 库存数
'------------------------------
电视 5 ←库存减少2
收音机 1

从「PROC_订单受理程序」开始订单登记，库存的更新都正常。
而且最重要的是，订单ID的值一定会以连续的值被编号♪

有的时候，表的主键不是简单的一列的主键，也有可能是多列组合的主键。
这种情况下这次的发号方法也有效。

例如，订单表的主键不是“订单ID列”，而是“顾客ID+订单ID”。这就是所谓的复合主键。
在这种情况下，就需要“按照客户进行连续连续发出订单ID”。

因此，在本次的程序中，如果把每个顾客的订购ID作为发号单位来处理的话，每个顾客都可以从1号开始连续发号。

请看下面的例子。

SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 V_客户编号 NUMBER := &客户编号；
3 V_发号单位 VARCHAR2（30）；
4 V_编号 NUMBER；
5 BEGIN
6 V_发号单位：＝’客户编号 ’ || V_顾客编号 || '的订单’；
7 PROC_顺序编号（V_发号单位，V_编号）；
8 DBMS_OUTPUT.PUT_LINE（V_发号单位 || ‘：’ || V_编号）；
9 END;
10 /

请在客户编号中输入值：100
V_顾客编号 NUMBER :=100；
顾客编号100的订单
ID：1 ←客户编号100的第一个订单ID是1

PL/SQL过程成功完成。

继续执行第二个订单：

SQL> R
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 V_客户编号 NUMBER := &客户编号；
3 V_发号单位 VARCHAR2（30）；
4 V_编号 NUMBER；
5 BEGIN
6 V_发号单位：＝’客户编号 ’ || V_顾客编号 || '的订单’；
7 PROC_顺序编号（V_发号单位，V_编号）；
8 DBMS_OUTPUT.PUT_LINE（V_发号单位 || ‘：’ || V_编号）；
9 END;
10 /

请在客户编号中输入值：100
V_顾客编号 NUMBER :=100；
顾客编号100的订单
ID：2 ←客户编号100的第2个订单ID是2

PL/SQL过程成功完成。

第三个订单
SQL> R
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 V_客户编号 NUMBER := &客户编号；
3 V_发号单位 VARCHAR2（30）；
4 V_编号 NUMBER；
5 BEGIN
6 V_发号单位：＝’客户编号 ’ || V_顾客编号 || '的订单’；
7 PROC_顺序编号（V_发号单位，V_编号）；
8 DBMS_OUTPUT.PUT_LINE（V_发号单位 || ‘：’ || V_编号）；
9 END;
10 /

请在客户编号中输入值：100
V_顾客编号 NUMBER :=100；
顾客编号100的订单
ID：3 ←客户编号100的第一个订单ID是3

PL/SQL过程成功完成。

其他客户：
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
2 V_客户编号 NUMBER := &客户编号；
3 V_发号单位 VARCHAR2（30）；
4 V_编号 NUMBER；
5 BEGIN
6 V_发号单位：＝’客户编号 ’ || V_顾客编号 || '的订单’；
7 PROC_顺序编号（V_发号单位，V_编号）；
8 DBMS_OUTPUT.PUT_LINE（V_发号单位 || ‘：’ || V_编号）；
9 END;
10 /

请在客户编号中输入值：200
V_顾客编号 NUMBER :=200；
顾客编号200的订单
ID：1 ←客户编号200的第一个订单ID是1

PL/SQL过程成功完成。

SQL>SELECT * FROM 顺序；

发号单位 编号
'------------------------------
订单ID 3
顾客ID 100

客户编号100的订购ID 3←上述例子中追加的发号单位
客户编号200的订购ID 1←上述例子中追加的发号单位

上边例子就是：
按照顾客为单位可以发行连续的订单ID的情况。
每次使用新的发号单位，都会在顺序表中登记发号单位。

本次到此为止