存储过程和触发器
001 介绍存储过程和如何调用系统的存储过程
存储过程---就像数据库中运行的方法(函数)
由存储过程名/存储过程参数组成/可以由返回结果
if else/while/变量/insert/select等，都可以在存储过程中使用
优点：
执行速度更快 - 在数据库中保存的存储过程语句都是编译过的
允许模块化程序设计 - 类似方法的复用
减少网络流通量 - 只要传输 存储过程的名称
系统存储过程：
由系统定义，存放在master数据库中
名称以sp_开头或xp_开头，自定义的存储过程可以以usp_开头
自定义存储过程：
由用户自己在数据库中创建的存储过程usp
系统存储过程：
sp_databases 列出服务器上的所有数据库
sp_helpdb 显示有关数据库或所有数据库的信息
sp_renamedb 重命名数据库的名称
sp_tables 返回当前环境下某个表的表的信息
sp_collumns 返回某个表的列的信息
sp_help 显示某个表的所有信息
sp_helpconstraint 显示某个表的约束
sp_stored_procedures 列出当前环境下的所有存储过程
sp_password 添加或修改登录账户的密码
sp_helptext 显示默认值、未加密的存储过程、用户定义的存储过程、触发器或视图的实际文本。
exec sp_databases //exec调用这个系统存储过程，可以没有exec
sp_helptext sp_tables //显示某个存储过程sp_tables的代码
alter替换Create 就是修改存储过程
如果C#参数类型如果是string，则SQL Server中为nvarchar(50)
----------------------------------------------
002 存储过程小案例
定义存储过程的语法
CREATE PROCEDURE 存储过程名
@参数1 数据类型 = 默认值 OUTPUT,
@参数n 数据类型 = 默认值 OUTPUT
AS
SQL语句
参数说明：参数可选，参数分为输入参数、输出参数，输入参数允许有默认值
EXEC 过程名 [参数]
1--创建存储过程：两个数相加
CREATE PROCEDURE usp_AddTwoNumber
@num1 int,
@num2 int
AS
BEGIN
SELECT @num1+@num2
END
--第一种调用：自定义存储过程的调用必须加EXEC
declare @n1 int=100,@n2 int=200
--EXEC usp_AddTwoNumber @num1=@n1,@num2=@n2
--第二章调用：
EXEC usp_AddTwoNumber @n1,@n2
--第三种调用：
EXEC usp_AddTwoNumber 100,200
2--创建存储过程：两数相加并返回
CREATE PROCEDURE usp_AddNumber
@num1 int,
@num2 int,
@sum int OUTPUT
AS
BEGIN
SET @sum=@num1+@num2
END
--调用这个存储过程：如果是存储过程中需要传出来的值，用OUTPUT进行修饰，外面调用的时候也需要加上OUTPUT
declare @n1 int=10,@n2 int=20,@sm int
EXEC usp_AddNumber @n1,@n2,@sm OUTPUT
SELECT @sm
--------------------------------------------------
003 模糊查询写成存储过程小案例
根据名字和年龄查询班级中的学生信系，并且把有多少个学生显示出来
select * from T_Name where name like ‘%m%‘ and age<30;
select COUNT(*) from T_Name where name like ‘%m%‘ and age<30;
3--创建存储过程：用户查询学生和数量
CREATE PROCEDURE usp_SelectStudent
@name nvarchar(50),
@age int,
@count int OUTPUT
AS
BEGIN
SET @count=(select COUNT(*) from T_Name where name like ‘%‘+@name+‘%‘ and age<@age);
select Id, name, age, salary from T_Name where name like ‘%‘+@name+‘%‘ and age<@age;
END
--调用这个存储过程：
DECLARE @num int
EXEC usp_SelectStudent ‘m‘,30,@num OUTPUT
SELECT @num
--------------------------------------------------------------
04 分页的存储过程
--分页
--每页5条，当前第3页，PageSize=5,CurrentPage=3,[(3-1)*5+1 3*5]
select * from (
select *,ROW_NUMBER() over(Order by Id) rownum from T_User ) t Where rownum Between 1 and 5;
Select ( Ceiling((select COUNT(*) from T_User)*1.0/5))
4--创建存储过程：分页及总页数
CREATE PROCEDURE usp_Pager
@PageSize int=10,
@CurrentPage int=1,
@SumPage int OUTPUT
AS
BEGIN
--总页数
SET @SumPage =Ceiling( (SELECT COUNT(*) FROM T_User)*1.0/@PageSize);
--当前页数据
select * from (
select *,ROW_NUMBER() over(Order by Id) rownum from T_User ) t Where rownum Between (@CurrentPage-1)*@PageSize+1 and @CurrentPage*@PageSize;
END
--调用分页存储过程
DECLARE @SumPage int
EXEC usp_Pager 5,3,@SumPage OUTPUT ;
SELECT @SumPage;
----------------------------------------------------------
05 提分案例
编写一个存储过程，为班级中没有及格的学生提分
要求：提分后，不及格的人数必须小于总人数的一半，并且获取提分的次数
提示：需要三个参数，及格分数线，每次提分的分数，循环次数
//查询没及格的学生数量，需要及格线PassLine
//查询总学生数量
//if 没及格>总数/2 就循环
{
//给不及格学生增加成绩，需要每次增加多少分数EveryScore
//再次查询没及格数量
//技术循环次数Count
}
5--创建存储过程：提分
CREATE PROCEDURE usp_SubmitScore
@PassLine int, --及格分数线
@EveryScore int, --每次提的分数
@Count int OUTPUT --提分次数
AS
BEGIN
SET @Count=0
DECLARE @TotalCount int ,@FailCount int;
SET @TotalCount =( SELECT COUNT(*) FROM T_StudentGrade );
SET @FailCount = (SELECT COUNT(*) FROM T_StudentGrade WHERE Math<@PassLine);
WHILE(@FailCount>=@TotalCount*1.0/2) --分奇偶
BEGIN
UPDATE T_StudentGrade SET Math=Math+@EveryScore ;
SET @FailCount = (SELECT COUNT(*) FROM T_StudentGrade WHERE Math<@PassLine);
SET @Count=@Count+1;
END
END
--调用提分存储过程
DECLARE @num int
EXEC usp_SubmitScore 300,5,@num OUTPUT
SELECT @num
-------------------------------------------------------
06 C#调用存储过程
根据Id更改姓名和年龄
6--创建存储过程：更改指定id的name和age
Alter PROCEDURE usp_UpdateT_Name
@id int,
@name nvarchar(50),
@age int
AS
BEGIN
UPDATE T_Name SET name=@name,age=@age WHERE Id=@id
END
EXEC usp_UpdateT_Name 2,‘我和你‘,20
#region C#调用存储过程
string constr="Data Source=.;Initial Catalog=Test;User ID=sa;Password=abcd5226584";
int id=2;
string name="庞统";
int age=18;
string sql="usp_UpdateT_Name";//调用这个存储过程
SqlParameter[] param={new SqlParameter(){ParameterName="@id",Value=id},
new SqlParameter("@name",name),
new SqlParameter(){ParameterName="@age",Value=18}};
using (SqlConnection conn = new SqlConnection(constr))
using(SqlCommand cmd=new SqlCommand(sql,conn))
{
conn.Open();
cmd.CommandType=CommandType.StoredProcedure;//指定命令文本的类型是存储过程
cmd.Parameters.AddRange(param);//调用存储过程时需要这些参数
cmd.ExecuteNonQuery();
}
#endregion
alter替换Create 就是修改存储过程
如果C#参数类型如果是string，则SQL Server中为nvarchar(50)
---------------------------------------------------------------
07 触发器案例
触发器的作用：自动化操作，减少了手动操作以及出错的几率。
触发器是一种特殊类型的存储过程，它不同于之前的我们介绍的存储过程。【在SQL内部把触发器看做事存储过程但是不能传递参数】
一般的存储过程通过存储过程名称被直接调用，而触发器主要是通过事件进行触发而被执行。
触发器是一个功能强大的工具，在表中数据发生变化时自动强制执行。触发器可以用于SQL Server约束、默认值和规则的完整性检查，还可以完成难以普遍约束实现的复杂功能。
那究竟何为触发器？在SQL Server里面也就是对某一个表的一定的操作，触发某种条件，从而执行的一段程序。触发器是一个特殊的存储工程。
触发器分为触发前执行还是触发后执行
After触发器：
在语句执行完毕之后触发
按语句触发，而不是所影响的行数，无论所影响的行数是多少都只触发一次。
只能建立在常规表上，不能建立在视图和临时表上。（*）
可以递归触发，最高可达32级。
Update(列)，在UPDATE语句触发时，判断某列是否被更新，返回BOOLEAN值。
介绍如何编写AFTER触发器。
Instead Of 触发器
用来替换原来的操作
不会递归触发
可以在约束被检查之前触发
可以建立在表和视图上（*）
介绍如何编写Insert Of触发器
触发器语法--删除
CREATE TRIGGER Tr_DeleteStudent ON Student
for delete
AS
BEGIN
INSERT INTO BackupStudent SELECT * FROM Deleted
END
触发器使用建议：
尽量不在触发器执行耗时操作，因为触发器会与SQL语句认为在同一个事务中。
触发器编写时注意多行触发时的处理。
过多触发器会造成数据库及应用程序的维护困难，同时对触发器过多的依赖，势必影响数据库的结构，同时增加了维护的复杂程序。
能不用就不用。
INSERT、DELETE、UPDATE的原理：
数据表，insert临时表，delete临时表
当我们执行insert操作时，会同时把数据插入insert临时表和数据表;
当我们执行delete操作时，会在删除数据表中的数据时同时把这条数据放到delete临时表;
当我们执行update操作时，会先把数据从数据表干掉，进入delete临时表，然后产生一条新的数据，会把这条数据插入insert临时表，同时插入数据表。
--学生表--删除操作
--删除这条数据的时候，已经删除的数据存放到一个新的表中
--先创建一个和student表结构一样的表
SELECT top 0 * into T_NewStu from T_Student --从student表中查询所有数据的第0行放入新表newstu中
select * from T_NewStu
--触发器:删除数据表中数据时，会同时把删除后的数据放入新的表中
--创建一个触发器
CREATE Trigger Tr_DeleteTStu on T_Student
AFTER DELETE --该触发器在删除之后执行
AS
BEGIN
--把deleted表中的数据添加到这个新表中,DELETED是临时表
INSERT INTO T_NewStu SELECT * FROM DELETED
END
--执行失败，是因为原表的Id自增，则新标newstu也自增，而被删除的deleted中数据确是自带了id的，所以原表Id不能自增,至少newstu不能自增。
原文：http://www.cnblogs.com/adolphyang/p/4985254.html