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

原创 2011年01月14日 00:25:00
------------------------------------

--用途:返回给定类别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自定义函数function

用户定义自定义函数像内置函数一样返回标量值,也可以将结果集用表格变量返回。 sql函数必须有返回值。标量函数:返回一个标量值。 表格值函数{内联表格值函数、多表格值函数}:返回行集(即返回多个值)...
  • qq_23833037
  • qq_23833037
  • 2016年11月15日 14:37
  • 6339

sql中常用函数的用法

1、sql中sum和count的区别                        ----sql聚合函数       (1)首先,sum是对一个字段求和,hive中字段的类型一般是string或者...
  • u011138533
  • u011138533
  • 2015年12月24日 11:58
  • 1106

sql server 自定义函数的使用

自定义函数用户定义自定义函数可以像内置函数一样返回标量值(一个确定类型的值(SQL语言支持的数据类型)),也可以将结果集用表格变量返回据此,用户自定义函数的类型也分为两种:标量函数:返回一个标量值表格...
  • Illusion_1202
  • Illusion_1202
  • 2016年07月21日 17:14
  • 939

SQL Server用户自定义函数

前几篇博文介绍了不少SQL Server系统自带函数方面的使用方法,这次将学习SQL Sever用户自定义函数的使用方法,算是对SQL Server函数的补充。希望与园里的朋友共同学习并取经。 ...
  • duanbeibei
  • duanbeibei
  • 2016年05月04日 10:24
  • 935

SQL中的自定义函数总结

自定义函数:根据返回值的类型可以分为标量函数(返回单个值)和表值函数(返回一个表)  Page  174; SQL中的自定义函数总共有三种 (1)标量函数——标量值函数 语法结构: CREATE ...
  • adreammaker
  • adreammaker
  • 2017年03月29日 20:43
  • 567

Microsoft SQL Server 自定义函数整理大全

SQL SERVER中是允许有自定义函数,Microsoft SQL Server 并不将用户限制在定义为 Transact-SQL 语言一部分的内置函数上,而是允许用户创建自己的用户定义函数。 函数...
  • ycl295644
  • ycl295644
  • 2016年02月29日 11:02
  • 2243

SQL Server 自定义函数

标量值函数 如果RETUR NS 子句指定一种标量数据类型,则函数为标量值函数 如果RETURNS 子句指定 TABLE,则函数为表值函数 如果RETURNS 子句指定的TABLE 不附带列,则该函数...
  • QingHeShiJiYuan
  • QingHeShiJiYuan
  • 2016年01月06日 15:45
  • 2087

sqlserver 自定义、删除、执行 函数和存储过程

自定义函数:```if (object_id('p_wm_get_dynstore', 'P') is not null) drop proc p_wm_get_dynstoregocreate ...
  • meylovezn
  • meylovezn
  • 2016年03月03日 11:50
  • 3419

自定义函数导致的sql性能问题

优化自定义函数
  • wanbin6470398
  • wanbin6470398
  • 2017年11月29日 10:00
  • 138

Spark SQL 用户自定义函数UDF、用户自定义聚合函数UDAF 教程(Java踩坑教学版)

http://www.cnblogs.com/xing901022/p/6436161.html 在Spark中,也支持Hive中的自定义函数。自定义函数大致可以分为三种...
  • zdy0_2004
  • zdy0_2004
  • 2017年02月24日 00:00
  • 530
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:常用sql自定义函数以及存储过程
举报原因:
原因补充:

(最多只允许输入30个字)