Quelle  P11.cob   Sprache: Cobol

      ******************************************************************
      ** Licensed Materials - Property of IBM
      **
      ** Governed under the terms of the International
      ** License Agreement for Non-Warranted Sample Code.
      **
      ** (C) COPYRIGHT International Business Machines Corp. 1995 - 2002
      ** All Rights Reserved.
      **
      ** US Government Users Restricted Rights - Use, duplication or
      ** disclosure restricted by GSA ADP Schedule Contract with IBM Cor
      ******************************************************************
      **
      ** SOURCE FILE NAME: tabsql.sqb
      **
      ** SAMPLE: Demonstrates common table expressions using SQL
      **
      **         This program demonstrates how to use common table
      **         expressions using the following advanced SQL statements
      **
      **   WITH
      **   PAYLEVEL AS (SELECT EMPNO, YEAR(HIREDATE) AS HIREYEAR, EDLEVE
      **      SALARY+BONUS+COMM AS TOTAL_PAY FROM EMPLOYEE
      **      WHERE EDLEVEL > 16),
      **   PAYBYED (EDUC_LEVEL, YEAR_OF_HIRE, AVG_TOTAL_PAY) AS
      **      (SELECT EDLEVEL, HIREYEAR, AVG(TOTAL_PAY) FROM PAYLEVEL
      **      GROUP BY EDLEVEL, HIREYEAR)
      **   SELECT EMPNO, EDLEVEL, YEAR_OF_HIRE, TOTAL_PAY, AVG_TOTAL_PAY
      **      FROM PAYLEVEL, PAYBYED
      **      WHERE EDLEVEL=EDUC_LEVEL
      **         AND HIREYEAR = YEAR_OF_HIRE
      **         AND TOTAL_PAY < AVG_TOTAL_PAY;
      **
      **            The second example is described in the "Nested Table
      **            Expressions" section of "Using SQL - Advanced".
      **   SELECT EDLEVEL, HIREYEAR, AVG(TOTAL_PAY)
      **      FROM (
      **         SELECT EMPNO, YEAR(HIREDATE) AS HIREYEAR, EDLEVEL,
      **            SALARY+BONUS+COMM AS TOTAL_PAY FROM EMPLOYEE
      **            WHERE EDLEVEL > 16
      **         ) AS PAY_LEVEL
      **      GROUP BY EDLEVEL, HIREYEAR
      **
      ** SQL STATEMENTS USED:
      **         BEGIN DECLARE SECTION
      **         END DECLARE SECTION
      **         CONNECT
      **         DECLARE
      **         FETCH
      **         OPEN
      **
      ** OUTPUT FILE: tabsql.out (available in the online documentation)
      ******************************************************************
      **
      ** For more information on the sample programs, see the README fil
      **
      ** For information on developing COBOL applications, see the
      ** Application Development Guide.
      **
      ** For information on using SQL statements, see the SQL Reference.
      **
      ** For the latest information on programming, compiling, and runni
      ** DB2 applications, visit the DB2 application development website
      **     http://www.software.ibm.com/data/db2/udb/ad
      ******************************************************************

       Identification Division.
       Program-ID. "tabsql".

       Data Division.
       Working-Storage Section.

           copy "sqlenv.cbl".
           copy "sql.cbl".
           copy "sqlca.cbl".

           EXEC SQL BEGIN DECLARE SECTION END-EXEC.
       01 userid            pic x(8).
       01 passwd.
         49 passwd-length   pic s9(4) comp-5 value 0.
         49 passwd-name     pic x(18).
       01 employee-empno    pic x(6).
       01 employee-edlevel  pic s9(4) comp-5.
       01 employee-hireyear pic s9(9) comp-5.
       01 employee-avg-tpay pic s9(11)v9(2) comp-3.
       01 employee-totpay   pic s9(11)v9(2) comp-3.
           EXEC SQL END DECLARE SECTION END-EXEC.

       77 disp-pay        pic z(11).99 usage display.
       77 disp-pay2       pic z(11).99 usage display.
       77 errloc          pic x(80).
       77 counter         pic s9(4) comp-5 value 0.

       Procedure Division.
       Main Section.
           display "Sample COBOL program: TABSQL".
           display " ".

      * Get database connection information.
           display "Enter your user id (default none): "
                with no advancing.
           accept userid.

           if userid = spaces
             EXEC SQL CONNECT TO sample END-EXEC
           else
             display "Enter your password : " with no advancing
             accept passwd-name.

      * Passwords in a CONNECT statement must be entered in a VARCHAR fo
      * with the length of the input string.
           inspect passwd-name tallying passwd-length for characters
              before initial " ".

           display " ".

           EXEC SQL CONNECT TO sample USER :userid USING :passwd
               END-EXEC.
           move "CONNECT TO" to errloc.
           call "checkerr" using SQLCA errloc.

      * COMMON TABLE EXPRESSION EXAMPLE
           display "COMMON TABLE EXPRESSIONS EXAMPLE".
           display "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^".

           EXEC SQL DECLARE c1 CURSOR FOR
                    WITH
                    PAYLEVEL AS (SELECT EMPNO, YEAR(HIREDATE) AS
                       HIREYEAR, EDLEVEL, (SALARY+BONUS+COMM) AS
                       TOTAL_PAY FROM EMPLOYEE
                       WHERE EDLEVEL > 6),
                    PAYBYED (EDUC_LEVEL, YEAR_OF_HIRE, AVG_TOTAL_PAY)
                       AS (SELECT EDLEVEL, HIREYEAR, AVG(TOTAL_PAY)
                       FROM PAYLEVEL GROUP BY EDLEVEL, HIREYEAR)
                    SELECT EMPNO, EDLEVEL, YEAR_OF_HIRE, TOTAL_PAY,
                       AVG_TOTAL_PAY FROM PAYLEVEL, PAYBYED
                       WHERE EDLEVEL=EDUC_LEVEL
                          AND HIREYEAR = YEAR_OF_HIRE
                          AND TOTAL_PAY < AVG_TOTAL_PAY END-EXEC.

           EXEC SQL OPEN c1 END-EXEC.
           move "OPEN c1" to errloc.
           call "checkerr" using SQLCA errloc.

           display "EMPNO EDLEVEL YEAR_OF_HIRE TOTAL_PAY AVG_TOTAL_P
      -       "AY".
      * FETCH the rows for the COMMON TABLE EXPRESSIONS select statement
           perform Common-Fetch-Loop thru End-Common-Fetch
              until SQLCODE not equal 0.

           display " ", counter, " record(s) selected".
           move 0 to counter.
           display " ".

      * NESTED TABLE EXPRESSIONS EXAMPLE
           display "NESTED TABLE EXPRESSIONS EXAMPLES".
           display "^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^".
           display "QUESTION: What is the average total pay, by educatio
      -            "n level".
           display " and year of hire, for those with an educat
      -            "ion".
           display " level greater than 16?".
           display "ANSWER:".

           EXEC SQL DECLARE c2 CURSOR FOR
                    SELECT EDLEVEL, HIREYEAR, AVG(TOTAL_PAY) AS
                       AVG_TOTAL_PAY FROM
                       (SELECT EMPNO, YEAR(HIREDATE) AS HIREYEAR,
                          EDLEVEL, SALARY+BONUS+COMM AS TOTAL_PAY FROM
                          EMPLOYEE WHERE EDLEVEL > 16)
                       AS PAY_LEVEL
                    GROUP BY EDLEVEL, HIREYEAR END-EXEC.

           EXEC SQL OPEN c2 END-EXEC.
           move "OPEN c2" to errloc.
           call "checkerr" using SQLCA errloc.

           display "EDLEVEL HIREYEAR AVG_TOTAL_PAY".
      * FETCH the rows for the NESTED TABLE EXPRESSIONS select statement
           perform Nested-Fetch-Loop thru End-Nested-Fetch
              until SQLCODE not equal 0.

           display " ", counter, " record(s) selected".

           EXEC SQL CONNECT RESET END-EXEC.
           move "CONNECT RESET" to errloc.
           call "checkerr" using SQLCA errloc.
       End-Main.
           go to End-Prog.

       Common-Fetch-Loop Section.
           EXEC SQL FETCH c1 INTO :employee-empno,
              :employee-edlevel, :employee-hireyear,
              :employee-totpay, :employee-avg-tpay END-EXEC.

           move employee-totpay to disp-pay.
           move employee-avg-tpay to disp-pay2.
           if SQLCODE not equal 0
              go to End-Common-Fetch.
           display employee-empno, " ", employee-edlevel, " ",
              employee-hireyear, " ", disp-pay, disp-pay2.

           add 1 to counter.
       End-Common-Fetch. exit.

       Nested-Fetch-Loop Section.
           EXEC SQL FETCH c2 INTO :employee-edlevel, :employee-hireyear,
              :employee-avg-tpay END-EXEC.

           move employee-avg-tpay to disp-pay.
           if SQLCODE not equal 0
              go to End-Nested-Fetch.
           display employee-edlevel, " ", employee-hireyear, " ",
              disp-pay.

           add 1 to counter.
       End-Nested-Fetch. exit.

       End-Prog.
           stop run.