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!
我也试过execute immediate str_sql using ....但是结果也是一样不行;M_user我只是想试试是否得到表空间的的表名,结果可以得到,但是不能用在delete from 中
你说的有一问题,表名是不能通过变变量来的。 你的程序做如下修改: 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;
to : BlueskyWide(谈趣者) 使用descade,具体怎么用,我没有用过这个。
完全可以的,看我以前写的: 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; /------------------------ | | | 相逢何必曾相识 | | | ------------------------
你的程序做如下修改:
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;
/------------------------
| |
| 相逢何必曾相识 |
| |
------------------------