返回表类型时，Oracle函数类型错误

Question

我有我的包头:

CREATE OR REPLACE PACKAGE my_package is 
TYPE my_type IS
TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;
FUNCTION my_func return my_type;
END my_package;

和函数my_func的body，我从中返回餐馆集合:

CREATE OR REPLACE PACKAGE BODY my_package is
FUNCTION my_func RETURN my_type IS
restaurants_table my_type; 
BEGIN
select ADRESS
BULK COLLECT  INTO restaurants_table
from restaurants 
FETCH NEXT 3 ROWS ONLY;

RETURN restaurants_table;
END my_func;
END my_package;

我想调用这个函数:

declare 
TYPE my_type IS
TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;
restaurants_table my_type; 
begin
restaurants_table := my_package.my_func();
end;

但是我得到错误:

PLS-00382: expression is of wrong type

当我尝试将函数的返回值分配给变量时，会发生这种情况:

restaurants_table := my_package.my_func();

我怎样才能正确地调用函数，这样我就可以在我的表类型变量restaurants_table返回值?之后我想打印它的索引:

dbms_output.put_line(restaurants_table(1));

Answer 1

我想调用这个函数:

应该

restaurants_table my_package.my_type;

不是

TYPE my_type IS
TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;
restaurants_table my_type; 

---

我没有你的表，所以我用了Scott的DEPT:

SQL> CREATE OR REPLACE PACKAGE my_package is
2      TYPE my_type IS
3         TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;
4      FUNCTION my_func return my_type;
5  END my_package;
6  /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY my_package is
2      FUNCTION my_func RETURN my_type IS
3          restaurants_table my_type;
4      BEGIN
5          select dname
6           BULK COLLECT  INTO restaurants_table
7          from dept
8          FETCH NEXT 3 ROWS ONLY;
9
10          RETURN restaurants_table;
11      END my_func;
12  END my_package;
13  /
Package body created.

测试:

SQL> declare
2    restaurants_table my_package.my_type;
3  begin
4      restaurants_table := my_package.my_func();
5      for i in 1 .. restaurants_table.count loop
6        dbms_output.put_line(restaurants_table(i));
7      end loop;
8  end;
9  /
ACCOUNTING
RESEARCH
SALES
PL/SQL procedure successfully completed.
SQL>

---

另一方面，您可以为此目的使用Oracle的内置类型-sys.odcivarchar2list:

SQL> CREATE OR REPLACE PACKAGE my_package is
2      FUNCTION my_func return sys.odcivarchar2list;
3  END my_package;
4  /
Package created.
SQL> CREATE OR REPLACE PACKAGE BODY my_package is
2      FUNCTION my_func RETURN sys.odcivarchar2list IS
3          restaurants_table sys.odcivarchar2list;
4      BEGIN
5          select dname
6           BULK COLLECT  INTO restaurants_table
7          from dept
8          FETCH NEXT 3 ROWS ONLY;
9
10          RETURN restaurants_table;
11      END my_func;
12  END my_package;
13  /
Package body created.
SQL> set serveroutput on
SQL> declare
2     restaurants_table sys.odcivarchar2list;
3  begin
4      restaurants_table := my_package.my_func();
5
6      for i in 1 .. restaurants_table.count loop
7        dbms_output.put_line(restaurants_table(i));
8      end loop;
9  end;
10  /
ACCOUNTING
RESEARCH
SALES
PL/SQL procedure successfully completed.
SQL>