SQL: Autogenerate generic id on record insert

Question

I have developed a procedure that adds a consultant to a table.

I would like to add a procedure to the consultant ID so that everytime a consultant gets added to the table a generic id is added to the record.

How can I create this procedure?

create or replace PROCEDURE ADD_CONSULTANT
    ( p_con_id           LDS_CONSULTANT.CONSULTANT_ID%type,
      p_con_name         LDS_CONSULTANT.CST_NAME%type,
      p_con_start        LDS_CONSULTANT.START_DATE%type,
      p_con_end          LDS_CONSULTANT.LEAVE_DATE%type,
      p_con_loc          LDS_CONSULTANT.LOCATION%type,
      p_con_spec         LDS_CONSULTANT.SPECIALIST_AREA%type)
    IS
    BEGIN

    INSERT INTO LDS_CONSULTANT (CONSULTANT_ID, CST_NAME, START_DATE, LEAVE_DATE, LOCATION, SPECIALIST_AREA)
    VALUES (p_con_id, p_con_name, p_con_start, p_con_end, p_con_loc, p_con_spec);
    END;

Does the answer here help? https://stackoverflow.com/questions/11296361/how-to-create-id-with-auto-increment-on-oracle?utm_medium=organic&utm_source=google_rich_qa&utm_campaign=google_rich_qa — ZAR, May 12 '18 at 21:52

score 0 · Answer 1 · answered May 13 '18 at 11:21

You didn't mention which database version you use, so the answer & the solution you choose might differ.

A simple solution is to use a sequence. Here's an example. First, a table:

SQL> CREATE TABLE lds_consultant (
  2    consultant_id     NUMBER,
  3    cst_name          VARCHAR2(20),
  4    start_date        DATE,
  5    leave_date        DATE,
  6    location          VARCHAR2(20),
  7    specialist_area   VARCHAR2(20)
  8  );

Table created.

A sequence:

SQL> CREATE SEQUENCE seq_cons;

Sequence created.

A procedure; I removed parameter which you originally used, as you don't need it any more.

SQL> CREATE OR REPLACE PROCEDURE add_consultant (
  2    p_con_name    lds_consultant.cst_name%TYPE,
  3    p_con_start   lds_consultant.start_date%TYPE,
  4    p_con_end     lds_consultant.leave_date%TYPE,
  5    p_con_loc     lds_consultant.location%TYPE,
  6    p_con_spec    lds_consultant.specialist_area%TYPE
  7  )
  8  IS
  9  BEGIN
 10    INSERT INTO lds_consultant (
 11      consultant_id,
 12      cst_name,
 13      start_date,
 14      leave_date,
 15      location,
 16      specialist_area
 17    ) VALUES (
 18      seq_cons.NEXTVAL,          --> this
 19      p_con_name,
 20      p_con_start,
 21      p_con_end,
 22      p_con_loc,
 23      p_con_spec
 24    );
 25  END;
 26  /

Procedure created.

Let's test it:

SQL> BEGIN
  2    add_consultant('Littlefoot',trunc(SYSDATE),NULL,'Croatia','IT');
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM lds_consultant;

CONSULTANT_ID CST_NAME             START_DATE LEAVE_DATE LOCATION             SPECIALIST_AREA
------------- -------------------- ---------- ---------- -------------------- --------------------
            1 Littlefoot           13.05.2018            Croatia              IT

SQL>

Another option is a database trigger:

SQL> CREATE OR REPLACE TRIGGER trg_bi_cons BEFORE
  2    INSERT ON lds_consultant
  3    FOR EACH ROW
  4  BEGIN
  5    :new.consultant_id := seq_cons.nextval;
  6  END;
  7  /

Trigger created.

Unlike the previous example, you don't even need to reference the consultant_id any more as trigger does that job.

SQL> CREATE OR REPLACE PROCEDURE add_consultant (
  2    p_con_name    lds_consultant.cst_name%TYPE,
  3    p_con_start   lds_consultant.start_date%TYPE,
  4    p_con_end     lds_consultant.leave_date%TYPE,
  5    p_con_loc     lds_consultant.location%TYPE,
  6    p_con_spec    lds_consultant.specialist_area%TYPE
  7  )
  8    IS
  9  BEGIN
 10    INSERT INTO lds_consultant (
 11      cst_name,
 12      start_date,
 13      leave_date,
 14      location,
 15      specialist_area
 16    ) VALUES (
 17      p_con_name,
 18      p_con_start,
 19      p_con_end,
 20      p_con_loc,
 21      p_con_spec
 22    );
 23
 24  END;
 25  /

Procedure created.

Testing:

SQL> BEGIN
  2    add_consultant('Bigfoot',trunc(SYSDATE),NULL,'France','Fashion');
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM lds_consultant;

CONSULTANT_ID CST_NAME             START_DATE LEAVE_DATE LOCATION             SPECIALIST_AREA
------------- -------------------- ---------- ---------- -------------------- --------------------
            1 Littlefoot           13.05.2018            Croatia              IT
            2 Bigfoot              13.05.2018            France               Fashion

SQL>

If you're on 12c database version, use the identity column:

CREATE TABLE lds_consultant (
  consultant_id     NUMBER        GENERATED BY DEFAULT ON NULL AS IDENTITY,,
  cst_name          VARCHAR2(20),
  start_date        DATE,
  leave_date        DATE,
  location          VARCHAR2(20),
  specialist_area   VARCHAR2(20)
);

The procedure would look like the one in the second (trigger) example, i.e. you don't need to reference the consultant_id column. As I run 11g XE on my laptop, I can't post the execution, but I'm sure you can do it yourself if necessary (and , of course, if you're on 12c).

SQL: Autogenerate generic id on record insert

1 Answers1