Sqlserver 自定義函數 Function使用介紹

2024-08-31 01:04:16

字體：大中小

來源：轉載

供稿：網友

一.FUNCTION:
在sqlserver2008中有3中自定義函數：標量函數/內聯表值函數/多語句表值函數，首先總結下他們語法的異同點：

同點：
1.創建定義是一樣的： a, CREATE FUNCTION F_NAME(傳入的參數名稱傳入參數的類型) b,RETURNS 返回值類型 c,AS 異點：1.標量函數返回的是一個數據類型值，內聯表值函數返回的是一個table，而多語句返回的是一個table的變量（類似前面兩個的結合）；
2.語法的結構：標量函數和多語句函數都是要有begin，，，end，內聯表值函數就沒有；
3.調用：標量函數要寫成在dbo,function_name；

標量函數，利用上篇文章寫的數據表，在[T_員工信息]表中查姓名為李異峰員工的工號：

USE SQL_SYSTEM GOCREATE FUNCTION F_GONGHAO(@XINGMING NVARCHAR(5))RETURNS INT ASBEGINDECLARE @GONGHAO INTSET @GONGHAO =(SELECT Y.工號 FROM[T_員工信息] AS Y WHERE Y.姓名 =@XINGMING )RETURN @GONGHAOENDGO/*上面是標量函數的一個簡單的例舉，下面就開始調用，注意是在dbo.下*/SELECT [姓名] FROM[T_員工信息] WHERE [姓名]='李異峰' /**對比的查詢*//*請注意觀察，和對比*/SELECT [姓名],DBO.F_GONGHAO ('李異峰')AS 工號 FROM[T_員工信息] WHERE [姓名]='李異峰'GO

F5下：

Sqlserver,Function

內聯表值函數：他返回的可是一個table哦，比如：篩選出2014.2.28號所有員工的打卡記錄：

USE SQL_SYSTEM GOCREATE FUNCTION F_DAKAJILU(@RIQI DATE)RETURNS TABLEASRETURN(SELECT* FROM[T_考勤]AS K WHERE K.日期 =@RIQI )GO/*需要注意的就是他的寫法上沒有begin+end*//*下面就是將date帶入函數中*/SELECT*FROM [F_DAKAJILU]('2014/02/28')GO

F5：

Sqlserver,Function

多語句表值函數就是，表值函數+內聯表值函數綜合版：就寫個返回當天打卡記錄和涉及員工的個人信息：

USE SQL_SYSTEM GOCREATE FUNCTION F_D_DAKAJILU(@RIQI DATE)RETURNS @TEMP_TABLE TABLE( /*這里要注意的就是既然是多語句的話，那你就要告訴計算機你要查詢的列是哪些*/[姓名]NVARCHAR(5) NOT NULL,[工號]INT NOT NULL,[職位]NVARCHAR(10) NOT NULL,[部門]NVARCHAR(5) NOT NULL,[是否夜班]NCHAR(1) NOT NULL,[日期]DATE NOT NULL,[當天上班時間]FLOAT(1)NOT NULL) /*以上就是要告訴計算機你要的一個基本信息列*/ASBEGININSERT INTO @TEMP_TABLE /*這句的意思是將下面的查詢結果插入到@TEMP_TABLE變量中去*/SELECT Y.姓名 ,Y.工號 ,Y.職位 ,Y.部門 ,K.夜班 ,K.日期 ,K.當天上班時間 FROM[T_員工信息] AS Y CROSS JOIN [T_考勤] AS K  /*這里我用的是交叉連接*/WHERE Y.工號 =K.工號 AND K.日期=@RIQI RETURN ENDGOSELECT*FROM[F_D_DAKAJILU]('2014/02/28')GO

F5：

Sqlserver,Function

二：CURSOR，當要檢索復雜的數據的每條數據的時候就可以用到它，類似于c語言的指針一樣，他可以在你的數據表中閱歷每條數據和更新。 1.新寫個cursor閱歷數據表(T_員工信息)的數據過程： 1.1.聲明一個只讀cursor：

