Code modularity, seeking suggestions

From: Howard Lee Harkness <hlh_nospam_at_excite.com>
Date: Thu, 19 Aug 1999 22:13:45 GMT
Message-ID: <7FD2432186B9CEC5.11DF2E2DC448445B.521862DE68E8B5A1@lp.airnews.net>

Here is a package that I cobbled together to generate comparisons of two tables with identical structure, which should have identical contents, provided the parallel data entry efforts are both the same. This is part of a Y2K migration project.

The two procedures in the package, secpsf and hlhpsf, are basically mirror images of each other. Each procedure just finds all assy/part combinations in one file that are not in the other. Is there a way to combine these into a single procedure, to which I pass the cursors and tables?

Or, is there a different and better approach to this?

CREATE OR REPLACE PACKAGE BODY Compare IS
--

  PROCEDURE secpsf
  IS
    CURSOR sec_cur
    IS
    SELECT *
    FROM sec_psf;
--

    CURSOR hlh_cur(part IN VARCHAR2, assy IN VARCHAR2)     IS
    SELECT *
    FROM hlh_psf
    WHERE psf_part = part
      AND psf_assy = assy;
    hlh_rec hlh_cur%ROWTYPE;
--

  BEGIN
    FOR sec_rec IN sec_cur
    LOOP

      OPEN hlh_cur(sec_rec.psf_part, sec_rec.psf_assy);
      FETCH hlh_cur INTO hlh_rec;
      IF hlh_cur%NOTFOUND
      THEN
        INSERT INTO sec_psf_diff
        VALUES
        (
        sec_rec.psf_assy,
        sec_rec.psf_part
        );
      END IF;
      CLOSE hlh_cur;

    END LOOP;
    COMMIT;
  END;
--

  PROCEDURE hlhpsf
  IS
    CURSOR hlh_cur
    IS
    SELECT *
    FROM hlh_psf;
--

    CURSOR sec_cur(part IN VARCHAR2, assy IN VARCHAR2)     IS
    SELECT *
    FROM sec_psf
    WHERE psf_part = part
      AND psf_assy = assy;
    sec_rec hlh_cur%ROWTYPE;
--

  BEGIN
    FOR hlh_rec IN hlh_cur
    LOOP

      OPEN sec_cur(hlh_rec.psf_part, hlh_rec.psf_assy);
      FETCH sec_cur INTO sec_rec;
      IF sec_cur%NOTFOUND
      THEN
        INSERT INTO hlh_psf_diff
        VALUES
        (
        hlh_rec.psf_assy,
        hlh_rec.psf_part
        );
      END IF;
      CLOSE sec_cur;

    END LOOP;
    COMMIT;
  END;
END Compare;

hlh_NOSPAM_at_excite.com is a valid, unmunged address! It is also so full of spam(!) that I don't read it. Received on Thu Aug 19 1999 - 17:13:45 CDT