How do I modify the values of a collection inside a table?-CodePudding

Basically, given a table with the below structure, I want to add a city at the end of a list of cities so that it'll be visited last in a trip with a number/id input by keyboard.

CREATE TYPE type_cities IS VARRAY(101) of varchar2(12);
    CREATE TABLE trip(
         trip NUMBER(4),
         name VARCHAR2(20), 
         cities type_cities,
         status varchar2(12) 
         );
    
    declare
        nrTrip number(4) := &nr;
        name_city varchar2(12) := &namecity;
        number_last number(4);
    begin
        number_last = trip(nrTrip).cities.count();
        trip(nrTrip).cities.extend();
        select name_city into trip(nrTrip).cities(number_last 1);
    end;

I don't know the syntax neccessary to do this (and I'd ask why it doesn't work if that's alright, it didn't work with

trip(nrTrip).cities(number_last 1) := name_city

either)

CodePudding user response：

Here's one option: fetch the row first, update it (i.e. add a new city into the array), update the table.

Type and table:

SQL> CREATE TYPE type_cities IS VARRAY(101) of varchar2(12);
  2  /

Type created.

SQL> CREATE TABLE trip(
  2       trip   NUMBER(4),
  3       name   VARCHAR2(20),
  4       cities type_cities,
  5       status varchar2(12)
  6       );

Table created.

Initial record (otherwise, there's nothing to update):

SQL> insert into trip (trip, name, cities, status)
  2    values (1, 'Test', type_cities('London'), 'OK');

1 row created.

Procedure:

SQL> declare
  2      nrTrip    number(4) := &nr;
  3      l_row     trip%rowtype;
  4      name_city varchar2(12) := '&namecity';
  5      number_last number(4);
  6  begin
  7    select *
  8      into l_row
  9      from trip
 10      where trip = nrTrip;
 11
 12    number_last := l_row.cities.count;
 13
 14    l_row.cities.extend;
 15    l_row.cities(number_last   1) := name_city;
 16
 17    update trip set cities = l_row.cities
 18       where trip = nrTrip;
 19  end;
 20  /
Enter value for nr: 1
Enter value for namecity: Zagreb

PL/SQL procedure successfully completed.

Result:

SQL> select * from trip;

      TRIP NAME                 CITIES                              STATUS
---------- -------------------- ----------------------------------- ------------
         1 Test                 TYPE_CITIES('London', 'Zagreb')     OK
                                                      --------
SQL>                                                  here it is