| 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: dml022.cob Sprache: Cobol |
|
||||||
|
IDENTIFICATION DIVISION.
PROGRAM-ID. DML022. ENVIRONMENT DIVISION. CONFIGURATION SECTION. SOURCE-COMPUTER. xyz. OBJECT-COMPUTER. xyz. DATA DIVISION. WORKING-STORAGE SECTION. * Standard COBOL (file "DML022.SCO") calling SQL * procedures in file "DML022.MCO" **************************************************************** * * COMMENT SECTION * * DATE 1987/08/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. * * DML022.SCO * WRITTEN BY: HU YANPING * TRANSLATED AUTOMATICALLY FROM EMBEDDED COBOL BY CHRIS SCHANZLE * * THIS ROUTINE TESTS THE SUBQUERY OF THE SQL LANGUAGE. * * REFERENCES * AMERICAN NATIONAL STANDARD database language - SQL * X3.135-1989 * * SECTION 5.24 <subquery> * **************************************************************** * EXEC SQL BEGIN DECLARE SECTION END-EXEC 01 EMPNO1 PIC X(3). 01 EMPNA1 PIC X(20). 01 GRADE1 PIC S9(4) DISPLAY SIGN LEADING SEPARATE. 01 SCITY PIC X(15). 01 WEMP PIC X(3). 01 WPNUM PIC X(3). * EXEC SQL END DECLARE SECTION END-EXEC 01 i PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 ii PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 uid PIC X(18). 01 uidx PIC X(18). 01 SQL-COD PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 SQLCODE PIC S9(9) COMP. 01 errcnt PIC S9(4) 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, dml022.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 TEST0096 ******************* DISPLAY " TEST0096 " DISPLAY " Reference ANSI X3.135-1989 5.24 DISPLAY " - - - - - - - - - - - - - - - - - - -" DISPLAY " *** Use of function in subquery. " DISPLAY " *** SELECT EMPNUM " DISPLAY " *** FROM STAFF " DISPLAY " *** WHERE GRADE < " DISPLAY " *** (SELECT MAX(GRADE) " DISPLAY " *** FROM STAFF) " * EXEC SQL DECLARE S5 CURSOR FOR * SELECT EMPNUM * FROM STAFF * WHERE GRADE < * (SELECT MAX(GRADE) * FROM STAFF) END-EXEC * EXEC SQL OPEN S5; CALL "SUB1" USING SQLCODE MOVE SQLCODE TO SQL-COD MOVE 0 TO i if (SQLCODE = 0) then DISPLAY " EMPNUM " END-IF MOVE 1 TO ii PERFORM P50 UNTIL ii > 19 * EXEC SQL CLOSE S5; CALL "SUB2" USING SQLCODE MOVE SQLCODE TO SQL-COD DISPLAY " Here i should be 3; i=", i " " if (i = 3) then DISPLAY " *** pass *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0096','pass','MCO') END-EXEC CALL "SUB3" USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY " dml022.sco *** fail *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0096','fail','MCO') END-EXEC ADD 1 TO errcnt CALL "SUB4" USING SQLCODE MOVE SQLCODE TO SQL-COD END-IF DISPLAY "====================================================" * EXEC SQL COMMIT WORK; CALL "SUB5" USING SQLCODE MOVE SQLCODE TO SQL-COD ******************** END TEST0096 ******************* ******************** BEGIN TEST0097 ******************* DISPLAY " TEST0097 " DISPLAY " Reference ANSI X3.135-1989 5.24 DISPLAY " - - - - - - - - - - - - - - - - - - -" DISPLAY " *** SELECT * " DISPLAY " *** FROM STAFF " DISPLAY " *** WHERE GRADE <= " DISPLAY " *** (SELECT AVG(GRADE)-1 " DISPLAY " *** FROM STAFF) " * EXEC SQL DECLARE S6 CURSOR FOR * SELECT * * FROM STAFF * WHERE GRADE <= * (SELECT AVG(GRADE)-1 * FROM STAFF) END-EXEC * EXEC SQL OPEN S6; CALL "SUB6" USING SQLCODE MOVE SQLCODE TO SQL-COD MOVE 0 TO i if (SQLCODE = 0) then DISPLAY "EMPNUM SNAME GRADE CITY " END-IF MOVE 1 TO ii PERFORM P49 UNTIL ii > 19 * EXEC SQL CLOSE S6; CALL "SUB7" USING SQLCODE MOVE SQLCODE TO SQL-COD DISPLAY " Here i should be 1; i=", i " " if (i = 1) then DISPLAY " *** pass *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0097','pass','MCO') END-EXEC CALL "SUB8" USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY " dml022.sco *** fail *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0097','fail','MCO') END-EXEC ADD 1 TO errcnt CALL "SUB9" USING SQLCODE MOVE SQLCODE TO SQL-COD END-IF DISPLAY "====================================================" * EXEC SQL COMMIT WORK; CALL "SUB10" USING SQLCODE MOVE SQLCODE TO SQL-COD ******************** END TEST0097 ******************* ******************** BEGIN TEST0098 ******************* DISPLAY " TEST0098 " DISPLAY " Reference ANSI X3.135-1989 5.24 DISPLAY " - - - - - - - - - - - - - - - - - - -" DISPLAY " *** Simple subquery. " DISPLAY " *** SELECT EMPNAME " DISPLAY " *** FROM STAFF " DISPLAY " *** WHERE EMPNUM IN " DISPLAY " *** (SELECT EMPNUM " DISPLAY " *** FROM WORKS " DISPLAY " *** WHERE PNUM = 'P2') " * EXEC SQL DECLARE S1 CURSOR FOR * SELECT EMPNAME * FROM STAFF * WHERE EMPNUM IN * (SELECT EMPNUM * FROM WORKS * WHERE PNUM = 'P2') END-EXEC * EXEC SQL OPEN S1; CALL "SUB11" USING SQLCODE MOVE SQLCODE TO SQL-COD MOVE 0 TO i if (SQLCODE = 0) then DISPLAY "EMPNAME " END-IF MOVE 1 TO ii PERFORM P48 UNTIL ii > 19 * EXEC SQL CLOSE S1; CALL "SUB12" USING SQLCODE MOVE SQLCODE TO SQL-COD DISPLAY " Here i should be 4; i=", i " " if (i = 4) then DISPLAY " *** pass *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0098','pass','MCO') END-EXEC CALL "SUB13" USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY " dml022.sco *** fail *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0098','fail','MCO') END-EXEC ADD 1 TO errcnt CALL "SUB14" USING SQLCODE MOVE SQLCODE TO SQL-COD END-IF DISPLAY "====================================================" * EXEC SQL COMMIT WORK; CALL "SUB15" USING SQLCODE MOVE SQLCODE TO SQL-COD ******************** END TEST0098 ******************* ******************** BEGIN TEST0099 ******************* DISPLAY " TEST0099 " DISPLAY " Reference ANSI X3.135-1989 5.24 DISPLAY " - - - - - - - - - - - - - - - - - - -" DISPLAY " ***Subquery with multiple levels of - " nesting. " DISPLAY " *** SELECT EMPNAME " DISPLAY " *** FROM STAFF " DISPLAY " *** WHERE EMPNUM IN " DISPLAY " *** (SELECT EMPNUM " DISPLAY " *** FROM WORKS " DISPLAY " *** WHERE PNUM IN " DISPLAY " *** (SELECT PNUM " DISPLAY " *** FROM PROJ " DISPLAY " *** WHERE PTYPE = 'Design')) " * EXEC SQL DECLARE S2 CURSOR FOR * SELECT EMPNAME * FROM STAFF * WHERE EMPNUM IN * (SELECT EMPNUM * FROM WORKS * WHERE PNUM IN * (SELECT PNUM * FROM PROJ * WHERE PTYPE = 'Design')) END-EXEC * EXEC SQL OPEN S2; CALL "SUB16" USING SQLCODE MOVE SQLCODE TO SQL-COD MOVE 0 TO i if (SQLCODE = 0) then DISPLAY "EMPNAME " END-IF MOVE 1 TO ii PERFORM P47 UNTIL ii > 19 * EXEC SQL CLOSE S2; CALL "SUB17" USING SQLCODE MOVE SQLCODE TO SQL-COD DISPLAY " Here i should be 3; i=", i " " if (i = 3) then DISPLAY " *** pass *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0099','pass','MCO') END-EXEC CALL "SUB18" USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY " dml022.sco *** fail *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0099','fail','MCO') END-EXEC ADD 1 TO errcnt CALL "SUB19" USING SQLCODE MOVE SQLCODE TO SQL-COD END-IF DISPLAY "====================================================" * EXEC SQL COMMIT WORK; CALL "SUB20" USING SQLCODE MOVE SQLCODE TO SQL-COD ******************** END TEST0099 ******************* ******************** BEGIN TEST0100 ******************* DISPLAY " TEST0100 " DISPLAY " Reference ANSI X3.135-1989 5.24 DISPLAY " - - - - - - - - - - - - - - - - - - -" DISPLAY "*** SELECT EMPNUM, EMPNAME " DISPLAY "*** FROM STAFF " DISPLAY "*** WHERE EMPNUM IN " DISPLAY "*** (SELECT EMPNUM " DISPLAY "*** FROM WORKS " DISPLAY "*** WHERE PNUM IN " DISPLAY "*** (SELECT PNUM " DISPLAY "*** FROM PROJ " DISPLAY "*** WHERE PTYPE IN " DISPLAY "*** (SELECT PTYPE " DISPLAY "*** FROM PROJ " DISPLAY "*** WHERE PNUM IN " DISPLAY "*** (SELECT PNUM " DISPLAY "*** FROM WORKS " DISPLAY "*** WHERE EMPNUM IN " DISPLAY "*** (SELECT EMPNUM " DISPLAY "*** FROM WORKS " DISPLAY "*** WHERE PNUM IN " DISPLAY "*** (SELECT PNUM " DISPLAY "*** FROM PROJ " DISPLAY "*** WHERE PTYPE = - " 'Design')))))) " * EXEC SQL DECLARE S3 CURSOR FOR * SELECT EMPNUM, EMPNAME * FROM STAFF * WHERE EMPNUM IN * (SELECT EMPNUM * FROM WORKS * WHERE PNUM IN * (SELECT PNUM * FROM PROJ * WHERE PTYPE IN * (SELECT PTYPE * FROM PROJ * WHERE PNUM IN * (SELECT PNUM * FROM WORKS * WHERE EMPNUM IN * (SELECT EMPNUM * FROM WORKS * WHERE PNUM IN * (SELECT PNUM * FROM PROJ * WHERE PTYPE = 'Design')))))) END-EXEC * EXEC SQL OPEN S3; CALL "SUB21" USING SQLCODE MOVE SQLCODE TO SQL-COD MOVE 0 TO i if (SQLCODE = 0) then DISPLAY "EMPNUM EMPNAME " END-IF MOVE 1 TO ii PERFORM P46 UNTIL ii > 19 * EXEC SQL CLOSE S3; CALL "SUB22" USING SQLCODE MOVE SQLCODE TO SQL-COD DISPLAY " Here i should be 4; i=", i " " if (i = 4) then DISPLAY " *** pass *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0100','pass','MCO') END-EXEC CALL "SUB23" USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY " dml022.sco *** fail *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0100','fail','MCO') END-EXEC ADD 1 TO errcnt CALL "SUB24" USING SQLCODE MOVE SQLCODE TO SQL-COD END-IF DISPLAY "====================================================" * EXEC SQL COMMIT WORK; CALL "SUB25" USING SQLCODE MOVE SQLCODE TO SQL-COD ******************** END TEST0100 ******************* ******************** BEGIN TEST0101 ******************* DISPLAY " TEST0101 " DISPLAY " Reference ANSI X3.135-1989 5.24 DISPLAY " - - - - - - - - - - - - - - - - - - -" DISPLAY " *** use of GROUP BY in subquery. " DISPLAY " *** SELECT EMPNUM,PNUM " DISPLAY " *** FROM WORKS " DISPLAY " *** WHERE HOURS <= ALL " DISPLAY " *** (SELECT AVG(HOURS) " DISPLAY " *** FROM WORKS " DISPLAY " *** GROUP BY PNUM) " * EXEC SQL DECLARE S22 CURSOR FOR * SELECT EMPNUM,PNUM * FROM WORKS * WHERE HOURS <= ALL * (SELECT AVG(HOURS) * FROM WORKS * GROUP BY PNUM) END-EXEC * EXEC SQL OPEN S22; CALL "SUB26" USING SQLCODE MOVE SQLCODE TO SQL-COD MOVE 0 TO i if (SQLCODE = 0) then DISPLAY " EMPNUM PNUM " END-IF MOVE 1 TO ii PERFORM P45 UNTIL ii > 19 * EXEC SQL CLOSE S22; CALL "SUB27" USING SQLCODE MOVE SQLCODE TO SQL-COD DISPLAY " Here i should be 2; i=", i " " if (i = 2) then DISPLAY " *** pass *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0101','pass','MCO') END-EXEC CALL "SUB28" USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY " dml022.sco *** fail *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0101','fail','MCO') END-EXEC ADD 1 TO errcnt CALL "SUB29" USING SQLCODE MOVE SQLCODE TO SQL-COD END-IF DISPLAY "====================================================" * EXEC SQL COMMIT WORK; CALL "SUB30" USING SQLCODE MOVE SQLCODE TO SQL-COD ******************** END TEST0101 ******************* ******************** BEGIN TEST0102 ******************* DISPLAY " TEST0102 " DISPLAY " Reference ANSI X3.135-1989 5.24 DISPLAY " - - - - - - - - - - - - - - - - - - -" DISPLAY " *** SELECT DISTINCT EMPNUM " DISPLAY " *** FROM WORKS WORKSX " DISPLAY " *** WHERE NOT EXISTS " DISPLAY " *** (SELECT * " DISPLAY " *** FROM WORKS WORKSY " DISPLAY " *** WHERE EMPNUM = 'E2' " DISPLAY " *** AND NOT EXISTS " DISPLAY " *** (SELECT * " DISPLAY " *** FROM WORKS WORKSZ " DISPLAY " *** WHERE WORKSZ.EMPNUM = - " WORKSX.EMPNUM " DISPLAY " *** AND WORKSZ.PNUM = - " WORKSY.PNUM)) " * EXEC SQL DECLARE S11 CURSOR FOR * SELECT DISTINCT EMPNUM * FROM WORKS WORKSX * WHERE NOT EXISTS * (SELECT * * FROM WORKS WORKSY * WHERE EMPNUM = 'E2' * AND NOT EXISTS * (SELECT * * FROM WORKS WORKSZ * WHERE WORKSZ.EMPNUM = WORKSX.EMPNUM * AND WORKSZ.PNUM = WORKSY.PNUM)) END-EXEC * EXEC SQL OPEN S11; CALL "SUB31" USING SQLCODE MOVE SQLCODE TO SQL-COD MOVE 0 TO i if (SQLCODE = 0) then DISPLAY "EMPNUM " END-IF MOVE 1 TO ii PERFORM P44 UNTIL ii > 19 * EXEC SQL CLOSE S11; CALL "SUB32" USING SQLCODE MOVE SQLCODE TO SQL-COD DISPLAY " Here i should be 2; i=", i " " if (i = 2) then DISPLAY " *** pass *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0102','pass','MCO') END-EXEC CALL "SUB33" USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY " dml022.sco *** fail *** " * EXEC SQL INSERT INTO TESTREPORT * VALUES('0102','fail','MCO') END-EXEC ADD 1 TO errcnt CALL "SUB34" USING SQLCODE MOVE SQLCODE TO SQL-COD END-IF DISPLAY "====================================================" * EXEC SQL COMMIT WORK; CALL "SUB35" USING SQLCODE MOVE SQLCODE TO SQL-COD ******************** END TEST0102 ******************* **** TESTER MAY CHOOSE TO INSERT CODE FOR errcnt > 0 STOP RUN. * **** Procedures for PERFORM statements P50. * EXEC SQL FETCH S5 INTO * :EMPNO1 END-EXEC CALL "SUB36" USING SQLCODE EMPNO1 MOVE SQLCODE TO SQL-COD if (SQLCODE = 0) then DISPLAY " ", EMPNO1 COMPUTE i = i + 1 END-IF ADD 1 TO ii . P49. * EXEC SQL FETCH S6 INTO * :EMPNO1,:EMPNA1,:GRADE1,:SCITY END-EXEC CALL "SUB37" USING SQLCODE EMPNO1 EMPNA1 GRADE1 SCITY MOVE SQLCODE TO SQL-COD if (SQLCODE = 0) then DISPLAY EMPNO1 " ", EMPNA1 " ", GRADE1 " ", SCITY COMPUTE i = i + 1 END-IF ADD 1 TO ii . P48. * EXEC SQL FETCH S1 INTO * :EMPNA1 END-EXEC CALL "SUB38" USING SQLCODE EMPNA1 MOVE SQLCODE TO SQL-COD if (SQLCODE = 0) then DISPLAY EMPNA1 COMPUTE i = i + 1 END-IF ADD 1 TO ii . P47. * EXEC SQL FETCH S2 INTO * :EMPNA1 END-EXEC CALL "SUB39" USING SQLCODE EMPNA1 MOVE SQLCODE TO SQL-COD if (SQLCODE = 0) then DISPLAY EMPNA1 COMPUTE i = i + 1 END-IF ADD 1 TO ii . P46. * EXEC SQL FETCH S3 INTO * :EMPNO1,:EMPNA1 END-EXEC CALL "SUB40" USING SQLCODE EMPNO1 EMPNA1 MOVE SQLCODE TO SQL-COD if (SQLCODE = 0) then DISPLAY EMPNO1 " ", EMPNA1 COMPUTE i = i + 1 END-IF ADD 1 TO ii . P45. * EXEC SQL FETCH S22 INTO :WEMP,:WPNUM; CALL "SUB41" USING SQLCODE WEMP WPNUM MOVE SQLCODE TO SQL-COD if (SQLCODE = 0) then DISPLAY " ", WEMP " ", WPNUM COMPUTE i = i + 1 END-IF ADD 1 TO ii . P44. * EXEC SQL FETCH S11 INTO * :EMPNO1 END-EXEC CALL "SUB42" USING SQLCODE EMPNO1 MOVE SQLCODE TO SQL-COD if (SQLCODE = 0) then DISPLAY EMPNO1 COMPUTE i = i + 1 END-IF ADD 1 TO ii . ¤ Dauer der Verarbeitung: 0.27 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 |
