oracle - Oracle SQL：自引用表层次结构。选择后如何使用临时表/循环将父母添加到顶部？

Question

• 我有一个表 Jobmst，它的主键为 jobmst_id，父级通过 jobmst_prntid 的自引用。

• 所以实际上父母变成了工作组或其他组，因为你可以嵌套很多层次。

• 我试图确定给定父母何时有多个日历（evntmst_ids）。

• 但是，稍后如果他们在层次结构深度中处于第 3 级，我想通过另一个未列出的 CTE，通过重新构成 jobmst_ids 的过程将第 2 级和第 1 级添加回来。

• 为此，我尝试使用名为 jids 的临时表。这个想法是重复添加父ID的孩子的所有jobmst_id，直到临时表中的记录数保持不变，此时将添加所有父母的父母等的父母。

但是，我一直在为 jids 得到这个：

---

PL/SQL: ORA-00942: 表或视图不存在

是否有某种背景使 jids 不可见？
我将如何修改代码来解决这个问题？
建议非常感谢。

---

declare
 cnt number;
 lastcnt number;
begin
with cals_in_group(jobmst_prntid, evntmst_id, cal_count) AS
(
  select jobmst_prntid, evntmst_id, count(1)
  from jobmst
  group by jobmst_prntid, evntmst_id
  order by jobmst_prntid, evntmst_id
),
group_stats_gt_1(jobmst_prntid, tot_job_in_group, num_cal_in_group) AS
(
  select jobmst_prntid, sum(cal_count) , count(1)
  from cals_in_group
  having count(1) > 1
  group by jobmst_prntid
  order by jobmst_prntid
),
/*
select num_cal_in_group, jobmst_prntid
from group_stats_gt_1
order by num_cal_in_group,jobmst_prntid
*/
jids(jobmst_id) AS
(
 select jobmst_prntid from group_stats_gt_1
)
--select jobmst_id from jids;

 select count(1)
 into cnt
 from jids;
 lastcnt := -1;
 WHILE cnt <> lastcnt
 LOOP
   lastcnt := cnt;
   insert into jids
    select jobmst_id 
    from jobmst 
    where jobmst_prntid in(select jobmst_id from jids)
    and   jobmst_id not in(select jobmst_id from jids);
  select count(1)
  into cnt
  from jids;
 END LOOP;
 select count(1) from jids;
end;

标签： oracle

Answer 1

为此，我尝试使用名为 jids 的临时表

如果jids是 CTE，则：

jids(jobmst_id) AS
(
 select jobmst_prntid from group_stats_gt_1
)

那么它就行不通了。你不能在里面插入任何东西。

如果它必须是一个真正的临时表，则创建一个全局临时表（或私有的，取决于您使用的 Oracle 版本）。

像这样的东西：

SQL> create global temporary table gtt_jids (jobmst_id number)
  2  on commit preserve rows;

Table created.

SQL> begin
  2    for cur_r in (select empno from emp where deptno = 10) loop
  3      insert into gtt_jids values (cur_r.empno);
  4    end loop;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select * from gtt_jids;

 JOBMST_ID
----------
      7782
      7839
      7934

SQL>

除了 Oracle 文档之外，请查看Global Temporary Tables @ORACLE-BASE，它的所有内容都在同一个地方，并附有示例。