How to append an Incrementing number to a table value in Oracle?-CodePudding

Using Oracle, I have a procedure to create unique ID numbers. Currently the ID number is built with the sysdate year concatenated with a "-". I then update the ID number to concatenate a value from within the table after the dash.

EX: 2022-8501.

Instead of using a table value, I want to increment the number after the dash by 1, and it should start back at 1 every year.

EX: 2022-1, 2022-2, 2022-3 // (Next year) 2023-1, 2023-2, 2023-3

What would be the best way to approach this? I have attached my current code below.

INSERT INTO PLANE_INFO.ID_NUMBERS (PLANE_ID)
    VALUES (TO_CHAR(SYSDATE, 'YYYY')||'-')
    RETURNING ENTRY_ID INTO v_entry_id;

UPDATE PLANE_INFO.ID_NUMBERS
    SET PLANE_ID = PLANE_ID || LPAD(v_entry_id,5,'0')
    WHERE ENTRY_ID = v_entry_id;

CodePudding user response：

The "best" way? Use a sequence.

create sequence seq;

insert into plane_info.id_numbers (plane_id)
values (extract (year from sysdate) ||'-'|| seq.nextval);

CodePudding user response：

[TL;DR] Just use a normal sequence and if you want to generate the pseudo-sequence that restarts every year then you can calculate it for display purposes (but you should not use it as a referential constraint).

If you have the table:

CREATE TABLE PLANE_INFO.ID_NUMBERS (
  id   NUMBER
       GENERATED ALWAYS AS IDENTITY
       CONSTRAINT table_name__id__pk PRIMARY KEY,
  year NUMBER(4)
       CONSTRAINT table_name__year__nn NOT NULL
);

Note: IDENTITY columns are available from Oracle 12.

Then you have an incrementing sequence (that you can use as the primary key and for any referential constraints) and a year column.

You can generate your pseudo-sequence (for display purposes, rather than as a referential constraint) using the ROW_NUMBER() analytic function and string concatenation:

SELECT t.*,
       YEAR
       || '-'
       || LPAD(ROW_NUMBER() OVER (PARTITION BY YEAR ORDER BY id), 5, '0')
         AS pseudo_seq
FROM   PLANE_INFO.ID_NUMBERS t

db<>fiddle here