截至目前,我们已经学会了在COBOL中使用文件.现在,我们将讨论COBOL程序如何与DB2交互.它涉及以下术语和减号;
嵌入式SQL
DB2应用程序编程
主机变量
SQLCA
SQL查询
游标
在COBOL程序中使用嵌入式SQL语句来执行标准SQL操作.在编译应用程序之前,SQL处理器会对嵌入式SQL语句进行预处理. COBOL被称为主机语言. COBOL-DB2应用程序是包含COBOL和DB2的应用程序.
嵌入式SQL语句的工作方式与普通的SQL语句相似,但有一些细微的变化.例如,查询的输出定向到一组预定义的变量,称为主机变量.另外一个INTO子句放在SELECT语句中.
以下是编写COBOL-DB2程序时要遵循的规则:
所有SQL语句必须在 EXEC SQL 和 ENDEXEC之间分隔. .
SQL语句必须在B区编码.
必须在WorkingStorage部分中声明程序中使用的所有表.这是通过使用 INCLUDE 语句完成的.
除INCLUDE和DECLARE TABLE之外的所有SQL语句都必须出现在Procedure Division中.
主机变量用于从表接收数据或插入数据一张桌子.必须为要在程序和DB2之间传递的所有值声明主机变量.它们在工作存储部分中声明.
主机变量不能是组项,但它们可以在主机结构中组合在一起.它们不能重命名或重新定义.将主变量与SQL语句一起使用,在前面加上冒号(:)..
以下是语法来声明主机变量并包含工作 - 存储部分中的表 :
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是一个SQL通信区域,DB2通过该区域将SQL执行的反馈传递给程序.它告诉程序执行是否成功. SQLCA下有许多预定义变量,如 SQLCODE ,其中包含错误代码. SQLCODE中的值'000'表示执行成功.
以下是在工作存储部分中声明SQLCA的语法&minus ;
DATA DIVISION. WORKING-STORAGE SECTION. EXEC SQL INCLUDE SQLCA END-EXEC.
我们假设我们有一个名为'Student'的表包含Student-Id,Student-姓名和学生地址.
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 '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
以下示例显示在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
以下示例显示在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
游标用于一次处理多行选择.它们是包含查询的所有结果的数据结构.它们可以在工作 - 储存部分或程序部门中定义.以下是与Cursor相关的操作 :
声明
打开
关闭
获取
光标声明可以在工作存储部分或程序部门完成.第一个语句是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语句准备SELECT执行.
EXEC SQL OPEN STUDCUR END-EXEC.
关闭语句释放光标占用的所有内存.在结束程序之前必须关闭光标.
EXEC SQL CLOSE STUDCUR END-EXEC.
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