在oracle SQL中创建返回表的函数

这是我的职责

create or replace FUNCTION split(i_str    IN  VARCHAR2,i_delim  IN  VARCHAR2 DEFAULT ':'
) RETURN TABLE  
AS 
BEGINRETURN SELECT trim(regexp_substr(i_str, '[^'||i_delim||']+', 1, LEVEL)) str FROM projetCONNECT BY instr(i_str, i_delim, 1, LEVEL - 1) > 0;
END;

这是错误代码

PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:     ( - + ; case mod new not null <an identifier>    <a double-quoted delimited-identifier> <a bind variable>    continue avg count current exists max min prior sql stddev    sum variance execute forall merge time timestamp interval    date <a string literal with character set specification>    <a number> <a single-quoted SQL string> pipe    <an alternatively-quoted string literal with character set specification>    <an altern
Errors: check compiler log

如果有人能帮我纠正这个代码，使它能正常工作

 发布于 2 年前

✅ 最佳回答：

返回数据集的一种方法是使用SYS_REFCURSOR。这种函数不能从SQL调用，但可以从编程语言（例如PL/SQL和C）调用。

CREATE OR REPLACE FUNCTION split
(i_str    IN  VARCHAR2,i_delim  IN  VARCHAR2 DEFAULT ':'
) RETURN SYS_REFCURSOR
ASv_cursor SYS_REFCURSOR;
BEGINOPEN v_cursor FORSELECT trim(regexp_substr(i_str, '[^'||i_delim||']+', 1, LEVEL)) str FROM DUALCONNECT BY instr(i_str, i_delim, 1, LEVEL - 1) > 0;RETURN v_cursor;
END split;

返回数据集的另一种方法是流水线函数。为此，您需要创建一个匹配的集合类型。

CREATE OR REPLACE TYPE type_table_of_varchar2 IS TABLE OF VARCHAR2(4000);CREATE OR REPLACE FUNCTION split
(i_str    IN  VARCHAR2,i_delim  IN  VARCHAR2 DEFAULT ':'
) RETURN type_table_of_varchar2 PIPELINED
AS
BEGINFOR rec IN(SELECT trim(regexp_substr(i_str, '[^'||i_delim||']+', 1, LEVEL)) AS str FROM projetCONNECT BY instr(i_str, i_delim, 1, LEVEL - 1) > 0) LOOPPIPE ROW (rec.str);END LOOP;
END split;

可以从SQL调用流水线函数：

SELECT * FROM TABLE( split('ABC:DEF:GHI') );

顺便说一下，我用FROM DUAL替换了FROM projet，因为您的查询与projet表数据无关。