Sqlserver 自定義函數(shù) Function使用介紹

USE SQL_SYSTEM 
GO
CREATE FUNCTION F_GONGHAO(@XINGMING NVARCHAR(5))
RETURNS INT 
AS
BEGIN
DECLARE @GONGHAO INT
SET @GONGHAO =(SELECT Y.工號(hào) FROM[T_員工信息] AS Y WHERE Y.姓名 =@XINGMING )
RETURN @GONGHAO
END
GO
/*上面是標(biāo)量函數(shù)的一個(gè)簡(jiǎn)單的例舉，下面就開(kāi)始調(diào)用，注意是在dbo.下*/
SELECT [姓名] FROM[T_員工信息] WHERE [姓名]='李異峰' /**對(duì)比的查詢*/
/*請(qǐng)注意觀察，和對(duì)比*/
SELECT [姓名],DBO.F_GONGHAO ('李異峰')AS 工號(hào) FROM[T_員工信息] WHERE [姓名]='李異峰'
GO

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

USE SQL_SYSTEM 
GO
CREATE FUNCTION F_D_DAKAJILU(@RIQI DATE)
RETURNS @TEMP_TABLE TABLE( /*這里要注意的就是既然是多語(yǔ)句的話，那你就要告訴計(jì)算機(jī)你要查詢的列是哪些*/
[姓名]NVARCHAR(5) NOT NULL,
[工號(hào)]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.工號(hào) ,Y.職位 ,Y.部門 ,K.夜班 ,K.日期 ,K.當(dāng)天上班時(shí)間 
FROM[T_員工信息] AS Y CROSS JOIN [T_考勤] AS K  /*這里我用的是交叉連接*/
WHERE Y.工號(hào) =K.工號(hào) AND K.日期=@RIQI 
RETURN 
END
GO
SELECT*
FROM[F_D_DAKAJILU]('2014/02/28')
GO

USE SQL_SYSTEM 
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION CURSOR_READ_T_員工信息
DECLARE CUR_T_員工信息 SCROLL CURSOR FOR
SELECT Y.姓名 ,Y.工號(hào) ,Y.職位 ,Y.部門 FROM[T_員工信息]AS Y ORDER BY Y.工號(hào) ASC 
COMMIT TRANSACTION CURSOR_READ_T_員工信息
GO

OPEN GLOBAL CUR_T_員工信息
GO

/*聲明4個(gè)變量用來(lái)接收暫存游標(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ù)值來(lái)確定cursor在數(shù)據(jù)表中讀取的情況=0為fetch執(zhí)行成功*/
WHILE(@@FETCH_STATUS =0)
BEGIN
PRINT'姓名：'+@XINGMING+' 工號(hào)：'+CONVERT(VARCHAR(3),@GONGHAO)+' 職位：'+@ZHIWEI+' 部門：'+@BUMEN
FETCH NEXT FROM[CUR_T_員工信息] INTO @XINGMING,@GONGHAO,@ZHIWEI,@BUMEN
END
GO

/*關(guān)閉*/
CLOSE GLOBAL CUR_T_員工信息
/*釋放內(nèi)存*/
DEALLOCATE GLOBAL CUR_T_員工信息
GO

USE SQL_SYSTEM 
/*下面聲明和打開(kāi)update——cursor*/
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRANSACTION TR_UPDATE_YG 
DECLARE CUR_T_YG CURSOR FOR
SELECT YG.姓名 ,YG.工號(hào) ,YG.職位 ,YG.部門 FROM[T_yuangongxinxi]AS YG FOR 
UPDATE 
OPEN GLOBAL CUR_T_YG
COMMIT TRANSACTION TR_UPDATE_YG

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[工號(hào)] =[工號(hào)]+100
WHERE CURRENT OF CUR_T_YG
FETCH NEXT FROM[CUR_T_YG] 
END

CLOSE GLOBAL CUR_T_YG
DEALLOCATE GLOBAL CUR_T_YG

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

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

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.工號(hào) =@GONGHAO 
/*更新*/
UPDATE[AA] 
SET[職位] =@ZHIWEI WHERE AA.工號(hào) =@GONGHAO 
SET @RETURNS = '已經(jīng)成功將工號(hào)為：【'+CONVERT(VARCHAR(3),@GONGHAO)+'】,姓名為：【'+@XINGMING+'】,職位【'+
@QIAN_RETURN_ZHIWEI+'】更新為：【'+@ZHIWEI
END

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

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 TABLE AA 

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

USE SQL_SYSTEM 
GO
UPDATE[AA] 
SET AA.職位 ='SQL高級(jí)工程師'
WHERE AA.工號(hào)=101
/*以上是個(gè)簡(jiǎn)單的更新操作，用于update觸發(fā)*/
SELECT* FROM[T_UPDATE_HOU] --修改成的數(shù)據(jù)
SELECT* FROM[T_UPDATE_QIAN] --待修改的數(shù)據(jù)
GO