我有我的包頭：
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));
uj5u.com熱心網友回復：
我想呼叫這個函式：
應該
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>
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/463391.html