删除所有表中的数据:
SQL> create or replace procedure delete_all_table
as
begin
for x in (select tname from tab where tabtype = 'TABLE') loop
delete from x.tname;
end loop;
end;
编译通不过,哪里写错了?
SQL> create or replace procedure delete_all_table
as
begin
for x in (select tname from tab where tabtype = 'TABLE') loop
delete from x.tname;
end loop;
end;
编译通不过,哪里写错了?
就不能在in里,去用游标吧
delete from x.tname;
这个也不对,要动态拼接
select tname from tab where tabtype = 'TABLE'
这个不懂,你自己单独执行下如果你要删除表名等于tname 的所有行数据,把delete 改下
仅供参考 我也刚学
delete from tab
where tab.name=x.tname
已写入 file afiedt.buf 1 create or replace procedure delete_all_table
2 authid current_user
3 as
4 begin
5 for x in (select tname from tab where tabtype = 'TABLE') loop
6 dbms_output.put_line(x.tname);
7 execute immediate 'delete from '||x.tname;
8 end loop;
9* end;
SQL> /过程已创建。SQL> exec delete_all_table;
EMP
DEPTPL/SQL 过程已成功完成。
1、
select 'delete from '||object_name||';' from all_objects where
object_type='TABLE' and owner='用户名';
2、存储过程的写法
create or replace procedure delete_all_table
as
v tab%rowtype;
cursor c is select tname from tab where tabtype= 'TABLE';
begin
open c;
loop
fetch c into v;
exit when c%notfound;
delete from v.tname;
end loop;
end;