PLSQL返回关联数组功能 [英] Plsql return associative array function

问题描述

我有一个问题关于PLSQL函数返回一个数组。
我已经找到了很多有关使用数组

I have a question about return an array on plsql function. I already found a lot of information about using arrays

但我挣扎应该返回数组的功能。

But I am struggling on a function which should return an array.

源示例：
的http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/composites.htm#LNPLS99931

当我从谷歌上搜索周围的理解，一种方法返回一个数组首先创建一个表数组拆开，然后用这种类型的函数内。

As I understand from googling around, a way to return an array is first creating a table array apart and then use this type inside the function.

我其实是想做到的是回到它是在函数内部创建一个数组。

What I actually trying to accomplish is to return an array which is created inside the function.

不知道是否我很清楚但在这里我有我code：

Not sure if I am clear but here I have me code:

CREATE OR REPLACE FUNCTION createChecksumArray( checksumNumber VARCHAR2 )
RETURN textGroupArray 
AS
  ..    
  TYPE textGroupArray  IS TABLE OF VARCHAR2(30)
  INDEX BY             BINARY_INTEGER;
  textGroups           textGroupArray;
  ..
BEGIN
  ..
  textGroups := textGroupArray();
  ..
  (A LOOP)
    -- add textgroup into the array
    IF textGroup != 0 THEN
      arrayCount               := arrayCount + 1;
      textGroups( arrayCount ) := TRIM( textGroup );
    END IF;
  (END THE LOOP)
  ..
  RETURN textGroups;

我首先提出的code函数外，进行测试，如果阵列工作。通过输出该阵列从试验结果

I firstly made the code outside a function, for testing if the array was working. The result from the test by outputting the array:

spaced text: 3536 2029 2712 3456 789 

我不能设法返回功能寿内部声明数组。不知道这是可能的，所以问题是：这是可能的，如果是这样，怎么样？笏我在这里干什么错了。

I couldn't manage to return the array declared inside the function tho. Not sure if this is possible, so the question is: Is this possible and if so, how? Wat am I doing here wrong.

我无法找到张贴在含有这种特定的主题＃1的问题。但是，对不起，如果我的搜索没有那么好。

I couldn't found a question posted on Stackoverflow containing this specific subject. But sorry if my searching wasn't that good.

另外，如果你不喜欢我的帖子，请添加一个编辑，所以我可以向你学习。

Also, if you don't like my posting, please add an edit so I can learn from you.

全部code /功能

Full code/function

推荐答案

您需要先创建类型，然后在函数声明中使用，如果。创建一个包，并有声明的类型。

You need to create type first, and then use if in function declaration. Create a package and declare type there.

create or replace package my_pkg as

  type textGroupArray IS TABLE OF VARCHAR2(30) INDEX BY BINARY_INTEGER;

  FUNCTION createChecksumArray(checksumNumber VARCHAR2) RETURN textGroupArray;

end my_pkg;
/

create or replace package body my_pkg as
    FUNCTION createChecksumArray( checksumNumber VARCHAR2 )
    RETURN textGroupArray 
    is
      ..    
      TYPE textGroupArray  IS TABLE OF VARCHAR2(30)
      INDEX BY             BINARY_INTEGER;
      textGroups           textGroupArray;
      ..
    BEGIN
      ..
      textGroups := textGroupArray();
      ..
      (A LOOP)
        -- add textgroup into the array
        IF textGroup != 0 THEN
          arrayCount               := arrayCount + 1;
          textGroups( arrayCount ) := TRIM( textGroup );
        END IF;
      (END THE LOOP)
      ..
      RETURN textGroups;
    end;

end my_pkg;
/

这篇关于PLSQL返回关联数组功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！