locked
Oracle -- T-sql RRS feed

  • Question

  • CREATE OR REPLACE PROCEDURE PURSUIT.product_line3(in_code product_lines_catalog1.code%TYPE DEFAULT NULL) IS
      -- MAin Cursor
      cursor c1 is
        select p.row_id row_id, code
          from product_lines_catalog1 p
         where ((in_code IS NOT NULL AND p.code = in_code) OR
               (in_code IS NULL AND upper(code) <> lower(code)));

      cursor c2(cp_parent_id product_lines_catalog_tree1.parent_id%TYPE) is
        select row_id,
               code,
               ematrix_family,
               attribute_name,
               ematrix_attribute_name,
               object_name
          from product_lines_catalog1
         where row_id IN (SELECT child_id
                            FROM product_lines_catalog_tree1
                           WHERE parent_id = cp_parent_id)
           and object_name IN ('PRODUCT_LINE_ITEM', 'PL_ITEM_ATTRIBUTES');

      cursor cursor1(cp_parent_id product_lines_catalog_tree1.parent_id%TYPE) is
        select row_id,
               code,
               ematrix_family,
               attribute_name,
               ematrix_attribute_name,
               object_name
          from product_lines_catalog1
         where row_id IN (SELECT child_id
                            FROM product_lines_catalog_tree1
                           WHERE parent_id = cp_parent_id)
           and object_name IN ('PL_ITEM_ATTRIBUTES');

      /* AND (object_name = 'PRODUCT_LINE_ITEM'
        AND object_type = 1)
        UNION
      select row_id, code, ematrix_family,attribute_name, ematrix_attribute_name
       from product_lines_catalog1
      where row_id IN (SELECT child_id
                         FROM product_lines_catalog_tree1
                        WHERE parent_id = cp_parent_id)
        AND (object_name = 'PL_ITEM_ATTRIBUTES'
        AND object_type = 2);*/

      cursor c3(cp_parent_id product_lines_catalog1.row_id%TYPE) is
        select row_id
          from product_lines_catalog1
         where row_id IN (SELECT child_id
                            FROM product_lines_catalog_tree1
                           WHERE parent_id = cp_parent_id)
           AND object_name = 'PL_ITEM_ATTRIBUTES'
           AND object_type = 2;

      cursor c4(cp_parent_id product_lines_catalog1.row_id%TYPE) is
        select code, ematrix_family, attribute_name, ematrix_attribute_name
          from product_lines_catalog1
         where row_id IN (SELECT child_id
                            FROM product_lines_catalog_tree1
                           WHERE parent_id = cp_parent_id)
           AND object_name = 'PL_ITEM_ATTRIBUTES'
           AND object_type = 3;
      v_commit_counter PLS_INTEGER := 0;
      v_insert_counter PLS_INTEGER;
    BEGIN

      v_commit_counter := 1;

      FOR r1 IN c1 LOOP
        FOR r2 IN c2(r1.row_id) LOOP
       
          -- Three Types
          -- PL_ITEM_ATTRIBUTES
       
          -- PRODUCT_LINE_ITEM 
       
          -- COUNTRY_LIST - OTHER
          IF r2.object_name = 'PL_ITEM_ATTRIBUTES' THEN
            FOR rec1 IN cursor1(r2.row_id) LOOP
              INSERT INTO product_line_test
                (ppl_code,
                 ematrix_family_name,
                 cnh_attribute_name,
                 ematrix_attribute_name)
              VALUES
                (r1.code || ' ' || NVL(r2.code, 'PARENT1'),
                 rec1.ematrix_family,
                 rec1.attribute_name,
                 rec1.ematrix_attribute_name);
            END LOOP;
          ELSE
            FOR r3 IN c3(r2.row_id) LOOP
           
              v_insert_counter := 0;
           
              INSERT INTO product_line_test
                (ppl_code,
                 ematrix_family_name,
                 cnh_attribute_name,
                 ematrix_attribute_name)
              VALUES
                (r1.code || ' ' || NVL(r2.code, 'PARENT'),
                 r2.ematrix_family,
                 r2.attribute_name,
                 r2.ematrix_attribute_name);
           
              COMMIT;
           
              FOR r4 IN c4(r3.row_id) LOOP
                v_commit_counter := v_commit_counter + 1;
                v_insert_counter := v_insert_counter + 1;
                /* dbms_output.put_line(' a: ' || r4.code || r4.ematrix_family ||
                r4.attribute_name ||
                r4.ematrix_attribute_name);*/
                INSERT INTO product_line_test
                  (ppl_code,
                   ematrix_family_name,
                   cnh_attribute_name,
                   ematrix_attribute_name)
                VALUES
                  (r1.code || ' ' || r2.code,
                   r2.ematrix_family,
                   r4.attribute_name,
                   r4.ematrix_attribute_name);
             
                IF MOD(v_commit_counter, 500) = 0 THEN
                  COMMIT;
                END IF;
             
              END LOOP;
           
              IF v_insert_counter = 0 THEN
                INSERT INTO product_line_test
                  (ppl_code,
                   ematrix_family_name,
                   cnh_attribute_name,
                   ematrix_attribute_name)
                VALUES
                  (r1.code || ' ' || r2.code, r2.ematrix_family, NULL, NULL);
              END IF; -- IF v_insert_counter = 0
           
            END LOOP;
         
          END IF; -- r2.object_name =
       
        END LOOP;
     
      END LOOP;

      -- Final Commit
      COMMIT;
    END product_line3;
    /
    Friday, September 2, 2011 6:19 PM

Answers