将减号查询(varchars 列表)的结果存储在 Oracle PL/SQL 中的变量中 [英] Store result of minus query ( list of varchars) in a variable in Oracle PL/SQL

问题描述

与 TABLE_TWO 相比，我正在使用以下减号查询来获取 TABLE_ONE 中存在的额外 project_id

I'm using below minus query to get the extra project_ids present in TABLE_ONE compared to TABLE_TWO

select project_id from TABLE_ONE minus select project_id from TABLE_TWO;

我想存储上述查询的结果，即变量中的 varchars 列表，因为我需要执行以下 2 个步骤:

I want to store result of above query which is list of varchars in a variable since i need to perform below 2 steps :

1. 如果上述查询返回任何 project_ids，发送一封电子邮件，在邮件正文中包含这些 project_ids
2. 在 TABLE_TWO 中插入那些额外的 project_id，以确保 TABLE_ONE 中存在的所有 project_id 都存在于 TABLE_TWO 中

对于第 2 步，我尝试了下面的查询并且成功了.

For step 2 I tried below query and it worked.

insert into TABLE_TWO columns (project_id) values (select project_id from TABLE_ONE minus select project_id from TABLE_TWO);

但是要执行以上 2 个步骤，我需要将查询结果存储在一个变量中.请让我知道该怎么做.我使用的是 Oracle 12c.

However to perform above 2 steps i need to store the query result in a variable. Please let me know how to do it. I'm using Oracle 12c.

推荐答案

不幸的是，将缺失的 ID 放入 table_two 的两种最自然的方法(多行 INSERT 或 >MERGE) 支持 RETURNING.. BULK COLLECT INTO 子句.

Unfortunately, neither of the two most natural ways to get the missing IDs into table_two (a multi-row INSERT or a MERGE) support the RETURNING.. BULK COLLECT INTO clause.

所以，我认为最好的办法是首先获取 id 列表，然后使用该列表来维护 table_two.

So, I think your best bet is to get the list of ids first and then use that list to maintain table_two.

像这样:

DECLARE 
  l_missing_id_list SYS.ODCINUMBERLIST;
BEGIN
  SELECT project_id
  BULK COLLECT INTO l_missing_id_list
  FROM 
    (
    SELECT t1.project_id FROM table_one t1
    MINUS
    SELECT t2.project_id FROM table_two t2 );
    
  FORALL i IN l_missing_id_list.FIRST..l_missing_id_list.LAST
    INSERT INTO table_two VALUES ( l_missing_id_list(i) );
    
  COMMIT;
  
  -- Values are now inserted and you have the list of IDs in l_missing_id_list to add to your email.
END;

这是基本概念.据推测，您在 TABLE_TWO 中有更多的列，而不仅仅是 id，因此您必须添加这些列.

That's the basic concept. Presumably you have more columns in TABLE_TWO than just the id, so you'll have to add those.

这篇关于将减号查询(varchars 列表)的结果存储在 Oracle PL/SQL 中的变量中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！