代码之家 › 专栏 › 技术社区 › vytaute

返回表类型时Oracle函数中的类型错误

collections plsql oracle database sql

vytaute · 技术社区 · 2 年前

我有我的包裹头:

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 我从中返回餐厅收藏:

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));

1 回复 | 直到 2 年前

Littlefoot 2 年前

我想调用这个函数:

应该是

restaurants_table my_package.my_type;

而不是

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

我没有你的桌子所以我用了斯科特的 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>