视图
视图是虚表，是一组sql语句查询的结果集，没有物理表，所以不能对视图做insert,
Update delete 操作，在引用视图的时候动态产生数据集。
优点：
1， 就是根据特定的业务需求，定制业务数据
2， 简化操作 特别是多表关联的时候，好的视图可以带来快的开发速度
3， 安全性，给视图设置权限Grant
缺点：
1， 由于不物理表，当用户需要修改视图关联的各表数据的时候就会很麻烦，特别是数据由有很多表关联出来的情况下。对于这点我也没什么好办法。
视图的语法
|
1
2
3
4
5
|
Create View [ViewName]
As
Select
* from
db.TableName //这里可以写复杂的select
语句
|
这样就定义了一个名字叫ViewName 的视图，需要使用的话可以用
Select * from [ViewName]
也可以加上限制条件
Select * from [ViewName] as A where A.字段名=条件
还有的时候我们是需要视图修改一些字段的含义的，例如数据库里的sex 字段你可能
存储的是0,1这种bit 数据，但是你不希望展示给用户的是0,1，这种情况下就有两种解决方法，一种是在展现的时候在代码里转换，一种是在数据库里头转换
Select A.name,A.sex,(
Case A.sex
When 0 then ‘男’
When 1 then ‘女’
end) as sexname
from [tablename] A
存储过程
存储过程， 是一组为了完成特定功能的SQL 语句，集经编译后存储在数据库中。存储过程的写法类似高级编程语言。
优点：
- 存储过程预习在服务器上编译，代码运行回比T-sql快，下次运行会在内存中找
- 存储过程不需要客户端传好多数据过来，减少带宽的消耗
- 更好维护，减少代码的修改量
- 安全性高，可对存储过程的使用做权限控制，同时传过来的参数会做参数化，一定程度上可以防止注入（没试过，一般要自己过滤掉）
缺点：
1． 可移植性很差
视图的语法
Create proc test
(
//定义输入输出参数
)
As
代码段
Go
使用存储过程
Exec test 参数
下面写一个分页的代码段
方式一：（错误的方式，但是很经典）
|
1
2
3
4
5
6
7
8
9
10
11
|
Create
proc getpagedata(
@pageIndex int,
@pageSize int
)Asselect
top @pageSize * from(select
ROW_NUMBER() OVER (ORDER
BY a.TypeName ASC) AS
rowid ,a.* from
HP0003 a )t where
t.rowid>@pageSize*@pageIndex
|
方式二：正确的写法
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
create
proc getpagedata(
@pageIndex int,
@pageSize int,
@rowcount int
output
)Asbegindeclare
@str_sql varchar(700)
select
@rowcount= count(*) from
HP0003
select
@str_sql=‘select top ‘+cast(@pageSize as
varchar)+‘ t.* from (select ROW_NUMBER() OVER (ORDER BY a.TypeName ASC) AS rowid ,a.* from HP0003 a ) t
where t.rowid>‘+cast(@pageSize*@pageIndex as
varchar)
exec (@str_sql)
end |
使用 exec getpagedata 1,2
另外还有一种有输出参数的
Create proc getrowcount(
@rowcount int output
)
As
Select @rowcount=count(*) from [Tablename]
使用方式
declare @rowcount int
exec getrowcount @rowcount output
print @rowcount
或者
declare @rowcount int
exec getrowcount @rowcount output
select @rowcount as count
以上两个存储过程也可以合成一个
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
|
create
proc getpagedata(
@pageIndex int,
@pageSize int,
@rowcount int
output
)Asbegindeclare
@str_sql varchar(700)
select
@rowcount= count(*) from
HP0003
select
@str_sql=‘select top ‘+cast(@pageSize as
varchar)+‘ t.* from (select ROW_NUMBER() OVER (ORDER BY a.TypeName ASC) AS rowid ,a.* from HP0003 a ) t
where t.rowid>‘+cast(@pageSize*@pageIndex as
varchar)
exec (@str_sql)
end |
使用方式
declare @count int
exec getpagedata 2,20,@count output
select @count
存储过程中经常还有中情况就是查询是有流程控制的，下面是一种简单的流程控制的例子
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
|
CREATE PROCEDURE [dbo].[GetXS0330]
(@DJID int,
@UserID int,
@StarDate datetime,@EndDate datetime)asbegin Declare
@ls_sql varchar(5000),@ls_sql02 varchar(5000)
IF isNull(@DJID,‘0‘)=‘0‘
Select @DJID=1
Select
@ls_sql02 = ‘ where 1=1 ‘
Select
@ls_sql02 = @ls_sql02 + ‘ and datediff(D,Date60,‘‘‘+ Cast(@StarDate as
varchar) +‘‘‘)<=0 ‘
select
@ls_sql02 = @ls_sql02 + ‘ and datediff(D,Date60,‘‘‘+ Cast(@EndDate as
varchar) +‘‘‘)>=0 ‘
IF isNull(@UserID,‘‘)<>‘‘
Begin
select
@ls_sql02 = @ls_sql02 + ‘ and UserID = ‘‘‘+ Cast(@UserID as
varchar) +‘‘‘ ‘
End
Else
Begin
select
@ls_sql02 = @ls_sql02 + ‘ and UserID in ‘
select
@ls_sql02 = @ls_sql02 + ‘ (‘
select
@ls_sql02 = @ls_sql02 + ‘ Select UserID from V_XS0006B where DJ in (2,3,4) and (DJID03=‘‘‘+ Cast(@DJID as
varchar) +‘‘‘ ‘
select
@ls_sql02 = @ls_sql02 + ‘ OR DJID03 in (Select DJID from XS0014 where GXID=‘‘‘+ Cast(@DJID as
varchar) +‘‘‘)) ‘
select
@ls_sql02 = @ls_sql02 + ‘ ) ‘
End
Select
@ls_sql=‘Select * from V_XS0330 ‘+@ls_sql02
Exec(@ls_sql)
End |
函数很类似于存储过程，区别于函数可以在select 里使用，而存储过程
不能，下面这张图说明了一些区别（网上找到的）
刚实习，写的不是很好，文章也没怎么组织，晚上有时间在调整下。有错误的给个提示，我数据库的环境是MS SQL2005