我的包中有一個函式，旨在將逗號分隔的 varchar2 輸入拆分為行，即。'一，二，三'變成：
- 一
- 二
- 三
我已將函式宣告為：
function unpack_list(
string_in in varchar2
) return result_str
is
result_rows result_str;
begin
with temp_table as (
SELECT distinct trim(regexp_substr(string_in, '[^,] ', 1, level)) str
FROM (SELECT string_in FROM dual) t
CONNECT BY instr(string_in, ',', 1, level - 1) > 0)
select str bulk collect into result_rows from temp_table;
RETURN result_rows;
end;
和回傳型別為：
type result_str is table of varchar2(100);
但是，呼叫函式如下：
select * from unpack_list('one1, two2')
給出以下錯誤：
ORA-00902: Invalid datatype
任何想法是什么原因造成的？
uj5u.com熱心網友回復：
您正在呼叫一個 PL/SQL 函式，該函式從 SQL 背景關系回傳一個 PL/SQL 集合型別（都在您的包中定義）。你不能直接這樣做。您可以從 PL/SQL 背景關系呼叫該函式，將結果分配給相同型別的變數，但這不是您嘗試使用它的方式。db<>fiddle顯示您的設定、錯誤以及它在 PL/SQL 塊中的作業。
您可以改為在架構級別宣告型別，如@Littlefoot 所示：
create type result_str is table of varchar2(100);
并洗掉會發生沖突的包定義；適用于 SQL 和 PL/SQL ( db<>fiddle )。
或者，如果您無法創建模式級別型別，則可以使用內置型別：
function unpack_list(
string_in in varchar2
) return sys.odcivarchar2list
is
result_rows sys.odcivarchar2list;
begin
with temp_table as (
SELECT distinct trim(regexp_substr(string_in, '[^,] ', 1, level)) str
FROM (SELECT string_in FROM dual) t
CONNECT BY instr(string_in, ',', 1, level - 1) > 0)
select str bulk collect into result_rows from temp_table;
RETURN result_rows;
end;
這也適用于 SQL 和 PL/SQL ( db<>fiddle )。
或者你可以使用流水線函式，你的 PL/SQL 集合型別：
function unpack_list(
string_in in varchar2
) return result_str pipelined
is
begin
for r in (
SELECT distinct trim(regexp_substr(string_in, '[^,] ', 1, level)) str
FROM (SELECT string_in FROM dual) t
CONNECT BY instr(string_in, ',', 1, level - 1) > 0)
loop
pipe row (r.str);
end loop;
RETURN;
end;
它適用于 SQL 或在 PL/SQL 中運行的 SQL，但不能直接分配給集合變數（db<>fiddle）。
您采用哪種方法取決于您需要如何真正呼叫該函式。可能存在一些性能差異，但您可能不會注意到，除非它們被重復和密集地呼叫。
uj5u.com熱心網友回復：
錯誤的原因前面已經描述過了，所以我將發布另一個可能的解決方案。對于 Oracle 19c（版本 19.7）及更高版本，您可以跳過創建表型別并使用SQL_MACRO添加。回傳的查詢將被集成到主查詢中。
create function unpack_list ( string_in varchar2 ) return clob sql_macro(table) is begin return q'[ select distinct trim(regexp_substr( unpack_list.string_in, '[^,] ', 1, level )) as str from dual connect by instr( unpack_list.string_in, ',', 1, level - 1 ) > 0 ]'; end; /
select * from unpack_list( string_in => 'one,two' )| 字串 | | :-- | | 一個 | | 二 |
db<>在這里擺弄
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/432943.html
上一篇：如何使游標選擇表資料更改？