Oracle下向某表(a,b,c,d)批量插入数据，其中d字段以c字段为依据在group by a,b组中增长

Question

Oracle下，有表X(a,b,c,d)，有表y(a,b,c)。

insert into X(a, b, c, d)
select a, b, c, func_serial(a, b, c) as d
from Y;

func_serial函数的作用是，在X中查找是否存在a,b,c三个字段和传入的参数相同的记录行，如果有，则返回该行的d值，如果没有，则计算X中a,b两个字段和传入参数相同的组中不重复c值的个数count，返回count+1。

不过好像在Oracle中不能这么操作，报：

ORA-04091: 表 X 发生了变化, 触发器/函数不能读它

想问下，在Oracle中一般是怎么比较好的解决这个需求的？

【下面举例说明一下】

表X：

a	b	c	d
1	2	3	1
4	5	4	1
4	5	6	2

表Y：

a	b	c
1	2	3
1	2	4
1	2	5
4	5	7
7	8	9

希望把这样的数据插入X中：

a	b	c	d
1	2	3	1
1	2	4	2
1	2	5	3
4	5	7	3
7	8	9	1

目前是这么写的：

with distinct_X as (
  select distinct a, b, c, d
  from X
),
group_X as (
  select a, b, count(distinct c) as cnt
  from X
  group by a, b
)
insert X(a, b, c, d)
select 
    m.a, 
    m.b, 
    m.c, 
    case nvl(n.d, ' ') when ' ' then 
      row_number() over (partition by m.a, m.b order by m.c) + nvl(o.cnt, 0)
    else n.d end as d
from Y m
left join distinct_X n on m.a = n.a and m.b = n.b and m.c = n.c
left join group_X o on m.a = n.a and m.b = n.b

但是这样有个问题，就是新插入的a,b值相同的n行数据中如果有部分是存在既有d值的，那么其余新生成的d值会不连续，中间浪费了很多编号……

Answer 1

根据题主对问题描述的更新，Y是否可分为两部分分别处理：

1. Y中a, b, c已经包含于X，则直接取X.d；

2. 将Y去除满足 (1) 的行后，剩下的部分按a, b排序并取序号。注意此序号须跳过X中对应同样a, b的d的最大值。

with
y_rn as (                                 -- 已去除第一部分
  select a, b, c, 
         row_number() over (partition by a, b order by c) as rn
  from y
  where (a,b,c) not in (select a, b, c from x)),

x_maxd as (
  select a, b, max(d) as maxd
  from x
  group by a, b)

select x.a, x.b, x.c, x.d                 -- 第一部分
from x inner join y using (a, b, c)
union all
select y_rn.a, y_rn.b, y_rn.c,            -- 第二部分
       coalesce(x_maxd.maxd, 0) + y_rn.rn -- 跳过最大值
from y_rn left join x_maxd using (a, b);

Answer 2

不管性能的话，可以改成循环的方式，在插入Y表之前计算d列的值，如：

for v_row in (select * from y) loop
  select count(*) + 1
  into v_count
  from x
  where x.a = v_row.a and x.b = v_row.b;
  insert into x(a,b,c,d)
  values(v_row.a, v_row.b, v_row.c, v_count);
  commit;
end loop;

第二种方式是提前将D列的值算好后，一次性插入到表X中。

insert into x (a, b, c, d)
select a, b, c, row_number() over (partition by a, b order by c) + nvl(x1.cnt,0)
from y left join (
  select a, b, count(*) as cnt
  from x
  group by a, b
) x1 on y.a = x1.a and y.b = x1.b

==============
下面是按照新问题的回复
1、D值不连续的问题，是逻辑本身的事情，和实现方法无关。
2、你给的例子中，1,2,4的行，计算出的D值应该是2才对。
3、distinct_x的子查询写法不准确，应该是

select a, b, count(distinct c)
from x
group by a, b