sqlserver 自定義函數(shù)-2023修改整理

千里之行，始于足下讓知識帶有溫度。第第2頁/共2頁精品文檔推薦sqlserver自定義函數(shù)--計算當前月的實際天數(shù)

CreateFUNCTIONdbo.CalcDaysOfMonth(@timevarchar(6))

RETURNSint

AS

BEGIN

DECLARE@Daysint

DECLARE@Monthint

DECLARE@Yearint

SET@Year=SUBSTRING(@time,1,4)

SET@Month=SUBSTRING(@time,5,6)

if(

@Month='1'

OR@Month='3'

OR@Month='5'

OR@Month='7'

OR@Month='8'

OR@Month='10'

OR@Month='12'

)

set@Days=31

elseif(

@Month='4'

OR@Month='6'

OR@Month='9'

OR@Month='11'

)

set@Days=30;

else

if(@Year%400=0OR(@Year%4=0AND@Year%1000))

set@Days=29

else

set@Days=28

RETURN(@Days)

END

--確定某年某月有多少天

CreateFUNCTIONDaysInMonth(@datedatetime)Returnsint

AS

BEGIN

RETURNDay(dateadd(mi,-3,DATEADD(m,DATEDIFF(m,0,@date)+1,0)))

END

--哪一天是輸入時間的星期一

CreateFUNCTIONMondayInDate(@datedatetime)RETURNSDATETIME

AS

BEGIN

RETURNDATEADD(week,DATEDIFF(week,0,@date),0)

END

--輸入時間的季度的第一天

CreateFUNCTIONQuarterInDate(@datedatetime)RETURNSDATETIME

AS

BEGIN

RETURNDATEADD(quarter,DATEDIFF(quarter,0,@date),0)

END

--輸入時間的季度的天數(shù)

CreateFUNCTIONQuarterDaysInDate(@datedatetime)RETURNSINT

AS

BEGIN

declare@mtinyint,@timeSMALLDATETIME

select@m=month(@date)

select@m=casewhen@mbetween1and3then1

when@mbetween4and6then4

when@mbetween7and9then7

else10end

select@time=datename(year,@date)+'-'+convert(varchar(10),@m)+'-01'

returndatediff(day,@time,dateadd(mm,3,@time))

END

--按指定符號分割字符串，返回分割后的元素個數(shù)，方法很簡單，就是看字符串中存在多少個分隔符號，然后再加一，就是要求的結果。

CreatefunctionGet_StrArrayLength

(

@strvarchar(1024),--要分割的字符串

@splitvarchar(10)--分隔符號

)

returnsint

as

begin

declare@locationint

declare@startint

declare@lengthint

set@str=ltrim(rtrim(@str))

set@location=charindex(@split,@str)

set@length=1

while@location0

begin

set@start=@location+1

set@location=charindex(@split,@str,@start)

set@length=@length+1

end

return@length

END

--按指定符號分割字符串，返回分割后指定索引的第幾個元素，象數(shù)組一樣便利

CreatefunctionGet_StrArrayStrOfIndex

(

@strvarchar(1024),--要分割的字符串

@splitvarchar(10),--分隔符號

@indexint--取第幾個元素

)

returnsvarchar(1024)

as

begin

declare@locationint

declare@startint

declare@nextint

declare@seedint

set@str=ltrim(rtrim(@str))

set@start=1

set@next=1

set@seed=len(@split)

set@location=charindex(@split,@str)

while@location0and@index>@next

begin

set@start=@location+@seed

set@location=charindex(@split,@str,@start)

set@next=@next+1

end

if@location=0select@location=len(@str)+1

--這兒存在兩種狀況：1、字符串不存在分隔符號2、字符串中存在分隔符號，跳出while循環(huán)后，@location為0，那默認為字

符串后邊有一個分隔符號。

returnsubstring(@str,@start,@location-@start)

END

selectdbo.Get_StrArrayStrOfIndex('8,9,4','',4)

--結合上邊兩個函數(shù)，象數(shù)組一樣遍歷字符串中的元素

createfunctionf_splitstr(@SourceSqlvarchar(8000),@StrSepratevarchar(100))

returns@temptable(F1varchar(100))

as

begin

declare@chasvarchar(100)

set@SourceSql=@SourceSql+@StrSeprate

