SQL中的视图、存储过程与函数

• SQL中的视图、存储过程与函数

1. 视图可以被看成是虚拟表或存储查询
2. 存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集（SQL语句和可选控制流语句） 经编译后存储在数据库中 用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它
3. 函数 存储过程执行的本质是一样的（但函数可以嵌入在sql中使用 可以在select中调用 存储过程则不行）
4. 表值函数与标量函数：前者只能返回一个表、后者则可以返回基类型

• 存储过程

create procedure AddStudent   --定义存储过程

@Name nvarchar(10),

@Age int,

@Bonus decimal(18,2),

@Gender int

as

begin

insert into Student(Name,Age,Bonus,Gender)values(@Name,@Age,@Bonus,@Gender)

end

exec AddStudent '张菲',21,200,2   --执行存储过程

select * from Student   --再次查询表内容

• 函数

1. 表值函数

<!--功能说明：分割字符串转成数据表 / 输入参数：@inputstr（原始字符串）, @seprator（分割符） / 输出参数：无-->

create function ufn_StrSplit   --创建函数

(

@inputstr varchar(max),   --创建函数参数

@seprator varchar(100)

)

returns @temp table(s varchar(200))

as

begin

declare @i int

set @inputstr=ltrim(rtrim(@inputstr))

set @i=charindex(@seprator,@inputstr)

while @i>0

begin

insert @temp values(left(@inputstr, @i-1))

set @inputstr=substring(@inputstr, @i+1, len(@inputstr)-@i)

set @i=charindex(@seprator, @inputstr)

end

return

end

1. 标量函数

<!--功能说明：返回两日期之间的时间 / 输入参数：@fromdate（日期）, @todate（日期）, @rand（浮点数） / 输出参数：nvarchar(100)-->

create function RandTime

(

@fromdate datetime,

@todate datetime,

@rand float

) 

returns nvarchar(100)

as 

begin

declare @return nvarchar(100)

declare @millisecond bigint   --直接精确到毫秒

declare @randdate datetime

declare @mindate datetime

--读取工作日时间约束

if @rand<0.4

set @rand=@rand+0.4

else if @rand>0.8

set @rand=@rand-0.1

select @mindate=(case when @fromdate>@todate then @todate else @fromdate end);  --取两个时间中的较小时间值

set @millisecond=datediff(ms,@fromdate,@todate);

set @randDate=dateadd(ms,@rand*@millisecond,@mindate);

        set @return=convert(varchar(19),@randdate,120);

    　 return @return

end

1. 数字函数

abs(): 绝对值

ceiling(): 舍入到最大整数/天花板

floor(): 舍入到最小整数/地板

round(): 四舍五入/”舍入到离我半径最近的数” round(-3.146,2)=-3.15

max(): 最大值

min(): 最小值

avg(): 平均值

sum(): 和

count(): 数量

1. 字符串函数

len(): 字符串长度

lower()/upper(): 转小写/大写

lrtim(): 去掉左侧空格

rtrim(): 去掉右侧空格

substring(string,start_position,length): 取子字符串/与C#不同 start_position参数从1开始

1. 日期函数

getdate(): 取得当前日期时间

dateadd(datepart,number,date): 计算增加后日期/date为待计算日期/number为增量/datepart为计量单位(年year/yy 季度quarter/qq 月month/mm 当年度第几天dayofyear/dy 天day/dd 小时hour/hh …)

datediff(datepart,startdate,enddate): 计算两日期差额

datepart(datepart,date): 返回一个日期的特定部分

1. 类型转换函数

<!--cast(expression as data_type)/convert(data_type as expression)-->

select cast(‘12’,int),cast(‘2008-10-10’,datetime),convert(vchar(50),’12’)

select convert(int,’123’)+1  //转换为计算类型

1. 空值处理函数

<!--Isnull(expression,value): expression不为空则返回expression 否则返回value-->

select isnull(name,’佚名’) as 姓名 from t_person1

1. case函数(单值判断/相当于C#switch case)

select

(

　　case level

　　when 1 then ‘good’

　　when 2 then ‘bad’

　　else ‘hello’

)

from t_person

1. case函数(范围判断)

select name

(

　　case

　　when salary<2000 then ‘低收入’

　　when salary>5000 then ‘高收入’

　　else ‘不明’

) as ‘收入水平’

from t_person