SQL之存储过程与触发器

• 存储过程

  • 为什么要用存储过程
  • 分类
  • 定义
  • 使用
  • 删除

• 触发器

  • 为什么要用触发器
  • 分类
  • 定义及修改
  • 使用
  • 禁用及删除

1.存储过程

 1.1为什么需要存储过程 在使用任何一门编程语言时（包括t-sql），我们总希望能够有现有的语言自带的库函数调用，这样我们在编码时就很节约时间。同理，为了在sql中方便程序设计，这里将能够实现不同功能的独立代码固化到sql的相应对象中，同时将这些存储过程加载到缓存中，好处就是存储过程一经编译，就成了数据库对象，且可以高效的运行，节约时间，不需每次都编写大量的代码，重新编译。 1.2存储过程分类    1.2.1系统存储过程       系统存储过程主要是为了系统调用的方便，在master数据库中，以sp_开头，比如:sp_help。          1.3存储过程的定义    语法：    create procedure proc_name @var1 type1 [output],...    [with recompile|encryption]    as    sql_states...    参数解释：    output:若有则指明为输出参数，否则为输入参数    recompile:要求每次对定义代码重新编译    encryption:加密，防止sql发布时一起发布出去    注：存储过程是定义在一个数据库上的，是与某个数据库绑定在一起的    如果要修改存储过程，则将create改为alter即可，其他语法不变。 1.4.存储过程的使用     类似于函数调用，使用execute或者exec命令     语法：     exec proc_name @param1,....     例子：     在student库中创建一个stu_major @major char(10)，@cn int存储过程。     输入专业major即可该专业的学生人数。     代码：

use studentgocreate procedure stu_major @major char(10),@cn int outputas       ---定义存储过程select @cn=(select COUNT(*)from studentwhere @major=department)use studentgodeclare @cc int   ---使用存储过程execute stu_major '计算机系',@cc OUTPUTselect @cc '人数'

效果：

   1.5删除存储过程      语法：      drop proc|procedure proc_name      例子：      比如删除上面定义的存储过程stu_major      drop proc stu_major

2.触发器

 触发器实际上是定义在表或视图上的存储过程，是特殊化的存储过程，只有在特定的操作在定义了对应触发器的表或视图上进行时才发生，和存储过程一样，触发器一经定义经成为了数据库对象。 2.1为什么要用触发器   表的数据完整性约束很大部分都已经在表设计时完成，但是仍然可能出现完整性约束问题，为了弥补这一方面的缺陷，引入了触发器。 2.2触发器分类    2.2.1按触发器执行的先后分        若触发器在引发触发器动作之前进行，则为instead of类型，在操作进行之后再触发的为after类型。    2.2.2按引发触发的操作类型分        分为DML触发和DDL触发。        DML（数据操作语言）触发：        在对表进行update、delete、insert操作时触发        DDL（数据定义语言）触发：        在对表进行create、drop、alter、deny、grant、revoke操作时触发。        主要是为了防止乱删除或改动数据库，减少重大损失。 2.3触发器定义    语法：    create  trigger trigger_name    on table_name|view_name    for [after|instead of]         insert|delete|update|create_table|alter_table    as       ....    说明：     after和instead of 任选其一，表明是after还是instead of类型的触发器。     若定义DML触发器，则应选                insert|delete|update|create_table|alter_table|drop_table           序列的前三种的任意组合，只要满足自身需求即可。     若要定义DDL触发器，应选择后面3种的任意组合，满足需求即可。       注：若要修改触发器，将create改为alter即可。     例子：     在student库中的student表上定义一个DML触发器，要求在插入     数据时在控制台打印“您正在添加数据”     代码：

use studentgocreate trigger tr_add  ---定义触发器on  student for insertasprint '您正在插入数据!'

  2.4触发器的使用     触发器在定义之后，只要有对应定义时的操作，触发器就会自动执行     例子：     在2.3定义的触发器的情况下，向student表插入一行数据     代码：

use studentgoinsert into studentvalues('1008','关云长',30,'物联网系','33333','b3')

效果：

     2.5触发器的禁用及删除        禁用：        语法：        alter  table table_name        enable|disable trigger        [all]|trigger1,trigger2...        说明：        enable:启用触发器        disable:禁用触发器        all:禁用所有触发器        trigger1,....绑定在表上的触发器序列        删除：        语法：        drop  trigger trigger_name        例子：        删除上面创建的tr_add触发器        代码：        drop  trigger tr_add