USE SQL_SYSTEM SET TRANSACTION ISOLATION LEVEL REPEATABLE READBEGIN TRANSACTION CURSOR_READ_T_員工信息DECLARE CUR_T_員工信息 SCROLL CURSOR FORSELECT Y.姓名 ,Y.工號 ,Y.職位 ,Y.部門 FROM[T_員工信息]AS Y ORDER BY Y.工號 ASC COMMIT TRANSACTION CURSOR_READ_T_員工信息GO

1.2.打開：

OPEN GLOBAL CUR_T_員工信息GO

1.3.閱歷每條數據：

/*聲明4個變量用來接收暫存游標中的數據*/DECLARE @XINGMING NVARCHAR(3),@GONGHAO INT,@ZHIWEI NVARCHAR(10),@BUMEN NVARCHAR(8)/*利用全局變量求取當前數據表中的數據行數和*/PRINT'當前數據表中有'+CAST(@@CURSOR_ROWS AS NVARCHAR(6))+'行數據。'/*讀取第一條數據并存入暫存變量中*/FETCH FIRST FROM[CUR_T_員工信息]INTO @XINGMING,@GONGHAO,@ZHIWEI,@BUMEN/*利用@@FETCH_STATUS返回的數值來確定cursor在數據表中讀取的情況=0為fetch執行成功*/WHILE(@@FETCH_STATUS =0)BEGINPRINT'姓名：'+@XINGMING+' 工號：'+CONVERT(VARCHAR(3),@GONGHAO)+' 職位：'+@ZHIWEI+' 部門：'+@BUMENFETCH NEXT FROM[CUR_T_員工信息] INTO @XINGMING,@GONGHAO,@ZHIWEI,@BUMENENDGO

F5:

Sqlserver,Function

1.4.用完后就關閉和釋放：

/*關閉*/CLOSE GLOBAL CUR_T_員工信息/*釋放內存*/DEALLOCATE GLOBAL CUR_T_員工信息GO

這樣的話就實現了讀取每條記錄的功能，然后就可以用它來更新相關的數據條（所有的工號值+100）：

USE SQL_SYSTEM /*下面聲明和打開update——cursor*/SET TRANSACTION ISOLATION LEVEL REPEATABLE READBEGIN TRANSACTION TR_UPDATE_YG DECLARE CUR_T_YG CURSOR FORSELECT YG.姓名 ,YG.工號 ,YG.職位 ,YG.部門 FROM[T_yuangongxinxi]AS YG FOR UPDATE OPEN GLOBAL CUR_T_YGCOMMIT TRANSACTION TR_UPDATE_YG

UPDATE:

PRINT'當前有'+CONVERT(VARCHAR(3),@@CURSOR_ROWS)+'條數據行。'FETCH NEXT FROM[CUR_T_YG]WHILE(@@FETCH_STATUS=0)BEGINUPDATE[T_yuangongxinxi] SET[工號] =[工號]+100WHERE CURRENT OF CUR_T_YGFETCH NEXT FROM[CUR_T_YG] END

SELECT:

Sqlserver,Function

關閉釋放：

CLOSE GLOBAL CUR_T_YGDEALLOCATE GLOBAL CUR_T_YG

三：PROCEDURE,存儲過程是利用SQL Server所提供的Transact-SQL語言所編寫的程序,同時也能在高級語言上調用其存儲過程。 3.1.無參數：說一個調用查詢數據表的pro：

USE SQL_SYSTEM GOCREATE PROCEDURE PRO_SELECT_TWITH RECOMPILE ASSELECT* FROM[T_yuangongxinxi] GOEXECUTE:EXECUTE PRO_SELECT_T

F5：

Sqlserver,Function

3.1.2.EXECUTE PRO_SELECT_T在這里其實就是一個view，還可以把execute調用結果插入到新表：

