Mapping table into collection-CodePudding

I have table MYTABLE like this:

|TS|DAY1|DAY2|...................................|DAY31|

I wish to have a table type, which maps this table. How can I do it at best? I though of something like:

CREATE OR REPLACE TYPE DAYS_T IS TABLE OF VARCHAR(250);

CREATE OR REPLACE TYPE MYTYPE_REC AS OBJECT
    (    
        DAY DAYS_T,   
        TS  DATE    
    );

CREATE OR REPLACE TYPE MYTABLE_T AS TABLE OF MYTYPE_REC;

But I wish, that DAY1..DAY31 belongs to record. So I would be able to use it like this:

MYTABLE_T myVarTable := MYTABLE_T();
FOR i IN 1 .. 31 LOOP
    myVarTable(1)(i) := i; 
END LOOP;   
INSERT INTO MYTABLE
SELECT '01.01.21', f.* FROM TABLE(myVarTable) f;

Everything is simplified to show you what I mean. For me is important to be able to put some values to collection in a loop and then insert everything at once into a real table. I can do it in either SQL or PL/SQL scope.

CodePudding user response：

You could use:

DECLARE
  myVarTable MYTABLE_T := MYTABLE_T();
BEGIN
  myVarTable.EXTEND();
  myVarTable(1) := MYTYPE_REC( DAYS_T(), DATE '2021-01-01' );
  myVarTable(1).day.EXTEND(31);

  FOR i IN 1 .. 31 LOOP
    myVarTable(1).day(i) := i; 
  END LOOP;

  INSERT INTO MYTABLE (ts, day1, day2, day3, /* ... */ day30, day31 )
  SELECT ts,
         day1,
         day2,
         day3,
         -- ...
         day30,
         day31
  FROM   (
    SELECT f.ts,
           d.rn,
           d.day
    FROM   TABLE(myVarTable) f
           CROSS APPLY (
             SELECT ROWNUM AS rn,
                    COLUMN_VALUE AS day
             FROM   TABLE(f.day)
           ) d
  )
  PIVOT (
    MAX(day) FOR rn IN (
      1 AS day1,
      2 AS day2,
      3 AS day3,
      -- ...
      30 AS day30,
      31 AS day31
    )
  );
END;
/

db<>fiddle here