SQL - 17.存储过程
1.存储过程---就像数据库中运行方法(函数)
和C#里的方法一样，由存储过程名/存储过程参数组成/可以有返回结果。
前面学的if else/while/变量 等，都可以在存储过程中使用
优点：
-
执行速度更快
-
允许模块化程序设计
-
提高系统安全性
-
减少网络流通量
系统存储过程
由系统定义，存放在master数据库中
名称以“sp_”开头或”xp_”开头
自定义存储过程
由用户在自己的数据库中创建的存储过程
| 系统存储过程 | 说明 |
| sp_databases | 列出服务器上的所有数据库。 |
| sp_helpdb | 报告有关指定数据库或所有数据库的信息 |
| sp_renamedb | 更改数据库的名称 |
| sp_tables | 返回当前环境下可查询的对象的列表 |
| sp_columns | 回某个表列的信息 |
| sp_help | 查看某个表的所有信息 |
| sp_helpconstraint | 查看某个表的约束 |
| sp_helpindex | 查看某个表的索引 |
| sp_stored_procedures | 列出当前环境中的所有存储过程。 |
| sp_password | 添加或修改登录帐户的密码。 |
| sp_helptext | 显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本。 |
创建存储过程
定义存储过程的语法
CREATE PROC[EDURE] 存储过程名
@参数1 数据类型 = 默认值 OUTPUT,
@参数n 数据类型 = 默认值 OUTPUT
AS
SQL语句
参数说明：
参数可选
参数分为输入参数、输出参数
输入参数允许有默认值
EXEC 过程名 [参数]
（1）创建
（2）执行
exec usp_GetBookByCateId 2
（3）更改
alter procedure usp_GetBookByCateId
@cateId int
as
begin
select * from Book where b_Cid=@cateId
select * from Category
end
（4）再执行：exec usp_GetBookByCateId 2
例题：
1.分页功能
|
（1）用ROW_NUMBER() over(order by XX)
select ROW_NUMBER() over(order by c_id) as 'nid',* from Category （2）取1-5列
select * from (select ROW_NUMBER() over(order by c_id) as nid,* from Category) as a where a.nid<=5
（3）创建存储过程 --分页ROW_NUMBER()的存储过程
create procedure usp_GetPagesBooks @PageIndex int = 1 as
begin
select * from (
select ROW_NUMBER() over(order by c_id) as nid,* from Category )
as a where a.nid > (@PageIndex - 1)*5 and a.nid<= @PageIndex *5 end
执行 exec usp_GetPagesBooks 1
exec usp_GetPagesBooks 2
|
2.调用带参数的存储过程
无参数的存储过程调用：
- Exec pro_GetAge
有参数的存储过程两种调用法：
- EXEC proGetPageData 60,55 ---按次序
- EXEC proGetPageData @labPass=55,@writtenPass=60 --参数名
参数有默认值时：
- EXEC proGetPageData --都用默认值
- EXEC proGetPageData 1 --页容量(@pageSize)默认值
- EXEC proGetPageData 1,5 --不用默认值
问题：如果我只想设置页容量(第二个参数)，页码使用默认值呢？怎么办？
alter procedure usp_GetPagesBooks
@PageIndex int = 1,@PageSize int = 5
as
begin
select * from
(
select ROW_NUMBER() over(order by c_id) as nid,* from Category
)
as a
where a.nid > (@PageIndex - 1)* @PageSize and a.nid<= @PageIndex * @PageSize
end
3.存储过程中使用输出参数
alter procedure usp_GetPagesBooks
@PageIndex int = 1,
@PageSize int = 5,@RowCount int output
as
begin
select * from
(
select ROW_NUMBER() over(order by c_id) as nid,* from Category
)
as a
where a.nid > (@PageIndex - 1)* @PageSize and a.nid<= @PageIndex * @PageSizeselect @RowCount = COUNT(*) from Category
set @PageIndex =10000
set @PageSize =100
end
如果希望在存储过程中查询当前页对应的结果集，而且还想产生总页数呢？
alter procedure usp_GetPagesBooks @PageIndex int = 1,@PageSize int = 5, @RowCount int output, --变量：总行数 @PageCount int output --变量：总页数 as
begin
select * from (
select ROW_NUMBER() over(order by c_id) as nid,* from Category )
as a where a.nid > (@PageIndex - 1)* @PageSize and a.nid<= @PageIndex * @PageSize select @RowCount = COUNT(*) from Category --变量：总行数select @PageCount = ceiling(CONVERT(float,@RowCount)/CONVERT(float,@PageSize )) --变量：总页数 set @PageIndex =10000 set @PageSize =100 end
-----------------------执行-----------------------------
-----执行-----declare @a int,@p int,@rc int,@pa int set @a=2 --变量：保存页码 set @p=4 --变量：保存页容量（每页显示多少行） set @rc=0 --变量：总行数set @pa=0 --变量：总页数 exec usp_GetPagesBooks @a,@p,@rc output,@pa output select @a,@p,@rc,@pa 如果直接ceiling(@RowCount/@PageSize)@pa的值为2，
|
未完….
