更新数据库表

Question

我有以下的数据库表：

在这些表中，我有以下要素：

• 容器：可以包含任何container_item元素;该关系使用表CONTAINER_CANDIDATES
• Container_Item：可以包含任何元素项;该关系使用表COMPOUNDDS
• 元素：我的系统中的基本元素。

让我词组用一个具体案例的问题：

在表元素，我可以存储以下内容：

Id = 1 ; ElementName = 'element001' 
Id = 2 ; ElementName = 'element002' 
Id = 3 ; ElementName = 'element003' 
Id = 4 ; ElementName = 'element004' 
Id = 5 ; ElementName = 'element005' 
Id = 6 ; ElementName = 'element006' 
Id = 7 ; ElementName = 'element007' 

在表CONTAINER_ITEM我可以存储以下内容：

Id = 1 ; ContainerItemName = 'item-id-aaa' 
Id = 2 ; ContainerItemName = 'item-id-bbb' 
Id = 3 ; ContainerItemName = 'item-id-ccc' 
Id = 4 ; ContainerItemName = 'item-id-ddd' 
Id = 5 ; ContainerItemName = 'item-id-eee' 

在表CONTAINER中，我可以存储以下元素：

以这种方式创建我的所有连接

 - ContainerName01 contains the following : 
      -> item-id-aaa (id = 1 in Container_Item table) 
      -> item-id-bbb (id = 2 in COntainer_Item table) 
      -> item-id-ccc (id = 3 in COntainer_Item table) 
      -> item-id-ddd (id = 4 in COntainer_Item table) 
     - ContainerName02 contains the following: 
      -> item-id-aaa (id = 1 in Container_Item table) 
      -> item-id-eee (id = 5 in COntainer_Item table) 

所以：

Id = 1; ContainerName = 'ContainerName01'; 
Id = 2; ContainerName = 'ContainerName02'; 

使用表I化合物进行以下连接：

- item-id-aaa (id = 1 in Container_Item table) 
     -> element001 (id = 1 in Elements table) 
     -> element002 (id = 2 in Elements table) 
    - item-id-bbb (id = 2 in Container_Item table) 
     -> element003 (id = 3 in Elements table) 
     -> element004 (id = 4 in Elements table) 
    - item-id-ccc (id = 3 in Container_Item table) 
     -> element005 (id = 5 in Elements table) 
     -> element006 (id = 6 in Elements table) 
    - item-id-ddd (id = 4 in Container_Item table) 
     -> element005 (id = 5 in Elements table) 
     -> element007 (id = 7 in Elemens table); 
    - item-id-eee (id = 5 in Container_Item table) 
     -> element-007 (id = 7 in Elemens table) 

使用表CONTAINER_CANDIDATES我提出以下连接。 现在的问题是，如何删除ContainerName01及其下的所有项目（容器项目和元素），以便其他Container（例如：ContainerName02）完全不受影响？

我想如果你想通过一个过程来实现这一目标的方式去将通过容器ID作为参数，然后删除该子项，以实现这一目标使用的是Oracle PL SQL程序

Answer 1

好吧，如果你遵循良好的做法，这不是一个很困难的问题。

首先，你有两个“多到多”跳表（CONTAINER_CANDIDATES & COMPOUNDS），在这些孤儿行将是完全无用的，我们对他们添加DELETE CASCADE。

ALTER TABLE CONTAINER_CANDIDATES 
ADD CONSTRAINT FK_CC_CONTAINER 
    FOREIGN KEY (CONTAINERID) 
    REFERENCES CONTAINER (ID) 
    ON DELETE CASCADE; 

ALTER TABLE CONTAINER_CANDIDATES 
ADD CONSTRAINT FK_CC_CONTAINER_ITEM 
    FOREIGN KEY (CONTAINERITEMID) 
    REFERENCES CONTAINER_ITEM (ID) 
    ON DELETE CASCADE; 

ALTER TABLE COMPOUNDS 
ADD CONSTRAINT FK_COMPOUNDS_CONTAINER_ITEM 
    FOREIGN KEY (CONTAINERITEMID) 
    REFERENCES CONTAINER_ITEM (ID) 
    ON DELETE CASCADE; 

ALTER TABLE COMPOUNDS 
ADD CONSTRAINT FK_COMPOUNDS_ELEMENTS 
    FOREIGN KEY (ELEMENTSID) 
    REFERENCES ELEMENTS (ID) 
    ON DELETE CASCADE; 

现在，事情几乎被自己的工作，一个小的存储过程，以确保我们不会让未使用的CONTAINER_ITEM和ELEMENTS和我们是很好的。

CREATE OR REPLACE PROCEDURE cascaded_delete_container (
    P_CONTAINER_ID VARCHAR2 
) IS 
BEGIN 
     -- remove the master from supplied ID 
     -- cascade on CONTAINER_CANDIDATES 
    DELETE FROM CONTAINER 
    WHERE ID = P_CONTAINER_ID; 

     -- remove CONTAINER_ITEM not used in CONTAINER_CANDIDATES 
     -- cascade on COMPOUNDS 
    DELETE FROM CONTAINER_ITEM 
    WHERE NOT EXISTS(
     SELECT 1 
     FROM CONTAINER_CANDIDATES 
     WHERE CONTAINER_ITEM.ID = CONTAINER_CANDIDATES.CONTAINERITEMID 
     ); 

     -- remove ELEMENTS not used in COMPOUNDS 
    DELETE FROM ELEMENTS 
    WHERE NOT EXISTS(
     SELECT 1 
     FROM COMPOUNDS 
     WHERE ELEMENTS.ID = COMPOUNDS.ELEMENTSID 
     ); 

    COMMIT; 

END; 
/

这不确定你从来没有在你的任何表中的孤儿。它使用Cascade来完成大部分工作，只需在两个从表中对未使用的数据进行小修改即可。

唯一的缺点是，如果您不使用它们，将不允许您在CONTAINER_ITEM和ELEMENTS中保持输入。

Answer 2

你通过光标获取。据我所知，您需要首先删除CONTAINER_CANDIDATES和COMPOUNDS表中的条目。例如：

create or replace procedure delete_container(p_container_id number) is 

    -- Get all compound child etries via container ID 
    cursor c_get_compounds(cp_container_id number) is 
    select comp.id 
     from compounds comp, container_candidates cc 
    where comp.containerItemID = cc.containerItemID 
     and cc.containerID = cp_container_id; 

    -- Get all container candidate child entries via container ID 
    cursor c_get_container_candidates(cp_container_id number) is 
    select cc.id 
     from container_candidates cc 
    where cc.containerID = cp_container_id; 

begin 

    -- Fetch all compound entries 
    for r in c_get_compounds(cp_container_id => p_container_id) loop 
    -- Delete compound entries 
    delete from compounds where id = r.id; 
    end loop; 

    -- Fetch all container candidates 
    for r in c_get_container_candidates(cp_container_id => p_container_id) loop 
    -- Delete container candidates 
    delete from container_candidates where id = r.id; 
    end loop; 

    -- Delete container entry 
    delete from container where id = p_container_id; 
end delete_container; 

检查ID是正确的代码