| 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: vectors-lib.pdf Sprache: Cobol |
|
||||||
|
IDENTIFICATION DIVISION.
PROGRAM-ID. DML001. ENVIRONMENT DIVISION. CONFIGURATION SECTION. SOURCE-COMPUTER. xyz. OBJECT-COMPUTER. xyz. DATA DIVISION. WORKING-STORAGE SECTION. * Standard COBOL (file "DML001.SCO") calling SQL * procedures in file "DML001.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. * * DML001.SCO * WRITTEN BY: HU YANPING * TRANSLATED AUTOMATICALLY FROM EMBEDDED COBOL BY CHRIS SCHANZLE * * THIS ROUTINE TESTS THE DECLARE CURSOR STATEMENT IN THE * LANGUGE OF SQL. THE FORMAT: * DECLARE <cursor name> CURSOR * FOR <cursor specification> * * REFERENCES * AMERICAN NATIONAL STANDARD database language - SQL * X3.135-1989 * * SECTION 8.3 <declare cursor> * **************************************************************** * EXEC SQL BEGIN DECLARE SECTION END-EXEC 01 PNO1 PIC X(3). 01 EMPNM1 PIC X(20). 01 EMPNO PIC X(3). 01 HOUR1 PIC S9(9) DISPLAY SIGN LEADING SEPARATE. 01 iii PIC S9(9) DISPLAY SIGN LEADING SEPARATE. * 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, dml001.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 TEST0001 ******************* MOVE 0 TO i DISPLAY " TEST0001 " DISPLAY "declare with ORDER BY < column specification > DESC - " " DISPLAY "reference X3.135-1989 section 8.3 General Rules 3) - " b)" DISPLAY " - - - - - - - - - - - - - - - - - - -" DISPLAY " *** FOR SELECT EMPNUM,HOURS " DISPLAY " *** FROM WORKS " DISPLAY " *** WHERE PNUM='P2' " DISPLAY " *** ORDER BY EMPNUM DESC " DISPLAY " " * EXEC SQL DECLARE X CURSOR * FOR SELECT EMPNUM,HOURS * FROM WORKS * WHERE PNUM='P2' * ORDER BY EMPNUM DESC END-EXEC * EXEC SQL OPEN X; CALL "SUB1" USING SQLCODE MOVE SQLCODE TO SQL-COD MOVE 1 TO ii PERFORM P50 UNTIL ii > 14 DISPLAY " EMPNO=", EMPNO ", i=", i " " DISPLAY "The answer should be EMPNO=E1, i=4 & " DISPLAY " order by EMPNO DESC. " if (EMPNO = "E1" AND i = 4) then DISPLAY " *** pass ***" DISPLAY "=====================================================" * EXEC SQL INSERT INTO TESTREPORT * VALUES('0001','pass','MCO') END-EXEC CALL "SUB2" USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY " dml001.sco *** fail ***" DISPLAY "=====================================================" * EXEC SQL INSERT INTO TESTREPORT * VALUES('0001','fail','MCO') END-EXEC ADD 1 TO errcnt CALL "SUB3" USING SQLCODE MOVE SQLCODE TO SQL-COD END-IF DISPLAY " " * EXEC SQL COMMIT WORK; CALL "SUB4" USING SQLCODE MOVE SQLCODE TO SQL-COD ******************** END TEST0001 ******************* ******************** BEGIN TEST0002 ******************* MOVE 0 TO i DISPLAY " TEST0002 " DISPLAY "declare with ORDER BY < unsigned integer > ASC " DISPLAY "reference X3.135-1989 section 8.3 General Rules 3) - " b)" DISPLAY " - - - - - - - - - - - - - - - - - - -" DISPLAY " *** FOR SELECT EMPNUM,HOURS " DISPLAY " *** FROM WORKS " DISPLAY " *** WHERE PNUM='P2' " DISPLAY " *** ORDER BY 2 ASC " DISPLAY " " * EXEC SQL DECLARE Y CURSOR * FOR SELECT EMPNUM,HOURS * FROM WORKS * WHERE PNUM='P2' * ORDER BY 2 ASC END-EXEC * EXEC SQL OPEN Y; CALL "SUB5" USING SQLCODE MOVE SQLCODE TO SQL-COD MOVE 1 TO ii PERFORM P49 UNTIL ii > 14 DISPLAY " i=", i " " DISPLAY "The answer should be i = 4 & " DISPLAY " order by HOUR1 ASC. " if (i = 4 AND HOUR1 = 80) then DISPLAY " *** pass ***" DISPLAY "=====================================================" * EXEC SQL INSERT INTO TESTREPORT * VALUES('0002','pass','MCO') END-EXEC CALL "SUB6" USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY " dml001.sco *** fail ***" DISPLAY "=====================================================" * EXEC SQL INSERT INTO TESTREPORT * VALUES('0002','fail','MCO') END-EXEC ADD 1 TO errcnt CALL "SUB7" USING SQLCODE MOVE SQLCODE TO SQL-COD END-IF DISPLAY " " * EXEC SQL COMMIT WORK; CALL "SUB8" USING SQLCODE MOVE SQLCODE TO SQL-COD ******************** END TEST0002 ******************* ******************** BEGIN TEST0003 ******************* MOVE 0 TO i DISPLAY " TEST0003 " DISPLAY "declare with ORDER BY < sort specification > ... " DISPLAY "reference X3.135-1989 section 8.3 General Rules 3) - " b)" DISPLAY " - - - - - - - - - - - - - - - - - - -" DISPLAY " *** FOR SELECT EMPNUM,HOURS " DISPLAY " *** FROM WORKS " DISPLAY " *** WHERE PNUM='P2' " DISPLAY " *** ORDER BY 2 DESC,EMPNUM DESC " DISPLAY " " * EXEC SQL DECLARE Z CURSOR * FOR SELECT EMPNUM,HOURS * FROM WORKS * WHERE PNUM='P2' * ORDER BY 2 DESC,EMPNUM DESC END-EXEC * EXEC SQL OPEN Z; CALL "SUB9" USING SQLCODE MOVE SQLCODE TO SQL-COD MOVE 1 TO ii PERFORM P48 UNTIL ii > 14 DISPLAY "The answer should be i = 4; i = ", i " & " DISPLAY " order by HOUR1 DESC, EMPNO DESC." if (i = 4 AND EMPNO = "E1") then DISPLAY " *** pass ***" DISPLAY "=====================================================" * EXEC SQL INSERT INTO TESTREPORT * VALUES('0003','pass','MCO') END-EXEC CALL "SUB10" USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY " dml001.sco *** fail ***" DISPLAY "=====================================================" * EXEC SQL INSERT INTO TESTREPORT * VALUES('0003','fail','MCO') END-EXEC ADD 1 TO errcnt CALL "SUB11" USING SQLCODE MOVE SQLCODE TO SQL-COD END-IF DISPLAY " " * EXEC SQL COMMIT WORK; CALL "SUB12" USING SQLCODE MOVE SQLCODE TO SQL-COD ******************** END TEST0003 ******************* ******************** BEGIN TEST0004 ******************* MOVE 0 TO i DISPLAY " TEST0004 " DISPLAY " declare with UNION " DISPLAY "reference X3.135-1989 section 8.3 General Rules 2)" DISPLAY " - - - - - - - - - - - - - - - - - - -" DISPLAY " *** FOR SELECT WORKS.EMPNUM " DISPLAY " *** FROM WORKS " DISPLAY " *** WHERE WORKS.PNUM='P2' " DISPLAY " *** UNION " DISPLAY " *** SELECT STAFF.EMPNUM " DISPLAY " *** FROM STAFF " DISPLAY " *** WHERE STAFF.GRADE=13 " DISPLAY " *** ORDER BY 1 DESC " DISPLAY " " * EXEC SQL DECLARE A CURSOR * FOR SELECT WORKS.EMPNUM * FROM WORKS * WHERE WORKS.PNUM='P2' * UNION * SELECT STAFF.EMPNUM * FROM STAFF * WHERE STAFF.GRADE=13 * ORDER BY 1 DESC END-EXEC * EXEC SQL OPEN A; CALL "SUB13" USING SQLCODE MOVE SQLCODE TO SQL-COD MOVE 1 TO ii PERFORM P47 UNTIL ii > 14 DISPLAY " i = ", i DISPLAY "The answer should be i = 5 & ORDER BY EMPNO DESC." if (i = 5 AND EMPNO = "E1") then DISPLAY " *** pass ***" DISPLAY "=====================================================" * EXEC SQL INSERT INTO TESTREPORT * VALUES('0004','pass','MCO') END-EXEC CALL "SUB14" USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY " dml001.sco *** fail ***" DISPLAY "=====================================================" * EXEC SQL INSERT INTO TESTREPORT * VALUES('0004','fail','MCO') END-EXEC ADD 1 TO errcnt CALL "SUB15" USING SQLCODE MOVE SQLCODE TO SQL-COD END-IF DISPLAY " " * EXEC SQL COMMIT WORK; CALL "SUB16" USING SQLCODE MOVE SQLCODE TO SQL-COD ******************** END TEST0004 ******************* ******************** BEGIN TEST0005 ******************* MOVE 0 TO i DISPLAY " TEST0005 " DISPLAY " declare with UNION ALL " DISPLAY "reference X3.135-1989 section 8.3 General Rules 2)" DISPLAY " - - - - - - - - - - - - - - - - - - -" DISPLAY " *** FOR SELECT WORKS.EMPNUM " DISPLAY " *** FROM WORKS " DISPLAY " *** WHERE WORKS.PNUM='P2' " DISPLAY " *** UNION ALL " DISPLAY " *** SELECT STAFF.EMPNUM " DISPLAY " *** FROM STAFF " DISPLAY " *** WHERE STAFF.GRADE=13 " DISPLAY " " * EXEC SQL DECLARE B CURSOR * FOR SELECT WORKS.EMPNUM * FROM WORKS * WHERE WORKS.PNUM='P2' * UNION ALL * SELECT STAFF.EMPNUM * FROM STAFF * WHERE STAFF.GRADE=13 END-EXEC * EXEC SQL OPEN B; CALL "SUB17" USING SQLCODE MOVE SQLCODE TO SQL-COD MOVE 1 TO ii PERFORM P46 UNTIL ii > 14 DISPLAY " The correct answer should be i=6,i=", i " &" DISPLAY "The correct answer should have some duplicate rows." if (i = 6) then DISPLAY " *** pass ***" DISPLAY "=====================================================" * EXEC SQL INSERT INTO TESTREPORT * VALUES('0005','pass','MCO') END-EXEC CALL "SUB18" USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY " dml001.sco *** fail ***" DISPLAY "=====================================================" * EXEC SQL INSERT INTO TESTREPORT * VALUES('0005','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 TEST0005 ******************* ******************** BEGIN TEST0158 ******************* MOVE 0 TO i DISPLAY " TEST0158 " DISPLAY " declare with UNION " DISPLAY "reference X3.135-1989 section 8.3 General Rules 2)" DISPLAY " - - - - - - - - - - - - - - - - - - -" DISPLAY " *** SELECT EMPNAME,PNUM,HOURS " DISPLAY " *** FROM STAFF,WORKS " DISPLAY " *** WHERE STAFF.EMPNUM = WORKS.EMPNUM " DISPLAY " *** UNION " DISPLAY " *** SELECT EMPNAME,PNUM,HOURS " DISPLAY " *** FROM STAFF,WORKS " DISPLAY " *** WHERE NOT EXISTS " DISPLAY " *** (SELECT HOURS " DISPLAY " *** FROM WORKS " DISPLAY " *** WHERE STAFF.EMPNUM = WORKS.EMPNUM)" DISPLAY " " * EXEC SQL DECLARE CC CURSOR * FOR SELECT EMPNAME,PNUM,HOURS * FROM STAFF,WORKS * WHERE STAFF.EMPNUM = WORKS.EMPNUM * UNION * SELECT EMPNAME,PNUM,HOURS * FROM STAFF,WORKS * WHERE NOT EXISTS * (SELECT HOURS * FROM WORKS * WHERE STAFF.EMPNUM = WORKS.EMPNUM) END-EXEC * EXEC SQL OPEN CC; CALL "SUB21" USING SQLCODE MOVE SQLCODE TO SQL-COD MOVE 1 TO ii PERFORM P45 UNTIL ii > 29 DISPLAY " The correct answer should be i=21; i=", i if (i = 21) then DISPLAY " *** pass ***" DISPLAY "=====================================================" * EXEC SQL INSERT INTO TESTREPORT * VALUES('0158','pass','MCO') END-EXEC CALL "SUB22" USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY " dml001.sco *** fail ***" DISPLAY "=====================================================" * EXEC SQL INSERT INTO TESTREPORT * VALUES('0158','fail','MCO') END-EXEC ADD 1 TO errcnt CALL "SUB23" USING SQLCODE MOVE SQLCODE TO SQL-COD END-IF DISPLAY " " * EXEC SQL COMMIT WORK; CALL "SUB24" USING SQLCODE MOVE SQLCODE TO SQL-COD ******************** END TEST0158 ******************* ******************** BEGIN TEST0159 ******************* MOVE 0 TO i DISPLAY " TEST0159 " DISPLAY " declare with UNION " DISPLAY "reference X3.135-1989 section 8.3 General Rules 2)" DISPLAY " - - - - - - - - - - - - - - - - - - -" DISPLAY " *** FOR SELECT PNUM,EMPNUM,HOURS " DISPLAY " *** FROM WORKS " DISPLAY " *** WHERE HOURS=80 " DISPLAY " *** UNION " DISPLAY " *** SELECT PNUM,EMPNUM,HOURS " DISPLAY " *** FROM WORKS " DISPLAY " *** WHERE HOURS=40 " DISPLAY " *** UNION " DISPLAY " *** SELECT PNUM, EMPNUM, HOURS " DISPLAY " *** FROM WORKS " DISPLAY " *** WHERE HOURS=20 " DISPLAY " *** ORDER BY 3,1 " * EXEC SQL DECLARE D CURSOR * FOR SELECT PNUM,EMPNUM,HOURS * FROM WORKS * WHERE HOURS=80 * UNION * SELECT PNUM,EMPNUM,HOURS * FROM WORKS * WHERE HOURS=40 * UNION * SELECT PNUM,EMPNUM,HOURS * FROM WORKS * WHERE HOURS=20 * ORDER BY 3,1 END-EXEC * EXEC SQL OPEN D; CALL "SUB25" USING SQLCODE MOVE SQLCODE TO SQL-COD MOVE 1 TO ii PERFORM P44 UNTIL ii > 39 DISPLAY " The correct answer should be i=10,i=", i " " if (i = 10) then DISPLAY " *** pass ***" DISPLAY "=====================================================" * EXEC SQL INSERT INTO TESTREPORT * VALUES('0159','pass','MCO') END-EXEC CALL "SUB26" USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY " dml001.sco *** fail ***" DISPLAY "=====================================================" * EXEC SQL INSERT INTO TESTREPORT * VALUES('0159','fail','MCO') END-EXEC ADD 1 TO errcnt CALL "SUB27" USING SQLCODE MOVE SQLCODE TO SQL-COD END-IF DISPLAY " " * EXEC SQL COMMIT WORK; CALL "SUB28" USING SQLCODE MOVE SQLCODE TO SQL-COD ******************** END TEST0159 ******************* ******************** BEGIN TEST0160 ******************* MOVE 0 TO i DISPLAY " TEST0160 " DISPLAY " declare with UNION " DISPLAY "reference X3.135-1989 section 8.3 General Rules 2)" DISPLAY " - - - - - - - - - - - - - - - - - - -" DISPLAY " *** FOR (SELECT PNUM,EMPNUM,HOURS " DISPLAY " *** FROM WORKS " DISPLAY " *** UNION " DISPLAY " *** SELECT PNUM,EMPNUM,HOURS " DISPLAY " *** FROM WORKS " DISPLAY " *** WHERE HOURS=80) " DISPLAY " *** UNION ALL " DISPLAY " *** SELECT PNUM,EMPNUM,HOURS " DISPLAY " *** FROM WORKS " DISPLAY " *** WHERE HOURS=12 " DISPLAY " *** ORDER BY 2,1 " * EXEC SQL DECLARE E CURSOR * FOR (SELECT PNUM,EMPNUM,HOURS * FROM WORKS * UNION * SELECT PNUM,EMPNUM,HOURS * FROM WORKS * WHERE HOURS=80) * UNION ALL * SELECT PNUM,EMPNUM,HOURS * FROM WORKS * WHERE HOURS=12 * ORDER BY 2,1 END-EXEC * EXEC SQL OPEN E; CALL "SUB29" USING SQLCODE MOVE SQLCODE TO SQL-COD MOVE 1 TO ii PERFORM P43 UNTIL ii > 39 DISPLAY " The correct answer should be i=14; i=", i " " if (i = 14) then DISPLAY " *** pass ***" DISPLAY "=====================================================" * EXEC SQL INSERT INTO TESTREPORT * VALUES('0160','pass','MCO') END-EXEC CALL "SUB30" USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY " dml001.sco *** fail ***" DISPLAY "=====================================================" * EXEC SQL INSERT INTO TESTREPORT * VALUES('0160','fail','MCO') END-EXEC ADD 1 TO errcnt CALL "SUB31" USING SQLCODE MOVE SQLCODE TO SQL-COD END-IF DISPLAY " " * EXEC SQL COMMIT WORK; CALL "SUB32" USING SQLCODE MOVE SQLCODE TO SQL-COD ******************** END TEST0160 ******************* **** TESTER MAY CHOOSE TO INSERT CODE FOR errcnt > 0 STOP RUN. * **** Procedures for PERFORM statements P50. * EXEC SQL FETCH X INTO :EMPNO,:HOUR1; CALL "SUB33" USING SQLCODE EMPNO HOUR1 MOVE SQLCODE TO SQL-COD if (SQLCODE = 0) then MOVE ii TO i DISPLAY " EMPNO=", EMPNO " and HOUR1=", HOUR1 " - " " END-IF ADD 1 TO ii . P49. * EXEC SQL FETCH Y INTO :EMPNO,:HOUR1; CALL "SUB34" USING SQLCODE EMPNO HOUR1 MOVE SQLCODE TO SQL-COD if (SQLCODE = 0) then MOVE ii TO i DISPLAY " EMPNO=", EMPNO " and HOUR1=", HOUR1 " " END-IF ADD 1 TO ii . P48. * EXEC SQL FETCH Z INTO :EMPNO,:HOUR1; CALL "SUB35" USING SQLCODE EMPNO HOUR1 MOVE SQLCODE TO SQL-COD if (SQLCODE = 0) then MOVE ii TO i DISPLAY " EMPNO=", EMPNO " and HOUR1=", HOUR1 " - " " END-IF ADD 1 TO ii . P47. * EXEC SQL FETCH A INTO :EMPNO; CALL "SUB36" USING SQLCODE EMPNO MOVE SQLCODE TO SQL-COD if (SQLCODE = 0) then MOVE ii TO i DISPLAY " EMPNO=", EMPNO " " END-IF ADD 1 TO ii . P46. * EXEC SQL FETCH B INTO :EMPNO; CALL "SUB37" USING SQLCODE EMPNO MOVE SQLCODE TO SQL-COD if (SQLCODE = 0) then MOVE ii TO i DISPLAY "EMPNO=", EMPNO " UNION ALL from WORKS and STAFF - " " END-IF ADD 1 TO ii . P45. * EXEC SQL FETCH CC INTO :EMPNM1,:PNO1,:HOUR1; CALL "SUB38" USING SQLCODE EMPNM1 PNO1 HOUR1 MOVE SQLCODE TO SQL-COD if (SQLCODE = 0) then MOVE ii TO i DISPLAY "empname=", EMPNM1 ";pnum=", PNO1 ",hours=", HOUR1 END-IF ADD 1 TO ii . P44. * EXEC SQL FETCH D INTO :PNO1,:EMPNO,:HOUR1; CALL "SUB39" USING SQLCODE PNO1 EMPNO HOUR1 MOVE SQLCODE TO SQL-COD if (SQLCODE = 0) then MOVE ii TO i DISPLAY "pnum=", PNO1 "; empnum=", EMPNO "; hours=", HOUR1 END-IF ADD 1 TO ii . P43. * EXEC SQL FETCH E INTO :PNO1,:EMPNO,:HOUR1; CALL "SUB40" USING SQLCODE PNO1 EMPNO HOUR1 MOVE SQLCODE TO SQL-COD if (SQLCODE = 0) then MOVE ii TO i DISPLAY " pnum=", PNO1 "; empnum=", EMPNO "; hours=", HOUR1 END-IF ADD 1 TO ii . ¤ Dauer der Verarbeitung: 0.30 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 |
