注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

天涯倦客的博客

祝福你朋友永远快乐!

 
 
 

日志

 
 

SQL中存储过程和自定义函数的区别  

2011-10-13 01:31:14|  分类: MS SQL |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

存储过程:

    存储过程可以使得对数据库的管理、以及显示关于数据库及其用户信息的工作容易得多。存储过程是 SQL 语句和可选控制流语句的预编译集合,以一个名称存储并作为一个单元处理。存储过程存储在数据库内,可由应用程序通过一个调用执行,而且允许用户声明变量、有条件执行以及其它强大的编程功能。
存储过程可包含程序流、逻辑以及对数据库的查询。它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。


    可以出于任何使用 SQL 语句的目的来使用存储过程,它具有以下优点

 

    1、可以在单个存储过程中执行一系列 SQL 语句。
    2、可以从自己的存储过程内引用其它存储过程,这可以简化一系列复杂语句。

    3、存储过程在创建时即在服务器上进行编译,所以执行起来比单个 SQL 语句快。


用户定义函数:


    Microsoft SQL Server 2000 允许创建用户定义函数。与任何函数一样,用户定义函数是可返回值的例程。根据所返回值的类型,每个用户定义函数可分成以下三个类别:

 
    1、返回可更新数据表的函数:如果用户定义函数包含单个 Select 语句且该语句可更新,则该函数返回的表格格式结果也可以更新。


    2、返回不可更新数据表的函数:如果用户定义函数包含不止一个 Select 语句,或包含一个不可更新的 Select 语句,则该函数返回的表格格式结果也不可更新。


    3、返回标量值的函数:用户定义函数可以返回标量值。


存储过程
   

    1. 功能强大,限制少
    2. 不能直接引用返回值
    3. 用select语句返回记录集

自定义函数


   1. 诸多限制,有许多语句不能使用,许多功能不能实现
   2. 可以直接引用返回值
   3. 用表变量返回记录集

 

  • 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
  评论这张
 
阅读(555)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017