---
title: "oracle 如何批处理清空多个表，保留表结构。"
author: "winzenghua"
date: "2008-12-25T14:05:00Z"
tags: []
slug: "oracle"
draft: false
lastmod: "2008-12-25T14:05:00Z"
id: 987910
url: "https://java4gis.iteye.com/blog/987910"
---

现在测试库有400多个表需要反复清空，用于测试。我在PL/SQL中只能单条用  
truncate table1;  
truncate table2;  
truncate table3;  
truncate table4;  
......  
一次只能选定一条执行，选定多条执行报错，能否写个存储过程，或者批处理，清空时执行下就可以了。

begin  
for c in(select t.constraint\_name, t.table\_name  
from USER\_CONSTRAINTS t  
where t.constraint\_type = 'R') loop  
EXECUTE IMMEDIATE 'alter table ' ||c.table\_name|| ' DISABLE CONSTRAINT '||c.constraint\_name;  
end loop;  
for c1 in(select table\_name from user\_tables) loop  
EXECUTE IMMEDIATE 'truncate table '||c1.table\_name;  
end loop;  
for c2 in(select t.constraint\_name, t.table\_name  
from USER\_CONSTRAINTS t  
where t.constraint\_type = 'R') loop  
EXECUTE IMMEDIATE 'alter table ' ||c2.table\_name|| ' ENABLE CONSTRAINT '||c2.constraint\_name;  
end loop;

end;