USE SQL_SYSTEM SELECT*INTO AA FROM[T_yuangongxinxi] GOTRUNCATE TABLE AA --TRUNCATE與DELETE的區別就是TR效率高于DE，而且DE清空時會在日志里面留下恢復記錄GOINSERT INTO AAEXECUTE PRO_SELECT_TGO

結果就不截圖了， 3.2.帶參pro，寫個修改后輸出修改的信息：例如公司的員工經常有職位變動的情況，就寫個這方面的，給出變動員工的工號就可以修改該員工的職位，然后將修改的操作反饋出來：

USE SQL_SYSTEM GOCREATE PROCEDURE PRO_DAUP_ZHIWEI@GONGHAO INT,@ZHIWEI NVARCHAR(10),@RETURNS NVARCHAR(50) OUTPUTASBEGIN/*獲取更新前的職位信息*/DECLARE @QIAN_RETURN_ZHIWEI NVARCHAR(10),@XINGMING NVARCHAR(3)SELECT @QIAN_RETURN_ZHIWEI =AA.職位 ,@XINGMING =AA.姓名 FROM[AA]WHERE AA.工號 =@GONGHAO /*更新*/UPDATE[AA] SET[職位] =@ZHIWEI WHERE AA.工號 =@GONGHAO SET @RETURNS = '已經成功將工號為：【'+CONVERT(VARCHAR(3),@GONGHAO)+'】,姓名為：【'+@XINGMING+'】,職位【'+@QIAN_RETURN_ZHIWEI+'】更新為：【'+@ZHIWEIEND

EXECUTE:

DECLARE @PRINTF NVARCHAR(50);EXECUTE PRO_DAUP_ZHIWEI 101,'SQL工程師',@PRINTF OUTPUT SELECT @PRINTF AS '更新消息'GO

F5:

Sqlserver,Function

PRO就說到這里了；
四：TRIGGER，觸發器，類似于一個地雷一樣，只要你觸犯了他的要求的話，他就開始工作了，也可以利用這個功能去維持或阻擋某些不想發生的錯誤操作， DDL：如：不許刪除某個【AA】表：

USE SQL_SYSTEM GOCREATE TRIGGER DROP_TON DATABASE FOR DROP_TABLE ASBEGINROLLBACK TRANSACTION PRINT'不能刪除的,因為我已經添加了觸發保護！'END

drop：

DROP TABLE AA

F5：

Sqlserver,Function

DML，是解決對最低層數據的問題：在這里就存在臨時的2個表：deleted 和 inserted 邏輯（概念）表，要搞明白就要知道trigger的工作原理： insert 操作時：

Sqlserver,Function

delete 操作時：

Sqlserver,Function

update 操作時：

Sqlserver,Function

簡單舉個例子來說下update的操作：也就是重點看inserted和deleted表上：

USE [SQL_SYSTEM]GO/****** Object: Trigger [dbo].[UPDATE_T] Script Date: 03/04/2014 16:04:21 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE TRIGGER [dbo].[UPDATE_T]ON [dbo].[AA] INSTEAD OF UPDATEASBEGININSERT INTO T_UPDATE_HOUSELECT* FROM[inserted] /*將更新成的數據插入到【T_UPDATE_HOU】中*/INSERT INTO T_UPDATE_QIANSELECT*FROM[deleted] /*將更新前的數據插入到【T_UPDATE_QIAN】中*/PRINT'更新完畢，更新前的數據已經寫入到【T_UPDATE_QIAN】，更新后的數據插入到【T_UPDATE_HOU】。'END

update：

USE SQL_SYSTEM GOUPDATE[AA] SET AA.職位 ='SQL高級工程師'WHERE AA.工號=101/*以上是個簡單的更新操作，用于update觸發*/SELECT* FROM[T_UPDATE_HOU] --修改成的數據SELECT* FROM[T_UPDATE_QIAN] --待修改的數據GO

F5：

Sqlserver,Function