Ziele Untersuchung
mit Columbo Integrität von
Datenbanken Interaktion und
Portierbarkeit Ergonomie der
Schnittstellen

Angebot Produkte Projekt Beratung

Mittel Analytik Modellierung Sprachen Algebra Logik Hardware Denken Kreativität

Zusammenhänge Gesellschaft Wirtschaft Branche Firma


products/sources/formale Sprachen/Cobol/Test-Suite/SQL M/ Datei vom 4.1.2008 mit Größe 11 kB

Quelle dml164.mco Sprache: unbekannt

-- SQL Test Suite, V6.0, SQL Module dml164.mco
-- 59-byte ID
--
MODULE DML164
LANGUAGE COBOL
AUTHORIZATION CTS1

DECLARE C0867  CURSOR FOR S0867

DECLARE C0869  CURSOR FOR
               CORRECT_OUTPUT EXCEPT ALL SELECT * FROM LOSSY_OUTPUT
               ORDER BY PORTVAL

PROCEDURE SUB1 (SQLCODE, SQLSTATE,
    :UIDX CHAR(18));
      SELECT USER INTO :UIDX FROM HU.ECCO;

PROCEDURE SUB2 (SQLCODE, SQLSTATE);
      ROLLBACK WORK;

PROCEDURE SUB3 (SQLCODE, SQLSTATE,
    :CNT1 NUMERIC (9),
    :KYWRD CHAR(3),
    :KYWRD CHAR(3),
    :KYWRD CHAR(3));
      SELECT COUNT(*) INTO :CNT1
                    FROM TIDES.LOCATIONS
                    WHERE LOC_NAME LIKE '% ' || :KYWRD || '%'
                    OR LOC_NAME LIKE  '%' || :KYWRD || ' %'
                    OR LOC_NAME = :KYWRD;

PROCEDURE SUB4 (SQLCODE, SQLSTATE,
    :CNT1 NUMERIC (9),
    :KYWRD CHAR(9),
    :KYWRD CHAR(9),
    :KYWRD CHAR(9));
      SELECT LOC_ID INTO :CNT1
                    FROM TIDES.LOCATIONS
                    WHERE LOC_NAME LIKE '% ' || :KYWRD || '%'
                    OR LOC_NAME LIKE  '%' || :KYWRD || ' %'
                    OR LOC_NAME = :KYWRD;

PROCEDURE SUB5 (SQLCODE, SQLSTATE,
    :LONGST CHAR(219));
      SELECT
                    'SELECT LOC_ID FROM TIDES.LOCATIONS'
                    '  WHERE (LOC_NAME LIKE ''% '' || ? || ''%'''
                    '  OR LOC_NAME LIKE  ''%'' || ? || '' %'''
                    '  OR LOC_NAME = ?)'
                    '  AND (LOC_NAME LIKE ''% '' || ? || ''%'''
                    '  OR LOC_NAME LIKE  ''%'' || ? || '' %'''
                    '  OR LOC_NAME = ?)'
                    INTO :LONGST
                    FROM HU.ECCO;

PROCEDURE SUB6 (SQLCODE, SQLSTATE,
    :LONGST CHAR(219));
      PREPARE S0867 FROM :LONGST;

PROCEDURE SUB7 (SQLCODE, SQLSTATE);
      ALLOCATE DESCRIPTOR 'D0867';

PROCEDURE SUB8 (SQLCODE, SQLSTATE);
      SET DESCRIPTOR 'D0867' COUNT = 6;

PROCEDURE SUB9 (SQLCODE, SQLSTATE,
    :CNT1 NUMERIC (9),
    :KYWRD CHAR(6));
      SET DESCRIPTOR 'D0867' VALUE :CNT1
                    TYPE = 1,
                    LENGTH = 6,
                    DATA = :KYWRD;

PROCEDURE SUB10 (SQLCODE, SQLSTATE,
    :CNT1 NUMERIC (9),
    :KYWRD CHAR(6));
      SET DESCRIPTOR 'D0867' VALUE :CNT1
                    TYPE = 1,
                    LENGTH = 6,
                    DATA = :KYWRD;

