脚本宝典收集整理的这篇文章主要介绍了php – SQL:如何插入自定义增量值,脚本宝典觉得挺不错的,现在分享给大家,也给大家做个参考。
Year | branch_Code | Registration_Number | ... ______________________________________________ 2018 | BRANCh1 | 1 | ... 2018 | BRANCH1 | 2 | ... 2018 | BRANCH2 | 1 | ...
因此,每次我将数据插入表中时,我都希望Registration_Number自动递增,并依赖于Year和Branch_Code.我试图先获取最大值并稍后插入,但如果我的客户同时插入,它有时会插入重复的数字.
有人有任何解决方案吗?
或者,您可以尝试这样的事情:
>创建一个表(我的例子中的regnum),它保存[year,branch_code]组合的最后一个registration_number
>在一个自治事务的函数中增加它(以便它的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,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,datum) values (2017,'branch 1',date '2017-01-01'); 1 row created. sql> insert into yourt (year,date '2017-01-25'); 1 row created. sql> insert into yourt (year,'branch 2',date '2017-04-14'); 1 row created. sql> insert into yourt (year,datum) values (2018,'branch 3',date '2018-07-11'); 1 row created. sql> insert into yourt (year,date '2018-05-21'); 1 row created. sql> insert into yourt (year,date '2018-03-14'); 1 row created. sql> insert into yourt (year,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),性能可能(将)受到影响.再次使用序列.
以上是脚本宝典为你收集整理的php – SQL:如何插入自定义增量值全部内容,希望文章能够帮你解决php – SQL:如何插入自定义增量值所遇到的问题。
本图文内容来源于网友网络收集整理提供,作为学习参考使用,版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ:384754419,请注明来意。