类型：转载 责任编辑：asp.net 日期：2007/05/23
热门软件下载：
下面的过程的目的是清楚所有的用户(TEMP)的数据库表，表之间有外键引用的情况.
同时，希望大家的在存储过程用于学习。分不够再加。由于是从sql server 的数
据库转来，所以大家从这方面入手较易.
create or replace procedure TruncateDatabase is
varchar(3000) SqlExecute:=select * from %tablename%;
varchar(40) tablename;
varchar(100) sql;
table(tablename varchar(100)) stack;
begin
insert into stack select TABLE_NAME from dba_tables where owner=TEMP;
while( exists(select * from stack))
begin
cursor mycur is select tablename from stack;
open mycur;
loop
fetch next from mycur into tablename;
exit when mycur%notfound;
if(exist(select * from user_constraints where table_name=tablename and table_name in (select tablename from stack) and CONSTRAINT_TYPE=R))
begin
dbms_output.putline(tablename);
end;
else
begin
delete from stack where tablename=tablename;
sql:=replace(sqlexecute,%tablename%,tablename);
dbms_output.put_line(sql);
execute immediate sql;
end;
end loop;
close mycur;
deallocate mycur;
end;
end;
网友回答：
SqlExecute varchar2(3000):=select * from %tablename%;
tablename varchar2(40) ;
s_ql varchar2(100) ;