php - SQL:如何插入自定义增量值
问题描述
目前我有一个看起来像这样的表:
Year | Branch_Code | Registration_Number | ...
______________________________________________
2018 | BRANCH1 | 1 | ...
2018 | BRANCH1 | 2 | ...
2018 | BRANCH2 | 1 | ...
因此,每次我将数据插入表中时,我都希望 Registration_Number 自动递增,并依赖于 Year 和 Branch_Code。我试图先获取最大值然后再插入,但如果我的客户同时插入,它有时会插入重复的数字。
有没有人有任何解决方案?
PS 我正在使用 Laravel 框架和 Oracle 数据库。
解决方案
我建议您使用序列并停止担心它。
或者,您可以尝试这样的事情:
- 创建一个表(
regnum
在我的示例中),其中包含registration_number
[year, branch_code] 组合的最后一个表 - 在一个自治事务的函数中增加它(这样它就
COMMIT
不会影响主事务) - 在触发器中填充目标表的(
yourt
在我的示例中)
就是这样:
先上表:
SQL> create table yourt (year number, branch_code varchar2(20), registration_number number, datum date);
Table created.
SQL> create table regnum (year number, branch_code varchar2(20), registration_number number);
Table created.
功能:
SQL> create or replace function f_regnum (par_year in number, par_branch_code in varchar2)
2 return number
3 is
4 pragma autonomous_transaction;
5 l_nextval number;
6 begin
7 select registration_number + 1
8 into l_nextval
9 from regnum
10 where year = par_year
11 and branch_code = par_branch_code
12 for update of registration_number;
13
14 update regnum set
15 registration_number = l_nextval
16 where year = par_year
17 and branch_code = par_branch_code;
18
19 commit;
20 return (l_nextval);
21
22 exception
23 when no_data_found then
24 lock table regnum in exclusive mode;
25
26 insert into regnum (year, branch_code, registration_number)
27 values (par_year, par_branch_code, 1);
28
29 commit;
30 return(1);
31 end;
32 /
Function created.
扳机:
SQL> create or replace trigger trg_bi_yourt
2 before insert on yourt
3 for each row
4 begin
5 :new.registration_number := f_regnum(:new.year, :new.branch_code);
6 end;
7 /
Trigger created.
测试:
SQL> insert into yourt (year, branch_code, datum) values (2017, 'branch 1', date '2017-01-01');
1 row created.
SQL> insert into yourt (year, branch_code, datum) values (2017, 'branch 1', date '2017-01-25');
1 row created.
SQL> insert into yourt (year, branch_code, datum) values (2017, 'branch 2', date '2017-04-14');
1 row created.
SQL> insert into yourt (year, branch_code, datum) values (2018, 'branch 3', date '2018-07-11');
1 row created.
SQL> insert into yourt (year, branch_code, datum) values (2018, 'branch 1', date '2018-05-21');
1 row created.
SQL> insert into yourt (year, branch_code, datum) values (2018, 'branch 3', date '2018-03-14');
1 row created.
SQL> insert into yourt (year, branch_code, datum) values (2018, 'branch 3', date '2018-05-17');
1 row created.
结果:
SQL> select * from yourt order by branch_code, year;
YEAR BRANCH_CODE REGISTRATION_NUMBER DATUM
---------- -------------------- ------------------- ----------
2017 branch 1 2 2017-01-25
2017 branch 1 1 2017-01-01
2018 branch 1 1 2018-05-21
2017 branch 2 1 2017-04-14
2018 branch 3 2 2018-03-14
2018 branch 3 3 2018-05-17
2018 branch 3 1 2018-07-11
7 rows selected.
SQL> select * from regnum order by branch_code, year;
YEAR BRANCH_CODE REGISTRATION_NUMBER
---------- -------------------- -------------------
2017 branch 1 2
2018 branch 1 1
2017 branch 2 1
2018 branch 3 3
SQL>
该解决方案将在多用户环境中工作,不会引发变异表错误,但如果您一次加载大量行(例如,使用 SQL*Loader),性能可能(将会)受到影响。再一次,使用一个序列。
推荐阅读
- autodispose - 当作用域处于 ON_PAUSE 或 ON_STOP 状态时,使用 Autodispose 的流是否应该停止发射?
- serial-port - 你们如何看待这个 RS-422 通信问题?
- java - 错误意外令牌:com.journeyapps:zxing-android-embedded:3.4.0 @ line 40, column 21. in build.gradle
- asp.net-mvc - 参数为空时的 MVC LINQ SELECT ALL
- git - 获取类型为`.js`的暂存文件列表
- python - 无法保存下一个重命名文件(Python)
- wordpress - Google Recaptcha 标签未显示在联系表 7 中
- webrtc - WebRTC Trickle ICE with TURN 通过互联网连接
- javascript - 如何在html文本区域中显示动态值
- mysql - 如果节点js中有mysql和restfull API事务如何使用回滚