PL / SQL逐行删除数据 [英] PL/SQL delete data row by row

问题描述

大家好，



任何人都可以告诉我如何从主表格中逐行删除数据&子表中的值使用循环。我有一个主表与其他表有一个外键关系。我想要的是，我需要从主表&获取ID使用此ID我想删除其他表中的数据以及主表内容。



*从主表中获取ID

*删除子表中包含从主表中获取的相同ID的值。

*从主表中删除主ID行。



这是一个数据库清理过程，包含垃圾数据。



我也是PL\SQL的新手。



谢谢&问候，

Jagadisha.N



我尝试过：

 声明 
 your_variable varchar2（ 19 ）; 
  BEGIN  
  FOR  x  IN （ SELECT  COORDINATEDVERSIONID  FROM  Mastertable 
 其中 ISCURRENTVERSION =  0   order   by  coordinatedversionid  asc ）LOOP 
  FOR  y  IN （ DELETE   FROM  Childtable 
  where  coordinatedversionidid = x.COORDINATEDVERSIONID  order   by  coordinatedversionid  asc  
 
  DELETE   FROM  Mastertable 其中 coordinatedversionidid = x.COORDINATEDVERSIONID 
 
）LOOP 
 
  END  LOOP; 
  END  LOOP; 
  END ; 

解决方案

查看此...



Oracle / PLSQL：带级联删除的外键 [ ^ ]



希望这对您有所帮助。



干杯

Hi all,

Can anybody tell me how to delete data row by row from a master table & values in the child table using a loop. I have a master table which has got a foreign key relationship with other table. What i want is, i need to fetch the ID from master table & with this ID i want to delete the data present in the other table, along with the master table content.

*Get ID from Master table
*Delete the values in child table containing the same ID that was fetched from Master table.
*Delete that Master ID row from master table.

This is done a process of database cleanup, which contain junk data.

Also i am new to PL\SQL.

Thanks & regards,
Jagadisha.N

What I have tried:

Declare
your_variable varchar2(19);
BEGIN
 FOR x IN (SELECT COORDINATEDVERSIONID FROM Mastertable
                 where ISCURRENTVERSION = 0 order by coordinatedversionid asc) LOOP
   FOR y IN (DELETE FROM Childtable
            where coordinatedversionid = x.COORDINATEDVERSIONID order by coordinatedversionid asc
                      
            DELETE FROM Mastertable where coordinatedversionid = x.COORDINATEDVERSIONID
          
            ) LOOP
                 
         END LOOP;
   END LOOP;
END;

解决方案

Hi,

Check this...

Oracle/PLSQL: Foreign Keys with cascade delete[^]

Hope this will help you.

Cheers

这篇关于PL / SQL逐行删除数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！