I didn't see any "gap free" requirement either.
Yes, I'm in favor to use sequences whenever possible.
I was assuming that the sequence identifier generator would require some sort of logic behind its generation (ie. year + campus + location + sequence).
Although creating 350+ sequences is no big deal, it would require system maintenance when new locations are created and also to reset it early.
The autonomous transaction pl/sql package would allow him to introduce complex logic on the identifier generator, while addressing the lock issue.
Here is a sample code that would generate a sequence based on year, campus, location and would require no yearly maintenance nor when new locations are created:
SQL>
SQL> create table x
2 ( seq_year number,
3 campus_id number,
4 location_id number,
5 current_value number,
6 constraint x_pk primary key ( seq_year, campus_id, location_id )
7 )
8 organization index
9 /
Table created.
SQL>
SQL> -- create the package spec
SQL> CREATE OR REPLACE PACKAGE x_pkg AS
2 FUNCTION getnextvalue (p_seq_year IN number, p_campus_id IN number, p_location_id IN number, p_increment IN NUMBER) RETURN NUMBER;
3 END x_pkg;
4 /
Package created.
SQL> -- create the package body
SQL> CREATE OR REPLACE PACKAGE BODY x_pkg AS
2 FUNCTION getnextvalue(p_seq_year IN number, p_campus_id IN number, p_location_id IN number, p_increment IN NUMBER) RETURN NUMBER IS
3 PRAGMA AUTONOMOUS_TRANSACTION;
4
5 v_nextval NUMBER;
6 BEGIN
7 UPDATE x SET current_value = current_value + p_increment
8 WHERE seq_year = p_seq_year
9 AND campus_id = p_campus_id
10 AND location_id = p_location_id
11 RETURNING current_value into v_nextval;
12
13 -- if no year, campus, collection location found, then insert
14 IF SQL%ROWCOUNT = 0 THEN
15 INSERT INTO x (seq_year, campus_id, location_id, current_value)
16 VALUES (p_seq_year, p_campus_id, p_location_id, 1);
17 v_nextval := 1;
18 END IF;
19 COMMIT;
20 RETURN v_nextval;
21 END getnextvalue;
22 END x_pkg;
23 /
Package body created.
SQL>
SQL> select x_pkg.getnextvalue(2020, 1, 1, 1) from dual connect by level <= 10
2 /
X_PKG.GETNEXTVALUE(2020,1,1,1)
------------------------------
1
2
3
4
5
6
7
8
9
10
10 rows selected.
SQL>
- Kilson Araujo