一.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.姓名
=
@XINGMING
)
9
RETURN
@GONGHAO
10
END
11
GO
12
/*
上面是标量函数的一个简单的例举，下面就开始调用，注意是在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
GO
10
/*
需要注意的就是他的写法上没有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
NULL
12
)
13
/*
以上就是要告诉计算机你要的一个基本信息列
*/
14
AS
15
BEGIN
16
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
END
22
GO
23
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
READ
3
BEGIN
TRANSACTION
CURSOR_READ_T_员工信息
4
DECLARE
CUR_T_员工信息 SCROLL
CURSOR
FOR
5
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
BEGIN
10
PRINT
'
姓名：
'
+
@XINGMING
+
'
工号：
'
+
CONVERT
(
VARCHAR
(
3
),
@GONGHAO
)
+
'
职位：
'
+
@ZHIWEI
+
'
部门：
'
+
@BUMEN
11
FETCH
NEXT
FROM
[
CUR_T_员工信息
]
INTO
@XINGMING
,
@GONGHAO
,
@ZHIWEI
,
@BUMEN
12
END
13
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
READ
4
BEGIN
TRANSACTION
TR_UPDATE_YG
5
DECLARE
CUR_T_YG
CURSOR
FOR
6
SELECT
YG.姓名 ,YG.工号 ,YG.职位 ,YG.部门
FROM
[
T_yuangongxinxi
]
AS
YG
FOR
7
UPDATE
8
OPEN
GLOBAL CUR_T_YG
9
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
BEGIN
5
UPDATE
[
T_yuangongxinxi
]
6
SET
[
工号
]
=
[
工号
]
+
100
7
WHERE
CURRENT
OF
CUR_T_YG
8
FETCH
NEXT
FROM
[
CUR_T_YG
]
9
END
SELECT:
关闭释放：
1
CLOSE
GLOBAL CUR_T_YG
2
DEALLOCATE
GLOBAL CUR_T_YG
三：PROCEDURE,
存储过程是利用SQL Server所提供的Transact-
SQL语言
所编写的程序,同时也能在高级语言上调用其存储过程。
3.1.无参数：说一个调用查询数据表的pro：
1
USE
SQL_SYSTEM
2
GO
3
CREATE
PROCEDURE
PRO_SELECT_T
4
WITH
RECOMPILE
5
AS
6
SELECT
*
FROM
[
T_yuangongxinxi
]
7
GO
8
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_T
10
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
+
'
】更新为：【
'
+
@ZHIWEI
17
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
GO
3
CREATE
TRIGGER
DROP_T
4
ON
DATABASE
FOR
DROP_TABLE
5
AS
6
BEGIN
7
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
UPDATE
10
AS
11
BEGIN
12
INSERT
INTO
T_UPDATE_HOU
13
SELECT
*
14
FROM
[
inserted
]
15
/*
将更新成的数据插入到【T_UPDATE_HOU】中
*/
16
INSERT
INTO
T_UPDATE_QIAN
17
SELECT
*
18
FROM
[
deleted
]
19
/*
将更新前的数据插入到【T_UPDATE_QIAN】中
*/
20
PRINT
'
更新完毕，更新前的数据已经写入到【T_UPDATE_QIAN】，更新后的数据插入到【T_UPDATE_HOU】。
'
21
END
update：
1
USE
SQL_SYSTEM
2
GO
3
UPDATE
[
AA
]
4
SET
AA.职位
=
'
SQL高级工程师
'
5
WHERE
AA.工号
=
101
6
/*
以上是个简单的更新操作，用于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
