SQL Server存储过程与函数

存储过程

为什么要用存储过程？存储过程有什么优势，在什么环境下使用？
       几个去 IBM 面试的兄弟回来抱怨：去了好几个不同的 IBM 项目组，几乎每个面试官问到数据库的时候都要问用没用过存储过程，烦人不？大家去面的程序员，又不是 DBA，以前的项目都没有用到存储，不照样运行的好好的？
存储过程真的那么重要吗，它到底有什么好处呢？
笔者认为，存储过程说白了就是一堆 SQL 的合并。中间加了点逻辑控制。
但是存储过程处理比较复杂的业务时比较实用。
比如说，一个复杂的数据操作。如果你在前台处理的话。可能会涉及到多次数据库连接。但如果你用存储过程的话。就只有一次。从响应时间上来说有优势。
也就是说存储过程可以给我们带来运行效率提高的好处。
另外，程序容易出现 BUG 不稳定，而存储过程，只要数据库不出现问题，基本上是不会出现什么问题的。也就是说从安全上讲，使用了存储过程的系统更加稳定。
数据量小的，或者和钱没关系的项目不用存储过程也可以正常运作。MySQL 的存储过程还有待实际测试。如果是正式项目，建议你用 sql server 或 oracle 的存储过程。数据与数据之间打交道的话，过程会比程序来的快的多。面试官问有没有用存储，实际上就是想知道前来面试的程序员到底做过数据量大的项目没。如果是培训出来的，或者小项目小公司出来的，对存储肯定接触的少了。
所以，要想进大公司，没有丰富存储过程经验，是不行的。
那么什么时候才可以用存储？对于数据量不是很大以及业务处理不是很复杂的小项目就无需要了么？
错。存储过程不仅仅适用于大型项目，对于中小型项目，使用存储过程也是非常有必要的。其威力和优势主要体现在：
  1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般 SQL 语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
  2.当对数据库进行复杂操作时(如对多个表进行 Update,Insert,Query,Delete 时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。这些操作，如果用程序来完成，就变成了一条条的 SQL 语句，可能要多次连接数据库。而换成存储，只需要连接一次数据库就可以了。
  3.存储过程可以重复使用,可减少数据库开发人员的工作量。
  4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权。

  5.减少网络传输。存储过程直接就在数据库服务器上跑（存储过程是直接定义在数据库中的），所有的数据访问都在服务器内部进行，不需要传输数据到其它终端。但我们的应付服务器通常与数据库是在同一内网，大数据的访问的瓶颈会是硬盘的速度，而不是网速。

 

什么时候使用存储过程比较合适?

create proc proc_test_insert  --如果要修改这个存储过程的话只要给create 改为alter就行了
 (
	@p_name nvarchar(50),
	@p_age int,
	@p_email varchar(100),
	@p_addres nvarchar(100),
	@p_remarks nvarchar(100)
	--@num nvarchar out -- 用来保存输出的错误信息，或者成功信息
  )
as
begin
 if @p_email is null or LEN(@p_email)<=0 
   print 'email不能为空';
 else  if @p_addres is null or LEN(@p_addres)<=0
  print '地址不能为空'
  else
  begin 
   insert into t_test values(@p_name,@p_age,@p_email,@p_addres,@p_remarks)
   print ' 插入成功'
  end
end
   
  
exec proc_test_insert  '武松',46,'wusong@163.com','',''    --插入不会成功，会报错："地址不能为空"

或者这样也可以

create proc proc_test_insert  --如果要修改这个存储过程的话只要给create 改为alter就行了 
 (
	@p_name nvarchar(50),
	@p_age int,
	@p_email varchar(100),
	@p_addres nvarchar(100),
	@p_remarks nvarchar(100),
	@num nvarchar(50) out -- 用来保存输出的错误信息，或者成功信息
  )
as
begin
set @num='';
 if @p_email is null or LEN(@p_email)<=0 
   set @num= 'email不能为空';
 else  if @p_addres is null or LEN(@p_addres)<=0
  set @num= '地址不能为空'
  else
  begin 
   insert into t_test values(@p_name,@p_age,@p_email,@p_addres,@p_remarks)
   set @num= ' 插入成功'
  end
end


   
declare @num nvarchar(50); 
exec proc_test_insert  '鲁智深',45,'','山东省济宁市梁山县','', @num out  --插入不会成功，会报错："email不能为空"
print @num

 

函数

创建一个返回值为表变量的函数

create function fun_location() --创建一个不带参数的函数
returns @loctable table --这个函数的返回值是一个名字叫@loctable的表变量
(
	id int, --@loctable表变量有个id字段
	name nvarchar(50)--@loctable表变量有个name字段
)
as
   begin	
	insert into @loctable (id,name)
	select loc.locId,loc.locName from location loc --从location表中才查出locId,locName,然后将它们插入到@loctable这个表变量中
	return 
	end
go

关于返回值是表变量的函数查询（像表一样查询）

select * FROM  dbo.fun_location()--注意函数名称前面一定要加dbo

创建一个返回值是字符串的函数

create function fun_book(@id int)--创建一个带一个参数名字的函数
returns varchar(20)
as
begin
	declare @fname varchar(20) --声明一个变量
	select @fname=b.Fname from T_Book b where b.FId=@id
	return @fname --返回这个参数值
end

关于返回值为字符串的函数的查询

select dbo.fun_book(3) --注意函数名前面加dbo