linux 级联删除文件夹,级联删除表中的记录(不通过级联约束)

注意:此文档是本人写的

--级联删除表中的记录

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;

/

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/50509/viewspace-174252/，如需转载，请注明出处，否则将追究法律责任。