Functions and Procedures
-
Define a function that, given the name of a department, returns the count of the number of instructors in that department.
create function dept_count (dept_name varchar(20)) returns integer begin declare d_count integer; select count (* ) into d_count from instructor where instructor.dept_name = dept_count.dept_name; return d_count; end ;
select dept_name, budget from department where dept_count (dept_name) > 12 ;
-
The dept_count function could instead be written as procedure:
create procedure dept_count_proc (in dept_name varchar(20), out d_count integer) begin select count(*) into d_count from instructor where instructor.dept_name = dept_count_proc.dept_name ; end ;
declare d_count integer; call dept_count_proc( 'Physics', d_count);
Triggers
- A trigger is a procedure that is executed automatically by the system as a side effect of an event
- Triggers can be activated before, after or instead of an event.
Old row & New row
- The referencing old row as clause can be used to create a tuple variable storing the old value of an updated or deleted row.
- The referencing new row as clause can be used to create a tuple variable storing the new value of an inserted or updated row.
create trigger trig_book after insert
on t_book for each row
update t_bookType set bookNum = bookNum+1 where new.bookTypeId = t_booktype.id;
DELIMITER |
create trigger trig_book2 after delete
on t_book for each row
begin
update t_bookType set bookNum = bookNum-1 where old.bookTypeId=t_booktype.id;
insert into t_log values(null,NOW(),'在book表里删除了一条数据');
delete from t_test where old.bookTypeId = t_test.id;
end
|
DELIMITER ;