SELECT 'trucate table ' || DBA_TABLES.TABLE_NAME || ' ;' FROM DBA_TABLES WHERE DBA_TABLES.OWNER ='SYS'
CREATE OR REPLACE PROCEDURE drop_data IS CURSOR myCursor IS SELECT table_name FROM user_tables; strSQL user_tables.table_name%type; BEGIN OPEN myCursor; FETCH myCursor INTO strSQL; WHILE myCursor%FOUND LOOP EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || strSQL; FETCH myCursor INTO strSQL; END LOOP; CLOSE myCursor; END; /
从user_tables里提取表名
用游标循环来执行删除
FROM DBA_TABLES
WHERE DBA_TABLES.OWNER ='SYS'
CREATE OR REPLACE PROCEDURE drop_data IS
CURSOR myCursor IS SELECT table_name FROM user_tables;
strSQL user_tables.table_name%type;
BEGIN
OPEN myCursor;
FETCH myCursor INTO strSQL;
WHILE myCursor%FOUND LOOP
EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || strSQL;
FETCH myCursor INTO strSQL;
END LOOP;
CLOSE myCursor;
END;
/
改成EXECUTE IMMEDIATE 'DROP TABLE ' || strSQL || ' CASCADE CONSTRAINTS';
就应该可以了。