COBOL - 数据库接口

  • 简述

    至此,我们已经了解了 COBOL 中文件的使用。现在,我们将讨论 COBOL 程序如何与 DB2 交互。它涉及以下条款 -
    • 嵌入式 SQL
    • DB2 应用程序
    • 宿主变量
    • SQLCA
    • SQL Queries
    • Cursors
  • 嵌入式 SQL

    嵌入式 SQL 语句在 COBOL 程序中用于执行标准 SQL 操作。嵌入式 SQL 语句在应用程序编译之前由 SQL 处理器进行预处理。COBOL 被称为Host Language. COBOL-DB2 应用程序是那些同时包含 COBOL 和 DB2 的应用程序。
    嵌入式 SQL 语句的工作方式与普通 SQL 语句类似,但有一些细微的变化。例如,查询的输出指向一组预定义的变量,这些变量称为Host Variables. 在 SELECT 语句中放置了一个额外的 INTO 子句。
  • DB2 应用程序编程

    以下是编写 COBOL-DB2 程序时要遵循的规则 -
    • 所有 SQL 语句必须在EXEC SQLENDEXEC..
    • SQL 语句必须在区域 B 中编码。
    • 程序中使用的所有表都必须在 WorkingStorage 部分中声明。这是通过使用INCLUDE陈述。
    • 除 INCLUDE 和 DECLARE TABLE 之外的所有 SQL 语句都必须出现在 Procedure Division 中。
  • 宿主变量

    宿主变量用于从表中接收数据或在表中插入数据。必须为要在程序和 DB2 之间传递的所有值声明主变量。它们在工作存储部分中声明。
    宿主变量不能是组项,但它们可以在宿主结构中组合在一起。他们不能Renamed或者Redefined. 在 SQL 语句中使用主机变量,在它们前面加上前缀colon (:)..

    句法

    以下是声明主机变量并在工作存储部分中包含表的语法 -
    
    DATA DIVISION.
       WORKING-STORAGE SECTION.
       
       EXEC SQL
       INCLUDE table-name
       END-EXEC.
       EXEC SQL BEGIN DECLARE SECTION
       END-EXEC.
       
       01 STUDENT-REC.
          05 STUDENT-ID PIC 9(4).
          05 STUDENT-NAME PIC X(25).
          05 STUDENT-ADDRESS X(50).
       EXEC SQL END DECLARE SECTION
       END-EXEC.
    
  • SQLCA

    SQLCA 是一个 SQL 通信区域,DB2 通过它将 SQL 执行的反馈传递给程序。它告诉程序执行是否成功。SQLCA 下有许多预定义的变量,例如SQLCODE其中包含错误代码。SQLCODE 中的值“000”表示执行成功。

    句法

    以下是在工作存储部分中声明 SQLCA 的语法 -
    
    DATA DIVISION.
    WORKING-STORAGE SECTION.
       EXEC SQL
       INCLUDE SQLCA
       END-EXEC.
    
  • SQL 查询

    假设我们有一个名为“Student”的表,其中包含 Student-Id、Student-Name 和 Student-Address。
    STUDENT 表包含以下数据 -
    
    Student Id     Student Name      Student Address
    1001           Mohtashim M.      Hyderabad
    1002           Nishant Malik     Delhi
    1003           Amitabh Bachan    Mumbai
    1004           Chulbul Pandey    Lucknow
    
    下面的例子展示了SELECT在 COBOL 程序中查询 -
    
    IDENTIFICATION DIVISION.
    PROGRAM-ID. HELLO.
    DATA DIVISION.
       WORKING-STORAGE SECTION.
       EXEC SQL
          INCLUDE SQLCA
       END-EXEC.
       
       EXEC SQL
          INCLUDE STUDENT
       END-EXEC.
       
       EXEC SQL BEGIN DECLARE SECTION
       END-EXEC.
          01 WS-STUDENT-REC.
             05 WS-STUDENT-ID PIC 9(4).
             05 WS-STUDENT-NAME PIC X(25).
             05 WS-STUDENT-ADDRESS X(50).
       EXEC SQL END DECLARE SECTION
       END-EXEC.
    PROCEDURE DIVISION.
       EXEC SQL
          SELECT STUDENT-ID, STUDENT-NAME, STUDENT-ADDRESS
          INTO :WS-STUDENT-ID, :WS-STUDENT-NAME, WS-STUDENT-ADDRESS FROM STUDENT
          WHERE STUDENT-ID=1004
       END-EXEC.
       
       IF SQLCODE = 0 
          DISPLAY WS-STUDENT-RECORD
       ELSE DISPLAY 'Error'
       END-IF.
    STOP RUN.
    
    JCL执行上述 COBOL 程序 -
    
    //SAMPLE JOB(TESTJCL,XXXXXX),CLASS = A,MSGCLASS = C
    //STEP001  EXEC PGM = IKJEFT01
    //STEPLIB  DD DSN = MYDATA.URMI.DBRMLIB,DISP = SHR
    //SYSPRINT DD SYSOUT=*
    //SYSUDUMP DD SYSOUT=*
    //SYSOUT   DD SYSOUT=*
    //SYSTSIN  DD *
       DSN SYSTEM(SSID)
       RUN PROGRAM(HELLO) PLAN(PLANNAME) -
       END
    /*
    
    当您编译并执行上述程序时,它会产生以下结果 -
    
    1004 Chulbul Pandey     Lucknow
    
    下面的例子展示了INSERT在 COBOL 程序中查询 -
    
    IDENTIFICATION DIVISION.
    PROGRAM-ID. HELLO.
    DATA DIVISION.
       WORKING-STORAGE SECTION.
       EXEC SQL
       INCLUDE SQLCA
       END-EXEC.
       
       EXEC SQL
       INCLUDE STUDENT
       END-EXEC.
       
       EXEC SQL BEGIN DECLARE SECTION
       END-EXEC.
          01 WS-STUDENT-REC.
             05 WS-STUDENT-ID PIC 9(4).
             05 WS-STUDENT-NAME PIC X(25).
             05 WS-STUDENT-ADDRESS X(50).
       EXEC SQL END DECLARE SECTION
       END-EXEC.
    PROCEDURE DIVISION.
       MOVE 1005 TO WS-STUDENT-ID.
       MOVE 'TutorialsPoint' TO WS-STUDENT-NAME.
       MOVE 'Hyderabad' TO WS-STUDENT-ADDRESS.
       
       EXEC SQL
          INSERT INTO STUDENT(STUDENT-ID, STUDENT-NAME, STUDENT-ADDRESS)
          VALUES (:WS-STUDENT-ID, :WS-STUDENT-NAME, WS-STUDENT-ADDRESS)
       END-EXEC.
       
       IF SQLCODE = 0 
          DISPLAY 'Record Inserted Successfully'
          DISPLAY WS-STUDENT-REC
       ELSE DISPLAY 'Error'
       END-IF.
    STOP RUN.
    
    JCL执行上述 COBOL 程序 -
    
    //SAMPLE JOB(TESTJCL,XXXXXX),CLASS = A,MSGCLASS = C
    //STEP001  EXEC PGM = IKJEFT01
    //STEPLIB  DD DSN = MYDATA.URMI.DBRMLIB,DISP=SHR
    //SYSPRINT DD SYSOUT = *
    //SYSUDUMP DD SYSOUT = *
    //SYSOUT   DD SYSOUT = *
    //SYSTSIN  DD *
       DSN SYSTEM(SSID)
       RUN PROGRAM(HELLO) PLAN(PLANNAME) -
       END
    /*
    
    当您编译并执行上述程序时,它会产生以下结果 -
    
    Record Inserted Successfully
    1005 TutorialsPoint     Hyderabad
    
    下面的例子展示了UPDATE在 COBOL 程序中查询 -
    
    IDENTIFICATION DIVISION.
    PROGRAM-ID. HELLO.
    DATA DIVISION.
       WORKING-STORAGE SECTION.
       
       EXEC SQL
       INCLUDE SQLCA
       END-EXEC.
       
       EXEC SQL
       INCLUDE STUDENT
       END-EXEC.
       
       EXEC SQL BEGIN DECLARE SECTION
       END-EXEC.
          01 WS-STUDENT-REC.
             05 WS-STUDENT-ID PIC 9(4).
             05 WS-STUDENT-NAME PIC X(25).
             05 WS-STUDENT-ADDRESS X(50).
       EXEC SQL END DECLARE SECTION
       END-EXEC.
    PROCEDURE DIVISION.
       MOVE 'Bangalore' TO WS-STUDENT-ADDRESS.
       EXEC SQL
          UPDATE STUDENT SET STUDENT-ADDRESS=:WS-STUDENT-ADDRESS
          WHERE STUDENT-ID = 1003
       END-EXEC.
       
       IF SQLCODE = 0 
          DISPLAY 'Record Updated Successfully'
       ELSE DISPLAY 'Error'
       END-IF.
    STOP RUN.
    
    JCL执行上述 COBOL 程序 -
    
    //SAMPLE JOB(TESTJCL,XXXXXX),CLASS = A,MSGCLASS = C
    //STEP001  EXEC PGM = IKJEFT01
    //STEPLIB  DD DSN = MYDATA.URMI.DBRMLIB,DISP = SHR
    //SYSPRINT DD SYSOUT = *
    //SYSUDUMP DD SYSOUT = *
    //SYSOUT   DD SYSOUT = *
    //SYSTSIN  DD *
       DSN SYSTEM(SSID)
       RUN PROGRAM(HELLO) PLAN(PLANNAME) -
       END
    /*
    
    当您编译并执行上述程序时,它会产生以下结果 -
    
    Record Updated Successfully
    
    以下example显示的用法DELETE在 COBOL 程序中查询 -
    
    IDENTIFICATION DIVISION.
    PROGRAM-ID. HELLO.
    DATA DIVISION.
    WORKING-STORAGE SECTION.
       EXEC SQL
       INCLUDE SQLCA
       END-EXEC.
       
       EXEC SQL
       INCLUDE STUDENT
       END-EXEC.
       
       EXEC SQL BEGIN DECLARE SECTION
       END-EXEC.
          01 WS-STUDENT-REC.
             05 WS-STUDENT-ID PIC 9(4).
             05 WS-STUDENT-NAME PIC X(25).
             05 WS-STUDENT-ADDRESS X(50).
       EXEC SQL END DECLARE SECTION
       END-EXEC.
    PROCEDURE DIVISION.
       MOVE 1005 TO WS-STUDENT-ID.
       
       EXEC SQL
          DELETE FROM STUDENT
          WHERE STUDENT-ID=:WS-STUDENT-ID
       END-EXEC.
       
       IF SQLCODE = 0 
          DISPLAY 'Record Deleted Successfully'
       ELSE DISPLAY 'Error'
       END-IF.
    STOP RUN.
    
    JCL执行上述 COBOL 程序 -
    
    //SAMPLE JOB(TESTJCL,XXXXXX),CLASS = A,MSGCLASS = C
    //STEP001  EXEC PGM = IKJEFT01
    //STEPLIB  DD DSN = MYDATA.URMI.DBRMLIB,DISP=SHR
    //SYSPRINT DD SYSOUT = *
    //SYSUDUMP DD SYSOUT = *
    //SYSOUT   DD SYSOUT = *
    //SYSTSIN  DD *
       DSN SYSTEM(SSID)
       RUN PROGRAM(HELLO) PLAN(PLANNAME) -
       END
    /*
    
    当您编译并执行上述程序时,它会产生以下结果 -
    
    Record Deleted Successfully
    
  • 光标

    游标用于一次处理多行选择。它们是保存所有查询结果的数据结构。它们可以在工作存储部分或程序部分中定义。以下是与光标相关的操作 -
    • 宣布
    • 打开
    • 拿来

    声明光标

    光标声明可以在工作存储部分或程序部分完成。第一个语句是 DECLARE 语句,它是一个不可执行的语句。
    
    EXEC SQL
       DECLARE STUDCUR CURSOR FOR
       SELECT STUDENT-ID, STUDENT-NAME, STUDENT-ADDRESS FROM STUDENT
       WHERE STUDENT-ID >:WS-STUDENT-ID
    END-EXEC.
    

    OPEN

    在使用游标之前,必须执行 Open 语句。Open 语句准备 SELECT 以供执行。
    
    EXEC SQL
       OPEN STUDCUR
    END-EXEC.
    

    关闭语句释放游标占用的所有内存。在结束程序之前必须关闭游标。
    
    EXEC SQL
       CLOSE STUDCUR
    END-EXEC.
    

    Fetch

    Fetch 语句识别游标并将值放在 INTO 子句中。当我们一次获取一行时,Fetch 语句被循环编码。
    
    EXEC SQL
       FETCH STUDCUR
       INTO :WS-STUDENT-ID, :WS-STUDENT-NAME, WS-STUDENT-ADDRESS
    END-EXEC.
    
    以下示例显示了使用游标从 STUDENT 表中获取所有记录的用法 -
    
    IDENTIFICATION DIVISION.
    PROGRAM-ID. HELLO.
    DATA DIVISION.
       WORKING-STORAGE SECTION.
       
       EXEC SQL
       INCLUDE SQLCA
       END-EXEC.
       
       EXEC SQL
       INCLUDE STUDENT
       END-EXEC.
       
       EXEC SQL BEGIN DECLARE SECTION
       END-EXEC.
          01 WS-STUDENT-REC.
             05 WS-STUDENT-ID PIC 9(4).
             05 WS-STUDENT-NAME PIC X(25).
             05 WS-STUDENT-ADDRESS X(50).
       EXEC SQL END DECLARE SECTION
       END-EXEC.
       
       EXEC SQL
          DECLARE STUDCUR CURSOR FOR
          SELECT STUDENT-ID, STUDENT-NAME, STUDENT-ADDRESS FROM STUDENT
          WHERE STUDENT-ID >:WS-STUDENT-ID
       END-EXEC.
    PROCEDURE DIVISION.
       MOVE 1001 TO WS-STUDENT-ID.
       PERFORM UNTIL SQLCODE = 100
       
       EXEC SQL
          FETCH STUDCUR
          INTO :WS-STUDENT-ID, :WS-STUDENT-NAME, WS-STUDENT-ADDRESS
       END-EXEC
       
       DISPLAY WS-STUDENT-REC
    END-PERFORM 
    STOP RUN.
    
    JCL执行上述 COBOL 程序 -
    
    //SAMPLE JOB(TESTJCL,XXXXXX),CLASS = A,MSGCLASS = C
    //STEP001  EXEC PGM=IKJEFT01
    //STEPLIB  DD DSN=MYDATA.URMI.DBRMLIB,DISP=SHR
    //SYSPRINT DD SYSOUT=*
    //SYSUDUMP DD SYSOUT=*
    //SYSOUT   DD SYSOUT=*
    //SYSTSIN  DD *
       DSN SYSTEM(SSID)
       RUN PROGRAM(HELLO) PLAN(PLANNAME) -
       END
    /*
    
    当您编译并执行上述程序时,它会产生以下结果 -
    
    1001 Mohtashim M.    Hyderabad
    1002 Nishant Malik      Delhi
    1003 Amitabh Bachan     Mumbai
    1004 Chulbul Pandey     Lucknow