I got a
productID - P0001KTC
P0001KTC is the sort of smart key users love and developers hate. But the customer is king, so here we are.
The OP's requirement is unclear. I have interpreted this line ...
I got a
productID- P0001KTC and P0001DR
... to mean the OP wants to increment the numeric element within the product category. However, there's a distinct possibility it's just a typo, in which case the following is just a waste of my time and energy. So it goes.
The best way to implement this is with a code control table, that is a table to manage the assigned numbers. Such an approach entails pessimistic locking, which serializes access to a Product Category (e.g. KTC). Presumably your users won't be creating new Products very often, so the scaling implicates aren't severe.
Here's our reference table:
create table product_categories ( product_category_code varchar2(3) not null , category_description varchar2(30) not null , constraint product_categories_pk primary key (product_category_code) ) / create table product_ids ( product_category_code varchar2(3) not null , last_number number(38) default 0 not null , constraint product_ids_pk primary key (product_category_code) , constraint product_ids_categories_fk foreign key (product_category_code) references product_categories (product_category_code) ) organization index /
May these two tables could be one table, but this implementation offers greater flexibility. Let's create our Product Categories:
insert all into product_categories (product_category_code, category_description) values (cd, descr) into product_ids (product_category_code) values (cd) select * from ( select 'KTC' as cd, 'Kitchen' as descr from dual union all select 'LR' as cd, 'Living Room' as descr from dual union all select 'DR' as cd, 'Dining Room' as descr from dual ) /
Here's the target table:
create table products ( product_id varchar2(10) not null , product_category_code varchar2(3) not null , product_description varchar2(30) not null , constraint products_pk primary key (product_id) , constraint products_fk foreign key (product_category_code) references product_categories (product_category_code) ) /
This function is where the magic happens. The function formats the new Product ID. It does this by taking out a pre-emptive lock on the row for the assigned Category. These locks are retained for the length of the transaction i.e. until the locking session commits or rolls back. So if there are two users creating Kitchen Products one will be left hanging on the other: this is why we generally try to avoid serializing table access in multi-user environments.
create or replace function get_product_id ( p_category_code in product_categories.product_category_code%type) return products.product_id%type is cursor lcur (p_code varchar2)is select last_number + 1 from product_ids where product_category_code = p_code for update of last_number; next_number product_ids.last_number%type; return_value products.product_id%type; begin open lcur( p_category_code); fetch lcur into next_number; if next_number > 999 then raise_application_error (-20000 , 'No more numbers available for ' || p_category_code); else return_value := 'PROD' || lpad(next_number, 3, '0') || p_category_code; end if; update product_ids t set t.last_number = next_number where current of lcur; close lcur; return return_value; end get_product_id; /
And here's the trigger:
create or replace trigger products_ins_trg before insert on products for each row begin :new.product_id := get_product_id (:new.product_category_code); end; /
Obviously, we could put the function code in the trigger body but it's good practice to keep business logic out of triggers.
Lastly, here's some test data...
insert into products ( product_category_code, product_description) values ('KTC', 'Refrigerator') / insert into products ( product_category_code, product_description) values ('DR', 'Dining table') / insert into products ( product_category_code, product_description) values ('KTC', 'Microwave oven') / insert into products ( product_category_code, product_description) values ('DR', 'Dining chair') / insert into products ( product_category_code, product_description) values ('DR', 'Hostess trolley') / insert into products ( product_category_code, product_description) values ('LR', 'Sofa') /
SQL> select * from products 2 / PRODUCT_ID PRO PRODUCT_DESCRIPTION ---------- --- ------------------------------ PROD001KTC KTC Refrigerator PROD001DR DR Dining table PROD002KTC KTC Microwave oven PROD002DR DR Dining chair PROD003DR DR Hostess trolley PROD001LR LR Sofa 6 rows selected. SQL>