| products/Sources/formale Sprachen/Cobol/Test-Suite/SQL M/ |
|
Quellcode-Bibliothek© Kompilation durch diese Firma[Weder Korrektheit noch Funktionsfähigkeit der Software werden zugesichert.]Datei: sdl027.cob Sprache: Cobol |
|
||||||
|
IDENTIFICATION DIVISION.
PROGRAM-ID. SDL027. ENVIRONMENT DIVISION. CONFIGURATION SECTION. SOURCE-COMPUTER. xyz. OBJECT-COMPUTER. xyz. DATA DIVISION. WORKING-STORAGE SECTION. * Standard COBOL (file "SDL027.SCO") calling SQL * procedures in file "SDL027.MCO". * STANDARD COBOL (file "SDL027.SCO") **************************************************************** * * COMMENT SECTION * * DATE 1990/09/14 STANDARD COBOL LANGUAGE * NIST SQL VALIDATION TEST SUITE V6.0 * DISCLAIMER: * This program was written by employees of NIST to test SQL * implementations for conformance to the SQL standards. * NIST assumes no responsibility for any party's use of * this program. * * SDL027.SCO * WRITTEN BY: SUN DAJUN * * THIS ROUTINE TESTS THE SCHEMA DEFINITION LANGUAGE FOR SQL. * * REFERENCES * AMERICAN NATIONAL STANDARD database language - SQL * X3.135-1989 * * SECTION 6.9 View Definition * **************************************************************** * EXEC SQL BEGIN DECLARE SECTION END-EXEC 01 CITY1 PIC X(9). 01 YEAR1 PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 Hi PIC S9(4)V9(4) DISPLAY SIGN LEADING SEPARATE. 01 Hi2 PIC S9(4)V9(4) DISPLAY SIGN LEADING SEPARATE. 01 Lo PIC S9(4)V9(4) DISPLAY SIGN LEADING SEPARATE. 01 AVGHi PIC S9(4)V9(4) DISPLAY SIGN LEADING SEPARATE. 01 MAX1 PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 MIN1 PIC S9(9) DISPLAY SIGN LEADING SEPARATE. * EXEC SQL END DECLARE SECTION END-EXEC 01 flag PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 uid PIC X(18). 01 uidx PIC X(18). 01 SQLCODE PIC S9(9) COMP. 01 errcnt PIC S9(4) DISPLAY SIGN LEADING SEPARATE. 01 SHOWNUM PIC -(5).9(3). 01 SHOWNUM1 PIC -(5).9(3). 01 SHOWNUM2 PIC -(5).9(3). 01 SQL-COD PIC S9(9) DISPLAY SIGN LEADING SEPARATE. * date_time declaration * 01 TO-DAY PIC 9(6). 01 THE-TIME PIC 9(8). PROCEDURE DIVISION. P0. MOVE "HU" TO uid CALL "AUTHID" USING uid MOVE "not logged in, not" TO uidx CALL "AUTHCK" USING SQLCODE uidx MOVE SQLCODE TO SQL-COD if (uid NOT = uidx) then DISPLAY "ERROR: User " uid " expected." DISPLAY "User " uidx " connected." DISPLAY " " STOP RUN END-IF MOVE 0 TO errcnt DISPLAY "SQL Test Suite, V6.0, Module COBOL, sdl027.sco" DISPLAY " " DISPLAY "59-byte ID" DISPLAY "TEd Version #" DISPLAY " " * date_time print * ACCEPT TO-DAY FROM DATE ACCEPT THE-TIME FROM TIME DISPLAY "Date run YYMMDD: " TO-DAY " at hhmmssff: " THE-TIME * EXEC SQL DELETE FROM TEMP_OBSERV; CALL "SUB1" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO TEMP_OBSERV * VALUES (1984, 'Sun City', 110, 44); CALL "SUB2" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO TEMP_OBSERV * VALUES (1984, 'Iceburg', 45, -90); CALL "SUB3" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO TEMP_OBSERV * VALUES (1984, 'Abeland', 101, 10); CALL "SUB4" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO TEMP_OBSERV * VALUES (1985, 'Sun City', 105, 50); CALL "SUB5" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO TEMP_OBSERV * VALUES (1985, 'Iceburg', 47, -82); CALL "SUB6" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO TEMP_OBSERV * VALUES (1985, 'Abeland', 98, -3); CALL "SUB7" USING SQLCODE MOVE SQLCODE TO SQL-COD ******************** BEGIN TEST0401 ******************* DISPLAY " TEST0401 " DISPLAY " VIEW with computed columns" DISPLAY " Reference X3.135-89 section 6.9 DISPLAY " - - - - - - - - - - - - - - - - - - -" DISPLAY " ***" DISPLAY " *** CREATE TABLE TEMP_OBSERV" DISPLAY " *** (YEAR_OBSERV NUMERIC(4)," DISPLAY " *** CITY CHAR(10)," DISPLAY " *** MAX_TEMP NUMERIC(5,2)," DISPLAY " *** MIN_TEMP NUMERIC(5,2))" DISPLAY " ***" DISPLAY " *** CREATE VIEW CELSIUS_OBSERV (CITY, - " YEAR_OBSERV, MIN_C, MAX_C)" DISPLAY " *** AS SELECT CITY, YEAR_OBSERV, (MIN_TEMP - - " 32) * 5 / 9," DISPLAY " *** (MAX_TEMP - 32) * 5 / 9" DISPLAY " *** FROM TEMP_OBSERV" DISPLAY " ***" DISPLAY " *** CREATE VIEW MULTI_YEAR_OBSERV (CITY, HIGH, - " LOW)" DISPLAY " *** AS SELECT CITY, AVG(MAX_TEMP), - " AVG(MIN_TEMP)" DISPLAY " *** FROM TEMP_OBSERV" DISPLAY " *** GROUP BY CITY" DISPLAY " ***" DISPLAY " *** CREATE VIEW EXTREME_TEMPS (YEAR_OBSERV, - " HIGH, LOW)" DISPLAY " *** AS SELECT YEAR_OBSERV, MAX(MAX_TEMP), - " MIN(MIN_TEMP)" DISPLAY " *** FROM TEMP_OBSERV" DISPLAY " *** GROUP BY YEAR" DISPLAY " " MOVE 1 TO flag MOVE 0 TO Hi MOVE 0 TO Lo * EXEC SQL SELECT CITY, YEAR_OBSERV, MIN_C, MAX_C * INTO :CITY1, :YEAR1, :Lo, :Hi * FROM CELSIUS_OBSERV * WHERE YEAR_OBSERV = 1984 AND MIN_C > 5; CALL "SUB8" USING SQLCODE CITY1 YEAR1 Lo Hi MOVE SQLCODE TO SQL-COD DISPLAY " The correct answer is approximately:" DISPLAY " CITY1 = Sun City, Year1 = 1984" DISPLAY " Lo = 6.67, Hi = 43.33" DISPLAY " SQLCODE >= 0 " MOVE Lo TO SHOWNUM MOVE Hi TO SHOWNUM1 DISPLAY " Your answer is:" DISPLAY " CITY1 = ", CITY1 ", YEAR1 = ", YEAR1 DISPLAY " Lo = ", SHOWNUM ", Hi = ", SHOWNUM1 DISPLAY " SQLCODE = ", SQL-COD if (SQLCODE LESS 0) then MOVE 0 TO flag END-IF if (CITY1 NOT = "Sun City" OR YEAR1 NOT = 1984) then MOVE 0 TO flag END-IF if (Lo < 6.65 OR Lo > 6.68 OR Hi < 43.31 OR Hi > 43.35) then MOVE 0 TO flag END-IF * EXEC SQL DECLARE MYOB CURSOR FOR SELECT CITY, HIGH, LOW * FROM MULTI_YEAR_OBSERV * ORDER BY CITY ASC END-EXEC * EXEC SQL OPEN MYOB; CALL "SUB9" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL FETCH MYOB INTO * :CITY1,:Hi, :Lo; CALL "SUB10" USING SQLCODE CITY1 Hi Lo MOVE SQLCODE TO SQL-COD * EXEC SQL CLOSE MYOB; CALL "SUB11" USING SQLCODE MOVE SQLCODE TO SQL-COD DISPLAY " The correct answer is approximately:" DISPLAY " Hi = 99.5, Lo = 3.5" MOVE Hi TO SHOWNUM1 MOVE Lo TO SHOWNUM DISPLAY " Your answer is:" DISPLAY " Hi = ", SHOWNUM1 ", Lo = ", SHOWNUM if (Hi < 99.3 OR Hi > 99.7 OR Lo > 3.7 OR Lo < 3.3) then MOVE 0 TO flag END-IF * EXEC SQL DECLARE ET CURSOR FOR SELECT HIGH, YEAR_OBSERV,LOW * FROM EXTREME_TEMPS * ORDER BY YEAR_OBSERV DESC END-EXEC * EXEC SQL OPEN ET; CALL "SUB12" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL FETCH ET * INTO :MAX1, :YEAR1,:MIN1; CALL "SUB13" USING SQLCODE MAX1 YEAR1 MIN1 MOVE SQLCODE TO SQL-COD * EXEC SQL CLOSE ET; CALL "SUB14" USING SQLCODE MOVE SQLCODE TO SQL-COD DISPLAY " The correct answer is:" DISPLAY " MAX1 = 105, MIN1 = -82" DISPLAY " Your answer is:" DISPLAY " MAX1 = ", MAX1 ", MIN1 = ", MIN1 if (MAX1 NOT = 105 AND MIN1 NOT = -82) then MOVE 0 TO flag END-IF if ( flag = 1 ) then DISPLAY " *** pass *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0401','pass','MCO'); CALL "SUB15" USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY " sdl027.sco *** fail *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0401','fail','MCO'); ADD 1 TO errcnt CALL "SUB16" USING SQLCODE MOVE SQLCODE TO SQL-COD END-IF DISPLAY "===============================================" * EXEC SQL COMMIT WORK; CALL "SUB17" USING SQLCODE MOVE SQLCODE TO SQL-COD ******************** END TEST0401 ******************** ******************** BEGIN TEST0391 ******************* DISPLAY " TEST0391 " DISPLAY " Correlation names used in self-join of view" DISPLAY " Reference X3.135-89 section 6.9 DISPLAY " - - - - - - - - - - - - - - - - - - -" DISPLAY " " MOVE 1 TO flag MOVE 0 TO Hi MOVE 0 TO Hi2 MOVE "NV" TO CITY1 * EXEC SQL DECLARE THERMO CURSOR FOR * SELECT X.CITY, X.MAX_C, Y.MAX_C, * (X.MAX_C + Y.MAX_C) / 2 * FROM CELSIUS_OBSERV X, CELSIUS_OBSERV Y * WHERE X.YEAR_OBSERV = 1984 AND * Y.YEAR_OBSERV = 1985 AND * X.CITY = Y.CITY * ORDER BY 4 DESC END-EXEC * EXEC SQL OPEN THERMO; CALL "SUB18" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL FETCH THERMO INTO :CITY1, :Hi, :Hi2, :AVGHi * ; CALL "SUB19" USING SQLCODE CITY1 Hi Hi2 AVGHi MOVE SQLCODE TO SQL-COD DISPLAY " The correct answer is approximately:" DISPLAY " CITY1 = Sun City, AVGHi = 41.945" DISPLAY " 1984 Hi = 43.33, 1985 Hi = 40.56" DISPLAY " SQLCODE >= 0 " MOVE Hi TO SHOWNUM MOVE Hi2 TO SHOWNUM1 MOVE AVGHi TO SHOWNUM2 DISPLAY " Your answer is:" DISPLAY " CITY1 = ", CITY1 ", AVGHi = ", SHOWNUM2 DISPLAY " 1984 Hi = ", SHOWNUM ", 1985 Hi = ", SHOWNUM1 DISPLAY " SQLCODE = ", SQL-COD if ( SQLCODE LESS 0) then MOVE 0 TO flag END-IF if (CITY1 NOT = "Sun City" OR AVGHi > 41.96 OR AVGHi < 41.93) then MOVE 0 TO flag END-IF if (Hi2 < 40.54 OR Hi2 > 40.57 OR Hi < 43.31 OR Hi > 43.35) then MOVE 0 TO flag END-IF if ( flag = 1 ) then DISPLAY " *** pass *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0391','pass','MCO'); CALL "SUB21" USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY " sdl027.sco *** fail *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0391','fail','MCO'); ADD 1 TO errcnt CALL "SUB22" USING SQLCODE MOVE SQLCODE TO SQL-COD END-IF DISPLAY "===============================================" * EXEC SQL COMMIT WORK; CALL "SUB23" USING SQLCODE MOVE SQLCODE TO SQL-COD ******************** END TEST0391 ******************** **** TESTER MAY CHOOSE TO INSERT CODE FOR errcnt > 0 STOP RUN. * **** Procedures for PERFORM statements ¤ Dauer der Verarbeitung: 0.18 Sekunden (vorverarbeitet) ¤ |
Haftungshinweis
Die Informationen auf dieser Webseite wurden
nach bestem Wissen sorgfältig zusammengestellt. Es wird jedoch weder Vollständigkeit, noch Richtigkeit,
noch Qualität der bereit gestellten Informationen zugesichert.
Bemerkung:Die farbliche Syntaxdarstellung ist noch experimentell.Bot Zugriff |
