Sqlserver自訂函數Function

標籤：

一.FUNCTION: 在sqlserver2008中有3中自訂函數：純量涵式/內聯資料表值函式/多語句資料表值函式，首先總結下他們文法的異同點： 同點：1.建立定義是一樣的：                                        a, CREATE FUNCTION F_NAME(傳入的參數名稱    傳入參數的類型)                                        b,RETURNS         傳回值類型                                        c,AS 異點：1.純量涵式返回的是一個資料類型值，內聯資料表值函式返回的是一個table，而多語句返回的是一個table的變數（類似前面兩個的結合）；            2.文法的結構：純量涵式和多語句函數都是要有begin，，，end，內聯資料表值函式就沒有；            3.調用：純量涵式要寫成在dbo,function_name；
純量涵式，利用上篇文章寫的資料表，在[T_員工資訊]表中查姓名為李異峰員工的工號：

 1 USE SQL_SYSTEM  2 GO 3 CREATE FUNCTION F_GONGHAO(@XINGMING NVARCHAR(5)) 4 RETURNS INT  5 AS 6 BEGIN 7 DECLARE @GONGHAO INT 8 SET @GONGHAO =(SELECT Y.工號  FROM[T_員工資訊] AS Y WHERE Y.姓名 [email protected] ) 9 RETURN @GONGHAO10 END11 GO12 /*上面是純量涵式的一個簡單的例舉，下面就開始調用，注意是在dbo.下*/13 SELECT [姓名] FROM[T_員工資訊] WHERE [姓名]=‘李異峰‘  /**對比的查詢*/14 /*請注意觀察，和對比*/15 SELECT [姓名],DBO.F_GONGHAO (‘李異峰‘)AS 工號 FROM[T_員工資訊] WHERE [姓名]=‘李異峰‘16 GO

 

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

 1 USE SQL_SYSTEM  2 GO 3 CREATE FUNCTION F_DAKAJILU(@RIQI DATE) 4 RETURNS TABLE 5 AS 6 RETURN( 7 SELECT* FROM[T_考勤]AS K WHERE K.日期 =@RIQI  8 ) 9 GO10 /*需要注意的就是他的寫法上沒有begin+end*/11 /*下面就是將date帶入函數中*/12 SELECT*13 FROM [F_DAKAJILU](‘2014/02/28‘)14 GO

F5：

 

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

 1 USE SQL_SYSTEM  2 GO 3 CREATE FUNCTION F_D_DAKAJILU(@RIQI DATE) 4 RETURNS @TEMP_TABLE TABLE(   /*這裡要注意的就是既然是多語句的話，那你就要告訴電腦你要查詢的列是哪些*/ 5 [姓名]NVARCHAR(5) NOT NULL, 6 [工號]INT NOT NULL, 7 [職位]NVARCHAR(10) NOT NULL, 8 [部門]NVARCHAR(5) NOT NULL, 9 [是否夜班]NCHAR(1) NOT NULL,10 [日期]DATE NOT NULL,11 [當天上班時間]FLOAT(1)NOT NULL12 )   13 /*以上就是要告訴電腦你要的一個基本資料列*/14 AS15 BEGIN16 INSERT INTO @TEMP_TABLE    /*這句的意思是將下面的查詢結果插入到@TEMP_TABLE變數中去*/17 SELECT Y.姓名 ,Y.工號 ,Y.職位 ,Y.部門 ,K.夜班 ,K.日期 ,K.當天上班時間 18 FROM[T_員工資訊] AS Y CROSS JOIN [T_考勤] AS K     /*這裡我用的是交叉串連*/19 WHERE Y.工號 =K.工號 AND K.日期=@RIQI 20 RETURN 21 END22 GO23 SELECT*24 FROM[F_D_DAKAJILU](‘2014/02/28‘)25 GO

F5：

 

 

