＋设为首页 ＋添加到收藏夹
declare
tbl_name varchar(100);
cursor cur_tables is select table_name from all_tables where owner = test;
begin
open cur_tables;
fetch cur_tables into tbl_name;
while cur_tables%FOUND
loop
－－ DELETE from tbl_name;
UPDATE M_USER set user_name = tbl_name;
fetch cur_tables into tbl_name;
end loop;
end;
这个脚本没有什么问题，但是一旦用delete 就不可以了，好像通过游标得到的变量只能用在update ,insert,这种语句；作为表名处理就不可以了
现在我想清空一个表空间内所有的表，就必须要删除功能，各位高手有 什么办法？
you need to use DBMS_SQL package or execute immediate to execute the dynamic SQL like delete <yourtable>
why update is success ? the table name is fixed to M_USER!
使用descade。
动态SQL是不能这样写的
你说的有一问题，表名是不能通过变变量来的。
你的程序做如下修改：
declare
tbl_name varchar(100);
comstr varchar2(200);
cursor cur_tables is select table_name from all_tables where owner = test;
begin
commstr:=delete from ;
open cur_tables;
fetch cur_tables into tbl_name;
while cur_tables%FOUND
loop
--以下为删表语句
commstr:=commstr||tbl_name;
execute immediate commstr;
-- DELETE from tbl_name;
UPDATE M_USER set user_name = tbl_name;
fetch cur_tables into tbl_name;
end loop;
close cur_tables;
end;
完全可以的，看我以前写的：
create or replace procedure SP_CLEAR(V_TABLE IN STRING) IS
/*---------------------------------------------------------------
--Function:delete all records of tables in v_table //
--Author:Liaozq //
--Date:2002/10/8 //
--Explain:(1)v_table must like this:table1,table2,table3,... //
(2)Before delete records from tables,recorder thess //
tables.
---------------------------------------------------------------*/
TYPE cur_type IS REF CURSOR;
c_tab cur_type;
v_str STRING(2000);
v_tab t_clear.wlbmc%TYPE;
v_sql STRING(3000);
begin
--Get table from v_table
--Reorder these tables
v_str:=upper(v_table);
v_str:=REPLACE(v_str,,,,);
v_str:=||v_str||;
v_sql:=SELECT WLBMC FROM T_CLEAR WHERE TRIM(WLBMC) IN (||v_str||) ORDER BY BZ DESC;
OPEN c_tab FOR v_sql;
LOOP
FETCH c_tab INTO v_tab;
EXIT WHEN c_tab%NOTFOUND;
v_sql:=delete from ||v_tab||;
EXECUTE IMMEDIATE v_sql;
END LOOP;
COMMIT;
--deal with exception
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
RAISE;
end SP_CLEAR;
/
------------------------
| |
| 相逢何必曾相识 |
| |
------------------------
