Remove records from TABLE TYPE PLSQL-CodePudding

I have a TABLE TYPE as below.

   TYPE custom_type_rec_ IS RECORD(
        pet_name VARCHAR2(30),
        pet_ctg VARCHAR2(60),
        pet_tpy VARCHAR2(100)
    );
   custom_type_ custom_type_rec_;   

   TYPE custom_type_rec_tab_type_ IS TABLE OF custom_type_rec_ INDEX BY BINARY_INTEGER; 
   custom_type_rec_tab_  custom_type_rec_tab_type_;

   i_ PLS_INTEGER := 0;

and data in above table is as below.

What I need to achieve is, scan the pet_ctg column with values in pet_tpy and if found, need to remove that record.

According to above example, I need to keep only 2 records, where pet_ctg is CTG-Z and CTG-M

How can I achieve this?

I am adding data into table as below.

FOR x_ IN get_data LOOP
   custom_type_.pet_name := x_.pet_name;
   custom_type_.pet_ctg := x_.pet_ctg;
   custom_type_.pet_tpy := x_.pet_tpy;

   i_ := i_   1;
   custom_type_rec_tab_(i_) := custom_type_;
END LOOP;

CodePudding user response：

You can use a WHILE loop to iterate over the associative array and call the DELETE method on the appropriate elements of the array:

DECLARE
  TYPE custom_type_rec_ IS RECORD(
    pet_name VARCHAR2(30),
    pet_ctg  VARCHAR2(60),
    pet_tpy  VARCHAR2(100)
  );

  TYPE custom_type_rec_tab_type_ IS TABLE OF custom_type_rec_ INDEX BY BINARY_INTEGER; 

  custom_type_rec_tab_ custom_type_rec_tab_type_;
  i_                   BINARY_INTEGER;
  next_i_              BINARY_INTEGER;
BEGIN
  custom_type_rec_tab_(1) := custom_type_rec_('AAA', 'CTG-Z', 'CTG-X');
  custom_type_rec_tab_(3) := custom_type_rec_('BBB', 'CTG-X', 'CTG-N');
  custom_type_rec_tab_(5) := custom_type_rec_('CCC', 'CTG-M', NULL);
  custom_type_rec_tab_(7) := custom_type_rec_('DDD', 'CTG-N', NULL);
  
  -- Delete the rows
  i_ := custom_type_rec_tab_.FIRST;
  WHILE i_ IS NOT NULL LOOP
    next_i_ := custom_type_rec_tab_.NEXT(i_);
    IF custom_type_rec_tab_(i_).pet_ctg NOT IN ('CTG-Z', 'CTG-M') THEN
      custom_type_rec_tab_.DELETE(i_);
    END IF;
    i_ := next_i_;
  END LOOP;
  
  -- Print the values in the associative array.
  i_ := custom_type_rec_tab_.FIRST;
  WHILE i_ IS NOT NULL LOOP
    DBMS_OUTPUT.PUT_LINE(
      custom_type_rec_tab_(i_).pet_name
      || ', ' || custom_type_rec_tab_(i_).pet_ctg
      || ', ' || custom_type_rec_tab_(i_).pet_tpy
    );
    i_ := custom_type_rec_tab_.NEXT(i_);
  END LOOP;
END;
/

Which outputs:

AAA, CTG-Z, CTG-X
CCC, CTG-M,

db<>fiddle here

CodePudding user response：

As @asterix suggested, you can use a separate collection - or rather, two collections - to record the values that are used:

DECLARE
...
   TYPE values_tab_type_ IS TABLE OF VARCHAR2(1) INDEX BY VARCHAR2(100);
   ctg_values_tab_ values_tab_type_;
   tpy_values_tab_ values_tab_type_;
...
BEGIN
...
   i_ := custom_type_rec_tab_.FIRST;
   WHILE i_ IS NOT NULL LOOP
      IF custom_type_rec_tab_(i_).pet_ctg IS NOT NULL
         AND NOT ctg_values_tab_.exists((custom_type_rec_tab_(i_).pet_ctg))
      THEN
         ctg_values_tab_(custom_type_rec_tab_(i_).pet_ctg) := 'X';
      END IF;
      IF custom_type_rec_tab_(i_).pet_tpy IS NOT NULL
         AND NOT tpy_values_tab_.exists((custom_type_rec_tab_(i_).pet_tpy))
      THEN
         tpy_values_tab_(custom_type_rec_tab_(i_).pet_tpy) := 'X';
      END IF;
      i_ := custom_type_rec_tab_.NEXT(i_);
   END LOOP;
...

With your example that ends up with ctg_values_tab_ containing indexes CTG-M, CTG-N, CTG-X and CTG-Z, and tpy_values_tab_ having CTG-N and CTG-X.

Then you can loop over the records again and either remove those where either value appears in the other list:

   i_ := custom_type_rec_tab_.FIRST;
   WHILE i_ IS NOT NULL LOOP
      IF (custom_type_rec_tab_(i_).pet_ctg IS NOT NULL
            AND tpy_values_tab_.exists((custom_type_rec_tab_(i_).pet_ctg))
         OR (custom_type_rec_tab_(i_).pet_tpy IS NOT NULL
            AND ctg_values_tab_.exists((custom_type_rec_tab_(i_).pet_tpy))))
      THEN
         custom_type_rec_tab_.DELETE(i_);
      END IF;
      i_ := custom_type_rec_tab_.NEXT(i_);
   END LOOP;

which will only retain the record with CCC, CTG-M and null; or blank the values that appear in the other list, and only delete if both end up null:

   i_ := custom_type_rec_tab_.FIRST;
   WHILE i_ IS NOT NULL LOOP
      -- null used pet_ctg values
      IF custom_type_rec_tab_(i_).pet_ctg IS NOT NULL
         AND tpy_values_tab_.exists((custom_type_rec_tab_(i_).pet_ctg))
      THEN
         custom_type_rec_tab_(i_).pet_ctg := null;
      END IF;
      -- null used pet_tpy values
      IF custom_type_rec_tab_(i_).pet_tpy IS NOT NULL
         AND ctg_values_tab_.exists((custom_type_rec_tab_(i_).pet_tpy))
      THEN
         custom_type_rec_tab_(i_).pet_tpy := null;
      END IF;
      -- delete if both values end up null
      IF custom_type_rec_tab_(i_).pet_ctg IS NULL
         AND custom_type_rec_tab_(i_).pet_tpy IS NULL
      THEN
         custom_type_rec_tab_.DELETE(i_);
      END IF;
      i_ := custom_type_rec_tab_.NEXT(i_);
   END LOOP;

which retain the record with AAA and CTG-Z and the record with CCC and CTG-M.

db<>fiddle

Or some other variation - it isn't entirely clear what you want to happen, for example if you end up with a record that only has pet_tpy set.