PL/SQL 存储过程，使用逗号分隔的参数来驱动动态 LIKE 子句

Question

有没有一种相当简单的方法来获取包含逗号分隔前缀列表的输入参数，并根据使用这些前缀的选择语句返回游标？

即(伪代码(

PROCEDURE get_by_prefix(p_list_of_prefixes IN varchar2, r_csr OUT SYS_REFCURSOR)
IS
BEGIN
OPEN r_csr FOR
SELECT * FROM my_table where some_column LIKE (the_individual_fields_from p_list_of_prefixes ||'%')
END

我已经尝试了各种组合，现在有两个问题 - 强制输入到合适的表中(我认为它需要进入表类型而不是VARCHAR2_TABLE(，其次让 like 子句有效地成为内部"伪表"中的 SELECT...

编辑：似乎人们正在建议使用带有一组潜在值的"IN"的方法 - 而我正在考虑使用LIKE。 我可以使用类似的技术 - 构建动态 SQL，但想知道是否有更优雅的方法......

Answer 1

PL/SQL没有逗号分隔列表的概念，也没有像Perl等那样的内置拆分器，所以你必须使用一个手动滚动的方法，比如这个：

https://stewashton.wordpress.com/2016/08/01/splitting-strings-surprise

(其他方法可用。然后，只需在一个步骤中填充集合并在下一个步骤中使用它，或者将两者组合成这样：

declare
    p_list_of_prefixes varchar2(100) := 'John,Jim,Jules,Janice,Jenny';
begin
    open :refcur for
        with params as
             ( select x.firstname
               from   xmltable(
                          'ora:tokenize($X, ",")'
                          passing p_list_of_prefixes as x
                          columns firstname varchar2(4000) path '.'
                      ) x
             )
           , people as
             ( select 'Dave Clark' as fullname from dual union all
               select 'Jim Potter' from dual union all
               select 'Jenny Jones' from dual
             )
        select x.firstname, p.fullname
        from   params x
               left join people p on p.fullname like x.firstname || '%';
end;

输出：

FIRSTNAME      FULLNAME
-------------- -----------
John           
Jim            Jim Potter
Jules          
Janice         
Jenny          Jenny Jones

Answer 2

按照您想要的方式使用LIKE很容易，但这是错误的解决方案。(请参阅原始帖子下的评论(。

无论如何 - 如果根据上级的命令或其他一些半合法的原因，您必须使用LIKE条件，它应该看起来像这样：

... where ',' || p_list_of_whatever || ',' like '%,' || some_column || ',%

需要在比较的两端连接逗号，因为您不希望列中的Jo与输入列表中的John匹配。从那里开始，您将看到为什么需要右侧的逗号，然后从那里开始，您将看到为什么在左侧也需要逗号。