如何遍历Oracle PLSQL中的分隔列表 [英] How to loop through a delimited list in Oracle PLSQL

问题描述

我正在研究一个Oracle过程，该过程在其中调用了另一个过程.我的参数(parm1)之一可以在逗号分隔的列表中包含一个或多个值.如何遍历这些值以一次将它们传递给另一过程?

I am working on an Oracle procedure that calls another procedure within it. One of my parameters (parm1) can contain one or more values in a comma separated list. How can I loop through these values to pass them one at a time to another procedure?

以下是我想要执行的操作的示例:

Here is an example of what I would like it to do:

When Parm1 = 123,312

callProcedure2(123)
callProcedure2(321)

-或-

When Parm1 123

callProcedure2(123)

我认为可以使用循环来完成此操作，但是我不知道如何在循环中将每个值用作单独的调用来获取它.

I think this can be accomplished using a loop but I can't figure out how to get it to use each value as a separated call within the loop.

任何帮助将不胜感激！

谢谢！

推荐答案

只需遍历子字符串:

declare 
  parm1 varchar2(1000) := '123,234,345,456,567,789,890';

  vStartIdx binary_integer;
  vEndIdx   binary_integer;
  vCurValue varchar2(1000);
begin

  vStartIdx := 0;
  vEndIdx   := instr(parm1, ','); 

  while(vEndIdx > 0) loop
    vCurValue := substr(parm1, vStartIdx+1, vEndIdx - vStartIdx - 1);

    -- call proc here
    dbms_output.put_line('->'||vCurValue||'<-');

    vStartIdx := vEndIdx;
    vEndIdx := instr(parm1, ',', vStartIdx + 1);  
  end loop;

  -- Call proc here for last part (or in case of single element)
  vCurValue := substr(parm1, vStartIdx+1);
  dbms_output.put_line('->'||vCurValue||'<-');

end;

这篇关于如何遍历Oracle PLSQL中的分隔列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！