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）创建

create procedure usp_GetBookByCateId

@cateId int

as

select * from Book where b_Cid=@cateId

（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 * @PageSize

select @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，

 

 

未完….