二：CURSOR，當要檢索複雜的資料的每條資料的時候就可以用到它，類似於c語言的指標一樣，他可以在你的資料表中閱曆每條資料和更新。 1.新寫個cursor閱曆資料表(T_員工資訊)的資料過程： 1.1.聲明一個唯讀cursor：

1 USE SQL_SYSTEM 2 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ3 BEGIN TRANSACTION CURSOR_READ_T_員工資訊4 DECLARE CUR_T_員工資訊 SCROLL CURSOR FOR5 SELECT Y.姓名 ,Y.工號 ,Y.職位 ,Y.部門  FROM[T_員工資訊]AS Y ORDER BY Y.工號 ASC 6 COMMIT TRANSACTION CURSOR_READ_T_員工資訊7 GO

1.2.開啟：

1 OPEN GLOBAL CUR_T_員工資訊2 GO

1.3.閱曆每條資料：

 1 /*聲明4個變數用來接收暫存遊標中的資料*/ 2 DECLARE @XINGMING NVARCHAR(3),@GONGHAO INT,@ZHIWEI NVARCHAR(10),@BUMEN NVARCHAR(8) 3 /*利用全域變數求取當前資料表中的資料行數和*/ 4 PRINT‘當前資料表中有‘+CAST(@@CURSOR_ROWS AS NVARCHAR(6))+‘行資料。‘ 5 /*讀取第一條資料並存入暫存變數中*/ 6 FETCH FIRST FROM[CUR_T_員工資訊]INTO @XINGMING,@GONGHAO,@ZHIWEI,@BUMEN 7 /*利用@@FETCH_STATUS返回的數值來確定cursor在資料表中讀取的情況=0為fetch執行成功*/ 8 WHILE(@@FETCH_STATUS =0) 9 BEGIN10 PRINT‘姓名：‘+@XINGMING+‘ 工號：‘+CONVERT(VARCHAR(3),@GONGHAO)+‘ 職位：‘+@ZHIWEI+‘ 部門：‘+@BUMEN11 FETCH NEXT FROM[CUR_T_員工資訊] INTO @XINGMING,@GONGHAO,@ZHIWEI,@BUMEN12 END13 GO

F5:

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

1 /*關閉*/2 CLOSE GLOBAL CUR_T_員工資訊3 /*釋放記憶體*/4 DEALLOCATE GLOBAL CUR_T_員工資訊5 GO

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

1 USE SQL_SYSTEM 2 /*下面聲明和開啟update——cursor*/3 SET TRANSACTION ISOLATION LEVEL REPEATABLE READ4 BEGIN TRANSACTION TR_UPDATE_YG 5 DECLARE CUR_T_YG  CURSOR FOR6 SELECT YG.姓名 ,YG.工號 ,YG.職位 ,YG.部門  FROM[T_yuangongxinxi]AS YG FOR 7 UPDATE 8 OPEN GLOBAL CUR_T_YG9 COMMIT TRANSACTION TR_UPDATE_YG

UPDATE:

1 PRINT‘當前有‘+CONVERT(VARCHAR(3),@@CURSOR_ROWS)+‘條資料行。‘2 FETCH NEXT FROM[CUR_T_YG]3 WHILE(@@FETCH_STATUS=0)4 BEGIN5 UPDATE[T_yuangongxinxi] 6 SET[工號] =[工號]+1007 WHERE CURRENT OF CUR_T_YG8 FETCH NEXT FROM[CUR_T_YG] 9 END

SELECT:

關閉釋放：

1 CLOSE GLOBAL CUR_T_YG2 DEALLOCATE GLOBAL CUR_T_YG

 

 

三：PROCEDURE,預存程序是利用SQL Server所提供的Transact-SQL語言所編寫的程式,同時也能在進階語言上調用其預存程序。 3.1.無參數：說一個調用查詢資料表的pro：

1 USE SQL_SYSTEM 2 GO3 CREATE PROCEDURE PRO_SELECT_T4 WITH RECOMPILE 5 AS6 SELECT* FROM[T_yuangongxinxi] 7 GO8  EXECUTE:9 EXECUTE PRO_SELECT_T

