[金仓数据库V8R6]return query语法改写sqlserver的表值函数return table语法

--SQLSERVER 语法

CREATE FUNCTION "FUN_SPLIT_MONITVALUE"

(

@V_SOURCE VARCHAR(2000),

@V_STRSEPRATE VARCHAR(10)

)

RETURNS @TEMP TABLE([FIELD] VARCHAR(100))

AS

BEGIN

IF @V_SOURCE IS NULL OR @V_SOURCE = '' --如果输入为空，直接返回

RETURN

IF @V_STRSEPRATE IS NULL OR @V_SOURCE = '' --如果输入为空，直接返回

RETURN

SET @V_SOURCE = REPLACE(@V_SOURCE,'[','')

SET @V_SOURCE = REPLACE(@V_SOURCE,']','')

DECLARE @I INT

SET @V_SOURCE = RTRIM(LTRIM(@V_SOURCE))

SET @I = CHARINDEX(@V_STRSEPRATE,@V_SOURCE)

WHILE @I >= 1

BEGIN

INSERT @TEMP VALUES(LEFT(@V_SOURCE,@I-1))

SET @V_SOURCE = SUBSTRING(@V_SOURCE,@I + 1,LEN(@V_SOURCE) - @I)

SET @I = CHARINDEX(@V_STRSEPRATE,@V_SOURCE)

END

IF @V_SOURCE <> ''

INSERT @TEMP VALUES(@V_SOURCE)

RETURN

END;

---参考改写

--用RETURN query来改写

CREATE or replace FUNCTION FUN_SPLIT_MONITVALUE

(

V_SOURCE VARCHAR(2000),

V_STRSEPRATE VARCHAR(10)

)

RETURNS TABLE("FIELD" VARCHAR(100))

AS

$$

DECLARE 

  I INT;

BEGIN

   if (select oid from sys_class where relname = 't_TEMP') is not null THEN

   drop table t_TEMP;

end if;

   create table t_TEMP("FIELD" VARCHAR(2000));

IF V_SOURCE IS NULL OR V_SOURCE = '' then --如果输入为空，直接返回

RETURN;

 end if;

IF V_STRSEPRATE IS NULL OR V_SOURCE = '' THEN--如果输入为空，直接返回

RETURN;

   end if;

V_SOURCE = REPLACE(V_SOURCE,'[','');

V_SOURCE = REPLACE(V_SOURCE,']','');

V_SOURCE = RTRIM(LTRIM(V_SOURCE));

I = CHARINDEX(V_STRSEPRATE,V_SOURCE);

WHILE I >= 1 loop

INSERT into t_TEMP VALUES(LEFT(V_SOURCE,I-1));

V_SOURCE = SUBSTRING(V_SOURCE,I + 1,LEN(V_SOURCE) - I);

I = CHARINDEX(V_STRSEPRATE,V_SOURCE);

end loop;

IF V_SOURCE <> '' then

INSERT into t_TEMP VALUES(V_SOURCE);

end if;

RETURN query select tt.FIELD from t_TEMP tt;

END;

$$language plpgsql;

--调用

select * from FUN_SPLIT_MONITVALUE();