断言、触发器、存储过程

断言 assertion
设置每一门课程最多有60人选修
create assertion a
check (60 >= all ( select count(*)
from sc
group by cno)
);

drop assertion a;

触发器 trigger

删除触发器
drop tigger a on student

存储过程

定义一个没有返回值的存储过程
create procedure myproc1(name varchar(20))
begin
if name is null or name = ‘’ then
select * from emp;
else
select * from emp where ename like concat("%",name,"%");
end if;
end

drop procedure myproc1

– 调用存储过程
call myproc1(null);
call myproc1(‘AR’);
call myproc1(‘ART’);

– 定义一个有返回值的存储过程
– found_rows() MySQL 中定义的一个函数，左右就是返回查询结果的条数
create procedure mypro2 (name varchar(20), out num int(3))
begin
if name is null or name = ‘’ then
select * from emp;
else
select * from emp where ename like concat("%",name,"%");
end if;
select found_rows() into num;
end

– 调用存储过程
call mypro2(null,@num)
select @num

call mypro2(“AR”,@num)
select @num