how to write get 4 activities against profile_id= 1 by using select satement?-CodePudding

I want to write a select query to get 4 activities against profile id= 1.

CodePudding user response：

Aha; screenshot reveals the secret - activity ID is passed as a comma-separated values string having 4 values (e.g. '1,6,7,8') which make those "4 activities"; each of them should be stored into its own row.

Sample table:

SQL> create table activity_profile_mapping
  2  (profile_id  number,
  3   activity_id number,
  4   created_by  varchar2(10),
  5   created     timestamp
  6  );

Table created.

Procedure: the trick is to split p_activity_id into rows.

SQL> create or replace procedure apm_add
  2    (p_profile_id    in activity_profile_mapping.profile_id%type,
  3     p_activity_id   in varchar2,
  4     p_created_by    in varchar2
  5    )
  6  as
  7     /* P_ACTIVITY_ID is passed as comma-separated values string and
  8        contains 4 values, e.g. '1,6,7,8'. They represent "4 activities
  9        from the title
 10     */
 11  begin
 12    insert into activity_profile_mapping
 13      (profile_id,
 14       activity_id,
 15       created_by,
 16       created
 17      )
 18      select p_profile_id,
 19             regexp_substr(p_activity_id, '[^,] ', 1, level),
 20             p_created_by,
 21             systimestamp
 22      from dual
 23      connect by level <= regexp_count(p_activity_id, ',')   1;
 24  end apm_add;
 25  /

Procedure created.

Testing:

SQL> begin
  2    apm_add (p_profile_id   => 100,
  3             p_activity_id  => '1,6,7,8',
  4             p_created_by   => 'Littlefoot');
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> select * from activity_profile_mapping;

PROFILE_ID ACTIVITY_ID CREATED_BY CREATED
---------- ----------- ---------- ------------------------------
       100           1 Littlefoot 07.12.21 19:51:52,480000
       100           6 Littlefoot 07.12.21 19:51:52,480000
       100           7 Littlefoot 07.12.21 19:51:52,480000
       100           8 Littlefoot 07.12.21 19:51:52,480000

SQL>