sqlserver2008—存储过程

--存储过程


1.什么叫存储过程
2.存储过程怎么用
3.存储过程什么时候用
4.存储过程的优缺点


--存储过程：一组为了完成特定功能的SQL语句集，经过编译后存储在数据库中（一种sql语句和流程控制语句的集合）
--*注意：触发器是特殊的存储过程


/*
2、存储过程的优点：
存储过程允许模块化程序设计：存储过程一旦创建，以后即可在程序中调用任意多次。
并允许应用程序统一访问数据库。
加快运行速度：第一次执行时进行编译并驻留在高速缓存中，以后再执行时，只需从高速缓存中调用已编译
好的二进制代码，
减少网络通信流量：存储过程存储在服务器上并在服务器上执行，网络上只传送存储过程执行的最终数据，
大大减少了网络流量。
作为安全性机制：通过设置用户只能使用存储过程访问数据，限制用户不能直接操作数据库中的敏感数据，
以保障数据的安全性。
屏蔽T-SQL命令，提供交互查询的客户接口，增加数据库应用的方便性


(1) 系统存储过程 : 由系统提供的存储过程，作为命令执行各种操作，前缀为sp_。可以在任何数据库中使用。
 sp_addtype：创建用户定义的数据类型 
 Sp_addlogin：创建登陆
 Sp_help：查看数据库对象的信息
 Sp_datebases：列出当前系统中的数据库 
 Sp_tables：返回当前环境下可查询的对象的列表 
 
 (2) 本地存储过程: 在用户数据库中创建的存储过程，这种存储过程完成特定数据库操作任务。
 (3) 临时存储过程：“#”表示局部临时存储过程，“##”表示全局临时存储过程。
 (4)远程存储过程：从远程服务器上调用存储过程。
 (5) 扩展存储过程: 在SQL Server环境之外执行的动态链接库称为扩展存储过程。用户使用编程语言（如C语言）创建，使用时需要先加载到SQL Server系统中。
*/


--创建存储过程:
/*
（1）创建语法格式：
CREATE PROCEDURE <proc_name> [(@para1 datatype [output]…)]
   AS <sql_satements>
   proc_name ： 存储过程名
   @para1：过程中的参数
   datatype ：参数的数据类型
   Output:表明参数是返回参数
   sql_satements:T_SQL语句
*/


--创建一个存储过程测试C001课程是否有学生选修
use Educ;
select * from Course;


create procedure TestNumber
as 
if((select SelectNum from Course where Cno = 'C001')>0)
begin
print 'C001号课程有学生选修了！'
end
else
begin
print 'C001号课程没有学生选修'
end
go


/*
存储过程的执行:
EXEC[UTE]  <proc_name > 
[(@para1 =] {value |@variable [output]…)] 
*/


--执行以上定义的存储过程
exec TestNumber;




--编写一个存储过程，查看'C002'号课被选修的情况，包括选修课程学生的学号、姓名、课程名和成绩。


create procedure C002_Detail_Info
as
select SC.Sno , Sname , Cname , Grade
from SC , Student , Course
where SC.Sno = Student.Sno and SC.Cno = Course.Cno and Course.Cno = 'C002'
go


--执行以上存储过程
exec C002_Detail_Info;


/*带参数的存储过程:
1、 带输入参数的存储过程：
Create procedure 存储过程名(参数列表)
As sql_statement
调用带参数的过程格式
1:EXECUTE 存储过程名  实参列表
2:EXECUTE 存储过程名  参数1=值1,参数2=值2…
*/


--编写存储过程，给定学生学号，查询该学生的课程平均成绩；如果未指定学生学号，
--则查询所有学生的课程平均成绩。


create procedure example3
@StuNo char(11) =null --存储过程定义形参时的初始化参数方法
as
if(@StuNo is null) --数据库中判断某字段是否为空的判断语句!
begin
select AVG(Grade) 所有学生的平均成绩 from SC group by Sno
end
else
begin
select Sno , AVG(Grade) 平均成绩 from SC
Group by Sno having Sno = @StuNo
end

go


drop procedure example3;


--执行带参数的存储过程
select * from SC;
exec example3 '20121323001';


/*
3.带返回参数的存储过程:
定义SP时，用 OUTPUT 声明的变量为输出参数
@para datetype=[default] output
*/


--例：建立SP，计算两个数的乘积。SP的入口参数是两个整数，SP的返回值为乘积


create procedure math @mult1 int ,@mult2 int ,@result int output
as
select @result = @mult1 * @mult2;
go


/*
执行时，指明返回参数的名称EXECUTE  <proc_name> @<variable> OUTPUT[…]
例：DECLARE @result int
EXEC math 5,6,@result output
*/


declare @result int
exec math 6 ,5, @result output;
print @result;


--编写一个存储过程，统计选修指定课程号的学生人数。要求存储过程通过返回统计的学生人数。
create procedure Count_Stu_Num @Cno char(4) , @countNum int output
as
select @countNum = Count(distinct Sno) from SC where Cno = @Cno
go


declare @countNum int
exec Count_Stu_Num 'C001' , @countNum output
print @countNum;


--返回状态字
create procedure isStudent @Sname char(10)
as
if((select COUNT (*) from Student where Sname = @Sname) > 0)
begin
return 1;
end
else
return 2;
go


select * from Student;


