Oracle uses cursors to delete all record scripts in all user data tables

Application scenario: Because the data in the database involves confidential information, I hope to delete all the data at once, and keep the data table structure for the new project development program.

Test Result: All data is deleted after query

There is a problem: data table If there is a foreign key, the following script may be unsuccessful, please delete or filter out the table yourself, see

How to do this: directly copy the following script contents to Pqsql

--oracle using cursors to delete all record scripts in all user data tables

Declare mystring NVARCHAR2 (1000): = "; --Define the string variables to output

Cursor MyCursor is--defines a cursor

SELECT * FROM User_tables ORDER BY TABLE_NAME; --Query all user tables

Myrecord Mycursor%rowtype; --Define Cursor record type
Counter int: = 0;
Begin
Open mycursor; --Open cursor
If Mycursor%isopen then--Judging open success
Loop-Loop Get recordset
Fetch mycursor into Myrecord; --Get the record in the cursor

If Mycursor%found then--the found property of the cursor determines if there is a record
Begin

mystring:= ' truncate from ' | | Myrecord.table_name;

Dbms_output.put_line (' Current action statement is ' | | mystring);

if (myrecord.table_name<> ' Table_info ') then
Execute immediate ' truncate TABLE ' | | Myrecord.table_name;
End If;

commit;--Immediate Execution

End

Else
Exit
End If;

End Loop;
Else
Dbms_output.put_line (' Cursor not open ');
End If;
Close MyCursor;

End

Oracle uses cursors to delete all record scripts in all user data tables