Quellcode-Bibliothek dml181.mco

-- SQL Test Suite, V6.0, SQL Module dml181.mco
-- 59-byte ID
-- 
MODULE DML181
LANGUAGE COBOL
AUTHORIZATION FLATER

DECLARE C0894  CURSOR FOR
               SELECT FIRSTNAME, LASTNAME, AVG(COST)
               FROM PEOPLE_ORDERS
               GROUP BY LASTNAME, FIRSTNAME
               ORDER BY LASTNAME, FIRSTNAME


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

PROCEDURE SUB2 (SQLCODE, SQLSTATE);
      ROLLBACK WORK;

PROCEDURE SUB3 (SQLCODE, SQLSTATE);
      CREATE TABLE LONG_NAMED_PEOPLE (
                    FIRSTNAME VARCHAR (373),
                    LASTNAME VARCHAR (373),
                    AGE INT,
                    PRIMARY KEY (FIRSTNAME, LASTNAME));

PROCEDURE SUB4 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB5 (SQLCODE, SQLSTATE);
      CREATE TABLE ORDERS (
                    FIRSTNAME VARCHAR (373),
                    LASTNAME VARCHAR (373),
                    TITLE VARCHAR (80),
                    COST NUMERIC(5,2),
                    FOREIGN KEY (FIRSTNAME, LASTNAME)
                    REFERENCES LONG_NAMED_PEOPLE);

PROCEDURE SUB6 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB7 (SQLCODE, SQLSTATE);
      CREATE VIEW PEOPLE_ORDERS AS
                    SELECT * FROM LONG_NAMED_PEOPLE JOIN ORDERS
                    USING (FIRSTNAME, LASTNAME);

PROCEDURE SUB8 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB9 (SQLCODE, SQLSTATE);
      INSERT INTO LONG_NAMED_PEOPLE VALUES (
             'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
             'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
             'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
             'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
             'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
             'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
             'aaaaaaaaaaaaaaaaaaaaaaaaa',
             'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'
             'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'
             'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'
             'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'
             'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'
             'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'
             'bbbbbbbbbbbbbbbbbbbbbbbbb',
                    20);

PROCEDURE SUB10 (SQLCODE, SQLSTATE);
      INSERT INTO LONG_NAMED_PEOPLE VALUES (
               'ccccccccccccccccccccccccccccccccccccccccccccccccccccccccc'
               'ccccccccccccccccccccccccccccccccccccccccccccccccccccccccc'
               'ccccccccccccccccccccccccccccccccccccccccccccccccccccccccc'
               'ccccccccccccccccccccccccccccccccccccccccccccccccccccccccc'
               'ccccccccccccccccccccccccccccccccccccccccccccccccccccccccc'
               'ccccccccccccccccccccccccccccccccccccccccccccccccccccccccc'
               'ccccccccccccccccccccccccccccccc',
               'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd'
               'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd'
               'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd'
               'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd'
               'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd'
               'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd'
               'ddddddddddddddddddddddddddddddd',
                    25);

PROCEDURE SUB11 (SQLCODE, SQLSTATE);
      INSERT INTO ORDERS VALUES (
              'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
              'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
              'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
              'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
              'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
              'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
              'aaaaaaaaaaaaaaaaaaaaaaaaa',
              'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'
              'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'
              'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'
              'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'
              'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'
              'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'
              'bbbbbbbbbbbbbbbbbbbbbbbbb',
              'Gidget Goes Skiing',
                    29.95);

PROCEDURE SUB12 (SQLCODE, SQLSTATE);
      INSERT INTO ORDERS VALUES (
              'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
              'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
              'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
              'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
              'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
              'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa'
              'aaaaaaaaaaaaaaaaaaaaaaaaa',
              'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'
              'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'
              'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'
              'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'
              'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'
              'bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb'
              'bbbbbbbbbbbbbbbbbbbbbbbbb',
              'Barney Goes Hawaiian',
                    19.95);

PROCEDURE SUB13 (SQLCODE, SQLSTATE);
      INSERT INTO ORDERS VALUES (
               'ccccccccccccccccccccccccccccccccccccccccccccccccccccccccc'
               'ccccccccccccccccccccccccccccccccccccccccccccccccccccccccc'
               'ccccccccccccccccccccccccccccccccccccccccccccccccccccccccc'
               'ccccccccccccccccccccccccccccccccccccccccccccccccccccccccc'
               'ccccccccccccccccccccccccccccccccccccccccccccccccccccccccc'
               'ccccccccccccccccccccccccccccccccccccccccccccccccccccccccc'
               'ccccccccccccccccccccccccccccccc',
               'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd'
               'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd'
               'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd'
               'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd'
               'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd'
               'ddddddddddddddddddddddddddddddddddddddddddddddddddddddddd'
               'ddddddddddddddddddddddddddddddd',
               'Invasion of the Smurfs',
                    9.95);

PROCEDURE SUB14 (SQLCODE, SQLSTATE);
    OPEN C0894;

PROCEDURE SUB15 (SQLCODE, SQLSTATE,
    :NAAM1 CHAR(373),
    :NAAM2 CHAR(373),
    :FLT1 NUMERIC (8,4));
      FETCH C0894 INTO :NAAM1, :NAAM2, :FLT1;

PROCEDURE SUB16 (SQLCODE, SQLSTATE,
    :NAAM1 CHAR(373),
    :NAAM2 CHAR(373),
    :FLT1 NUMERIC (8,4));
      FETCH C0894 INTO :NAAM1, :NAAM2, :FLT1;

PROCEDURE SUB17 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB18 (SQLCODE, SQLSTATE);
      DROP TABLE ORDERS CASCADE;

PROCEDURE SUB19 (SQLCODE, SQLSTATE);
      COMMIT WORK;

PROCEDURE SUB20 (SQLCODE, SQLSTATE);
      DROP TABLE LONG_NAMED_PEOPLE CASCADE;

PROCEDURE SUB21 (SQLCODE, SQLSTATE);
      COMMIT WORK;

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

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

PROCEDURE SUB24 (SQLCODE, SQLSTATE);
      COMMIT WORK;