关闭

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

296人阅读 评论(0) 收藏 举报
------------------------------------

--用途:返回给定类别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表



0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:14994次
    • 积分:279
    • 等级:
    • 排名:千里之外
    • 原创:9篇
    • 转载:8篇
    • 译文:2篇
    • 评论:2条
    文章分类
    杂记