php – SQL：如何插入自定义增量值

或者,您可以尝试这样的事情：

>创建一个表(我的例子中的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),性能可能(将)受到影响.再次使用序列.