Question

I have a database table with two columns customer_id Number and first_name VARCHAR2
For customer_id, the number will be auto incremented. I need first_name with the value of customer_id in a specific format.

See the below example:

If the customer_id is auto incremented to value 1, the first_name should be NAME-1 if the customer_id is auto incremented to value 2, the first_name should be with NAME-2

The only feasible option I got so far is performing 3 sql statements.
One for insert and the next one is to read the incremented value and perform update to the first_name column.

I greatly appreciate the help and guidance if anyone can suggest a better way of handling this in minimum possible sql queries.

Answer 1

One option is to create an identity column (for the ID column) and a database trigger which sets first name to desired value.

表:

SQL> create table test (customer_id number generated always as identity,
  2                     first_name varchar2(10));

Table created.

Trigger:

SQL> create or replace trigger trg_bi_test
  2    before insert on test
  3    for each row
  4  begin
  5    :new.first_name := :new.first_name || - || :new.customer_id;
  6  end;
  7  /

Trigger created.

测试:

SQL> insert into test (first_name) values ( Scott );

1 row created.

SQL> insert into test (first_name) values ( Mike );

1 row created.

结果:

SQL> select * from test;

CUSTOMER_ID FIRST_NAME
----------- ----------
          1 Scott-1
          2 Mike-2

SQL>

Answer 2

由于“NAME-1”的体质,或者使用一种表述:

select
  *,
  concat(name,  - , id) as name_id
from mytable

或形成一种观点:

create view mytable_x as (
  select
    id,
    name as raw_name,
    concat(name,  - , id) as name,
    -- other columns
  from mytable
)

Answer 3

希望:

select Customer_id, concat(first_name, CONCAT( - , customer_id)) as first_name
from Customers;

友情链接