如何将拆分结果推入数组PL SQL并使用ref游标返回数组数据

debugcn 发表于 Dev

伊曼纽尔·安蒂科（Emanuele Antico）

我正在尝试将拆分结果推送到数组中，但是我不知道该怎么做。这是我的存储过程：

   CODE_KZ_i NVARCHAR2 (4000):='';
   FTKZ NVARCHAR2 (4000):='';

   TYPE namesarray IS TABLE OF NVARCHAR2(4000);
   FTarray namesarray:=namesarray();
   i NUMBER;

开始

    FOR FTKZ IN ( SELECT REGEXP_SUBSTR (KZ,'[^;]+', 1,LEVEL)  TXT  FROM DUAL 
                  CONNECT BY REGEXP_SUBSTR (KZ,'[^;]+', 1, LEVEL) IS NOT NULL)

    LOOP

    FTarray.extend;

    IF CONTA = 0 THEN CODE_KZ:=FTKZ.TXT;
    ELSE 
    CODE_KZ:= FTKZ.TXT;

    END IF;

    FTarray(i):= CODE_KZ;
    DBMS_OUTPUT.put_line('FT:'|| FTarray(i));
    i:=i+1;
    CONTA:=CONTA+1;

我的目标是将FTKZ.TXT的结果推入FTarray。有人可以帮我吗？

下一步是在sys_refcursor中返回数组的数据。我在名为KZ的输入中得到了一些代码，我必须返回链接到此表中的kz的另一个代码：SG_AN_FT

    open p_cur_result for
    SELECT DISTINCT FT.CODE as FT INTO P_RESULT FROM SG_AN_FT FT 
          INNER JOIN SG_BOM_PIVOT_PN_KZ BOM
          ON FT.ID = BOM.ID_FT
          INNER JOIN SG_AN_KZ K
          ON K.ID = BOM.ID_KZ
          WHERE K.CODE in (SELECT * FROM TABLE(cast(FTarray as FT));

    Errore(317,60): PL/SQL: ORA-00902: tipo di dati non valido

先感谢您

亚历克斯·普尔

您的循环代码看起来非常接近。为KZ添加定义和虚拟值并清除其他变量，您可以执行以下操作：

declare
  -- made up as not shown in the question
  KZ NVARCHAR2 (4000) := 'A;B;C;D';

  CODE_KZ NVARCHAR2 (4000);

  TYPE namesarray IS TABLE OF NVARCHAR2(4000);
  FTarray namesarray:=namesarray();
  CONTA NUMBER := 1;
begin
  FOR FTKZ IN ( SELECT REGEXP_SUBSTR (KZ,'[^;]+', 1,LEVEL)  TXT  FROM DUAL 
                CONNECT BY REGEXP_SUBSTR (KZ,'[^;]+', 1, LEVEL) IS NOT NULL)
  LOOP
    FTarray.extend;

    -- redundant as both branches do the same thing; also can't be zero
    IF CONTA = 0 THEN
      CODE_KZ:=FTKZ.TXT;
    ELSE 
      CODE_KZ:= FTKZ.TXT;
    END IF;

    FTarray(CONTA):= CODE_KZ;
    DBMS_OUTPUT.put_line('FT:'|| FTarray(CONTA));
    CONTA:=CONTA+1;
  END LOOP;
end;
/

PL/SQL procedure successfully completed.

FT:A
FT:B
FT:C
FT:D

您的IF CONTA = 0检查建议您从索引零开始循环，这将ORA-06532: Subscript outside of limit随着集合从一个非零索引而得到。无论如何，该检查似乎都是多余的，因为您在两个分支中都向变量分配了相同的值，因此您可以FTKZ.TXT直接将其分配给新扩展的数组元素。您根本不需要CONTA或根本不需要CODE_KZ：

declare
  -- made up as not shown in the question
  KZ NVARCHAR2 (4000) := 'A;B;C;D';

  TYPE namesarray IS TABLE OF NVARCHAR2(4000);
  FTarray namesarray:=namesarray();
begin
  FOR FTKZ IN ( SELECT REGEXP_SUBSTR (KZ,'[^;]+', 1,LEVEL)  TXT  FROM DUAL 
                CONNECT BY REGEXP_SUBSTR (KZ,'[^;]+', 1, LEVEL) IS NOT NULL)
  LOOP
    FTarray.extend;
    FTarray(FTArray.count) := FTKZ.TXT;
    DBMS_OUTPUT.put_line('FT:'|| FTarray(FTArray.count));
  END LOOP;
end;
/

PL/SQL procedure successfully completed.

FT:A
FT:B
FT:C
FT:D

但是您也不需要使用循环，可以使用它bulk collect来一次性填充您的集合：

declare
  -- made up as not shown in the question
  KZ NVARCHAR2 (4000) := 'A;B;C;D';

  TYPE namesarray IS TABLE OF NVARCHAR2(4000);
  FTarray namesarray:=namesarray();
begin
  SELECT REGEXP_SUBSTR (KZ,'[^;]+', 1,LEVEL) TXT
  BULK COLLECT INTO FTarray
  FROM DUAL 
  CONNECT BY REGEXP_SUBSTR (KZ,'[^;]+', 1, LEVEL) IS NOT NULL;

  FOR i IN 1..FTarray.count LOOP
    DBMS_OUTPUT.put_line('FT:' || FTarray(i));
  END LOOP;
end;
/

PL/SQL procedure successfully completed.

FT:A
FT:B
FT:C
FT:D

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。