PROCEDURE SUB11 (SQLCODE, SQLSTATE,
    :CNT1 NUMERIC (9),
    :KYWRD CHAR(6));
      SET DESCRIPTOR 'D0867' VALUE :CNT1
                    TYPE = 1,
                    LENGTH = 6,
                    DATA = :KYWRD;

PROCEDURE SUB12 (SQLCODE, SQLSTATE,
    :CNT1 NUMERIC (9),
    :WRD2 CHAR(7));
      SET DESCRIPTOR 'D0867' VALUE :CNT1
                    TYPE = 1,
                    LENGTH = 7,
                    DATA = :WRD2;

PROCEDURE SUB13 (SQLCODE, SQLSTATE,
    :CNT1 NUMERIC (9),
    :WRD2 CHAR(7));
      SET DESCRIPTOR 'D0867' VALUE :CNT1
                    TYPE = 1,
                    LENGTH = 7,
                    DATA = :WRD2;

PROCEDURE SUB14 (SQLCODE, SQLSTATE,
    :CNT1 NUMERIC (9),
    :WRD2 CHAR(7));
      SET DESCRIPTOR 'D0867' VALUE :CNT1
                    TYPE = 1,
                    LENGTH = 7,
                    DATA = :WRD2;

PROCEDURE SUB15 (SQLCODE, SQLSTATE);
      OPEN C0867 USING SQL DESCRIPTOR 'D0867';

PROCEDURE SUB16 (SQLCODE, SQLSTATE,
    :CNT1 NUMERIC (9));
      FETCH C0867 INTO :CNT1;

PROCEDURE SUB17 (SQLCODE, SQLSTATE,
    :CNT1 NUMERIC (9));
      FETCH C0867 INTO :CNT1;

PROCEDURE SUB18 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB19 (SQLCODE, SQLSTATE);
      INSERT INTO HU.TESTREPORT
                      VALUES('0867','pass','MCO');

PROCEDURE SUB20 (SQLCODE, SQLSTATE);
      INSERT INTO HU.TESTREPORT
                      VALUES('0867','fail','MCO');

PROCEDURE SUB21 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB22 (SQLCODE, SQLSTATE,
    :CNT1 NUMERIC (9));
      SELECT COUNT(*) INTO :CNT1
                    FROM TIDES.LOCATIONS A
                    WHERE NOT UNIQUE (
                    SELECT * FROM TIDES.LOCATIONS
                    WHERE DEG_LATITUDE = A.DEG_LATITUDE
                    AND DEG_LONGITUDE = A.DEG_LONGITUDE);

PROCEDURE SUB23 (SQLCODE, SQLSTATE,
    :CNT1 NUMERIC (9));
      SELECT COUNT(*) INTO :CNT1
                    FROM HU.WORKS A WHERE UNIQUE
                    (SELECT EMPNUM FROM HU.STAFF B
                    WHERE A.EMPNUM = B.EMPNUM
                    AND GRADE = 13
                    AND CITY = 'Deale');

PROCEDURE SUB24 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB25 (SQLCODE, SQLSTATE);
      INSERT INTO HU.TESTREPORT
                      VALUES('0868','pass','MCO');

PROCEDURE SUB26 (SQLCODE, SQLSTATE);
      INSERT INTO HU.TESTREPORT
                      VALUES('0868','fail','MCO');

PROCEDURE SUB27 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB28 (SQLCODE, SQLSTATE);
      CREATE TABLE BILLS_SENT (
                    BILLID INT PRIMARY KEY,
                    BILLAMOUNT DECIMAL(7,2));

PROCEDURE SUB29 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB30 (SQLCODE, SQLSTATE);
      CREATE TABLE PAYMENTS (
                    BILLID INT,
                    PAYID INT PRIMARY KEY,
                    PAYAMOUNT DECIMAL(7,2));

PROCEDURE SUB31 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB32 (SQLCODE, SQLSTATE);
      CREATE VIEW NO_PAY AS SELECT * FROM BILLS_SENT
                    EXCEPT CORRESPONDING SELECT * FROM PAYMENTS;

