首页 > 代码库 > SQL 表值函数操作

SQL 表值函数操作

 

CREATE FUNCTION [dbo].[f_getpy_tb] ( @str VARCHAR(100) )RETURNS @tb TABLE ( re VARCHAR(8000) )AS     BEGIN        DECLARE @re TABLE ( id INT, re VARCHAR(8000) )  --数据处理中间表        DECLARE @i INT ,            @ilen INT ,            @splitchr VARCHAR(1)        SELECT  @splitchr =   --两个拼音之间的分隔符(目的是为了通用性考虑)                ,                @i = 1 ,                @ilen = LEN(@str)        INSERT  INTO @re                SELECT  @i ,                        py                FROM    YingShe                WHERE   chr = SUBSTRING(@str, @i, 1)        WHILE @i < @ilen             BEGIN                SET @i = @i + 1                INSERT  INTO @re                        SELECT  @i ,                                re + @splitchr + py                        FROM    @re a ,                                YingShe b                        WHERE   a.id = @i - 1                                AND b.chr = SUBSTRING(@str, @i, 1)            END        INSERT  INTO @tb                SELECT  re                FROM    @re                WHERE   id = @i        RETURN     ENDGO