程式師世界 >> 數據庫知識 >> 其他數據庫知識 >> MSSQL >> Sqlserver 自定義函數 Function運用引見

Sqlserver 自定義函數 Function運用引見

編輯：MSSQL

Sqlserver 自定義函數 Function運用引見。本站提示廣大學習愛好者：（Sqlserver 自定義函數 Function運用引見）文章只能為提供參考，不一定能成為您想要的結果。以下是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_員工信息]表中查姓名為李異峰員工的工號：

USE SQL_SYSTEM 
GO
CREATE FUNCTION F_GONGHAO(@XINGMING NVARCHAR(5))
RETURNS INT 
AS
BEGIN
DECLARE @GONGHAO INT
SET @GONGHAO =(SELECT Y.工號 FROM[T_員工信息] AS Y WHERE Y.姓名 =@XINGMING )
RETURN @GONGHAO
END
GO
/*下面是標量函數的一個復雜的例舉，上面就開端調用，留意是在dbo.下*/
SELECT [姓名] FROM[T_員工信息] WHERE [姓名]='李異峰' /**比照的查詢*/
/*請留意察看，和比照*/
SELECT [姓名],DBO.F_GONGHAO ('李異峰')AS 工號 FROM[T_員工信息] WHERE [姓名]='李異峰'
GO

F5下：

內聯表值函數：他前往的可是一個table哦，比方：挑選出2014.2.28號一切員工的打卡記載：

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

F5：

多語句表值函數就是，表值函數+內聯表值函數綜合版：就寫個前往當天打卡記載和觸及員工的團體信息：

USE SQL_SYSTEM 
GO
CREATE 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
) 
/*以上就是要通知計算機你要的一個根本信息列*/
AS
BEGIN
INSERT 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 
END
GO
SELECT*
FROM[F_D_DAKAJILU]('2014/02/28')
GO

F5：

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

USE SQL_SYSTEM 
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION CURSOR_READ_T_員工信息
DECLARE CUR_T_員工信息 SCROLL CURSOR FOR
SELECT 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)
BEGIN
PRINT'姓名：'+@XINGMING+' 工號：'+CONVERT(VARCHAR(3),@GONGHAO)+' 職位：'+@ZHIWEI+' 部門：'+@BUMEN
FETCH NEXT FROM[CUR_T_員工信息] INTO @XINGMING,@GONGHAO,@ZHIWEI,@BUMEN
END
GO

F5:

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

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

這樣的話就完成了讀取每條記載的功用，然後就可以用它來更新相關的數據條（一切的工號值+100）：

USE SQL_SYSTEM 
/*上面聲明和翻開update——cursor*/
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION TR_UPDATE_YG 
DECLARE CUR_T_YG CURSOR FOR
SELECT YG.姓名 ,YG.工號 ,YG.職位 ,YG.部門 FROM[T_yuangongxinxi]AS YG FOR 
UPDATE 
OPEN GLOBAL CUR_T_YG
COMMIT TRANSACTION TR_UPDATE_YG

UPDATE:

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

SELECT:

封閉釋放：

CLOSE GLOBAL CUR_T_YG
DEALLOCATE GLOBAL CUR_T_YG

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

USE SQL_SYSTEM 
GO
CREATE PROCEDURE PRO_SELECT_T
WITH RECOMPILE 
AS
SELECT* FROM[T_yuangongxinxi] 
GO
EXECUTE:
EXECUTE PRO_SELECT_T

F5：

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

USE SQL_SYSTEM 
SELECT*
INTO AA 
FROM[T_yuangongxinxi] 
GO
TRUNCATE TABLE AA --TRUNCATE與DELETE的區別就是TR效率高於DE，而且DE清空時會在日志外面留下恢復記載
GO
INSERT INTO AA
EXECUTE PRO_SELECT_T
GO

後果就不截圖了， 3.2.帶參pro，寫個修正後輸入修正的信息：例如公司的員工常常有職位變化的狀況，就寫個這方面的，給出變化員工的工號就可以修正該員工的職位，然後將修正的操作反應出來：

USE SQL_SYSTEM 
GO
CREATE PROCEDURE PRO_DAUP_ZHIWEI
@GONGHAO INT,@ZHIWEI NVARCHAR(10),@RETURNS NVARCHAR(50) OUTPUT
AS
BEGIN
/*獲取更新前的職位信息*/
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+'】更新為：【'+@ZHIWEI
END

EXECUTE:

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

F5:

PRO就說到這裡了；
四：TRIGGER，觸發器，相似於一個地雷一樣，只需你冒犯了他的要求的話，他就開端任務了，也可以應用這個功用去維持或阻撓某些不想發作的錯誤操作， DDL：如：不許刪除某個【AA】表：

USE SQL_SYSTEM 
GO
CREATE TRIGGER DROP_T
ON DATABASE FOR DROP_TABLE 
AS
BEGIN
ROLLBACK TRANSACTION 
PRINT'不能刪除的,由於我曾經添加了觸發維護！'
END

drop：

DROP TABLE AA

F5：

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

delete 操作時：

update 操作時：

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

USE [SQL_SYSTEM]
GO
/****** Object: Trigger [dbo].[UPDATE_T] Script Date: 03/04/2014 16:04:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [dbo].[UPDATE_T]
ON [dbo].[AA] INSTEAD OF UPDATE
AS
BEGIN
INSERT INTO T_UPDATE_HOU
SELECT* 
FROM[inserted] 
/*將更新成的數據拔出到【T_UPDATE_HOU】中*/
INSERT INTO T_UPDATE_QIAN
SELECT*
FROM[deleted] 
/*將更新前的數據拔出到【T_UPDATE_QIAN】中*/
PRINT'更新終了，更新前的數據曾經寫入到【T_UPDATE_QIAN】，更新後的數據拔出到【T_UPDATE_HOU】。'
END

update：

USE SQL_SYSTEM 
GO
UPDATE[AA] 
SET AA.職位 ='SQL初級工程師'
WHERE AA.工號=101
/*以上是個復雜的更新操作，用於update觸發*/
SELECT* FROM[T_UPDATE_HOU] --修正成的數據
SELECT* FROM[T_UPDATE_QIAN] --待修正的數據
GO

F5：