Oracle-如何将记录添加到相同类型的集合(多集联合) [英] Oracle - How to add a Record to a Collection of the same Type (Multiset Union)

问题描述

我已经使用MULTISET UNION来将一个集合加载到另一个相同类型的集合中，但是我现在正在使用 Records ，并想在其中添加一个 Record 相同类型的集合.无论出于何种原因，我都无法弄清楚适当的语法或执行此操作的正确方法，因为MULTISET UNION似乎不能像我以前使用Collections那样与Records配合使用.

I have used MULTISET UNION in order to load a collection into another collection of the same type however I am now working with Records and want to add a Record into a Collection of the same Type. For whatever reason I just can't figure out the appropriate syntax or simply the proper way to do this as MULTISET UNION appears to not play nice with Records in the way I'm used to working with Collections.

我已经在最后添加了有关此代码工作方式的摘要(假定仍然可以工作).

I've added a summary of how this code works at the end (supposed to work anyway).

下面的屏幕截图和代码

Screenshot and Code Below

LINE 44: c_los_ms_tbl_ret := c_los_ms_tbl_ret MULTISET UNION los_ms_row;

DECLARE  

  TYPE t_los_ms_rec IS RECORD(
      appt_id NUMBER DEFAULT NULL,
      appt_name VARCHAR(300) DEFAULT NULL,
      tot_units NUMBER DEFAULT 0,
      new_rentals NUMBER DEFAULT 0,
      term_rentals NUMBER DEFAULT 0,
      avg_los_all NUMBER DEFAULT 0
  );

  TYPE t_los_ms_tbl IS TABLE OF t_los_ms_rec;

  /* Two collections based on Table Type of Record t_los_ms_rec */
  c_los_ms_tbl_ret  t_los_ms_tbl  :=  t_los_ms_tbl();  
  c_los_ms_tbl      t_los_ms_tbl  :=  t_los_ms_tbl();  

  FUNCTION los_func(p_appt_ids IN VARCHAR) RETURN t_los_ms_tbl
  IS
        los_ms_row  t_los_ms_rec; /* Declare Row based on Record Type */

  BEGIN
    /* Outer loop: iterate through all user selected appartments. */
    FOR los IN 
    (
      SELECT 1001 AS appt_id, 45 AS tot_units, 10 AS new_rentals, 3 AS term_rentals, 'Building1' AS appt_name
        FROM dual UNION ALL
      SELECT 1002 AS appt_id, 37 AS tot_units, 6  AS new_rentals, 4 AS term_rentals, 'Building2' AS appt_name
        FROM duaL
    )
    LOOP      
      /* Set Row Fields to the Data being returned by Outer Loop.  Fake Table data from dual. */
      los_ms_row.appt_name    := los.appt_name;      
      los_ms_row.appt_id      := los.appt_id;
      los_ms_row.new_rentals  := los.new_rentals;
      los_ms_row.term_rentals := los.term_rentals;
      los_ms_row.tot_units    := los.tot_units;
      los_ms_row.avg_los_all  := 45; /* Made up Number */

      /* Output Apartment Name for testing */
      dbms_output.put_line('Apartment Name' || los_ms_row.appt_name);

      /* Save Row Data into Collection */ /* HOW DO I POPULATE COLLECTION WITH A RECORD */
      c_los_ms_tbl_ret  := c_los_ms_tbl_ret MULTISET UNION los_ms_row;

    END LOOP;

    RETURN c_los_ms_tbl_ret; /* Return Populated Collection */

  END los_func;  

BEGIN  
  /* Call Function and Store Returned Collection into a collection of same type */
  c_los_ms_tbl  :=  los_func(p_appt_ids => '1001,1002');

  FOR r IN c_los_ms_tbl.FIRST .. c_los_ms_tbl.LAST
  LOOP
    dbms_output.put_line(c_los_ms_tbl(r).avg_los_all);
  END LOOP;

END;

摘要

• 记录类型已声明. t_los_ms_rec
• 根据记录t_los_ms_tbl
声明的表类型

• 根据表类型c_los_ms_tbl_ret和c_los_ms_tbl

  • Record Type is Declared. t_los_ms_rec
  • Table Type Declared based on Record t_los_ms_tbl

  • Two Collections Declared based on Table Type c_los_ms_tbl_ret and c_los_ms_tbl

    在主脚本的BEGIN块中，使用单元ID作为参数调用返回类型为t_los_ms_tbl的Collection的函数los_func().

    In the BEGIN block of the Main script the Function los_func() that returns a Collection of type t_los_ms_tbl is called using the Apartment IDs as it's parameters.

    错误报告-

    Error report -

    ORA-06550:第44行，第32列: PLS-00306:调用"MULTISET_UNION_ALL"时参数的数量或类型错误

    ORA-06550: line 44, column 32: PLS-00306: wrong number or types of arguments in call to 'MULTISET_UNION_ALL'

    推荐答案

    MULTISET UNION用于从两个嵌套表中创建一个嵌套表.您正在尝试使用MULTISET UNION将嵌套表和单个记录连接在一起.

    MULTISET UNION is used to create one nested table from two nested tables. You are trying to use MULTISET UNION to join together a nested table and a single record.

    有两种方法可以解决此问题:

    There are two ways you can fix this:

    1. 从单个记录中制成一个单元素表:

    1. Make a one-element table out of the single record:

        c_los_ms_tbl_ret  := c_los_ms_tbl_ret MULTISET UNION t_los_ms_tbl(los_ms_row);
    

  • 放弃使用MULTISET UNION并将新记录追加到表中:

  • Forgo the use of MULTISET UNION and just append the new record to the table:

        c_los_ms_tbl_ret.EXTEND(1);
        c_los_ms_tbl_ret(c_los_ms_tbl_ret.COUNT) := los_ms_row;
    

  这篇关于Oracle-如何将记录添加到相同类型的集合(多集联合)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！