数据库开发及ADO.NET(30)——存储过程、系统存储过程、创建存储过程(编写分页存储过程)、编写存储过程、调用带参数的存储过程、存储过程中使用输出参数

一、存储过程

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

2、和C#里的方法一样，由存储过程名/存储过程参数组成/可以有返回结果。

3、前面学的if else/while/变量 等，都可以在存储过程中使用。

4、优点：

• 执行速度更快：在数据库中保存的存储过程语句都是编译过的，运行时无需再次编译。
• 允许模块化程序设计：类似方法的复用。
• 提高系统安全性：防止SQL注入。
• 减少网络流通量：当应用程序和数据库在不同的服务器上时，只需传输存储过程名。

5、系统存储过程：

• 由系统定义，存放在master数据库中。
• 名称以“sp_”开头或”xp_”开头。

6、自定义存储过程：由用户在自己的数据库中创建的存储过程。

二、系统存储过程

代码：

EXEC sp_databases  //列出服务器上的所有数据库，包括系统数据库。
EXEC  sp_renamedb 'Northwind','Northwind1'
EXEC sp_tables
EXEC sp_columns stuInfo  
EXEC sp_help stuInfo
EXEC sp_helpconstraint stuInfo
EXEC sp_helpindex stuMarks
EXEC sp_helptext 'view_stuInfo_stuMarks' 
EXEC sp_stored_procedures

三、创建存储过程

7、定义存储过程的语法：

    CREATE  PROC[EDURE]  存储过程名 
    @参数1  数据类型 = 默认值 OUTPUT,
    @参数n  数据类型 = 默认值 OUTPUT
    AS
      SQL语句

8、参数说明：

• 参数可选
• 参数分为输入参数、输出参数
• 输入参数允许有默认值

9、调用系统存储过程和自定义存储过程都是同样的语法：EXEC  过程名  [参数]

10、例：编写分页存储过程

--编写分页存储过程
create procedure proGetPageData
@pageIndex int,
@pageSize int
as
declare @sqlStr varchar(300)
set @sqlStr='select top '+str(@pageSize)+' * from Category where c_id not in(select top '+str((@pageIndex-1)*@pageSize)+' c_id from Category order by c_addtime)order by c_addtime'
print @sqlStr
EXEC(@sqlStr)

execute proGetPageData 3,3

四、编写存储过程

11、编写存储过程usp_GetBookByCid

12、要求传入参数:@categoryId int   ，调用存储过程，传入分类ID，查处属于该分类的书籍列表。

五、调用带参数的存储过程

13、无参数的存储过程调用：

Exec pro_GetAge

14、有参数的存储过程两种调用法：

EXEC proGetPageData 60,55 ---按次序

EXEC proGetPageData @labPass=55,@writtenPass=60 --参数名

15、参数有默认值时：

EXEC proGetPageData --都用默认值 
EXEC proGetPageData 1  --页容量(@pageSize)默认值 
EXEC proGetPageData 1,5   --不用默认值

问题：如果我只想设置页容量(第二个参数)，页码使用默认值呢？怎么办？

EXEC proGetPageData @pageSize=10

六、存储过程中使用输出参数

16、如果希望在存储过程中查询当前页对应的结果集，而且还想产生总页数呢？

create procedure [dbo].[proGetPageData2] –带输出参数的存储过程
@pageIndex int=1,
@pageSize int=3,
@pageCount int output, --总页数
@rowCount int output -- 总行数
as
declare @sqlStr nvarchar(300),@sqlCount nvarchar(300)
SET @sqlCount = 'SELECT @rowCount=COUNT(b_id),@pageCount=CEILING((COUNT(b_id)+0.0)/'+ CAST(@pageSize AS VARCHAR)+') FROM Book'
print @sqlCount
EXEC SP_EXECUTESQL @sqlCount,N'@rowCount INT OUTPUT,@pageCount INT OUTPUT',@rowCount OUTPUT,@pageCount OUTPUT
set @sqlStr='select top '+str(@pageSize)+' * from Category where c_id not in(select top '+str((@pageIndex-1)*@pageSize)+' c_id from Category order by c_addtime)order by c_addtime'
print @sqlStr
EXEC(@sqlStr)

declare @pc int
declare @rc int
exec [proGetPageData2] 1,3,@pc output,@rc output
select @pc,@rc

17、存储过程汇总：

• （1）在SQL server2008中，存储过程在数据库中的“可编程性”里面，可编程性、表、视图等是并列关系。
• （2）只要调用了存储过程的名称，就会执行begin  end中的所有代码。过程名相当于C#中的方法名，begin  end相当于大括号，begin  end中的内容相当于方法体。
• （3）系统存储过程和自定义存储过程两种。
• （4）调用系统存储过程和自定义存储过程都是同样的语法：exec 过程名 参数值
• （5）修改存储过程：alter  proc  过程名  。（修改的是存储过程里的具体代码）。
• （6）第一次创建用create，后面的修改都用alter  。

 

18、通过row_number()的分页方式存储过程：

（1）分页代码示例，这里获取到第1页：

select  *  from
(
select  row_number()  over  (order  by  c_id)  as  r1,*  from  Category
)  as  a
where  a.r1<=5

（2）简单的分页存储过程代码示例，每页5条记录：（要显示第二页时的调用代码：exec  usp_GetPageBooks  2）

alter  proc  usp_GetPageBooks
@pageIndex  int  1  //默认显示第一页。
as
select  *  from
(
select  row_number()  over  (order  by  c_id)  as  r1,*  from  Category
) as  a
where  a.r1>(@pageIndex-1)*5  and  a.r1<=@pageIndex*5

（3）传入页码和每页大小两个参数：

alter  proc  usp_GetPageBooks
@pageIndex  int  1  --默认显示第一页。
@pageSize  int  5  --默认每页有5行。
as
select  *  from
(
select  row_number()  over  (order  by  c_id)  as  r1,*  from  Category
) as  a
where  a.r1>(@pageIndex-1)* @pageSize  and  a.r1<=@pageIndex*@pageSize
调用：exec  usp_GetPageBooks  1  --调用第一页，每页5行。

--传入第一个参数，第二个参数用默认值。
usp_GetPageBooks  2  --调用第二页，每页5行。
exec  usp_GetPageBooks  1,3  --调用第一页，每页3行

--传入两个参数，按照传入参数的顺序先后赋值。
exec  usp_GetPageBooks  2,3  --调用第二页，每页3行
exec  usp_GetPageBooks  4,10  --调用第4页，每页10行
exec  usp_GetPageBooks  @pageIndex=1, @pageSize=3 --显示传参，调用第一页，每页3行
exec  usp_GetPageBooks  @pageIndex=3, @pageSize=3  --调用第三页，每页3行
exec  usp_GetPageBooks  @pageSize=6, @pageIndex=7  --调用第7页，每页6行。

--显示传参时，参数顺序可以不用考虑。
exec  usp_GetPageBooks  @pageSize=8  --调用第1页，每页8行。
--只传部分参数时，剩下的参数使用默认值。
--等等，以此类推。

19、输入参数和输出参数：输入参数传的是参数的复制品，所以对参数本身无影响。输出参数传的是参数的引用（地址），所以参数本身会随着输出参数的改变而改变。

20、几个存储过程相关的重要知识点：

convert(float,@rowCount)  --把int类型的局部变量@rowCount转换为float型。
convert(float,@pageSize)  --把int类型的局部变量@pageSize转换为float型。
ceiling(convert(float,@rowCount)/convert(float,@pageSize))  --ceiling是把紧跟其后的小数型进行进一法运算。