**************************************************************** * * COMMENT SECTION * * DATE 1987/08/21 EMBEDDED 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.PCO * WRITTEN BY: HU YANPING * TRANSLATED AUTOMATICALLY FROM EMBEDDED C 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> * ****************************************************************
MOVE"HU"TO uid CALL"AUTHID"USING uid MOVE"not logged in, not"TO uidx EXECSQLSELECT
USER INTO :uidx FROM HU.ECCO END-EXEC 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, Embedded COBOL, dml001.pco" 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 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" "
EXECSQL DECLARE X CURSOR FORSELECT EMPNUM,HOURS FROM WORKS
WHERE PNUM='P2' ORDERBY EMPNUM DESC END-EXEC EXECSQLOPEN X END-EXEC 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 "=====================================================" EXECSQLINSERTINTO TESTREPORT
VALUES('0001','pass','PCO') END-EXEC MOVE SQLCODE TO SQL-COD else DISPLAY" dml001.pco *** fail ***" DISPLAY "=====================================================" EXECSQLINSERTINTO TESTREPORT
VALUES('0001','fail','PCO') END-EXEC ADD 1 TO errcnt MOVE SQLCODE TO SQL-COD END-IF DISPLAY" " EXECSQL COMMIT WORK END-EXEC 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" "
EXECSQL DECLARE Y CURSOR FORSELECT EMPNUM,HOURS FROM WORKS
WHERE PNUM='P2' ORDERBY 2 ASC END-EXEC EXECSQLOPEN Y END-EXEC 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 "=====================================================" EXECSQLINSERTINTO TESTREPORT
VALUES('0002','pass','PCO') END-EXEC MOVE SQLCODE TO SQL-COD else DISPLAY" dml001.pco *** fail ***" DISPLAY "=====================================================" EXECSQLINSERTINTO TESTREPORT
VALUES('0002','fail','PCO') END-EXEC ADD 1 TO errcnt MOVE SQLCODE TO SQL-COD END-IF DISPLAY" " EXECSQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD
******************** END TEST0002 ******************* ******************** BEGIN TEST0003 *******************
DISPLAY" *** FOR SELECT EMPNUM,HOURS " DISPLAY" *** FROM WORKS " DISPLAY" *** WHERE PNUM='P2' " DISPLAY" *** ORDER BY 2 DESC,EMPNUM DESC " DISPLAY" "
EXECSQL DECLARE Z CURSOR FORSELECT EMPNUM,HOURS FROM WORKS
WHERE PNUM='P2' ORDERBY 2 DESC,EMPNUM DESC END-EXEC EXECSQLOPEN Z END-EXEC 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 "=====================================================" EXECSQLINSERTINTO TESTREPORT
VALUES('0003','pass','PCO') END-EXEC MOVE SQLCODE TO SQL-COD else DISPLAY" dml001.pco *** fail ***" DISPLAY "=====================================================" EXECSQLINSERTINTO TESTREPORT
VALUES('0003','fail','PCO') END-EXEC ADD 1 TO errcnt MOVE SQLCODE TO SQL-COD END-IF DISPLAY" " EXECSQL COMMIT WORK END-EXEC 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" "
EXECSQL DECLARE A CURSOR FORSELECT WORKS.EMPNUM FROM WORKS
WHERE WORKS.PNUM='P2'
UNION SELECT STAFF.EMPNUM FROM STAFF
WHERE STAFF.GRADE=13 ORDERBY 1 DESC END-EXEC EXECSQLOPEN A END-EXEC 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 "=====================================================" EXECSQLINSERTINTO TESTREPORT
VALUES('0004','pass','PCO') END-EXEC MOVE SQLCODE TO SQL-COD else DISPLAY" dml001.pco *** fail ***" DISPLAY "=====================================================" EXECSQLINSERTINTO TESTREPORT
VALUES('0004','fail','PCO') END-EXEC ADD 1 TO errcnt MOVE SQLCODE TO SQL-COD END-IF DISPLAY" " EXECSQL COMMIT WORK END-EXEC 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" "
EXECSQL DECLARE B CURSOR FORSELECT WORKS.EMPNUM FROM WORKS
WHERE WORKS.PNUM='P2'
UNION ALL SELECT STAFF.EMPNUM FROM STAFF
WHERE STAFF.GRADE=13 END-EXEC EXECSQLOPEN B END-EXEC 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 "=====================================================" EXECSQLINSERTINTO TESTREPORT
VALUES('0005','pass','PCO') END-EXEC MOVE SQLCODE TO SQL-COD else DISPLAY" dml001.pco *** fail ***" DISPLAY "=====================================================" EXECSQLINSERTINTO TESTREPORT
VALUES('0005','fail','PCO') END-EXEC ADD 1 TO errcnt MOVE SQLCODE TO SQL-COD END-IF DISPLAY" " EXECSQL COMMIT WORK END-EXEC 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" "
EXECSQL DECLARE CC CURSOR FORSELECT 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 EXECSQLOPEN CC END-EXEC 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 "=====================================================" EXECSQLINSERTINTO TESTREPORT
VALUES('0158','pass','PCO') END-EXEC MOVE SQLCODE TO SQL-COD else DISPLAY" dml001.pco *** fail ***" DISPLAY "=====================================================" EXECSQLINSERTINTO TESTREPORT
VALUES('0158','fail','PCO') END-EXEC ADD 1 TO errcnt MOVE SQLCODE TO SQL-COD END-IF DISPLAY" " EXECSQL COMMIT WORK END-EXEC 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 "
EXECSQL DECLARE D CURSOR FORSELECT 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 ORDERBY 3,1 END-EXEC EXECSQLOPEN D END-EXEC 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 "=====================================================" EXECSQLINSERTINTO TESTREPORT
VALUES('0159','pass','PCO') END-EXEC MOVE SQLCODE TO SQL-COD else DISPLAY" dml001.pco *** fail ***" DISPLAY "=====================================================" EXECSQLINSERTINTO TESTREPORT
VALUES('0159','fail','PCO') END-EXEC ADD 1 TO errcnt MOVE SQLCODE TO SQL-COD END-IF DISPLAY" "
EXECSQL COMMIT WORK END-EXEC 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 "
EXECSQL 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 ORDERBY 2,1 END-EXEC
EXECSQLOPEN E END-EXEC 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 "=====================================================" EXECSQLINSERTINTO TESTREPORT
VALUES('0160','pass','PCO') END-EXEC MOVE SQLCODE TO SQL-COD else DISPLAY" dml001.pco *** fail ***" DISPLAY "=====================================================" EXECSQLINSERTINTO TESTREPORT
VALUES('0160','fail','PCO') END-EXEC ADD 1 TO errcnt MOVE SQLCODE TO SQL-COD END-IF
DISPLAY" " EXECSQL COMMIT WORK END-EXEC MOVE SQLCODE TO SQL-COD
******************** END TEST0160 *******************
**** TESTER MAY CHOOSE TO INSERT CODE FOR errcnt > 0 STOPRUN.
* **** Procedures for PERFORM statements
P50. EXECSQL FETCH X INTO :EMPNO,:HOUR1 END-EXEC 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. EXECSQL FETCH Y INTO :EMPNO,:HOUR1 END-EXEC 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. EXECSQL FETCH Z INTO :EMPNO,:HOUR1 END-EXEC 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. EXECSQL FETCH A INTO :EMPNO END-EXEC MOVE SQLCODE TO SQL-COD if (SQLCODE = 0) then MOVE ii TO i DISPLAY" EMPNO=", EMPNO " " END-IF ADD 1 TO ii
.
P46. EXECSQL FETCH B INTO :EMPNO END-EXEC 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. EXECSQL FETCH CC INTO :EMPNM1,:PNO1,:HOUR1 END-EXEC 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. EXECSQL FETCH D INTO :PNO1,:EMPNO,:HOUR1 END-EXEC 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. EXECSQL FETCH E INTO :PNO1,:EMPNO,:HOUR1 END-EXEC 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
.
¤ Diese beiden folgenden Angebotsgruppen bietet das Unternehmen0.15Angebot
Wie Sie bei der Firma Beratungs- und Dienstleistungen beauftragen können
¤
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.