|
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
.
[ zur Elbe Produktseite wechseln0.35Quellennavigators
Analyse erneut starten
]
|
