Sqlserver 自定義函數(shù) Function使用介紹
一.FUNCTION:
在sqlserver2008中有3中自定義函數(shù)：標量函數(shù)/內(nèi)聯(lián)表值函數(shù)/多語句表值函數(shù)，首先總結下他們語法的異同點：
同點：
1.創(chuàng)建定義是一樣的： a, CREATE FUNCTION F_NAME(傳入的參數(shù)名稱 傳入?yún)?shù)的類型) b,RETURNS 返回值類型 c,AS 異點：1.標量函數(shù)返回的是一個數(shù)據(jù)類型值，內(nèi)聯(lián)表值函數(shù)返回的是一個table，而多語句返回的是一個table的變量（類似前面兩個的結合）；
2.語法的結構：標量函數(shù)和多語句函數(shù)都是要有begin，，，end，內(nèi)聯(lián)表值函數(shù)就沒有；
3.調(diào)用：標量函數(shù)要寫成在dbo,function_name；
標量函數(shù)，利用上篇文章寫的數(shù)據(jù)表，在[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
/*上面是標量函數(shù)的一個簡單的例舉，下面就開始調(diào)用，注意是在dbo.下*/
SELECT [姓名] FROM[T_員工信息] WHERE [姓名]='李異峰' /**對比的查詢*/
/*請注意觀察，和對比*/
SELECT [姓名],DBO.F_GONGHAO ('李異峰')AS 工號 FROM[T_員工信息] WHERE [姓名]='李異峰'
GO
F5下：
內(nèi)聯(lián)表值函數(shù)：他返回的可是一個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帶入函數(shù)中*/
SELECT*
FROM [F_DAKAJILU]('2014/02/28')
GO
F5：
多語句表值函數(shù)就是，表值函數(shù)+內(nèi)聯(lián)表值函數(shù)綜合版：就寫個返回當天打卡記錄和涉及員工的個人信息：
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，當要檢索復雜的數(shù)據(jù)的每條數(shù)據(jù)的時候就可以用到它，類似于c語言的指針一樣，他可以在你的數(shù)據(jù)表中閱歷每條數(shù)據(jù)和更新。 1.新寫個cursor閱歷數(shù)據(jù)表(T_員工信息)的數(shù)據(jù)過程： 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.閱歷每條數(shù)據(jù)：
/*聲明4個變量用來接收暫存游標中的數(shù)據(jù)*/ DECLARE @XINGMING NVARCHAR(3),@GONGHAO INT,@ZHIWEI NVARCHAR(10),@BUMEN NVARCHAR(8) /*利用全局變量求取當前數(shù)據(jù)表中的數(shù)據(jù)行數(shù)和*/ PRINT'當前數(shù)據(jù)表中有'+CAST(@@CURSOR_ROWS AS NVARCHAR(6))+'行數(shù)據(jù)。' /*讀取第一條數(shù)據(jù)并存入暫存變量中*/ FETCH FIRST FROM[CUR_T_員工信息]INTO @XINGMING,@GONGHAO,@ZHIWEI,@BUMEN /*利用@@FETCH_STATUS返回的數(shù)值來確定cursor在數(shù)據(jù)表中讀取的情況=0為fetch執(zhí)行成功*/ 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_員工信息 /*釋放內(nèi)存*/ DEALLOCATE GLOBAL CUR_T_員工信息 GO
這樣的話就實現(xiàn)了讀取每條記錄的功能，然后就可以用它來更新相關的數(shù)據(jù)條（所有的工號值+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)+'條數(shù)據(jù)行。' 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語言所編寫的程序,同時也能在高級語言上調(diào)用其存儲過程。 3.1.無參數(shù)：說一個調(diào)用查詢數(shù)據(jù)表的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調(diào)用結果插入到新表：
USE SQL_SYSTEM SELECT* INTO AA FROM[T_yuangongxinxi] GO TRUNCATE TABLE AA --TRUNCATE與DELETE的區(qū)別就是TR效率高于DE，而且DE清空時會在日志里面留下恢復記錄 GO INSERT INTO AA EXECUTE PRO_SELECT_T GO
結果就不截圖了， 3.2.帶參pro，寫個修改后輸出修改的信息： 例如公司的員工經(jīng)常有職位變動的情況，就寫個這方面的，給出變動員工的工號就可以修改該員工的職位，然后將修改的操作反饋出來：
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 = '已經(jīng)成功將工號為：【'+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，觸發(fā)器，類似于一個地雷一樣，只要你觸犯了他的要求的話，他就開始工作了，也可以利用這個功能去維持或阻擋某些不想發(fā)生的錯誤操作， DDL：如：不許刪除某個【AA】表：
USE SQL_SYSTEM GO CREATE TRIGGER DROP_T ON DATABASE FOR DROP_TABLE AS BEGIN ROLLBACK TRANSACTION PRINT'不能刪除的,因為我已經(jīng)添加了觸發(fā)保護！' END
drop：
DROP TABLE AA
F5：
DML，是解決對最低層數(shù)據(jù)的問題： 在這里就存在臨時的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] /*將更新成的數(shù)據(jù)插入到【T_UPDATE_HOU】中*/ INSERT INTO T_UPDATE_QIAN SELECT* FROM[deleted] /*將更新前的數(shù)據(jù)插入到【T_UPDATE_QIAN】中*/ PRINT'更新完畢，更新前的數(shù)據(jù)已經(jīng)寫入到【T_UPDATE_QIAN】，更新后的數(shù)據(jù)插入到【T_UPDATE_HOU】。' END
update：
USE SQL_SYSTEM GO UPDATE[AA] SET AA.職位 ='SQL高級工程師' WHERE AA.工號=101 /*以上是個簡單的更新操作，用于update觸發(fā)*/ SELECT* FROM[T_UPDATE_HOU] --修改成的數(shù)據(jù) SELECT* FROM[T_UPDATE_QIAN] --待修改的數(shù)據(jù) GO
F5：
在去年的的時候我寫了一個利用trigger對多表連接的view進行更新的操作：
相關文章
sql05不能用了,數(shù)據(jù)庫又沒個08怎么辦呢 這時候不用卸載vs10 和sql08、05，只需要安裝一個sql08就行了2011-12-12
可帶查詢條件的SQL語句的分頁存儲過程,在項目開發(fā)中很實用，感興趣的朋友可以了解下，希望本文可以鞏固你的存儲過程的相關知識2013-01-01
在select語句中可以使用groupby子句將行劃分成較小的組，然后，使用聚組函數(shù)返回每一個組的匯總信息，另外，可以使用having子句限制返回的結果集2012-11-11
在項目開發(fā)中，經(jīng)常出現(xiàn)這樣的需求。在新增或修改一個主表數(shù)據(jù)時，對應的從表也要進行同步，此時我們是怎么操作的了？2011-07-07
對于包含敏感數(shù)據(jù)的庫，要實現(xiàn)備份加密。即備份文件別人拿到也不能還原和查看其中的數(shù)據(jù)2012-07-07
創(chuàng)建用戶定義函數(shù)，它是返回值的已保存的 Transact-SQL 例程。用戶定義函數(shù)不能用于執(zhí)行一組修改全局數(shù)據(jù)庫狀態(tài)的操作。與系統(tǒng)函數(shù)一樣，用戶定義函數(shù)可以從查詢中喚醒調(diào)用。也可以像存儲過程一樣，通過 EXECUTE 語句執(zhí)行2012-07-07
SqlServer數(shù)據(jù)庫中文亂碼問題解決方法
SQL版的亂碼問題還是出現(xiàn)在SQL SERVER的安裝設置上。默認安裝時系統(tǒng)默認的排序規(guī)則是拉丁文的排序規(guī)則，但一般人在安裝時沒有考慮到這一點，安裝時只是點取下一步，安裝完成后，造成了SQL版在使用過程中出現(xiàn)亂碼2020-03-03
本篇文章是對如何在sqlserver代理中配置郵件會話進行了詳細的分析介紹，需要的朋友參考下2013-06-06
關于SQL Server數(shù)據(jù)庫中的用戶權限和角色管理功能實現(xiàn)
在本文中,我們介紹了在SQL Server中創(chuàng)建用戶、分配權限和管理角色的基礎知識,請記住定期審查和更新用戶權限,以符合您組織的安全政策和數(shù)據(jù)訪問要求,這篇文章主要介紹了關于SQL Server數(shù)據(jù)庫中的用戶權限和角色管理,需要的朋友可以參考下2024-03-03
這篇文章主要介紹了sql帶分隔符的截取字符串示例,需要的朋友可以參考下2014-03-03