ad_treenode_check.sql

来自「Java写的ERP系统」· SQL 代码 · 共 28 行

/**
 *  Check Tree (Nodes)
 *  - Is there a root for every tree?
 */ 
DECLARE
    CURSOR  Cur_Tree    IS
        SELECT  *
        FROM    AD_Tree t
        WHERE NOT EXISTS (SELECT * FROM AD_TreeNode tn 
        	WHERE t.AD_Tree_ID=tn.AD_Tree_ID AND Parent_ID IS NULL);
BEGIN
    FOR CT IN Cur_Tree LOOP
        --  Insert into TreeNode
        INSERT INTO AD_TreeNode
            (AD_Client_ID, AD_Org_ID,
            IsActive, Created, CreatedBy, Updated, UpdatedBy,
            AD_Tree_ID, Node_ID, Parent_ID, SeqNo)
        VALUES
            (CT.AD_Client_ID, CT.AD_Org_ID,
            CT.IsActive, CT.Created, CT.CreatedBy, CT.Updated, CT.UpdatedBy,
            CT.AD_Tree_ID, 0, NULL, 0);
        DBMS_OUTPUT.PUT_LINE('Created Tree Root for ' || CT.Name
            || ' AD_Tree_ID='|| CT.AD_Tree_ID);
    END LOOP;
    COMMIT;
END;