第七天 -- 《2014-07-14 SQL进阶》1 -- 存储过程

一、上午《02、存储过程的概念》

1、存储过程---就像数据库中运行方法(函数)

　　和C#里的方法一样，由存储过程名、存储过程参数组成 ，可以有返回结果。
前面学的if else/while/变量/insert/select 等，都可以在存储过程中使用

（1）优点：
　　执行速度更快 – 在数据库中保存的存储过程SQL语句都是编译过的
　　允许模块化程序设计 – 类似方法的复用
　　提高系统安全性 – 防止SQL注入
　　减少网络流通量 – 只要传输 存储过程的名称

（2）系统存储过程
　　由系统定义，存放在master数据库中
　　名称以“sp_”开头或”xp_”开头。
（3）自定义存储过程
　　由用户在自己的数据库中创建的存储过程。一般以usp_开头。

 

二、上午《03、第一个存储过程的创建》--《04、创建有多个形参的存储过程》

1、创建存储过程

--（1）创建存储过程：
--go
--create procedure  usp_存储过程名称
--(形参) 可以以这个里面声明参数
--as  --相当于方法体开始
---- 自定义局部变量
---- 逻辑语句sql、事务、调用其他存储过程等
--go

--（2）调用存储过程：

--execute  存储过程名  [参数列表，...]

例子如下：

--例一：查询所有学员信息
if exists(select * from sysobjects where name='usp_getAllStuInfo')
    drop proc usp_getAllStuInfo
go
--创建存储过程，上面如果已存在就删除它
create procedure usp_getAllStuInfo
as
    select * from Student
go

--调用存储过程，获取所有学员信息
exec usp_getAllStuInfo

--例二：查询指定性别的学员信息
if exists(select * from sysobjects where name='usp_getAllStuInfoBySex')
    drop proc usp_getAllStuInfoBySex
go

create procedure usp_getAllStuInfoBySex
    @sex  char(2)     --形参不能用declare
as
select * from Student where Sex=@sex
go

--调用存储过程。返回指定性别的学员信息
exec usp_getAllStuInfoBySex  'dafgfhghj'  
--注意存储过程参数为char(2)，只截取前两个字节（此例为‘da’，如果参数输入Unicode的‘女人’，就只截取‘女’）


--例三：获取指定性别和班级名称的学员信息
if exists(select * from sysobjects where name='usp_getAllStuInfoBySexAndClassName')
    drop proc usp_getAllStuInfoBySexAndClassName
go
create proc usp_getAllStuInfoBySexAndClassName
 @sex char(2),                        --注意逗号别忘
 @className nvarchar(50) 
as
 declare @classId int --局部变量：科目 ID
 set @classId=(select classid from grade where classname=@className) 
 select * from Student where Sex=@sex and ClassId=@classId
go

--调用存储过程，返回指定班级名称和性别的学员信息
exec usp_getAllStuInfoBySexAndClassName  '女',  '七期班'

 

2、存储过程注意点

　　（1）调用存储传入参数时，可以传入可转换的值。

　　　　比如形参要求字符串，你除了可以传入字符串外，其实可以传入任何值。因为它们都可以转字符串。

　　　　假如形参要求数值，你就应该传入数值，若传入数值字符串也不会错（因为可转换），但如果传入其他字符串无法转为数值时，会报错。

　　（2）若参数定义为字符串（可变或不可变），传入超长字符串，会按（参数定义）要求的长度截取前N个字符。不会报错。

       （3）注意区分Unicode和非Unicode数据类型，前者1个字符占2字节，后者1个字符占1字节

 

 

三、上午《05、创建有默认值参数的存储过程》

1、默认参数和C#中概念类似

--参数传递次序要一致：第一个实参默认就是传递给第一个形参... 以此类推
--如果形参定义了默认值，那么调用存储过程时可以写default代表使用默认值。
--也可以使用 @参数 = 值 的方式调用存储过程，这样就与顺序没有关系了。
--一旦使用了 @name = value  形式之后，所有后续的参数就必须以  @name = value  的形式传递。前面的参数没有限制

如下例：

--创建有默认值的存储过程
if exists(select * from sysobjects where name='usp_getAllStuInfoBySexAndClassName')
   drop proc usp_getAllStuInfoBySexAndClassName
go
create proc usp_getAllStuInfoBySexAndClassName
   @sex char(2)='男',     --往往会将有默认值的参数写在所有参数列表的最后，调用的时候比较方便。
   @className nvarchar(50) 
as
   declare @classId int --科目 ID
   set @classId=(select classid from grade where classname=@className) 
   select * from Student where Sex=@sex and ClassId=@classId
go

--调用存储过程
exec usp_getAllStuInfoBySexAndClassName  @className='七期班', @sex='女'   --类似具名实参，就和次序无关了

 

 

四、上午《06、创建带输出参数的存储过程》--《07、创建有返回值的存储过程》

