| 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: dml059.cob Sprache: Cobol
|
IDENTIFICATION DIVISION.
PROGRAM-ID. DML059. ENVIRONMENT DIVISION. CONFIGURATION SECTION. SOURCE-COMPUTER. xyz. OBJECT-COMPUTER. xyz. DATA DIVISION. WORKING-STORAGE SECTION. * Standard COBOL (file "DML059.SCO") calling SQL * procedures in file "DML059.MCO". * STANDARD COBOL (file "DML059.SCO") **************************************************************** * * COMMENT SECTION * * DATE 1989/07/21 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. * * DML059.SCO * WRITTEN BY: SUN DAJUN * TRANSLATED AUTOMATICALLY FROM STANDARD C BY CHRIS SCHANZLE * * THIS ROUTINE TESTS THE SET FUNCTION SPECIFICATION IN * COMMON ELEMENTS OF THE SQL LANGUAGE. * * REFERENCES * AMERICAN NATIONAL STANDARD database language - SQL * X3.135-1989 * * SECTION 5.8 <set function specification> * SECTION 8. Data manipulation language * **************************************************************** * EXEC SQL BEGIN DECLARE SECTION END-EXEC 01 int1 PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 int2 PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 int3 PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 uid PIC X(18). 01 uidx PIC X(18). * EXEC SQL END DECLARE SECTION END-EXEC 01 SQLCODE PIC S9(9) COMP. 01 errcnt PIC S9(4) DISPLAY SIGN LEADING SEPARATE. 01 flag PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 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 * EXEC SQL SELECT * USER INTO :uidx FROM HU.ECCO; CALL "SUB1" USING SQLCODE uidx 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, dml059.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 ******************** BEGIN TEST0257 ******************* DISPLAY " Set Function Specification " DISPLAY "Reference X3.135-1989 section 5.8 General Rules " DISPLAY " ------------------------------------------ " DISPLAY " TEST0257 " DISPLAY " Reference 5.8 General Rules 1)" DISPLAY " - - - - - - - - - - - - - - - - - - - - - " DISPLAY " " DISPLAY " " DISPLAY " INSERT INTO VTABLE" DISPLAY " VALUES (10,11,12,13,15);" DISPLAY " " DISPLAY " INSERT INTO VTABLE" DISPLAY " VALUES (100,111,1112,113,115);" DISPLAY " " DISPLAY " DECLARE SUN CURSOR " DISPLAY " FOR SELECT COL1,MAX(COL2 + COL3),MIN(COL3 - " - COL2)" DISPLAY " FROM VTABLE" DISPLAY " GROUP BY COL1" DISPLAY " ORDER BY COL1;" * EXEC SQL INSERT INTO VTABLE * VALUES (10,11,12,13,15); CALL "SUB2" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO VTABLE * VALUES (100,111,1112,113,115); CALL "SUB3" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL DECLARE SUN CURSOR * FOR SELECT COL1,MAX(COL2 + COL3),MIN(COL3 - COL2) * FROM VTABLE * GROUP BY COL1 * ORDER BY COL1 END-EXEC * EXEC SQL OPEN SUN; CALL "SUB4" USING SQLCODE MOVE SQLCODE TO SQL-COD DISPLAY " The correct answer is :" DISPLAY " 0, 3, 1" DISPLAY " 10, 50, 1" DISPLAY " 100, 1223, 100" DISPLAY " 1000, 1000, 5000" DISPLAY " Your answer is :" MOVE 0 TO flag * EXEC SQL FETCH SUN INTO :int1,:int2,:int3; CALL "SUB5" USING SQLCODE int1 int2 int3 MOVE SQLCODE TO SQL-COD DISPLAY " ", int1 ", ", int2 ", ", int3 if (int1 = 0 AND int2 = 3 AND int3 = 1) then MOVE flag TO flag else MOVE 1 TO flag END-IF * EXEC SQL FETCH SUN INTO :int1,:int2,:int3; CALL "SUB6" USING SQLCODE int1 int2 int3 MOVE SQLCODE TO SQL-COD DISPLAY " ", int1 ", ", int2 ", ", int3 if (int1 = 10 AND int2 = 50 AND int3 = 1) then MOVE flag TO flag else MOVE 1 TO flag END-IF * EXEC SQL FETCH SUN INTO :int1,:int2,:int3; CALL "SUB7" USING SQLCODE int1 int2 int3 MOVE SQLCODE TO SQL-COD DISPLAY " ", int1 ", ", int2 ", ", int3 if (int1 = 100 AND int2 = 1223 AND int3 = 100) then MOVE flag TO flag else MOVE 1 TO flag END-IF * EXEC SQL FETCH SUN INTO :int1,:int2,:int3; CALL "SUB8" USING SQLCODE int1 int2 int3 MOVE SQLCODE TO SQL-COD DISPLAY " ", int1 ", ", int2 ", ", int3 if (int1 = 1000 AND int2 = 1000 AND int3 = 5000) then MOVE flag TO flag else MOVE 1 TO flag END-IF * EXEC SQL CLOSE SUN; CALL "SUB9" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL ROLLBACK WORK; CALL "SUB10" USING SQLCODE MOVE SQLCODE TO SQL-COD if (flag = 0) then DISPLAY " *** pass *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0257','pass','MCO'); CALL "SUB11" USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY " dml059.sco *** fail *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0257','fail','MCO'); CALL "SUB12" USING SQLCODE ADD 1 TO errcnt MOVE SQLCODE TO SQL-COD END-IF DISPLAY "===============================================" * EXEC SQL COMMIT WORK; CALL "SUB13" USING SQLCODE MOVE SQLCODE TO SQL-COD ******************** END TEST0257 ******************* ******************** BEGIN TEST0258 ******************* DISPLAY " Set Function Specification " DISPLAY "Reference X3.135-1989 section 5.8 General Rules " DISPLAY " ------------------------------------------ " DISPLAY " TEST0258 " DISPLAY " Reference 5.8 General Rules 1)" DISPLAY " - - - - - - - - - - - - - - - - - - - - - " DISPLAY " " DISPLAY " " DISPLAY " INSERT INTO VTABLE" DISPLAY " VALUES (10,11,12,13,15);" DISPLAY " " DISPLAY " INSERT INTO VTABLE" DISPLAY " VALUES (100,111,1112,113,115);" DISPLAY " " DISPLAY " DECLARE UN CURSOR " DISPLAY " FOR SELECT COL1,SUM(:int3 * COL2 * - " COL3)" DISPLAY " FROM VTABLE" DISPLAY " GROUP BY COL1" DISPLAY " HAVING SUM(COL2 * COL3) > 2000 " DISPLAY " OR SUM(COL2 * COL3) < -2000" DISPLAY " ORDER BY COL1;" DISPLAY " " MOVE 2 TO int3 * EXEC SQL INSERT INTO VTABLE * VALUES (10,11,12,13,15); CALL "SUB14" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO VTABLE * VALUES (100,111,1112,113,115); CALL "SUB15" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL DECLARE UN CURSOR * FOR SELECT COL1,SUM(:int3 * COL2 * COL3) * FROM VTABLE * GROUP BY COL1 * HAVING SUM(COL2 * COL3) > 2000 * OR SUM(COL2 * COL3) < -2000 * ORDER BY COL1 END-EXEC * EXEC SQL OPEN UN; CALL "SUB16" USING SQLCODE int3 MOVE SQLCODE TO SQL-COD DISPLAY " The correct answer is :" DISPLAY " 100, 366864" DISPLAY " 1000, -12000000" DISPLAY " Your answer is :" MOVE 0 TO flag * EXEC SQL FETCH UN INTO :int1,:int2; CALL "SUB17" USING SQLCODE int1 int2 MOVE SQLCODE TO SQL-COD DISPLAY " ", int1 ", ", int2 if (int1 = 100 AND int2 = 366864) then MOVE flag TO flag else MOVE 1 TO flag END-IF * EXEC SQL FETCH UN INTO :int1,:int2; CALL "SUB18" USING SQLCODE int1 int2 MOVE SQLCODE TO SQL-COD DISPLAY " ", int1 ", ", int2 if (int1 = 1000 AND int2 = -12000000) then MOVE flag TO flag else MOVE 1 TO flag END-IF * EXEC SQL CLOSE UN; CALL "SUB19" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL ROLLBACK WORK; CALL "SUB20" USING SQLCODE MOVE SQLCODE TO SQL-COD if (flag = 0) then DISPLAY " *** pass *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0258','pass','MCO'); CALL "SUB21" USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY " dml059.sco *** fail *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0258','fail','MCO'); CALL "SUB22" USING SQLCODE ADD 1 TO errcnt MOVE SQLCODE TO SQL-COD END-IF DISPLAY "===============================================" * EXEC SQL COMMIT WORK; CALL "SUB23" USING SQLCODE MOVE SQLCODE TO SQL-COD ******************** END TEST0258 ******************* ******************** BEGIN TEST0259 ******************* DISPLAY " ANY, SOME in HAVING Clause " DISPLAY "Reference X3.135-1989 section 5.23 General Rules " DISPLAY " ------------------------------------------ " DISPLAY " TEST0259 " DISPLAY " Reference 5.8 General Rules 1)" DISPLAY " - - - - - - - - - - - - - - - - - - - - - " DISPLAY " " DISPLAY " " DISPLAY " INSERT INTO VTABLE" DISPLAY " VALUES (10,11,12,13,15);" DISPLAY " " DISPLAY " INSERT INTO VTABLE" DISPLAY " VALUES (100,111,1112,113,115);" DISPLAY " " DISPLAY " DECLARE SUN1 CURSOR " DISPLAY " FOR SELECT COL1,MAX(COL2)" DISPLAY " FROM VTABLE" DISPLAY " GROUP BY COL1" DISPLAY " HAVING MAX(COL2) > ANY (SELECT GRADE FROM - " STAFF) " DISPLAY " AND MAX(COL2) < SOME (SELECT HOURS FROM - " WORKS)" DISPLAY " ORDER BY COL1;" DISPLAY " " * EXEC SQL INSERT INTO VTABLE * VALUES (10,11,12,13,15); CALL "SUB24" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO VTABLE * VALUES (100,111,1112,113,115); CALL "SUB25" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL DECLARE SUN1 CURSOR * FOR SELECT COL1,MAX(COL2) * FROM VTABLE * GROUP BY COL1 * HAVING MAX(COL2) > ANY (SELECT GRADE FROM STAFF) * AND MAX(COL2) < SOME (SELECT HOURS FROM WORKS) * ORDER BY COL1 END-EXEC * EXEC SQL OPEN SUN1; CALL "SUB26" USING SQLCODE MOVE SQLCODE TO SQL-COD DISPLAY " The correct answer is :" DISPLAY " 10, 20" DISPLAY " Your answer is :" MOVE 0 TO flag * EXEC SQL FETCH SUN1 INTO :int1,:int2; CALL "SUB27" USING SQLCODE int1 int2 MOVE SQLCODE TO SQL-COD DISPLAY " ", int1 ", ", int2 if (int1 = 10 AND int2 = 20) then MOVE flag TO flag else MOVE 1 TO flag END-IF * EXEC SQL CLOSE SUN1; CALL "SUB28" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL ROLLBACK WORK; CALL "SUB29" USING SQLCODE MOVE SQLCODE TO SQL-COD if (flag = 0) then DISPLAY " *** pass *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0259','pass','MCO'); CALL "SUB30" USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY " dml059.sco *** fail *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0259','fail','MCO'); CALL "SUB31" USING SQLCODE ADD 1 TO errcnt MOVE SQLCODE TO SQL-COD END-IF DISPLAY "===============================================" * EXEC SQL COMMIT WORK; CALL "SUB32" USING SQLCODE MOVE SQLCODE TO SQL-COD ******************** END TEST0259 ******************* ******************** BEGIN TEST0260 ******************* DISPLAY " EXISTS in HAVING Clause " DISPLAY "Reference X3.135-1989 section 5.8 General Rules " DISPLAY " ------------------------------------------ " DISPLAY " TEST0260 " DISPLAY " Reference 5.23 General Rules 3)" DISPLAY " - - - - - - - - - - - - - - - - - - - - - " DISPLAY " " DISPLAY " " DISPLAY " INSERT INTO VTABLE" DISPLAY " VALUES (10,11,12,13,15);" DISPLAY " " DISPLAY " INSERT INTO VTABLE" DISPLAY " VALUES (100,111,1112,113,115);" DISPLAY " " DISPLAY " DECLARE SUN2 CURSOR " DISPLAY " FOR SELECT COL1,MAX(COL2)" DISPLAY " FROM VTABLE" DISPLAY " GROUP BY COL1" DISPLAY " HAVING EXISTS (SELECT * FROM STAFF" DISPLAY " WHERE EMPNUM = 'E1') - " " DISPLAY " AND MAX(COL2) BETWEEN 10 AND - " 90" DISPLAY " ORDER BY COL1;" DISPLAY " " MOVE 0 TO int1 MOVE 0 TO int2 * EXEC SQL INSERT INTO VTABLE * VALUES (10,11,12,13,15); CALL "SUB33" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL INSERT INTO VTABLE * VALUES (100,111,1112,113,115); CALL "SUB34" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL DECLARE SUN2 CURSOR * FOR SELECT COL1,MAX(COL2) * FROM VTABLE * GROUP BY COL1 * HAVING EXISTS (SELECT * FROM STAFF * WHERE EMPNUM = 'E1') * AND MAX(COL2) BETWEEN 10 AND 90 * ORDER BY COL1 END-EXEC * EXEC SQL OPEN SUN2; CALL "SUB35" USING SQLCODE MOVE SQLCODE TO SQL-COD DISPLAY " The correct answer is :" DISPLAY " 10, 20" DISPLAY " Your answer is :" MOVE 0 TO flag * EXEC SQL FETCH SUN2 INTO :int1,:int2; CALL "SUB36" USING SQLCODE int1 int2 MOVE SQLCODE TO SQL-COD DISPLAY " ", int1 ", ", int2 if (int1 = 10 AND int2 = 20) then MOVE flag TO flag else MOVE 1 TO flag END-IF * EXEC SQL CLOSE SUN2; CALL "SUB37" USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL ROLLBACK WORK; CALL "SUB38" USING SQLCODE MOVE SQLCODE TO SQL-COD if (flag = 0) then DISPLAY " *** pass *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0260','pass','MCO'); CALL "SUB39" USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY " dml059.sco *** fail *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0260','fail','MCO'); CALL "SUB40" USING SQLCODE ADD 1 TO errcnt MOVE SQLCODE TO SQL-COD END-IF DISPLAY "===============================================" * EXEC SQL COMMIT WORK; CALL "SUB41" USING SQLCODE MOVE SQLCODE TO SQL-COD ******************** END TEST0260 ******************* ******************** BEGIN TEST0264 ******************* DISPLAY " - " clause>" DISPLAY "Reference X3.135-1989 section 5.8 General Rules " DISPLAY " ------------------------------------------ " DISPLAY " TEST0264 " DISPLAY " Reference 5.23 General Rules 1)" DISPLAY " - - - - - - - - - - - - - - - - - - - - - " DISPLAY " " DISPLAY " EXEC SQL DECLARE CURSOR FOR SELECT SUM(COL1)" DISPLAY " FROM VTABLE" DISPLAY " WHERE :int3 + COL1 > COL2" DISPLAY " HAVING MAX(COL1) > 100;" DISPLAY " " DISPLAY " " MOVE 0 TO int1 MOVE 10 TO int3 MOVE 0 TO flag * EXEC SQL DECLARE SUN3 CURSOR * FOR SELECT SUM(COL1) * FROM VTABLE * WHERE :int3 + COL1 > COL2 * HAVING MAX(COL1) > 100 END-EXEC MOVE SQLCODE TO SQL-COD * EXEC SQL OPEN SUN3; CALL "SUB42" USING SQLCODE INT3 MOVE SQLCODE TO SQL-COD * EXEC SQL FETCH SUN3 INTO :int1; CALL "SUB43" USING SQLCODE INT1 MOVE SQLCODE TO SQL-COD * EXEC SQL CLOSE SUN3; CALL "SUB44" USING SQLCODE MOVE SQLCODE TO SQL-COD DISPLAY " The correct answer is :" DISPLAY " 1000" DISPLAY " 1110" DISPLAY " Your answer is :" DISPLAY " ", int1 if (int1 = 1000) then MOVE flag TO flag else MOVE 1 TO flag END-IF MOVE 0 TO int1 MOVE 1000 TO int3 * EXEC SQL OPEN SUN3; CALL "SUB42" USING SQLCODE INT3 MOVE SQLCODE TO SQL-COD * EXEC SQL FETCH SUN3 INTO :int1; CALL "SUB46" USING SQLCODE INT1 MOVE SQLCODE TO SQL-COD DISPLAY " ", int1 if (int1 = 1110) then MOVE flag TO flag else MOVE 1 TO flag END-IF if (flag = 0) then DISPLAY " *** pass *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0264','pass','MCO'); CALL "SUB47" USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY " dml059.sco *** fail *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0264','fail','MCO'); CALL "SUB48" USING SQLCODE ADD 1 TO errcnt MOVE SQLCODE TO SQL-COD END-IF DISPLAY "===============================================" * EXEC SQL COMMIT WORK; CALL "SUB49" USING SQLCODE MOVE SQLCODE TO SQL-COD ******************** END TEST0264 ******************* **** TESTER MAY CHOOSE TO INSERT CODE FOR errcnt > 0 STOP RUN. * **** Procedures for PERFORM statements [ zur Elbe Produktseite wechseln0.52Quellennavigators Analyse erneut starten ] |