drop procedure if exists SLAMProced;
delimiter \\
create procedure SLAMProced(a int)
BEGIN
if a =1
then select * from student;else
select * from sc;
end if;
END \\
delimiter ;
3.调用存储过程
call SLAMProced(1);
call SLAMProced(2);
1.2 触发器
1.触发器(trigger)是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法,它是与表事件相关的特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,比如当对一个表进行操作( insert,delete, update)时就会激活它执行。
2.创建一个触发器
drop trigger if exists SLAMProcedDel;
delimiter \\
create trigger SLAMProcedDel
after delete on student for each row
BEGIN
delete from sc where sc.snum = old.snum;
END \\
delimiter ;
drop trigger if exists SLAMProcedInsert;
delimiter \\
create trigger SLAMProcedInsert
after insert on student for each row
BEGIN
insert into sc value(new.snum, null, null);
END \\
delimiter ;
5.只有当我们在对 student 表进行数据插入时才会触发这个触发器;
insert into student value('01','SLAM','19901030','女');
6.创建一个修改数据的触发器
drop trigger if exists SLAMProcedUpdate;
delimiter \\
create trigger SLAMProcedUpdate
after update on student for each row
BEGIN
update sc set sc.snum ='20' where sc.snum = old.snum;
END \\
delimiter ;