我想撰寫一個選擇查詢來獲取針對組態檔 id=1 的 4 個活動。
uj5u.com熱心網友回復：
啊哈; 螢屏截圖揭示了秘密 - 活動 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>
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/376662.html