oracle问题

如何用SQLScript删除Database所有表中的记录？

可执行以下Script 来删除对应DB 中的所有Table 的记录，并且允许特别指定一些表不处理。 if exists ( select * from tempdb。dbo。sysobjects where name like '#tmp_Sys_DBS%' ) drop table #tmp_Sys_DBS Go -- create one new table to record table category Create Table [dbo]。 #tmp_Sys_DBS( Obj_ID int identity (1,1) Not Null , Obj_Name sysname N...全部

  可执行以下Script 来删除对应DB 中的所有Table 的记录，并且允许特别指定一些表不处理。 if exists ( select * from tempdb。dbo。sysobjects where name like '#tmp_Sys_DBS%' ) drop table #tmp_Sys_DBS Go -- create one new table to record table category Create Table [dbo]。
  #tmp_Sys_DBS( Obj_ID int identity (1,1) Not Null , Obj_Name sysname Not Null , Obj_Type char (2) Not Null, Category_Code varchar(30) Not Null , Description nvarchar(128) Null ) -- some tables protected by FMIS insert #tmp_Sys_DBS (Obj_Name , Obj_Type , Category_Code , Description ) Values ('SYSTEM_TABLE','U' , 'Protected' , 'System Parameter Table ') -- Inventory Part insert #tmp_Sys_DBS (Obj_Name , Obj_Type , Category_Code , Description ) Values ('Stock','U' , 'Inventory' , '') insert #tmp_Sys_DBS (Obj_Name , Obj_Type , Category_Code , Description ) Values ('Stock_Detail','U' , 'Inventory' , '') Go Declare @vDeleteData varchar(100) Declare @vTableName varchar(100) Declare @vTableName1 varchar(100) Declare @vCount int Select @vCount=1 Declare @cursorAllTableName cursor Set @cursorAllTableName = cursor for select name from sysobjects where type='U' Order by Name Desc Open @cursorAllTableName Fetch next from @cursorAllTableName Into @vTableName While @@Fetch_Status=0 Begin Select @vTableName1 = Upper(Ltrim(Rtrim(@vTableName))) If Exists ( Select * From #tmp_Sys_DBS Where obj_name = @vTableName1 ) PRINT 'XX> #'+ convert(varchar (10),@vCount)+'# Do not delete / truncate Table : ' + @vTableName1 Else Begin select @vDeleteData = 'if Exists ( Select * From ' + @vTableName + ' ) ' select @vDeleteData = @vDeleteData + ' Truncate Table '+@vTableName exec (@vDeleteData) -- Print ' delete / truncate : ' + @vDeleteData PRINT '==> #'+ convert(varchar (10),@vCount)+'# Finished to delete / truncate Table : ' + @vTableName1 End Fetch next from @cursorAllTableName Into @vTableName Select @vCount=@vCount+1 End Close @cursorAllTableName Deallocate @cursorAllTableName Print '--**-- Complete successfully! --**--'。
  收起