逗号分隔的字符串字段，关联表查询

场景1

有两个表，一个表记录了某些产品的公共信息，产品id用逗号分隔拼成字符串作为一个字段；一个表示产品表。如下：

公共信息表（info）

id	pro_ids	num
1	12,14	10000.0
2	20,22	200000.0

产品表（product）

id	name
12	产品12
14	产品14
20	产品20
22	产品22

现在以公共信息表为主表，根据pro_ids字段关联产品表，预期效果如下：

结果表

pro_ids	pro_names	num
12,14	产品12，产品14	10000.0
20,22	产品20，产品22	200000.0

SQL：

select
    t.pro_ids,
    wm_concat(t1.name) pro_names
from info t,product t1
where instr(t.pro_ids, t1.id) > 0
group by pro_ids

场景2

依旧是上面的公共信息表info。有一个公共信息的操作页面，用户往这个表新增记录的时候，需要判断，已添加过的产品不能再次添加。

• 注意：用户新增信息的时候，产品是多选的，产品id以逗号分隔的形式提交字符串，如下：
• 提交参数：“11,13,20”，20在第二条记录中已经出现了，所以就不能添加

问题：两个以逗号分隔字符串（pro_ids字段 和 提交的字符串）如何匹配

思路：把提交的字符串参数，转换成表的形式，就和场景1一样了

SQL：

select 
    t1.COLUMN_VALUE as id 
from info t,TABLE(fn_split(#{ids},',')) t1
where instr(','|| t.pro_ids || ',',','|| t1.COLUMN_VALUE || ',')>0

 创建 记录表 数据结构：

CREATE OR REPLACE TYPE "TY_STR_SPLIT" IS TABLE OF VARCHAR2 (8000)

自定义 分隔字符串 函数： 

CREATE OR REPLACE FUNCTION fn_split
(
  p_str       IN VARCHAR2,
  p_delimiter IN VARCHAR2 DEFAULT ','
) RETURN ty_str_split IS
  j         INT := 0;
  i         INT := 1;
  len       INT := 0;
  len1      INT := 0;
  str       VARCHAR2(32767);
  str_split ty_str_split := ty_str_split();
BEGIN
  len  := length(p_str);
  len1 := length(p_delimiter);

  WHILE j < len LOOP
    j := instr(p_str, p_delimiter, i);

    IF j = 0 THEN
      j   := len;
      str := substr(p_str, i);
      str_split.extend;
      str_split(str_split.count) := str;

      IF i >= len THEN
        EXIT;
      END IF;
    ELSE
      str := substr(p_str, i, j - i);
      i   := j + len1;
      str_split.extend;
      str_split(str_split.count) := str;
    END IF;
  END LOOP;

  RETURN str_split;
END fn_split;

• 之所以使用前后加上逗号的方式匹配判断，是因为怕出现如下情况：
• pro_ids = ”12,14“，COLUMN_VALUE=”1“，这样COLUMN_VALU就出现在pro_ids中了，但我们的期望是，只有12和14才能匹配上，改成如下：
• pro_ids = “,12,14,”，COLUMN_VALUE=“,1,”，这样COLUMN_VALU就不存在pro_ids中了