我们如何在过程中从IN参数获取VARRAY的值 [英] how can we get the value of of VARRAY from IN Parameter in procedure

问题描述

我是PL/SQL的新手...在Varray中，如何从IN参数中获取多个值....否则还有另一种获取值的方法...

I am new to PL/SQL... In Varray how can i get multiple value from IN parameter..... else Is there another ways to get the values...

我想通过VArray插入值...如果有其他选择，那就好了. 编码:

I want to interate the values through VArray... if any other options then its fine.. coding:

CREATE OR REPLACE
PROCEDURE dynamic_query_build(
    vr_plan_sku_id IN VARCHAR2 )
IS
type plan_sku_id_array IS VARRAY(999) OF VARCHAR2(5000);
plan_sku_id plan_sku_id_array;
total           INTEGER;
vrx_plan_sku_id VARCHAR2(3000);
BEGIN
  vrx_plan_sku_id:= REPLACE(vr_plan_sku_id,',',chr(39)||','||chr(39));
  plan_sku_id    := plan_sku_id_array(chr(39)||vrx_plan_sku_id||chr(39));
  total          := plan_sku_id.count;
  FOR i          IN 1 .. total
  LOOP
    dbms_output.put_line(plan_sku_id(i));
  END LOOP;
EXCEPTION
WHEN OTHERS THEN
  raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END dynamic_query_build;

执行:

set serveroutput on;
declare
vr_plan_sku_id varchar2(200) := '5863314,5863315';
BEGIN
   dynamic_query_build(vr_plan_sku_id);
END;
/

我的输出:

anonymous block completed
'5863314','5863315'

预期输出:

5863314
5863315

现在它正在考虑作为单个值....

now it is considering as single value....

推荐答案

我使用过程dynamic_query_build创建了一个匿名块.在其中添加了代码，该代码会将VARCHAR2变量拆分为varray. 我认为，您的问题的关键是这一行-plan_sku_id.EXTEND(); 您可以动态扩展varray，但只能扩展到达到定义的最大值(在您的情况下为999).

I created anonymous block with procedure dynamic_query_build. Added there code, that will split VARCHAR2 variable into varray. I think, the key to your question is this line - plan_sku_id.EXTEND(); You can extend varray dynamically, but only till it reaches defined maximum (in your case - 999).

DECLARE
vr_plan_sku_id varchar2(200) := '5863314,5863315';
PROCEDURE dynamic_query_build(
    vr_plan_sku_id IN VARCHAR2 )
IS
type plan_sku_id_array IS VARRAY(999) OF VARCHAR2(5000);
plan_sku_id plan_sku_id_array;
total           INTEGER;

position PLS_INTEGER := 0;
last_position PLS_INTEGER := 1;
tmp VARCHAR2(5000);
counter PLS_INTEGER := 1;
BEGIN
  plan_sku_id := plan_sku_id_array();
  LOOP
    position := INSTR(vr_plan_sku_id, ',', last_position);
    IF position > 0 THEN
      tmp := SUBSTR(vr_plan_sku_id, last_position, position - last_position);
      last_position := position + 1;
    ELSE
      tmp := SUBSTR(vr_plan_sku_id, last_position);
    END IF;
    plan_sku_id.EXTEND();
    plan_sku_id(counter) := tmp;
    counter := counter + 1;
    EXIT WHEN position = 0 OR counter > 10;
  END LOOP;
  total          := plan_sku_id.count;
  FOR i          IN 1 .. total
  LOOP
    dbms_output.put_line(plan_sku_id(i));
  END LOOP;
EXCEPTION
WHEN OTHERS THEN
  raise_application_error(-20001,'An error was encountered - '||SQLCODE||' -ERROR- '||SQLERRM);
END dynamic_query_build;
BEGIN
   dynamic_query_build(vr_plan_sku_id);
END;
/

这篇关于我们如何在过程中从IN参数获取VARRAY的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！