我可以使用PL/SQL触发器来检查类别，并根据类别将其合并为增量数字吗? [英] Can I use a PL/SQL trigger to check category and concat to a incremental number based on category?
问题描述
我得到了productID-P0001KTC和P0001DR.
I got a productID - P0001KTC and P0001DR.
如果产品类别是厨房，我将分配一个productID - PROD001KTC，否则，如果类别是餐厅，则productID应该是PROD001DR.
If product category is kitchen, I will assign a productID - PROD001KTC, else if the category is dining room, then the productID should be PROD001DR.
是否可以在触发器内编写序列以检查产品类别并如上所述分配ID?
Is it possible to write a sequence inside a trigger to check the product category and assign an id as mentioned above?
如果插入了另一个客厅类别的产品，则ID为PROD001LR.
if there is another living room category product inserted then the id will be PROD001LR.
- 厨房-PROD001KTC，PROD002KTC ...
- 餐厅-PROD001DR，PROD002DR ....
- 客厅-PROD001LR，PROD002LR ...
推荐答案
P0001KTC是用户喜欢和开发人员讨厌的那种智能密钥.但是客户为王，所以我们在这里.
P0001KTC is the sort of smart key users love and developers hate. But the customer is king, so here we are.
客户的要求是增加产品类别中的数字元素，以便将相同的数字用于不同的类别:P0001KTC，P0001DR，P0002KTC，P0001LR，P0002LR等.单调递增的序列不能做到这一点.
The customer's requirement is to increment the numeric element within the product category, so that the same number is used for different categories: P0001KTC , P0001DR , P0002KTC, P0001LR, P0002LR, etc. A monotonically increasing sequence cannot do this.
最好的实现是代码控制表，该表是管理分配的号码的表.这种方法需要悲观锁定，该操作会序列化对产品类别(例如KTC)的访问.大概用户不会经常创建新产品，因此扩展性并不严重.
The best implementation is 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 the users won't be creating new Products very often, so the scaling implications aren't severe.
工作中的PoC
这是我们的参考表:
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 )
/
这是目标表:
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)
)
/
此功能是发生魔术的地方.该函数格式化新的产品ID.它通过在分配的类别的行上抢先锁定来实现此目的.这些锁定将在事务期间保持不变，即直到锁定会话提交或回滚为止.因此，如果有两个用户创建厨房产品"，一个将挂在另一个用户上:这就是为什么我们通常尝试避免在多用户环境中序列化表访问.
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;
/
这是触发器:
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>
请注意，将智能键建模为单个列是一个坏主意.最好将其构建为复合键，例如unique (product_category, product_number)，其中product_number是从上面的代码控制表生成的.为了显示目的，我们仍然需要product_id，但是它应该从基础列中派生.使用虚拟列很容易，就像这样:
Note that modelling the smart key as a single column is a bad idea. It is better to build it as a composite key, say unique (product_category, product_number), where product_number is generated from the code control table above. We still need the product_id for display purposes, but it should be derived from the underlying columns. This is easy using virtual columns, like this:
create table products (
product_id varchar2(10)
generated always as 'PROD' || to_char(product_no,'FM003') || product_category_code;
, product_category_code varchar2(3) not null
, product_no number not null
, product_description varchar2(30) not null
, constraint products_pk primary key (product_id)
, constraint products_uk unique (product_category_code, product_no)
, constraint products_fk foreign key (product_category_code)
references product_categories (product_category_code)
)
/
这篇关于我可以使用PL/SQL触发器来检查类别，并根据类别将其合并为增量数字吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