--执行
declare @ret_isIn int 
declare @Sname char(10)
set @Sname = '张露' --变量在数据库中的赋值方式
exec @ret_isIn = isStudent @Sname
print '返回值:'
print @ret_isIn;




--删除存储过程
drop procedure isStudent;


/*


----------------------以下内容来源http://www.cnblogs.com/yangzhong/archive/2010/12/01/1893137.html-------------------
什么时候用到存储过程:
存储过程一般用于处理比较复杂的任务，基础ms这个平台，可以大大降低耗时，其编译机制也提高了数据库执行速度。
当然在系统控制方便方面，例如当系统进行调整时，这是只需要将后台存储过程进行更改，而不需要更改客户端程序。也无需重新安装客户端应用程序。
存储过程不仅仅适用于大型项目，对于中小型项目，使用存储过程也是非常有必要的。其威力和优势主要体现在：
  1.存储过程只在创造时进行编译，以后每次执行存储过程都不需再重新编译，而一般 SQL 语句每执行一次就编译一次,所以使用存储过程可提高数据库执行速度。
（这涉及到原理性的问题，你记住就好！）
  2.当对数据库进行复杂操作时(如对多个表进行 Update,Insert,Query,Delete 时），可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用。这些操作，如果用程序来完成，就变成了一条条的 SQL 语句，可能要多次连接数据库。而换成存储，只需要连接一次数据库就可以了。
（尽可能少的连接数据库，可以减少时间损耗；事务方面在批量操作中非常重要，因为事务可以回溯，当出错时，可以进行回溯，保证数据的完整性！）
  3.存储过程可以重复使用,可减少数据库开发人员的工作量。
（体现在分页存储过程，以及下面这个例子：）
例子：create PROC [dbo].[jobs_public_select]


@TableName VARCHAR(2000),/*表名*/


@ParamName VARCHAR(2000),/*查询字段字符串*/


@ParamWhere NVARCHAR(2000)/*条件字符串*/


AS


BEGIN


   Declare @SQL varchar(500)


   set @SQL='SELECT '+@ParamName+' from '+@TableName+'  WHERE  1=1'


   IF @ParamWhere<>''


   BEGIN


      SET @SQL=@SQL+@ParamWhere


   END


   exec(@SQL)


END


（这个例子主要作用就是公共查询功能，你只需要传递表名，查询的字段，条件即可。你可以以此类推，写个公共删除，公共更新的；操作无非也这几种哈。）


  4.安全性高,可设定只有某此用户才具有对指定存储过程的使用权。
（这方面在赋权限，主要体现在，连接时采用哪个用户连接数据库，而对应的这个用户也有对应的数据库操作权限。）
优点：
1.速度快。尤其对于较为复杂的逻辑，减少了网络流量之间的消耗
我有的过程和函数达到了几百行，一个微型编译器，相信用程序就更麻烦了。
（在获取权限那个存储过程深有体会，你也可以写个C#的算法，然后与存储过程进行速度比较。）
2.写程序简单，采用存储过程调用类，调用任何存储过程都只要1-2行代码。
(我不知道别人怎么调用，我是深受其益）
3.升级、维护方便（你只需要更改存储过程就好，比如添加一个字段等）
4.调试其实也并不麻烦，可以用查询分析器（基础好，一般没有遇到很大的错误！）
5.如果把所有的数据逻辑都放在存储过程中，那么asp.net只需要负责界面的显示阿什么的，出错的可能性最大就是在存储过程。我碰到的就一般是这种情况。
（减少了排错的时间）
缺点：
1.可移植性差，我一直采用sql server开发，可是如果想卖自己的东西，发现自己简直就是在帮ms卖东西，呵呵。想换成mysql，确实移植麻烦。
2.采用存储过程调用类，需要进行两次调用操作，一次是从sql server中取到过程的参数信息，并且建立参数；第二次才是调用这个过程。多了一次消耗。
不过这个缺点可以在项目开发完成，过程参数完全确定之后，把所有过程参数信息倒入到一个xml文件中来提高性能。
当一个业务同时对多个表进行处理的时候采用存储过程比较合适。
1.                             使用存储过程在一般情况下会提高性能，因为数据库优化了存储过程的数据访问计划并应用缓存方便以后的查询；


2.                             存储过程单独保护存在于数据库中。客户端可以获取权限执行存储过程,而不需要对底层的具体表设置其他的访问权限；


3.                             存储过程会使得维护起来更加方便，因为通常修改一个存储过程要比在一个已经发布的组件中修改SQL语句更加方便；


4.                             存储过程给底层数据格式增添了额外的抽象层。使得使用存储过程的客户端对存储过程的实现细节以及对底层数据格式是隔离独立的；


5.                             存储过程能够缓解网络带宽，因为可以批量执行SQL语句而不是从客户端发送超负载的请求。


复杂的数据处理用存储过程，如有些报表处理
多条件多表联合查询,并做分页处理
总结：


1．             当一个事务涉及到多个SQL语句时或者涉及到对多个表的操作时就要考虑用存储过程；


2．             当在一个事务的完成需要很复杂的商业逻辑时（比如，对多个数据的操作，对多个状态的判断更改等）要考虑；


3．             还有就是比较复杂的统计和汇总也要考虑，但是过多的使用存储过程会降低系统的移植性。


-----------------------------------------------------------------------------------------------------------------
*/