数据库接口( Database Interface)
截至目前,我们已经学会了在COBOL中使用文件。 现在,我们将讨论COBOL程序如何与DB2交互。 它涉及以下条款 -
- 嵌入式SQL
- DB2应用程序编程
- 主机变量
- SQLCA
- SQL查询
- Cursors
嵌入式SQL
嵌入式SQL语句在COBOL程序中用于执行标准SQL操作。 在编译应用程序之前,SQL处理器会对嵌入式SQL语句进行预处理。 COBOL被称为Host Language 。 COBOL-DB2应用程序是包含COBOL和DB2的应用程序。
嵌入式SQL语句的工作方式与普通的SQL语 例如,查询的输出指向一组预定义的变量,这些变量称为Host Variables 。 另一个INTO子句放在SELECT语句中。
DB2应用程序编程
以下是编写COBOL-DB2程序时要遵循的规则 -
必须在EXEC SQL和ENDEXEC.之间分隔所有SQL语句ENDEXEC. 。
SQL语句必须在区域B中编码。
必须在WorkingStorage部分声明程序中使用的所有表。 这是通过使用INCLUDE语句完成的。
除INCLUDE和DECLARE TABLE之外的所有SQL语句都必须出现在Procedure Division中。
主机变量
主机变量用于从表接收数据或在表中插入数据。 必须为要在程序和DB2之间传递的所有值声明主机变量。 它们在工作储存科申报。
主变量不能是组项,但它们可以在主机结构中组合在一起。 它们无法Renamed或Redefined 。 将主变量与SQL语句一起使用,用colon (:).作为前缀colon (:). 。
语法 (Syntax)
以下是声明主机变量并在工作存储部分中包含表的语法 -
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'表示成功执行。
语法 (Syntax)
以下是在Working-Storage部分声明SQLCA的语法 -
DATA DIVISION.
WORKING-STORAGE SECTION.
EXEC SQL
INCLUDE SQLCA
END-EXEC.
SQL Queries
假设我们有一个名为'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
以下示例显示了COBOL程序中SELECT查询的用法 -
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
以下示例显示了COBOL程序中INSERT查询的用法 -
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 'IoWiki' 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 IoWiki Hyderabad
以下示例显示了COBOL程序中UPDATE查询的用法 -
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显示了COBOL程序中DELETE查询的用法 -
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
Cursors
游标用于一次处理多行选择。 它们是包含查询所有结果的数据结构。 它们可以在工作 - 存储部分或程序部门中定义。 以下是与光标相关的操作 -
- Declare
- Open
- Close
- Fetch
声明游标
游标声明可以在工作存储部分或程序部门完成。 第一个语句是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.
Close
Close语句释放游标占用的所有内存。 在结束程序之前必须关闭光标。
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