PL-SQL 存储过程拆分字符串 [英] PL-SQL stored procedure split string

问题描述

我想做的是:

在java中我有一个像['AB','BC','CD','DE']这样的数组，我想把它连接到像AB、BC、CD、DE"这样的东西并将它发送到程序作为论据.

In java I have an array like ['AB','BC','CD','DE'] which I want to concat to something like "AB,BC,CD,DE" and send it to the procedure as an argument.

在程序中，我的想法是，我想做类似

In the procedure, my idea is, I would like to do something like

v_passedArgs --(AB,BC,CD,DE)

SELECT * FROM SOME_TABLE WHERE SOME_COL IN (v_passedArgs.split(','))

是否可以做类似的事情，或者您有其他想法?谢谢

Is it possible to do something like that or maybe you have another idea? Thank you

推荐答案

您必须创建自己的函数.

You have to create your own function.

您可以使用 Oracle PL/SQL 集合；这是一段代码，用于从带有给定分隔符 (p_sep) 的输入字符串列表 (p_list) 中返回此类集合:

You can work with an Oracle PL/SQL collection; here is a piece of code to return such a collection, from an input string list (p_list) with a given separator (p_sep):

CREATE OR REPLACE TYPE t_my_list AS TABLE OF VARCHAR2(100);
CREATE OR REPLACE
FUNCTION cto_table(p_sep in Varchar2, p_list IN VARCHAR2)
  RETURN t_my_list
AS
  l_string VARCHAR2(32767) := p_list || p_sep;
  l_sep_index PLS_INTEGER;
  l_index PLS_INTEGER := 1;
  l_tab t_my_list     := t_my_list();
BEGIN
  LOOP
    l_sep_index := INSTR(l_string, p_sep, l_index);
    EXIT
  WHEN l_sep_index = 0;
    l_tab.EXTEND;
    l_tab(l_tab.COUNT) := TRIM(SUBSTR(l_string,l_index,l_sep_index - l_index));
    l_index            := l_sep_index + 1;
  END LOOP;
  RETURN l_tab;
END cto_table;
/

然后如何将它与 SELECT 中的 TABLE 关键字一起使用 - TABLE 关键字将集合转换为可在 Oracle SQL 中使用的对象查询:

Then how to use it with the TABLE keyword in your SELECT - the TABLE keyword converts the collection into a object usable inside Oracle SQL queries:

SELECT * FROM SOME_TABLE WHERE SOME_COL IN (
      select * from TABLE(cto_table(',', v_passedArgs))
)

这篇关于PL-SQL 存储过程拆分字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！