while(@SourceSql0

Select@str=REPLACE(@str,

SUBSTRING(@str,@i,1),

NCHAR(UNICODE(SUBSTRING(@str,@i,1))+@step))

,@i=PATINDEX(@patCOLLATELATIN1_GENERAL_BIN,@str)

RETURN(@str)

END

GO

declare@s1varchar(8000)

select@s1='中２-３４５６a７８ＳＴＵＶabｎ中國ｏｐｗｘｙｚ'

selectdbo.f_convert(@s1,0),dbo.f_convert(@s1,1)

函數(shù)返回值是表

createtabletest(idintprimarykey,namechar(10))

insertintotestvalues(1,'test1')

insertintotestvalues(2,'test2')

insertintotestvalues(3,'test3')

insertintotestvalues(4,'test4')

1、標量函數(shù)

createfunctionreturn_count()

returnsint

as

begin

declare@countint

select@count=count(*)fromtest

return@count

end

--調用

selectdbo.return_count()cont--count為顯示的列頭

--運行結果

--count

--4

2、內嵌表值函數(shù)

createfunctionreturn_test()

returnstable

as

--begin內聯(lián)表值函數(shù)不能用begin-end

returnselectnamefromtest

--end

--調用

select*fromreturn_test()

--運行結果

--name

--test1

--test2

--test3

--test4

3、多語句表值函數(shù)

createfunctionreturn_test_multi()

returns@temptable(idint,namechar(10))

as

begin

insertinto@tempselect*fromtestwhereidin(1,2)

return--記住，一定不要遺忘寫return

end

--調用

select*fromdbo.return_test_multi()

--運行結果

--idname

--1test1

--2test2

在查詢結果中增強一個自動增長的ID

selectid=identity(int,1,1),*into#TfromtestTable

select*from#T

droptable#T

sql刪除重復的記錄

打開測試數(shù)據(jù)庫test，并以表w01為例，將下面的SQL語句放入sql2000查詢分析器中，一段一段執(zhí)行即可看

到效果

在sql2000下創(chuàng)建測試數(shù)據(jù)表

ifexists(select*fromdbo.sysobjectswhereid=object_id(N'[dbo].[w01]')andOBJECTPROPERTY(id,N'IsUserTable')=1)

droptable[dbo].[w01]

在sql2022下創(chuàng)建測試數(shù)據(jù)表，假如是sql2022則用本段來推斷數(shù)據(jù)表是否存在

ifexists(select1fromsys.tableswherename='w01')

droptablew01

開頭創(chuàng)建測試數(shù)據(jù)庫

GO

createtablew01(gs903varchar(32),gs1002varchar(32))

insertintow01

select'1','a'

unionallselect'1','a'

unionallselect'1','a'

unionallselect'2','a'

unionallselect'2','e'

unionallselect'3','b'

unionallselect'3','d'

go

select*fromw01

go

為表w01添加一個可以表示唯一標示的自增字段ID

altertablew01add[ID][int]IDENTITY(1,1)NOTFORREPLICATIONNOTNULL

查詢刪除前的數(shù)據(jù)和記錄數(shù)：7

select*fromw01

selectcount(*)fromw01

查詢具有重復記錄的全部記錄;3

selectgs903,gs1002,count(*)ascountfromw01groupbygs903,gs1002

havingcount(*)>1

orderbycountdesc

刪除重復的數(shù)據(jù)：2行

deletefromw01whereidnotin(selectmax(id)fromw01groupbygs903,gs1002)

看看刪除后還有沒有重復記錄：0

selectgs903,gs1002,count(*)ascountfromw01groupbygs903,gs1002

havingcount(*)>1

orderbycountdesc

刪除后的數(shù)據(jù)和記錄數(shù)：7-2=5

select*fromw01

selectcount(*)fromw01

用SQL語句添加刪除修改字段

增強字段

altertabledocdspadddspcodechar(200)

刪除字段

AlterTABLEtable_NAMEDropCOLUMNcolumn_NAME

修改字段類型

AlterTABLEtable_nameAlterCOLUMNcolumn_namenew_data_type

改名

sp_rename

更改當前數(shù)據(jù)庫中用戶創(chuàng)建對象（如表、列或用戶定義數(shù)據(jù)類型）的名稱。

語法

sp_rename[@objname=]'object_name',

[@newname=]'new_name'

[,[@objtype=]'object_type']

--假設要處理的表名為:tb

--推斷要添加列的表中是否有主鍵

ifexists(selec