数据库mysql的函数、过程和触发器的使用

Functions and Procedures

1. 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 ;
   

2. 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 ;