PROCEDURE SUB33 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB34 (SQLCODE, SQLSTATE);
      INSERT INTO BILLS_SENT VALUES (
                    0, 100.00);

PROCEDURE SUB35 (SQLCODE, SQLSTATE);
      INSERT INTO BILLS_SENT VALUES (
                    1, 80.00);

PROCEDURE SUB36 (SQLCODE, SQLSTATE);
      INSERT INTO BILLS_SENT VALUES (
                    2, 50.00);

PROCEDURE SUB37 (SQLCODE, SQLSTATE);
      INSERT INTO PAYMENTS VALUES (
                    0, 0, 100.00);

PROCEDURE SUB38 (SQLCODE, SQLSTATE);
      INSERT INTO PAYMENTS VALUES (
                    1, 1, 40.00);

PROCEDURE SUB39 (SQLCODE, SQLSTATE);
      INSERT INTO PAYMENTS VALUES (
                    1, 2, 40.00);

PROCEDURE SUB40 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB41 (SQLCODE, SQLSTATE,
    :CNT1 NUMERIC (9));
      SELECT BILLID INTO :CNT1
                    FROM NO_PAY;

PROCEDURE SUB42 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB43 (SQLCODE, SQLSTATE);
      CREATE TABLE CORRECT_OUTPUT (
                    PORTVAL INT);

PROCEDURE SUB44 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB45 (SQLCODE, SQLSTATE);
      CREATE TABLE LOSSY_OUTPUT (
                    PORTVAL INT);

PROCEDURE SUB45V (SQLCODE, SQLSTATE);
      CREATE VIEW CORRECT_AND_LOSSY (PORTVAL)
               AS SELECT * FROM CORRECT_OUTPUT INTERSECT ALL
                  SELECT PORTVAL FROM LOSSY_OUTPUT;

PROCEDURE SUB46 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB47 (SQLCODE, SQLSTATE);
      INSERT INTO LOSSY_OUTPUT VALUES (1);

PROCEDURE SUB48 (SQLCODE, SQLSTATE);
      INSERT INTO LOSSY_OUTPUT VALUES (2);

PROCEDURE SUB49 (SQLCODE, SQLSTATE);
      INSERT INTO LOSSY_OUTPUT VALUES (1);

PROCEDURE SUB50 (SQLCODE, SQLSTATE);
      INSERT INTO LOSSY_OUTPUT VALUES (1);

PROCEDURE SUB51 (SQLCODE, SQLSTATE);
      INSERT INTO LOSSY_OUTPUT VALUES (3);

PROCEDURE SUB52 (SQLCODE, SQLSTATE);
      INSERT INTO LOSSY_OUTPUT VALUES (1);

PROCEDURE SUB53 (SQLCODE, SQLSTATE);
      INSERT INTO LOSSY_OUTPUT VALUES (4);

PROCEDURE SUB54 (SQLCODE, SQLSTATE);
      INSERT INTO LOSSY_OUTPUT VALUES (1);

PROCEDURE SUB55 (SQLCODE, SQLSTATE);
      INSERT INTO LOSSY_OUTPUT VALUES (3);

PROCEDURE SUB56 (SQLCODE, SQLSTATE);
      INSERT INTO LOSSY_OUTPUT VALUES (2);

PROCEDURE SUB57 (SQLCODE, SQLSTATE);
      INSERT INTO CORRECT_OUTPUT VALUES (1);

PROCEDURE SUB58 (SQLCODE, SQLSTATE);
      INSERT INTO CORRECT_OUTPUT VALUES (1);

PROCEDURE SUB59 (SQLCODE, SQLSTATE);
      INSERT INTO CORRECT_OUTPUT VALUES (2);

PROCEDURE SUB60 (SQLCODE, SQLSTATE);
      INSERT INTO CORRECT_OUTPUT VALUES (1);

PROCEDURE SUB61 (SQLCODE, SQLSTATE);
      INSERT INTO CORRECT_OUTPUT VALUES (1);

