oracle 如何到达：触发器中的子类型的新值

kq4fsx7k 于 2023-04-20 发布在 Oracle

关注(0)|答案(1)|浏览(136)

我怎样才能只计算那些与insert具有相同省的行呢？

create or replace trigger sequence_num_trigg before insert on Distributor for each row 
declare counter NUMBER;
BEGIN
select count(*) INTO counter from distributor where treat(address as distributor_address_ty).province = :new.treat(address as distributor_address_ty).province;
if counter != 0 then
:new.seq_no := 1 + counter;
else
:new.seq_no := 0;
END IF;
END;

我知道这部分不对

where treat(address as distributor_address_ty).province = :new.treat(address as distributor_address_ty).province;

怎么解决呢？

oracle

来源：https://stackoverflow.com/questions/75982709/how-to-reach-new-value-from-sub-type-in-trigger

1条答案

按热度按时间

polkgigr1#

不要使用触发器。相反，使用标识列（或序列）为每行提供唯一的id，然后在查询表时使用ROW_NUMBER分析函数生成seq_no：

Create or replace type distributor_address_ty as object (
  province VARCHAR2(10)
) NOT FINAL;

create table distributor (
  id      NUMBER
          GENERATED ALWAYS AS IDENTITY,
  address distributor_address_ty
);

然后插入一些示例数据：

INSERT INTO distributor (address)
VALUES (distributor_address_ty('A'));

INSERT INTO distributor (address)
SELECT distributor_address_ty('A') FROM DUAL UNION ALL
SELECT distributor_address_ty('B') FROM DUAL UNION ALL
SELECT distributor_address_ty('A') FROM DUAL;

然后，您可以使用以下命令生成seq_no：

SELECT d.id,
       d.address.province,
       ROW_NUMBER() OVER (PARTITION BY d.address.province ORDER BY d.id) AS seq_no
FROM   distributor d;

其输出：
| ID| ADDRESS.PROVINCE |SEQ_NO|
| --------------|--------------|--------------|
| 1|A|1|
| 二|A|二|
| 四|A|三|
| 三|B|1|
fiddle

赞(0）回复(0）举报 2023-04-20

我来回答

oracle 如何到达：触发器中的子类型的新值

1条答案

相关问题

热门标签

最新问答