下面是一段自己修改的一个oracle存储过程，里面错误应该很多，大家找。按错误个数及难度给分.

[updatedb]

下面的过程的目的是清楚所有的用户(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;

[wylwyl1130]

SqlExecute varchar2(3000):='select * from %tablename%';

tablename varchar2(40) ;

s_ql varchar2(100) ;

[wylwyl1130]

太多了，还是重写吧

[updatedb]

请改正一下，我没有什么这方面的书来看呀

[wylwyl1130]

--没太看懂你的意思，把理解的写出来了，首先要sys赋给用户select any table to 用户



create or replace procedure TruncateDatabase is


tbname varchar2(40) ;


v_count number;

cursor mycur is select TABLE_NAME from dba_tables where owner='TEMP';


begin

for c1 in mycur loop

select count(*),max(table_name) into v_count,tbname from user_constraints where table_name = c1.table_name and CONSTRAINT_TYPE='R';

if v_count = 1 then

dbms_output.putline(tablename);

else


null;--plsql中不可以直接select ..from ....，必须要有into 变量





execption


when others then
dbms_output.putline('others');


end TruncateDatabase;

[wylwyl1130]

我有别人写的plsql笔记，不多，很快就能读完
给出你的邮箱

[updatedb]

[e-mail]

[wylwyl1130]

已发，请查收

[lxmlhh]

deallocate mycur;
这个是SQLSERVER的吗。

[lxmlhh]

你的存储过程不用commit和rollback的吗？