Sqlserver 自定義函數(shù) Function使用介紹
一.FUNCTION:
在sqlserver2008中有3中自定義函數(shù):標(biāo)量函數(shù)/內(nèi)聯(lián)表值函數(shù)/多語句表值函數(shù),首先總結(jié)下他們語法的異同點(diǎn):
同點(diǎn):
1.創(chuàng)建定義是一樣的: a, CREATE FUNCTION F_NAME(傳入的參數(shù)名稱 傳入?yún)?shù)的類型) b,RETURNS 返回值類型 c,AS 異點(diǎn):1.標(biāo)量函數(shù)返回的是一個(gè)數(shù)據(jù)類型值,內(nèi)聯(lián)表值函數(shù)返回的是一個(gè)table,而多語句返回的是一個(gè)table的變量(類似前面兩個(gè)的結(jié)合);
2.語法的結(jié)構(gòu):標(biāo)量函數(shù)和多語句函數(shù)都是要有begin,,,end,內(nèi)聯(lián)表值函數(shù)就沒有;
3.調(diào)用:標(biāo)量函數(shù)要寫成在dbo,function_name;
標(biāo)量函數(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 /*上面是標(biāo)量函數(shù)的一個(gè)簡單的例舉,下面就開始調(diào)用,注意是在dbo.下*/ SELECT [姓名] FROM[T_員工信息] WHERE [姓名]='李異峰' /**對比的查詢*/ /*請注意觀察,和對比*/ SELECT [姓名],DBO.F_GONGHAO ('李異峰')AS 工號 FROM[T_員工信息] WHERE [姓名]='李異峰' GO
F5下:
內(nèi)聯(lián)表值函數(shù):他返回的可是一個(gè)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ù)綜合版:就寫個(gè)返回當(dāng)天打卡記錄和涉及員工的個(gè)人信息:
USE SQL_SYSTEM GO CREATE FUNCTION F_D_DAKAJILU(@RIQI DATE) RETURNS @TEMP_TABLE TABLE( /*這里要注意的就是既然是多語句的話,那你就要告訴計(jì)算機(jī)你要查詢的列是哪些*/ [姓名]NVARCHAR(5) NOT NULL, [工號]INT NOT NULL, [職位]NVARCHAR(10) NOT NULL, [部門]NVARCHAR(5) NOT NULL, [是否夜班]NCHAR(1) NOT NULL, [日期]DATE NOT NULL, [當(dāng)天上班時(shí)間]FLOAT(1)NOT NULL ) /*以上就是要告訴計(jì)算機(jī)你要的一個(gè)基本信息列*/ AS BEGIN INSERT INTO @TEMP_TABLE /*這句的意思是將下面的查詢結(jié)果插入到@TEMP_TABLE變量中去*/ SELECT Y.姓名 ,Y.工號 ,Y.職位 ,Y.部門 ,K.夜班 ,K.日期 ,K.當(dāng)天上班時(shí)間 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,當(dāng)要檢索復(fù)雜的數(shù)據(jù)的每條數(shù)據(jù)的時(shí)候就可以用到它,類似于c語言的指針一樣,他可以在你的數(shù)據(jù)表中閱歷每條數(shù)據(jù)和更新。 1.新寫個(gè)cursor閱歷數(shù)據(jù)表(T_員工信息)的數(shù)據(jù)過程: 1.1.聲明一個(gè)只讀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個(gè)變量用來接收暫存游標(biāo)中的數(shù)據(jù)*/ DECLARE @XINGMING NVARCHAR(3),@GONGHAO INT,@ZHIWEI NVARCHAR(10),@BUMEN NVARCHAR(8) /*利用全局變量求取當(dāng)前數(shù)據(jù)表中的數(shù)據(jù)行數(shù)和*/ PRINT'當(dāng)前數(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.用完后就關(guān)閉和釋放:
/*關(guān)閉*/ CLOSE GLOBAL CUR_T_員工信息 /*釋放內(nèi)存*/ DEALLOCATE GLOBAL CUR_T_員工信息 GO
這樣的話就實(shí)現(xiàn)了讀取每條記錄的功能,然后就可以用它來更新相關(guā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'當(dāng)前有'+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:
關(guān)閉釋放:
CLOSE GLOBAL CUR_T_YG DEALLOCATE GLOBAL CUR_T_YG
三:PROCEDURE,存儲(chǔ)過程是利用SQL Server所提供的Transact-SQL語言所編寫的程序,同時(shí)也能在高級語言上調(diào)用其存儲(chǔ)過程。 3.1.無參數(shù):說一個(gè)調(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在這里其實(shí)就是一個(gè)view,還可以把execute調(diào)用結(jié)果插入到新表:
USE SQL_SYSTEM SELECT* INTO AA FROM[T_yuangongxinxi] GO TRUNCATE TABLE AA --TRUNCATE與DELETE的區(qū)別就是TR效率高于DE,而且DE清空時(shí)會(huì)在日志里面留下恢復(fù)記錄 GO INSERT INTO AA EXECUTE PRO_SELECT_T GO
結(jié)果就不截圖了, 3.2.帶參pro,寫個(gè)修改后輸出修改的信息: 例如公司的員工經(jīng)常有職位變動(dòng)的情況,就寫個(gè)這方面的,給出變動(dò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ā)器,類似于一個(gè)地雷一樣,只要你觸犯了他的要求的話,他就開始工作了,也可以利用這個(gè)功能去維持或阻擋某些不想發(fā)生的錯(cuò)誤操作, DDL:如:不許刪除某個(gè)【AA】表:
USE SQL_SYSTEM GO CREATE TRIGGER DROP_T ON DATABASE FOR DROP_TABLE AS BEGIN ROLLBACK TRANSACTION PRINT'不能刪除的,因?yàn)槲乙呀?jīng)添加了觸發(fā)保護(hù)!' END
drop:
DROP TABLE AA
F5:
DML,是解決對最低層數(shù)據(jù)的問題: 在這里就存在臨時(shí)的2個(gè)表:deleted 和 inserted 邏輯(概念)表,要搞明白就要知道trigger的工作原理: insert 操作時(shí):
delete 操作時(shí):
update 操作時(shí):
簡單舉個(gè)例子來說下update的操作:也就是重點(diǎn)看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 /*以上是個(gè)簡單的更新操作,用于update觸發(fā)*/ SELECT* FROM[T_UPDATE_HOU] --修改成的數(shù)據(jù) SELECT* FROM[T_UPDATE_QIAN] --待修改的數(shù)據(jù) GO
F5:
在去年的的時(shí)候我寫了一個(gè)利用trigger對多表連接的view進(jìn)行更新的操作:
相關(guān)文章
使用mongovue把sqlserver數(shù)據(jù)導(dǎo)入mongodb的步驟
MongoVUE免費(fèi)版支持MySQL導(dǎo)入Mongo,所以思路是SQLServer導(dǎo)入MySQL,再從MySQL導(dǎo)入Mongo。2014-01-01解決sql server保存對象字符串轉(zhuǎn)換成uniqueidentifier失敗的問題
這篇文章主要介紹了解決sql server保存對象字符串轉(zhuǎn)換成uniqueidentifier失敗的問題,具有很好的參考價(jià)值,希望對大家有所幫助。一起跟隨小編過來看看吧2020-10-10SQL語句實(shí)現(xiàn)查詢SQL Server內(nèi)存使用狀況
這篇文章主要介紹了SQL語句實(shí)現(xiàn)查詢SQL Server內(nèi)存使用狀況,本文直接給出實(shí)現(xiàn)代碼,需要的朋友可以參考下2015-07-07SQL使用登錄名連接數(shù)據(jù)庫報(bào)錯(cuò)(錯(cuò)誤代碼18456問題)
18456是因密碼或用戶名錯(cuò)誤而使身份驗(yàn)證失敗,如果用戶名密碼正確,則該登錄名沒有連接數(shù)據(jù)庫引擎權(quán)限,本文就來介紹一下解決方法,具有一定的參考價(jià)值,感興趣的可以了解一下2023-10-10sqlserver建立新用戶及關(guān)聯(lián)數(shù)據(jù)庫教程
本文講的是SQLserver數(shù)據(jù)庫創(chuàng)建新用戶方法以及賦予此用戶特定權(quán)限的方法,非常的簡單實(shí)用,有需要的小伙伴可以參考下2018-01-01淺析SQL Server的聚焦使用索引和查詢執(zhí)行計(jì)劃
本文通過介紹默認(rèn)使用索引、強(qiáng)制使用聚集索引、強(qiáng)制使用非聚集索引讓我們知道對于檢索所有列結(jié)果集使用主鍵的聚集索引是最佳選擇。有興趣的朋友可以看下2016-12-12SQL 查詢和刪除重復(fù)字段數(shù)據(jù)的方法
這篇文章介紹了SQL 查詢和刪除重復(fù)字段數(shù)據(jù)的方法,有需要的朋友可以參考一下2013-08-08通過navicat連接SQL?Server數(shù)據(jù)庫的詳細(xì)步驟
本文介紹如何通過navicat連接SQL?Server數(shù)據(jù)庫,以往總是使SQL?Server客戶端來連接SQL?Server數(shù)據(jù)庫,但是SQL?Server客戶端一般有幾百M(fèi)的大小,而且安裝繁瑣配置麻煩,如果可以通過Navicat直接連接SQL?Server則會(huì)非常輕松方便,需要的朋友可以參考下2023-12-12