1、带输出参数的存储过程

注意：声明形参的时候要加上output；调用存储过程的时候也要加output

--根据班级和性别查询学员，同时返回总人数和指定性别的人数
if exists(select * from sysobjects where name='usp_getSInfoAndCount')
    drop proc usp_getSInfoAndCount
go
create proc  usp_getSInfoAndCount
 @totalNum int output,          --指定班级的总人数
 @claNum int output,             --指定班级和性别的总人数
 @ClassName nvarchar(50),  --输入参数：需要用户传入值
 @sex char(2)='男' 
as
    declare @cid int = (select classid from grade where classname=@ClassName) --根据班级名称获取班级ID
    select * from Student where ClassId=@cid and Sex=@sex
    set @totalNum=(select COUNT(*) from Student )--总人数
    set @claNum =(select COUNT(*) from Student where ClassId=@cid and Sex=@sex)
go

--调用在输出参数的存储过程
declare @tnum int, @cnum int    --声明变量用来接收输出参数的值
exec usp_getSInfoAndCount @ClassName='七期班' ,@totalNum=@tnum output, @claNum=@cnum output
print @tnum
print @cnum

 

2、有返回值的存储过程

注意（1）return只能反回整数值。所以需要从存储过程中返回结果的，多使用output参数

　　（2）调用存储过程时要用 exec @变量 = 存储过程名 参数    的形式来调用。

---返回指定人数
if exists(select * from sysobjects where name='usp_getNameByNo')
   drop proc usp_getNameByNo
go
create proc usp_getNameByNo
   @cid int        --指定班级ID
as
 declare @cnt int 
 set @cnt = (select COUNT(*) from Student where ClassId=@cid) 

 return @cnt --return只能返回 整数值
go

--调用存储过程，返回指定学号的学员名字
declare @count int 
exec @count= usp_getNameByNo  6
print @count

 

 

五、下午《01、C#使用存储过程的大致方式》--《04、调用有输出参数的存储过程》

1、ADO.NET使用DataAdapter调用存储过程

注意：调用有参数的存储过程时，DataAdapter.SelectCommand.CommandType要设置为 StoredProcedure

//调用无参存储过程“usp_getAllStuInfo”
SqlDataAdapter da = new SqlDataAdapter("usp_getAllStuInfo", connStr);
DataTable dt = new DataTable();
da.Fill(dt);
this.dgvList.DataSource = dt;

//调用有参数的存储过程
SqlDataAdapter da = new SqlDataAdapter("usp_getAllStuInfoBySex", connStr);
//1.确定命令的类型是存储过程：你得告诉服务器这是存储过程.有参数的时候就必须设置命令的类型
da.SelectCommand.CommandType = CommandType.StoredProcedure;
//2.声明定义参数
SqlParameter p = new SqlParameter("@sex", "男");
//3.将参数添加到数据适配器的SelectCommand对象中
da.SelectCommand.Parameters.Add(p);
DataTable dt = new DataTable();
da.Fill(dt);
this.dgvList.DataSource = dt;

 

2、调用有输出参数和返回值的存储过程

注意，输出参数和返回值都需要设置SQLParameter

SqlDataAdapter da = new SqlDataAdapter("usp_getSInfoAndCount", connStr);
//1.指定命令类型：
//Text=An SQL text command. (Default.)；StoredProcedure=The name of a stored procedure.；TableDirect=The name of a table.
da.SelectCommand.CommandType = CommandType.StoredProcedure;

//2.创建存储过程中对应的参数
SqlParameter[] ps = { 
                    new SqlParameter("@ClassName", “十四期班”),
                    //2.1对于输出参数和返回值，当然不是给值，而是确定数据类型
                    new SqlParameter("@totalNum", SqlDbType.Int),
                    new SqlParameter("@claNum", SqlDbType.Int),
                    new SqlParameter("@sex",  "女"),
                    new SqlParameter("cnt", SqlDbType.Int)
                    };
//3.将参数添加到命令对象中
da.SelectCommand.Parameters.AddRange(ps);

//4.对于 output参数，一定需要设置参数的方向
ps[0].Direction = ParameterDirection.Input; //默认值此值--输入参数
//如果参数的方向是output,那么就说明它是向服务器请求参数中对应名称的变量值。那么服务器就会将对应的变量值返回存储到参数对象的Value属性中
ps[1].Direction = ParameterDirection.Output;
ps[2].Direction = ParameterDirection.Output;
ps[3].Direction = ParameterDirection.Input;
//返回值，要设置为ReturnValue
ps[4].Direction = ParameterDirection.ReturnValue;

DataTable dt = new DataTable();
da.Fill(dt);
this.dgvList.DataSource = dt;
//显示存储过程的输出参数和返回值
MessageBox.Show(ps[1].Value + "  " + ps[2].Value + "  " + ps[4].Value);