* 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> * ****************************************************************
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" " STOPRUN 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 FROMDATE ACCEPT THE-TIME FROMTIME DISPLAY"Date run YYMMDD: " TO-DAY " at hhmmssff: " THE-TIME
******************** BEGIN TEST0096 *******************
DISPLAY" TEST0096 " DISPLAY" Reference ANSI X3.135-1989 5.24 GR 1) " 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
******************** END TEST0096 ******************* ******************** BEGIN TEST0097 *******************
DISPLAY" TEST0097 " DISPLAY" Reference ANSI X3.135-1989 5.24 GR 1) " 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
******************** END TEST0097 ******************* ******************** BEGIN TEST0098 *******************
DISPLAY" TEST0098 " DISPLAY" Reference ANSI X3.135-1989 5.24 GR 2) " 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" ***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
******************** END TEST0099 ******************* ******************** BEGIN TEST0100 *******************
DISPLAY" TEST0100 " DISPLAY" Reference ANSI X3.135-1989 5.24 GR 2) " 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
******************** END TEST0100 ******************* ******************** BEGIN TEST0101 *******************
DISPLAY" TEST0101 " DISPLAY" Reference ANSI X3.135-1989 5.24 GR 3) " 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
******************** END TEST0101 ******************* ******************** BEGIN TEST0102 *******************
DISPLAY" TEST0102 " DISPLAY" Reference ANSI X3.135-1989 5.24 GR 3) " 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
******************** END TEST0102 *******************
**** TESTER MAY CHOOSE TO INSERT CODE FOR errcnt > 0 STOPRUN.
* **** 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.14 Sekunden
(vorverarbeitet)
¤
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.