F5：

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

 

 1 USE SQL_SYSTEM  2 SELECT* 3 INTO AA  4 FROM[T_yuangongxinxi]  5 GO 6 TRUNCATE TABLE AA  --TRUNCATE與DELETE的區別就是TR效率高於DE，而且DE清空時會在日誌裡面留下恢複記錄 7 GO 8 INSERT INTO AA 9 EXECUTE PRO_SELECT_T10 GO

 

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

 1 USE SQL_SYSTEM  2 GO 3 CREATE PROCEDURE PRO_DAUP_ZHIWEI 4 @GONGHAO INT,@ZHIWEI NVARCHAR(10),@RETURNS NVARCHAR(50) OUTPUT 5 AS 6 BEGIN 7 /*擷取更新前的職位資訊*/ 8 DECLARE @QIAN_RETURN_ZHIWEI NVARCHAR(10),@XINGMING NVARCHAR(3) 9 SELECT @QIAN_RETURN_ZHIWEI =AA.職位 ,@XINGMING =AA.姓名 10 FROM[AA]11 WHERE AA.工號 =@GONGHAO 12 /*更新*/13 UPDATE[AA] 14 SET[職位] =@ZHIWEI WHERE AA.工號 =@GONGHAO 15 SET @RETURNS = ‘已經成功將工號為：【‘+CONVERT(VARCHAR(3),@GONGHAO)+‘】,姓名為：【‘+@XINGMING+‘】,職位【‘+16 @QIAN_RETURN_ZHIWEI+‘】更新為：【‘+@ZHIWEI17 END

EXECUTE:

1 DECLARE @PRINTF NVARCHAR(50);2 EXECUTE PRO_DAUP_ZHIWEI 101,‘SQL工程師‘,@PRINTF OUTPUT 3 SELECT @PRINTF AS ‘更新訊息‘4 GO

F5:

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

1 USE SQL_SYSTEM 2 GO3 CREATE TRIGGER DROP_T4 ON DATABASE FOR DROP_TABLE 5 AS6 BEGIN7 ROLLBACK TRANSACTION 8 PRINT‘不能刪除的,因為我已經添加了觸發保護！‘9 END

drop：

1 DROP TABLE AA 

F5：

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

 

 

delete    操作時：

update   操作時：

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

 1 USE [SQL_SYSTEM] 2 GO 3 /****** Object:  Trigger [dbo].[UPDATE_T]    Script Date: 03/04/2014 16:04:21 ******/ 4 SET ANSI_NULLS ON 5 GO 6 SET QUOTED_IDENTIFIER ON 7 GO 8 CREATE TRIGGER [dbo].[UPDATE_T] 9 ON [dbo].[AA] INSTEAD OF UPDATE10 AS11 BEGIN12 INSERT INTO T_UPDATE_HOU13 SELECT* 14 FROM[inserted] 15 /*將更新成的資料插入到【T_UPDATE_HOU】中*/16 INSERT INTO  T_UPDATE_QIAN17 SELECT*18 FROM[deleted] 19 /*將更新前的資料插入到【T_UPDATE_QIAN】中*/20 PRINT‘更新完畢，更新前的資料已經寫入到【T_UPDATE_QIAN】，更新後的資料插入到【T_UPDATE_HOU】。‘21 END

update：

1 USE SQL_SYSTEM 2 GO3 UPDATE[AA] 4 SET AA.職位 =‘SQL進階工程師‘5 WHERE AA.工號=1016 /*以上是個簡單的更新操作，用於update觸發*/7 SELECT* FROM[T_UPDATE_HOU] --修改成的資料8 SELECT* FROM[T_UPDATE_QIAN] --待修改的資料9 GO

F5：

在去年的的時候我寫了一個利用trigger對多表串連的view進行更新的操作：

http://www.cnblogs.com/liyifeng/archive/2013/05/05/3056968.html

Sqlserver自訂函數Function