Oracle uses a cursor to delete all record scripts in all user data tables

Oracle uses a cursor to delete all record scripts in all user data tables

Application Scenario: because the data in the Oracle database involves confidential information, we hope to delete all the data at a time and only keep the data table structure for the development of new projects.

Test Result: All data has been deleted.

Problem: if the data table has a foreign key, the following script may fail to be executed. delete or filter the table by yourself. See

Operation Method: copy the following script content to PQSQL for execution.

 

-- Oracle deletes all record scripts in all user data tables with a cursor

Declare mystring NVARCHAR2 (1000): = ''; -- Define the string variable to be output

Cursor mycursor is -- defines the cursor

Select * from user_tables order by table_name; -- Query all user tables

Myrecord mycursor % rowtype; -- defines the cursor record type
Counter int: = 0;
Begin
Open mycursor; -- open the cursor
If mycursor % isopen then -- determines whether the open is successful
Loop -- Obtain Record Sets cyclically
Fetch mycursor into myrecord; -- get records in the cursor

If mycursor % found then -- the cursor's found attribute determines whether a record exists
Begin

Mystring: = 'truncate from' | myrecord. table_name;

Dbms_output.put_line ('current operation statement is '| mystring );

If (myrecord. table_name <> 'table _ info') then
Execute immediate 'truncate table' | myrecord. table_name;
End if;

Commit; -- run now

End;

Else
Exit;
End if;

End loop;
Else
Dbms_output.put_line ('cursor not opened ');
End if;
Close mycursor;

End;