--级联删除表中的记录
CREATE PROCEDURE proc_delete_data(p_tname varchar(100))
begin
declare v_tablename varchar(50);
declare flag int default 0;
--定义一个游标,此游标返回指定表的子表
declare cur_constraint cursor for select b.table_name
from information_schema.key_column_usage a,information_schema.key_column_usage b
where a.table_schema=b.referenced_table_schema
and a.table_name=b.referenced_table_name
and a.column_name=b.referenced_column_name
and a.constraint_name='PRIMARY'
and a.table_schema=(select database())
and a.table_name=upper(p_tname);
--定义异常处理
declare continue handler for not found
set flag=1;
open cur_constraint;
repeat
fetch cur_constraint into v_tablename;
if flag=0 then
--递归寻找指定表的子表
call proc_delete_data(v_tablename);
end if;
until flag=1
end repeat;
close cur_constraint;
--动态执行SQL，。
set @v_sql = concat( 'delete from ', p_tname );
prepare stmt from @v_sql;
execute stmt ;
deallocate prepare stmt;
END;
/