如何在oracle存储过程中填充数组? [英] How to populate an array in an oracle stored procedure?

问题描述

如何在存储过程中使用数组(Varray).实际上，我创建了一个存储过程，从中检索元素列表.

How to use array( Varray) in store procedure. Actually,i have make a stored procedure from which i retrieve a list of elements.

例如:

create or replace procedure GetTargetFields ( fileformat  in varchar2,
                                              filefields out Varray(4) )
IS

BEGIN

 SELECT id 
   INTO filefields  
   FROM tablename;

END;

推荐答案

use BULK COLLECT INTO:

SQL> CREATE OR REPLACE TYPE vrray_4 AS VARRAY(4) OF VARCHAR2(10);
  2  /

Type created
SQL> CREATE OR REPLACE PROCEDURE GetTargetFields(fileformat IN VARCHAR2,
  2                                              filefields OUT vrray_4) IS
  3  BEGIN
  4     SELECT dummy BULK COLLECT INTO filefields FROM dual;
  5  END;
  6  /

Procedure created
SQL> DECLARE
  2     x vrray_4;
  3  BEGIN
  4     GetTargetFields(NULL, x);
  5  END;
  6  /

PL/SQL procedure successfully completed

还要确保您的查询不返回超过 4 行(对于 VARRAY(4))，否则您将遇到 ORA-22165

Also make sure that your query doesn't return more than 4 rows (for a VARRAY(4)) or you will run into ORA-22165

这篇关于如何在oracle存储过程中填充数组?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！