Oracle PLSQL extend type with date abs timestamp-CodePudding

I have the following code, which seems to be working. Is there a way I can add a date, timestamp to the type definition? If so, how would reference the columns so I can INSERT data into them. For example, let's say I want to add 10 seconds to SYSDATE and SYSTIMESTAMP for each new row?

 create table t ( x int );

 declare
      type numlist is table of number index by pls_integer;
      s numlist;
    begin
      for i in 1 .. 100000
      loop
        s(i) := i;
      end loop;
      forall i in 1 .. 100000
      insert into t values (s(i));
   end;
   /

CodePudding user response：

Using exactly the same method as you did with numbers:

create table t ( x int, y DATE, z TIMESTAMP WITH TIME ZONE );

Then

DECLARE
  TYPE numlist IS TABLE OF NUMBER;
  TYPE datelist IS TABLE OF DATE;
  TYPE timestamplist IS TABLE OF TIMESTAMP WITH TIME ZONE;
  xs numlist       := numlist();
  ys datelist      := datelist();
  zs timestamplist := timestamplist();

  n     CONSTANT PLS_INTEGER              := 100;
  nowd  CONSTANT DATE                     := SYSDATE;
  nowts CONSTANT TIMESTAMP WITH TIME ZONE := SYSTIMESTAMP;
BEGIN
  xs.EXTEND(n);
  ys.EXTEND(n);
  zs.EXTEND(n);
  FOR i IN 1 .. n LOOP
    xs(i) := i;
    ys(i) := nowd   (i - 1) * INTERVAL '10' SECOND;
    zs(i) := nowts   (i - 1) * INTERVAL '10' SECOND;
  END LOOP;

  FORALL i IN 1 .. n
    INSERT INTO t (x, y, z) VALUES (xs(i), ys(i), zs(i));
END;
/

db<>fiddle here

CodePudding user response：

I think this is what you're looking for:

CREATE TABLE t (
  c1 INT,
  dt DATE
);

DECLARE
  TYPE numlist IS
    TABLE OF t%rowtype INDEX BY PLS_INTEGER;
  s    numlist;
  l_dt DATE := sysdate;
BEGIN
  FOR i IN 1..10 LOOP
    s(i).c1 := i;
    s(i).dt := l_dt;
    l_dt := l_dt   INTERVAL '10' SECOND;
  END LOOP;

  FORALL i IN 1..10
    INSERT INTO t (
      c1,
      dt
    ) VALUES (
      s(i).c1,
      s(i).dt
    );

END;
/

CodePudding user response：

You can create a type and array of type like so

CREATE or REPLACE TYPE example_type IS OBJECT (
    your_num             NUMBER,
    time      TIMESTAMP,
    date      DATE
    );

CREATE TYPE array_of_example_type IS TABLE OF example_type;

Then you can cycle through like:

FOR i in array_of_example_type.FIRST .. array_of_example_type.LAST
LOOP 
END;

Then use oracle functions like add_months(), add_years(), to_date() to add whatever you want.

In the for loop, you can reference the object if the data is already in the object like:

array_of_example_type(i).your_num;
array_of_example_type(i).date;
array_of_example_type(i).time;