PROCEDURE SUB62 (SQLCODE, SQLSTATE);
      INSERT INTO CORRECT_OUTPUT VALUES (1);

PROCEDURE SUB63 (SQLCODE, SQLSTATE);
      INSERT INTO CORRECT_OUTPUT VALUES (3);

PROCEDURE SUB64 (SQLCODE, SQLSTATE);
      INSERT INTO CORRECT_OUTPUT VALUES (1);

PROCEDURE SUB65 (SQLCODE, SQLSTATE);
      INSERT INTO CORRECT_OUTPUT VALUES (4);

PROCEDURE SUB66 (SQLCODE, SQLSTATE);
      INSERT INTO CORRECT_OUTPUT VALUES (1);

PROCEDURE SUB67 (SQLCODE, SQLSTATE);
      INSERT INTO CORRECT_OUTPUT VALUES (4);

PROCEDURE SUB68 (SQLCODE, SQLSTATE);
      INSERT INTO CORRECT_OUTPUT VALUES (2);

PROCEDURE SUB69 (SQLCODE, SQLSTATE);
    OPEN C0869;

PROCEDURE SUB70 (SQLCODE, SQLSTATE,
    :CNT1 NUMERIC (9));
      FETCH C0869 INTO :CNT1;

PROCEDURE SUB71 (SQLCODE, SQLSTATE,
    :CNT1 NUMERIC (9));
      FETCH C0869 INTO :CNT1;

PROCEDURE SUB72 (SQLCODE, SQLSTATE,
    :CNT1 NUMERIC (9));
      FETCH C0869 INTO :CNT1;

PROCEDURE SUB73 (SQLCODE, SQLSTATE,
    :CNT1 NUMERIC (9));
      FETCH C0869 INTO :CNT1;

PROCEDURE SUB74 (SQLCODE, SQLSTATE);
      CLOSE C0869;

PROCEDURE SUB75 (SQLCODE, SQLSTATE,
    :CNT1 NUMERIC (9));
      SELECT COUNT(*) INTO :CNT1
                    FROM CORRECT_AND_LOSSY
                    WHERE PORTVAL = 1;

PROCEDURE SUB76 (SQLCODE, SQLSTATE,
    :CNT1 NUMERIC (9));
      SELECT COUNT(*) INTO :CNT1
                    FROM CORRECT_AND_LOSSY
                    WHERE PORTVAL = 2;

PROCEDURE SUB77 (SQLCODE, SQLSTATE,
    :CNT1 NUMERIC (9));
      SELECT COUNT(*) INTO :CNT1
                    FROM CORRECT_AND_LOSSY
                    WHERE PORTVAL = 3;

PROCEDURE SUB78 (SQLCODE, SQLSTATE,
    :CNT1 NUMERIC (9));
      SELECT COUNT(*) INTO :CNT1
                    FROM CORRECT_AND_LOSSY
                    WHERE PORTVAL = 4;

PROCEDURE SUB79 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB80V (SQLCODE, SQLSTATE);
      DROP VIEW CORRECT_AND_LOSSY CASCADE;

PROCEDURE SUB80 (SQLCODE, SQLSTATE);
      DROP TABLE BILLS_SENT CASCADE;

PROCEDURE SUB81 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB82 (SQLCODE, SQLSTATE);
      DROP TABLE PAYMENTS CASCADE;

PROCEDURE SUB83 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB84 (SQLCODE, SQLSTATE);
      DROP TABLE CORRECT_OUTPUT CASCADE;

PROCEDURE SUB85 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB86 (SQLCODE, SQLSTATE);
      DROP TABLE LOSSY_OUTPUT CASCADE;

PROCEDURE SUB87 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB88 (SQLCODE, SQLSTATE);
      INSERT INTO HU.TESTREPORT
                      VALUES('0869','pass','MCO');

PROCEDURE SUB89 (SQLCODE, SQLSTATE);
      INSERT INTO HU.TESTREPORT
                      VALUES('0869','fail','MCO');

PROCEDURE SUB90 (SQLCODE, SQLSTATE);
      COMMIT WORK;