常用sql自定义函数以及存储过程

------------------------------------

--用途：返回给定类别id的树路径[取其名称]

--说明：父节点与子节点路径以>>联结,改为自定义后使用该函数

--时间：2011-1-13 16:40:28

------------------------------------

CREATE      FUNCTION Getpath

               (@id INT)

RETURNS VARCHAR(1000)

AS

  BEGIN

    DECLARE

      @re_str  AS VARCHAR(1000)

    SET @re_str = ''

    SELECT @re_str = classname

    FROM   uds_class

    WHERE  classid = @id

    WHILE EXISTS (SELECT 1

                  FROM   uds_class

                  WHERE  classid = @id

                         AND classparentid <> classid)

      BEGIN

        SELECT @id = b.classid,

               @re_str = b.classname + '>>' + @re_str

        FROM   uds_class a,

               uds_class b

        WHERE  a.classid = @id

               AND a.classparentid = b.classid

      END

    RETURN @re_str

  END

------------------------------------

--用途：返回当前文档多个关键词构造的参照表@tb

--说明：原始关键词以空格分割,改为自定义后使用该函数

--时间：2011-1-13 16:40:28

------------------------------------

CREATE FUNCTION GetRelaTag(@docid int)

RETURNS @tb table([name] varchar(20)) AS

begin

declare @index int

declare @name varchar(20),@tagstr varchar(500)

select @tagstr=tagnamestr from tss_document where docid=@docid

if @tagstr<>'' set @tagstr=@tagstr+space(1)

while(@tagstr<>'')

begin

set @index = charindex(space(1),@tagstr)

set @name = substring(@tagstr,1,@index-1)

set @tagstr = substring(@tagstr,@index+1,len(@tagstr)-@index)

insert into @tb values(@name)

end

return

end

------------------------------------

--用途：获得与当前资料相关的资源

--说明：关键词改为自定义后使用该过程,用到自定义函数GetRelaTag

--时间：2011-1-13 16:40:28

------------------------------------

CREATE  PROCEDURE usp_getrelakey3(

               @docid INT,

               @topN  INT)

AS

SELECT IDENTITY(INT,1,1) as tid,* INTO #tmp FROM GetRelaTag(@docid)

DECLARE @relatable TABLE (

    docid INT,

    tagnamestr  varchar(500))

  DECLARE @resulttable TABLE (

    docid INT,

    score INT) --依据关键词匹配度加分

  INSERT INTO @relatable  SELECT   docid,tagnamestr  FROM  tss_document
   WHERE   docid<>@docid and status='发布' and tagnamestr is not null  ORDER BY docid

  DECLARE  @min INT

  SELECT @min = Min(docid)  FROM   @relatable

  WHILE @min IS NOT NULL

    BEGIN

      BEGIN

        DECLARE @mininner INT

        SELECT @mininner = Min(tid)  FROM   #tmp

        WHILE @mininner IS NOT NULL

          BEGIN

            IF EXISTS (SELECT *  FROM   #tmp  WHERE tid=@mininner and  [name] IN (SELECT *  FROM   GetRelaTag(@min)))

              BEGIN

                IF EXISTS (SELECT *  FROM  @resulttable  WHERE  docid = @min) --已存在则更新分数

                  UPDATE @resulttable  SET score = Isnull(score,0) + 1  WHERE  docid = @min

                 ELSE

                  INSERT INTO @resulttable   VALUES(@min,1)

              END

            SELECT @mininner = Min(tid)  FROM   #tmp  WHERE  tid> @mininner

          END

      END

      SELECT @min = Min(docid)   FROM   @relatable   WHERE  docid > @min --更新“指针”内容，使之移到下一记录

    END

  SET ROWCOUNT  @topN

  SELECT   b.*

  FROM     @resulttable a

           LEFT JOIN tss_document b

             ON a.docid = b.docid

  WHERE b.status='发布'

  ORDER BY a.score DESC

GO

------------------------------------

--用途：获得给定类别id的所有父类包括其本身

--说明：注意自联结的使用

--时间：2011-1-13 16:40:28

------------------------------------

CREATE  FUNCTION getallparentInfor

              (@id INT)

RETURNS @t TABLE(classid INT,className varchar(300))

AS

  BEGIN

    declare @CName as varchar(300)

    select top 1 @CName=className from uds_class where classid=@id

    INSERT INTO @t

    VALUES     (@id,@CName)

    WHILE EXISTS (SELECT 1

                  FROM   uds_class

                  WHERE  classid = @id

                         AND classparentid <> classid)

      BEGIN

        SELECT @id = b.classid,@CName=b.className

        FROM   uds_class a,

               uds_class b

        WHERE  a.classid = @id

               AND a.classparentid = b.classid

        INSERT INTO @t

        VALUES     (@id,@CName)

      END

    RETURN

  END

------------------------------------

--用途：获得给定类别id的所有子类但不包括其本身

--时间：2011-1-13 16:40:28

------------------------------------

create function GetChildren (@id int)

returns @t table(classid int)

as

begin

    insert @t select classid from uds_class where classparentid = @id

    while @@rowcount > 0

        insert @t select a.ClassID from uds_class as a inner join @t as b

        on a.classparentid = b.classid and a.ClassID not in(select classid from @t)

   return

end

------------------------------------

--用途：获得给定类别id的所有子类包括其本身

--说明：用到GetChildren 函数

--时间：2011-1-13 16:40:28

------------------------------------

CREATE  function GetChildrenIncSelf(@ClassID int)

Returns table

AS

Return (select * from GetChildren(@ClassID)

union

select @ClassID ) --包含本身的类别id表

秒客网

常用sql自定义函数以及存储过程

相关文章