SQL之存储过程与触发器

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 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即可该专业的学生人数。
     代码：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
use student
go
create procedure stu_major @major char(10),@cn int output
as       ---定义存储过程
select @cn=
(
select COUNT(*)
from student
where @major=department
)

use student
go
declare @cc int   ---使用存储过程
execute stu_major '计算机系',@cc OUTPUT
select @cc '人数'

1
2
3
4
5
6
7
   1.5删除存储过程
      语法：
      drop proc|procedure proc_name
      例子：
      比如删除上面定义的存储过程stu_major
      drop proc stu_major

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
 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触发器，要求在插入
     数据时在控制台打印“您正在添加数据”
     代码：

1
2
3
4
5
6
7
8
use student
go
create trigger tr_add  ---定义触发器
on  student 
for insert
as
print '您正在插入数据!'

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

1
2
3
4
5
use student
go
insert into student
values('1008','关云长',30,'物联网系','33333','b3')

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
     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