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

Question

这是我的函数

create or replace FUNCTION split(
i_str    IN  VARCHAR2,
i_delim  IN  VARCHAR2 DEFAULT ':'
) RETURN TABLE  
AS 
BEGIN
RETURN SELECT trim(regexp_substr(i_str, '[^'||i_delim||']+', 1, LEVEL)) str 
FROM projet
CONNECT 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

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

Answer 1

返回数据集的一种方法是使用SYS_REFCURSOR。这样的函数不能从SQL中调用，但可以从编程语言(例如PL/SQL和c#)中调用。

CREATE OR REPLACE FUNCTION split
(
i_str    IN  VARCHAR2,
i_delim  IN  VARCHAR2 DEFAULT ':'
) RETURN SYS_REFCURSOR
AS
v_cursor SYS_REFCURSOR;
BEGIN
OPEN v_cursor FOR
SELECT trim(regexp_substr(i_str, '[^'||i_delim||']+', 1, LEVEL)) str 
FROM DUAL
CONNECT 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
BEGIN
FOR rec IN
(
SELECT trim(regexp_substr(i_str, '[^'||i_delim||']+', 1, LEVEL)) AS str 
FROM projet
CONNECT BY instr(i_str, i_delim, 1, LEVEL - 1) > 0
) LOOP
PIPE ROW (rec.str);
END LOOP;
END split;

可以从SQL中调用流水线函数:

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

我已经用FROM DUAL代替了FROM projet，因为您的查询与projet表数据无关。