一、简介

简单记录一下存储过程的使用。存储过程是预编译SQL语句集合，也可以包含一些逻辑语句，而且当第一次调用存储过程时，被调用的存储过程会放在缓存中，当再次执行时，则不需要编译可以立马执行，使得其执行速度会非常快。

二、使用

创建格式    create procedure 过程名( 变量名     变量类型 )　as    begin   ........    end　

create procedure getGroup(@salary int)
as
begin
   SELECT d_id AS '部门编号', AVG(e_salary) AS '部门平均工资' FROM employee
　　GROUP BY d_id 
　　HAVING AVG(e_salary) > @salary
end     

调用时格式，exec 过程名  参数

exec getGroup 7000

当我们需要输出参数时，可以在参数类型后使用output进行声明

 

create procedure getAvg(@avg_salary int output,)
as
begin
   SELECT @avg_salary =  AVG(e_salary)  FROM employee
end     
--调用带输出参数的过程
declare @avg_salary  int
exec getAvg avg_salary  

三、在存储过程中实现分页

3.1 要实现分页，首先要知道实现的原理，其实就是查询一个表中的前几条数据

select top 10 * from table  --查询表前10条数据 
select top 10 * from table where id not in (select top (10) id  from tb) --查询前10条数据  （条件是id 不属于table 前10的数据中）

3.2 当查询第三页时，肯定不需要前20 条数据，则可以

select top 10 * from table where id not in (select top ((3-1) * 10) id  from tb) --查询前10条数据  （条件是id 不属于table 前10的数据中）

3.3 将可变数字参数化，写成存储过程如下

create proc sp_pager
(
    @size int , --每页大小
    @index int --当前页码
)
as
begin
    declare @sql nvarchar(1000)
    if(@index = 1) 
        set @sql = 'select top ' + cast(@size as nvarchar(20)) + ' * from tb'
    else 
        set @sql = 'select top ' + cast(@size as nvarchar(20)) + ' * from tb where id not in( select top '+cast((@index-1)*@size as nvarchar(50))+' id  from tb )'
    execute(@sql)
end

 3.4 当前的这种写法，要求id必须连续递增，所以有一定的弊端

所以可以使用 row_number()，使用select语句进行查询时，会为每一行进行编号，编号从1开始，使用时必须要使用order by 根据某个字段预排序，还可以使用partition by 将 from 子句生成的结果集划入应用了 row_number 函数的分区,类似于分组排序，写成存储过程如下

create proc sp_pager
(
    @size int,
    @index int
)
as
begin
    select * from ( select row_number() over(order by id ) as [rowId], * from table) as b
    where [rowId] between @size*(@index-1)+1  and @size*@index
end

 四、触发器

触发器是一种特殊的存储过程，可在数据进行增删改之后自动执行一些其他的操作

4.1 创建触发器

DML触发器有两个特殊的表：inserted 和 deleted，inserted ，这两个表仅仅触发器运行时存在，可通过这两个表来精确地确定触发触发器的动作对数据表所做的修改

当执行insert时，inserted 表中会存入插入数据

当执行deleted时，deleted表中会存入刚删除的数据

当执行Update时，Inserted表中为新更改的数据，deleted表中为被修改之前的数据

insert触发器 在执行完insert后被触发，实现功能：新插入一个子订单则在订单表中将子订单个数加1

create trigger trig_insert
on SubOrder after insert
as  
begin
    declare @order_id --新增订单编号
    declare @order_count int --子订单个数
    select @order_id=  order_id from inserted --获取新增子订单的订单编号
    select @order_count = order_count from Order where order_id =@order_id --获取当前订单中子订单个数
    set @order_count =@order_count +1 --将该班级的总人数+1
    update t_class set order_count =@order_count where order_id=@order_id -- 修改主订单中订单子订单个数
end

delete触发器 在执行完delete后被触发，实现功能：删除一个子订单则在订单表中将子订单个数减1

create trigger trig_delete
on SubOrder after delete
as  
begin
    declare @order_id --删除订单编号
    declare @order_count int --子订单个数
    select @order_id=  order_idfrom deleted--获取新增子订单的订单编号
    select @order_count = order_count from Order where order_id =@order_id --获取当前订单中子订单个数
    set @order_count =@order_count -1 --将该班级的总人数+1
    update t_class set order_count =@order_count where order_id=@order_id -- 修改主订单中订单子订单个数
end

update触发器 在执行完delete后被触发 查询修改后子订单的订单编号

create trigger trig_updata
on SubOrder after updata
as  
begin
    select order_id from inserted
end

4.2 查询系统中的触发器

select * from sysobjects where xtype='TR'

4.3 删除触发器

drop trigger trig_name