如何使用select软件编写针对profile_id= 1的get 4活动？

Question

我想编写一个select查询，以获得针对profile id= 1的4个活动。

Answer 1

屏幕截图显示秘密活动ID作为逗号分隔的值字符串传递，该字符串具有4个值(例如'1,6,7,8')，这些值构成“4个活动”；每个活动都应该存储在自己的行中。

样本表：

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.

过程:诀窍是将p_activity_id分割成行。

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.

测试：

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>