将多列减号查询的结果存储在 Oracle PL/SQL 中的变量中 [英] Store result of minus query with multiple columns in a variable in Oracle PL/SQL

问题描述

与 TABLE_TWO 相比，我使用下面的代码来获取 TABLE_ONE 中存在的额外项目 ID(来源 将减号查询(varchars列表)的结果存储在Oracle PL/SQL中的一个变量中)

I'm using below code to fetch the extra Project IDs present in TABLE_ONE compared to TABLE_TWO (source Store result of minus query ( list of varchars) in a variable in Oracle PL/SQL)

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 相比， TABLE_ONE 中存在的任何额外 project_id 都应与相应的 Project Names 一起插入到 TABLE_TWO 中.怎么做?请注意，我需要将减号查询结果存储在一个变量中，因为我将对结果执行多个步骤.

Now I want to add corresponding Project Names also to second table. Whatever extra project_ids present in TABLE_ONE compared to TABLE_TWO should be inserted in TABLE_TWO along with corresponding Project Names . How to do it? Please note that I need to store the minus query result in a variable since i will be performing multiple steps with the result.

推荐答案

SYS.ODCINUMBERLIST 只是一个列表，其中每条记录都有一个编号.您需要使用新的记录类型来存储每行中的多个字段.

SYS.ODCINUMBERLIST is just a list where each record has a single number. You need to use a new record type to store the multiple fields in each row.

您可以定义一个记录类型来代替 SYS.ODCINUMBERLIST.但我倾向于将 SELECT 语句移动为显式游标，以便您可以将新类型定义为游标 %ROWTYPE.这样，您的记录类型和选择语句始终保持一致，但它会稍微改变您的代码结构.

You could define a record type to use in place of SYS.ODCINUMBERLIST. But I'd be inclined to move the SELECT statement to be an explicit cursor, so that you could define a new type as cursor %ROWTYPE. That way, your record type and the select statement are always consistent, but it changes the structure of your code a bit.

这是这种方法:

DECLARE 

  CURSOR c_select IS
      SELECT project_id, project_name
      FROM 
        (
          SELECT t1.project_id, t1.project_name FROM table_one t1
          MINUS
          SELECT t2.project_id, t2.project_name FROM table_two t2 );

  TYPE l_missing_row_list_typ IS TABLE OF c_select%ROWTYPE;
  l_missing_row_list l_missing_row_list_typ;
  
BEGIN
  OPEN c_select;
  FETCH c_select BULK COLLECT INTO l_missing_row_list;
  CLOSE c_select;
  
  FORALL i IN l_missing_row_list.FIRST..l_missing_row_list.LAST
    INSERT INTO table_two VALUES ( l_missing_row_list(i).project_id, l_missing_row_list(i).project_name );
    
  COMMIT;
  
  -- Values are now inserted and you have the list of IDs in l_missing_row_list to add to your email.
END;

关于BULK COLLECT 的一个说明(此处和您的原始帖子中都有)——您批量收集的数据存储在 PGA 内存中.所以不要使用这个代码，如果有变化，新记录的数量会不合理(可能超过几千).

One note about the BULK COLLECT (both here and in your original post) -- the data you are bulk collecting is stored in PGA memory. So do not use this code as is if there is a change the number of new records will be unreasonable (maybe more than a few thousand).

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