oracle环境见：
1:代码procobdemo.pco
***************************************************************** * Sample Program 2: Cursor Operations * * * * This program logs on to ORACLE, declares and opens a cursor, * * fetches the names, salaries, and commissions of all * * salespeople, displays the results, then closes the cursor. * ***************************************************************** IDENTIFICATION DIVISION. PROGRAM-ID. CURSOR-OPS. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 USERNAME PIC X(10) VARYING. 01 PASSWD PIC X(10) VARYING. 01 SID PIC X(10) VARYING. 01 EMP-REC-VARS. 05 EMP-NAME PIC X(10) VARYING. 05 SALARY PIC S9(6)V99 DISPLAY SIGN LEADING SEPARATE. 05 COMMISSION PIC S9(6)V99 DISPLAY SIGN LEADING SEPARATE. EXEC SQL VAR SALARY IS DISPLAY(8,2) END-EXEC. EXEC SQL VAR COMMISSION IS DISPLAY(8,2) END-EXEC. EXEC SQL END DECLARE SECTION END-EXEC. EXEC SQL INCLUDE SQLCA END-EXEC. 01 DISPLAY-VARIABLES. 05 D-EMP-NAME PIC X(10). 05 D-SALARY PIC Z(4)9.99. 05 D-COMMISSION PIC Z(4)9.99. PROCEDURE DIVISION. BEGIN-PGM. EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC. PERFORM LOGON. EXEC SQL DECLARE SALESPEOPLE CURSOR FOR SELECT ENAME, SAL, COMM FROM EMP WHERE JOB LIKE 'SALES%' END-EXEC. EXEC SQL OPEN SALESPEOPLE END-EXEC. DISPLAY " ". DISPLAY "SALESPERSON SALARY COMMISSION". DISPLAY "----------- ---------- ----------". FETCH-LOOP. EXEC SQL WHENEVER NOT FOUND DO PERFORM SIGN-OFF END-EXEC. EXEC SQL FETCH SALESPEOPLE INTO :EMP-NAME, :SALARY, :COMMISSION END-EXEC. MOVE EMP-NAME-ARR TO D-EMP-NAME. MOVE SALARY TO D-SALARY. MOVE COMMISSION TO D-COMMISSION. DISPLAY D-EMP-NAME, " ", D-SALARY, " ", D-COMMISSION. MOVE SPACES TO EMP-NAME-ARR. GO TO FETCH-LOOP. LOGON. MOVE "scott" TO USERNAME-ARR. MOVE 5 TO USERNAME-LEN. MOVE "tiger" TO PASSWD-ARR. MOVE 5 TO PASSWD-LEN. MOVE "XE" TO SID-ARR. MOVE 2 TO SID-LEN. EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWD USING :SID END-EXEC. DISPLAY " ". DISPLAY "CONNECTED TO ORACLE AS USER: ", USERNAME-ARR. SIGN-OFF. EXEC SQL CLOSE SALESPEOPLE END-EXEC. DISPLAY " ". DISPLAY "HAVE A GOOD DAY.". DISPLAY " ". EXEC SQL COMMIT WORK RELEASE END-EXEC. STOP RUN. SQL-ERROR. EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC. DISPLAY " ". DISPLAY "ORACLE ERROR DETECTED:". DISPLAY " ". DISPLAY SQLERRMC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN.
取自例子程序,做出如下修改:
密码大写是不行的这里改为tiger(用户名大小写都ok)
加了变量SID用来存放服务名,在执行连接时候跟在USING后
EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWD USING :SID END-EXEC.
我的环境是instantclient客户端,连接docker容器中的oracle服务.这里服务名为XE.
怎么配置见:
2:编译
没有用官方的makefile---demo_procob_ic.mk(这个比较坑,把我的instantclient动态链接库删光光)
这里手工自己编译:
$ procob iname=procobdemo.pcoPro*COBOL: Release 11.2.0.4.0 - Production on 星期二 5月 8 23:20:10 2018Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.系统默认选项值取自于: /opt/ora11g/instantclient_11_2/precomp/admin/pcbcfg.cfg$ cobc -x -o procobdemo procobdemo.cob -L/opt/ora11g/instantclient_11_2/lib /opt/ora11g/instantclient_11_2/cobsqlintf.o -lpthread -lclntsh$ ./procobdemo CONNECTED TO ORACLE AS USER: scott SALESPERSON SALARY COMMISSION----------- ---------- ----------ALLEN 1600.00 300.00WARD 1250.00 500.00MARTIN 1250.00 1400.00TURNER 1500.00 0.00 HAVE A GOOD DAY.
注意:一定要把 /opt/ora11g/instantclient_11_2/cobsqlintf.o 连进来,不然编译链接通过但执行时出现:
libcob: module 'SQLADR' not found
错误.
3.自己写的Makfile
COB=cobcPROCOB=$(ORACLE_HOME)/bin/procobPRG=procobdemoCOBS=procobdemo.cobCOBFLAGS=-xCOBSQLINTF=$(ORACLE_HOME)/cobsqlintf.oLDPATH=-L$(ORACLE_HOME)/libLDLIBS= -lpthread -lclntsh.SUFFIXES: .cob .pco.pco.cob: @echo "PROCOB ......" $(PROCOB) iname=$*.pco$(PRG): $(COBS) @echo "compile cobol ......" $(COB) $(COBFLAGS) -o $@ $(COBS) $(LDPATH) $(COBSQLINTF) $(LDLIBS)clean: rm $(PRG) $(PRG).cob $(PRG).lis
4.This program uses dynamic SQL Method 1 to create a table, insert a row, commit the insert, then drop the table.
***************************************************************** * Sample Program 6: Dynamic SQL Method 1 * * * * This program uses dynamic SQL Method 1 to create a table, * * insert a row, commit the insert, then drop the table. * *procob iname=sample6.pco *cobc -x -o sample6 sample6.cob -L/opt/ora11g/instantclient_11_2/lib /opt/ora11g/instantclient_11_2/cobsqlintf.o -lpthread -lclntsh ***************************************************************** IDENTIFICATION DIVISION. PROGRAM-ID. DYNSQL1. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. * INCLUDE THE ORACLE COMMUNICATIONS AREA, A STRUCTURE * THROUGH WHICH ORACLE MAKES ADDITIONAL RUNTIME STATUS * INFORMATION AVAILABLE TO THE PROGRAM. EXEC SQL INCLUDE SQLCA END-EXEC. * INCLUDE THE ORACLE COMMUNICATIONS AREA, A STRUCTURE * THROUGH WHICH ORACLE MAKES ADDITIONAL RUNTIME STATUS * INFORMATION AVAILABLE TO THE PROGRAM. EXEC SQL INCLUDE ORACA END-EXEC. * THE OPTION ORACA=YES MUST BE SPECIFIED TO ENABLE USE OF * THE ORACA. EXEC ORACLE OPTION (ORACA=YES) END-EXEC. * THE RELEASE_CURSOR=YES OPTION INSTRUCTS PRO*COBOL TO * RELEASE IMPLICIT CURSORS ASSOCIATED WITH EMBEDDED SQL * STATEMENTS. THIS ENSURES THAT ORACLE DOES NOT KEEP PARSE * LOCKS ON TABLES, SO THAT SUBSEQUENT DATA MANIPULATION * OPERATIONS ON THOSE TABLES DO NOT RESULT IN PARSE-LOCK * ERRORS. EXEC ORACLE OPTION (RELEASE_CURSOR=YES) END-EXEC. EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 USERNAME PIC X(10) VALUE "SCOTT". 01 PASSWD PIC X(10) VALUE "tiger". 01 SID PIC X(10) VALUE "XE". 01 DYNSTMT PIC X(80) VARYING. EXEC SQL END DECLARE SECTION END-EXEC. * DECLARE VARIABLES NEEDED TO DISPLAY COMPUTATIONALS. 01 ORASLNRD PIC 9(9). PROCEDURE DIVISION. MAIN. * BRANCH TO PARAGRAPH SQLERROR IF AN ORACLE ERROR OCCURS. EXEC SQL WHENEVER SQLERROR GOTO SQLERROR END-EXEC. * SAVE TEXT OF CURRENT SQL STATEMENT IN THE ORACA IF AN ERROR * OCCURS. MOVE 1 TO ORASTXTF. * CONNECT TO ORACLE. EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWD USING :SID END-EXEC. DISPLAY " ". DISPLAY "CONNECTED TO ORACLE AS USER: " WITH NO ADVANCING. DISPLAY USERNAME. DISPLAY " ". * EXECUTE A STRING LITERAL TO CREATE THE TABLE. HERE, YOU * GENERALLY USE A STRING VARIABLE INSTEAD OF A LITERAL, AS IS * DONE LATER IN THIS PROGRAM. BUT, YOU CAN USE A LITERAL IF * YOU WISH. DISPLAY "CREATE TABLE DYN1 (COL1 CHAR(4))". DISPLAY " ". EXEC SQL EXECUTE IMMEDIATE "CREATE TABLE DYN1 (COL1 CHAR(4))" END-EXEC. * ASSIGN A SQL STATEMENT TO THE VARYING STRING DYNSTMT. * SET THE -LEN PART TO THE LENGTH OF THE -ARR PART. MOVE "INSERT INTO DYN1 VALUES ('TEST')" TO DYNSTMT-ARR. MOVE 36 TO DYNSTMT-LEN. DISPLAY DYNSTMT-ARR. DISPLAY " ". * EXECUTE DYNSTMT TO INSERT A ROW. THE SQL STATEMENT IS A * STRING VARIABLE WHOSE CONTENTS THE PROGRAM MAY DETERMINE * AT RUN TIME. EXEC SQL EXECUTE IMMEDIATE :DYNSTMT END-EXEC. * COMMIT THE INSERT. EXEC SQL COMMIT WORK END-EXEC. * CHANGE DYNSTMT AND EXECUTE IT TO DROP THE TABLE. MOVE "DROP TABLE DYN1" TO DYNSTMT-ARR. MOVE 19 TO DYNSTMT-LEN. DISPLAY DYNSTMT-ARR. DISPLAY " ". EXEC SQL EXECUTE IMMEDIATE :DYNSTMT END-EXEC. * COMMIT ANY PENDING CHANGES AND DISCONNECT FROM ORACLE. EXEC SQL COMMIT RELEASE END-EXEC. DISPLAY "HAVE A GOOD DAY!". DISPLAY " ". STOP RUN. SQLERROR. * ORACLE ERROR HANDLER. PRINT DIAGNOSTIC TEXT CONTAINING * ERROR MESSAGE, CURRENT SQL STATEMENT, AND LOCATION OF ERROR. DISPLAY SQLERRMC. DISPLAY "IN ", ORASTXTC. MOVE ORASLNR TO ORASLNRD. DISPLAY "ON LINE ", ORASLNRD, " OF ", ORASFNMC. * DISABLE ORACLE ERROR CHECKING TO AVOID AN INFINITE LOOP * SHOULD ANOTHER ERROR OCCUR WITHIN THIS PARAGRAPH. EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC. * ROLL BACK ANY PENDING CHANGES AND DISCONNECT FROM ORACLE. EXEC SQL ROLLBACK RELEASE END-EXEC. STOP RUN. 编译运行：
$ procob iname=sample6.pcoPro*COBOL: Release 11.2.0.4.0 - Production on 星期四 5月 17 10:58:44 2018Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.系统默认选项值取自于: /opt/ora11g/instantclient_11_2/precomp/admin/pcbcfg.cfg$ cobc -x -o sample sample6.cob -L/opt/ora11g/instantclient_11_2/lib /opt/ora11g/instantclient_11_2/cobsqlintf.o -lpthread -lclntsh$ ./sample CONNECTED TO ORACLE AS USER: SCOTT CREATE TABLE DYN1 (COL1 CHAR(4)) INSERT INTO DYN1 VALUES ('TEST') DROP TABLE DYN1 HAVE A GOOD DAY! ~/prg/embc/oracob$ rm sample *.lis *.cob 5.
***************************************************************** * Sample Program 7: Dynamic SQL Method 2 * * * * This program uses dynamic SQL Method 2 to insert two rows * * into the EMP table, then delete them. * *procob iname=sample7.pco *cobc -x -o sample7 sample7.cob -L/opt/ora11g/instantclient_11_2/lib /opt/ora11g/instantclient_11_2/cobsqlintf.o -lpthread -lclntsh ***************************************************************** IDENTIFICATION DIVISION. PROGRAM-ID. DYNSQL2. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. * INCLUDE THE SQL COMMUNICATIONS AREA, A STRUCTURE THROUGH * WHICH ORACLE MAKES RUNTIME STATUS INFORMATION (SUCH AS ERROR * CODES, WARNING FLAGS, AND DIAGNOSTIC TEXT) AVAILABLE TO THE * PROGRAM. EXEC SQL INCLUDE SQLCA END-EXEC. * INCLUDE THE ORACLE COMMUNICATIONS AREA, A STRUCTURE THROUGH * WHICH ORACLE MAKES ADDITIONAL RUNTIME STATUS INFORMATION * AVAILABLE TO THE PROGRAM. EXEC SQL INCLUDE ORACA END-EXEC. * THE OPTION ORACA=YES MUST BE SPECIFIED TO ENABLE USE OF * THE ORACA. EXEC ORACLE OPTION (ORACA=YES) END-EXEC. EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 USERNAME PIC X(10) VALUE "SCOTT". 01 PASSWD PIC X(10) VALUE "tiger". 01 SID PIC X(10) VALUE "XE". 01 DYNSTMT PIC X(80) VARYING. 01 EMPNO PIC S9(4) COMPUTATIONAL VALUE 1234. 01 DEPTNO1 PIC S9(4) COMPUTATIONAL VALUE 10. 01 DEPTNO2 PIC S9(4) COMPUTATIONAL VALUE 20. EXEC SQL END DECLARE SECTION END-EXEC. * DECLARE VARIABLES NEEDED TO DISPLAY COMPUTATIONALS. 01 EMPNOD PIC 9(4). 01 DEPTNO1D PIC 9(2). 01 DEPTNO2D PIC 9(2). 01 ORASLNRD PIC 9(9). PROCEDURE DIVISION. MAIN. * BRANCH TO PARAGRAPH SQLERROR IF AN ORACLE ERROR OCCURS. EXEC SQL WHENEVER SQLERROR GOTO SQLERROR END-EXEC. * SAVE TEXT OF CURRENT SQL STATEMENT IN THE ORACA IF AN ERROR * OCCURS. MOVE 1 TO ORASTXTF. * CONNECT TO ORACLE. EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWD USING :SID END-EXEC. DISPLAY " ". DISPLAY "CONNECTED TO ORACLE.". DISPLAY " ". * ASSIGN A SQL STATEMENT TO THE VARYING STRING DYNSTMT. BOTH * THE ARRAY AND THE LENGTH PARTS MUST BE SET PROPERLY. NOTE * THAT THE STATEMENT CONTAINS TWO HOST VARIABLE PLACEHOLDERS, * V1 AND V2, FOR WHICH ACTUAL INPUT HOST VARIABLES MUST BE * SUPPLIED AT EXECUTE TIME. MOVE "INSERT INTO EMP (EMPNO, DEPTNO) VALUES (:V1, :V2)" TO DYNSTMT-ARR. MOVE 49 TO DYNSTMT-LEN. * DISPLAY THE SQL STATEMENT AND ITS CURRENT INPUT HOST * VARIABLES. DISPLAY DYNSTMT-ARR. MOVE EMPNO TO EMPNOD. MOVE DEPTNO1 TO DEPTNO1D. DISPLAY " V1 = ", EMPNOD, " V2 = ", DEPTNO1D. * THE PREPARE STATEMENT ASSOCIATES A STATEMENT NAME WITH A * STRING CONTAINING A SQL STATEMENT. THE STATEMENT NAME IS * A SQL IDENTIFIER, NOT A HOST VARIABLE, AND THEREFORE DOES * NOT APPEAR IN THE DECLARE SECTION. * A SINGLE STATEMENT NAME MAY BE PREPARED MORE THAN ONCE, * OPTIONALLY FROM A DIFFERENT STRING VARIABLE. EXEC SQL PREPARE S FROM :DYNSTMT END-EXEC. * THE EXECUTE STATEMENT EXECUTES A PREPARED SQL STATEMENT * USING THE SPECIFIED INPUT HOST VARIABLES, WHICH ARE * SUBSTITUTED POSITIONALLY FOR PLACEHOLDERS IN THE PREPARED * STATEMENT. FOR EACH OCCURRENCE OF A PLACEHOLDER IN THE * STATEMENT THERE MUST BE A VARIABLE IN THE USING CLAUSE. * THAT IS, IF A PLACEHOLDER OCCURS MULTIPLE TIMES IN THE * STATEMENT, THE CORRESPONDING VARIABLE MUST APPEAR * MULTIPLE TIMES IN THE USING CLAUSE. THE USING CLAUSE MAY * BE OMITTED ONLY IF THE STATEMENT CONTAINS NO PLACEHOLDERS. * A SINGLE PREPARED STATEMENT MAY BE EXECUTED MORE THAN ONCE, * OPTIONALLY USING DIFFERENT INPUT HOST VARIABLES. EXEC SQL EXECUTE S USING :EMPNO, :DEPTNO1 END-EXEC. * INCREMENT EMPNO AND DISPLAY NEW INPUT HOST VARIABLES. ADD 1 TO EMPNO. MOVE EMPNO TO EMPNOD. MOVE DEPTNO2 TO DEPTNO2D. DISPLAY " V1 = ", EMPNOD, " V2 = ", DEPTNO2D. * REEXECUTE S TO INSERT THE NEW VALUE OF EMPNO AND A * DIFFERENT INPUT HOST VARIABLE, DEPTNO2. A REPREPARE IS NOT * NECESSARY. EXEC SQL EXECUTE S USING :EMPNO, :DEPTNO2 END-EXEC. * ASSIGN A NEW VALUE TO DYNSTMT. MOVE "DELETE FROM EMP WHERE DEPTNO = :V1 OR DEPTNO = :V2" TO DYNSTMT-ARR. MOVE 50 TO DYNSTMT-LEN. * DISPLAY THE NEW SQL STATEMENT AND ITS CURRENT INPUT HOST * VARIABLES. DISPLAY DYNSTMT-ARR. DISPLAY " V1 = ", DEPTNO1D, " V2 = ", DEPTNO2D. * REPREPARE S FROM THE NEW DYNSTMT. EXEC SQL PREPARE S FROM :DYNSTMT END-EXEC. * EXECUTE THE NEW S TO DELETE THE TWO ROWS PREVIOUSLY * INSERTED. EXEC SQL EXECUTE S USING :DEPTNO1, :DEPTNO2 END-EXEC. * ROLLBACK ANY PENDING CHANGES AND DISCONNECT FROM ORACLE. EXEC SQL ROLLBACK RELEASE END-EXEC. DISPLAY " ". DISPLAY "HAVE A GOOD DAY!". DISPLAY " ". STOP RUN. SQLERROR. * ORACLE ERROR HANDLER. PRINT DIAGNOSTIC TEXT CONTAINING * ERROR MESSAGE, CURRENT SQL STATEMENT, AND LOCATION OF ERROR. DISPLAY SQLERRMC. DISPLAY "IN ", ORASTXTC. MOVE ORASLNR TO ORASLNRD. DISPLAY "ON LINE ", ORASLNRD, " OF ", ORASFNMC. * DISABLE ORACLE ERROR CHECKING TO AVOID AN INFINITE LOOP * SHOULD ANOTHER ERROR OCCUR WITHIN THIS PARAGRAPH. EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC. * ROLL BACK ANY PENDING CHANGES AND DISCONNECT FROM ORACLE. EXEC SQL ROLLBACK RELEASE END-EXEC. STOP RUN.
6.
***************************************************************** * Sample Program 8: Dynamic SQL Method 3 * * * * This program uses dynamic SQL Method 3 to retrieve the names * * of all employees in a given department from the EMP table. * ***************************************************************** IDENTIFICATION DIVISION. PROGRAM-ID. DYNSQL3. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. * INCLUDE THE SQL COMMUNICATIONS AREA, A STRUCTURE THROUGH * WHICH ORACLE MAKES RUNTIME STATUS INFORMATION (SUCH AS ERROR * CODES, WARNING FLAGS, AND DIAGNOSTIC TEXT) AVAILABLE TO THE * PROGRAM. EXEC SQL INCLUDE SQLCA END-EXEC. * INCLUDE THE ORACLE COMMUNICATIONS AREA, A STRUCTURE THROUGH * WHICH ORACLE MAKES ADDITIONAL RUNTIME STATUS INFORMATION * AVAILABLE TO THE PROGRAM. EXEC SQL INCLUDE ORACA END-EXEC. * THE ORACA=YES OPTION MUST BE SPECIFIED TO ENABLE USE OF * THE ORACA. EXEC ORACLE OPTION (ORACA=YES) END-EXEC. EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 USERNAME PIC X(10) VALUE "SCOTT". 01 PASSWD PIC X(10) VALUE "tiger". 01 SID PIC X(10) VALUE "XE". 01 DYNSTMT PIC X(80) VARYING. 01 ENAME PIC X(10). 01 DEPTNO PIC S9999 COMPUTATIONAL VALUE 10. EXEC SQL END DECLARE SECTION END-EXEC. * DECLARE VARIABLES NEEDED TO DISPLAY COMPUTATIONALS. 01 DEPTNOD PIC 9(2). 01 ENAMED PIC X(10). 01 SQLERRD3 PIC 9(2). 01 ORASLNRD PIC 9(4). PROCEDURE DIVISION. MAIN. * BRANCH TO PARAGRAPH SQLERROR IF AN ORACLE ERROR OCCURS. EXEC SQL WHENEVER SQLERROR GO TO SQLERROR END-EXEC. * SAVE TEXT OF CURRENT SQL STATEMENT IN THE ORACA IF AN ERROR * OCCURS. MOVE 1 TO ORASTXTF. * CONNECT TO ORACLE. EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWD USING :SID END-EXEC. DISPLAY " ". DISPLAY "CONNECTED TO ORACLE.". DISPLAY " ". * ASSIGN A SQL QUERY TO THE VARYING STRING DYNSTMT. BOTH THE * ARRAY AND THE LENGTH PARTS MUST BE SET PROPERLY. NOTE THAT * THE STATEMENT CONTAINS ONE HOST VARIABLE PLACEHOLDER, V1, * FOR WHICH AN ACTUAL INPUT HOST VARIABLE MUST BE SUPPLIED * AT OPEN TIME. MOVE "SELECT ENAME FROM EMP WHERE DEPTNO = :V1" TO DYNSTMT-ARR. MOVE 40 TO DYNSTMT-LEN. * DISPLAY THE SQL STATEMENT AND ITS CURRENT INPUT HOST * VARIABLE. DISPLAY DYNSTMT-ARR. MOVE DEPTNO TO DEPTNOD. DISPLAY " V1 = ", DEPTNOD. DISPLAY " ". DISPLAY "EMPLOYEE". DISPLAY "--------". * THE PREPARE STATEMENT ASSOCIATES A STATEMENT NAME WITH A * STRING CONTAINING A SELECT STATEMENT. THE STATEMENT NAME, * WHICH MUST BE UNIQUE, IS A SQL IDENTIFIER, NOT A HOST * VARIABLE, AND SO DOES NOT APPEAR IN THE DECLARE SECTION. EXEC SQL PREPARE S FROM :DYNSTMT END-EXEC. * THE DECLARE STATEMENT ASSOCIATES A CURSOR WITH A PREPARED * STATEMENT. THE CURSOR NAME, LIKE THE STATEMENT NAME, DOES * NOT APPEAR IN THE DECLARE SECTION. EXEC SQL DECLARE C CURSOR FOR S END-EXEC. * THE OPEN STATEMENT EVALUATES THE ACTIVE SET OF THE PREPARED * QUERY USING THE SPECIFIED INPUT HOST VARIABLES, WHICH ARE * SUBSTITUTED POSITIONALLY FOR PLACEHOLDERS IN THE PREPARED * QUERY. FOR EACH OCCURRENCE OF A PLACEHOLDER IN THE * STATEMENT THERE MUST BE A VARIABLE IN THE USING CLAUSE. * THAT IS, IF A PLACEHOLDER OCCURS MULTIPLE TIMES IN THE * STATEMENT, THE CORRESPONDING VARIABLE MUST APPEAR MULTIPLE * TIMES IN THE USING CLAUSE. THE USING CLAUSE MAY BE * OMITTED ONLY IF THE STATEMENT CONTAINS NO PLACEHOLDERS. * OPEN PLACES THE CURSOR AT THE FIRST ROW OF THE ACTIVE SET * IN PREPARATION FOR A FETCH. * A SINGLE DECLARED CURSOR MAY BE OPENED MORE THAN ONCE, * OPTIONALLY USING DIFFERENT INPUT HOST VARIABLES. EXEC SQL OPEN C USING :DEPTNO END-EXEC. * BRANCH TO PARAGRAPH NOTFOUND WHEN ALL ROWS HAVE BEEN * RETRIEVED. EXEC SQL WHENEVER NOT FOUND GO TO NOTFOUND END-EXEC. GETROWS. * THE FETCH STATEMENT PLACES THE SELECT LIST OF THE CURRENT * ROW INTO THE VARIABLES SPECIFIED BY THE INTO CLAUSE, THEN * ADVANCES THE CURSOR TO THE NEXT ROW. IF THERE ARE MORE * SELECT-LIST FIELDS THAN OUTPUT HOST VARIABLES, THE EXTRA * FIELDS ARE NOT RETURNED. SPECIFYING MORE OUTPUT HOST * VARIABLES THAN SELECT-LIST FIELDS RESULTS IN AN ORACLE ERROR. EXEC SQL FETCH C INTO :ENAME END-EXEC. MOVE ENAME TO ENAMED. DISPLAY ENAMED. * LOOP UNTIL NOT FOUND CONDITION IS DETECTED. GO TO GETROWS. NOTFOUND. MOVE SQLERRD(3) TO SQLERRD3. DISPLAY " ". DISPLAY "QUERY RETURNED ", SQLERRD3, " ROW(S).". * THE CLOSE STATEMENT RELEASES RESOURCES ASSOCIATED WITH THE * CURSOR. EXEC SQL CLOSE C END-EXEC. * COMMIT ANY PENDING CHANGES AND DISCONNECT FROM ORACLE. EXEC SQL COMMIT RELEASE END-EXEC. DISPLAY " ". DISPLAY "HAVE A GOOD DAY!". DISPLAY " ". STOP RUN. SQLERROR. * ORACLE ERROR HANDLER. PRINT DIAGNOSTIC TEXT CONTAINING * ERROR MESSAGE, CURRENT SQL STATEMENT, AND LOCATION OF ERROR. DISPLAY SQLERRMC. DISPLAY "IN ", ORASTXTC. MOVE ORASLNR TO ORASLNRD. DISPLAY "ON LINE ", ORASLNRD, " OF ", ORASFNMC. * DISABLE ORACLE ERROR CHECKING TO AVOID AN INFINITE LOOP * SHOULD ANOTHER ERROR OCCUR WITHIN THIS PARAGRAPH. EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC. * RELEASE RESOURCES ASSOCIATED WITH THE CURSOR. EXEC SQL CLOSE C END-EXEC. * ROLL BACK ANY PENDING CHANGES AND DISCONNECT FROM ORACLE. EXEC SQL ROLLBACK RELEASE END-EXEC. STOP RUN.
7、嵌入PL/SQL
创建程序包和程序包体sample11.sql:
CONNECT SCOTT/tigerCREATE OR REPLACE PACKAGE emp_demo_pkg AS TYPE emp_cur_type IS REF CURSOR RETURN emp%ROWTYPE; PROCEDURE open_cur ( cursor IN OUT emp_cur_type, dept_num IN number);END emp_demo_pkg;/ CREATE OR REPLACE PACKAGE BODY emp_demo_pkg AS PROCEDURE open_cur ( cursor IN OUT emp_cur_type, dept_num IN number) IS BEGIN OPEN cursor FOR SELECT * FROM emp WHERE deptno = dept_num ORDER BY ename ASC; END;END emp_demo_pkg;/
pro*cobol程序
***************************************************************** * Sample Program 11: Cursor Variable Operations * * * * This program logs on to ORACLE, allocates and opens a cursor * * variable fetches the names, salaries, and commissions of all * * salespeople, displays the results, then closes the cursor. * *procob iname=sample11.pco SQLCHECK=SEMANTICS *cobc -x -o sample11 sample11.cob -L/opt/ora11g/instantclient_11_2/lib /opt/ora11g/instantclient_11_2/cobsqlintf.o -lpthread -lclntsh ***************************************************************** IDENTIFICATION DIVISION. PROGRAM-ID. CURSOR-VARIABLES. ENVIRONMENT DIVISION. DATA DIVISION. WORKING-STORAGE SECTION. EXEC SQL BEGIN DECLARE SECTION END-EXEC. 01 USERNAME PIC X(10) VARYING. 01 PASSWD PIC X(10) VARYING. 01 SID PIC X(10) VARYING. 01 EMP-CUR SQL-CURSOR. 01 EMP-INFO. 05 EMP-NUM PIC S9(4) COMP. 05 EMP-NAM PIC X(10) VARYING. 05 EMP-JOB PIC X(10) VARYING. 05 EMP-MGR PIC S9(4) COMP. 05 EMP-DAT PIC X(10) VARYING. 05 EMP-SAL PIC S9(6)V99 DISPLAY SIGN LEADING SEPARATE. 05 EMP-COM PIC S9(6)V99 DISPLAY SIGN LEADING SEPARATE. 05 EMP-DEP PIC S9(4) COMP. 01 EMP-INFO-IND. 05 EMP-NUM-IND PIC S9(4) COMP. 05 EMP-NAM-IND PIC S9(4) COMP. 05 EMP-JOB-IND PIC S9(4) COMP. 05 EMP-MGR-IND PIC S9(4) COMP. 05 EMP-DAT-IND PIC S9(4) COMP. 05 EMP-SAL-IND PIC S9(4) COMP. 05 EMP-COM-IND PIC S9(4) COMP. 05 EMP-DEP-IND PIC S9(4) COMP. EXEC SQL END DECLARE SECTION END-EXEC. EXEC SQL INCLUDE SQLCA END-EXEC. 01 DISPLAY-VARIABLES. 05 D-DEP-NUM PIC Z(3)9. 05 D-EMP-NAM PIC X(10). 05 D-EMP-SAL PIC Z(4)9.99. 05 D-EMP-COM PIC Z(4)9.99. 05 D-EMP-DEP PIC 9(2). PROCEDURE DIVISION. BEGIN-PGM. EXEC SQL WHENEVER SQLERROR DO PERFORM SQL-ERROR END-EXEC. PERFORM LOGON. EXEC SQL ALLOCATE :EMP-CUR END-EXEC. DISPLAY "Enter department number (0 to exit): " WITH NO ADVANCING. ACCEPT D-EMP-DEP. MOVE D-EMP-DEP TO EMP-DEP. IF EMP-DEP <= 0 GO TO SIGN-OFF END-IF. MOVE EMP-DEP TO D-DEP-NUM. EXEC SQL EXECUTE BEGIN emp_demo_pkg.open_cur(:EMP-CUR, :EMP-DEP); END; END-EXEC. DISPLAY " ". DISPLAY "For department ", D-DEP-NUM, ":". DISPLAY " ". DISPLAY "EMPLOYEE SALARY COMMISSION". DISPLAY "---------- ---------- ----------". FETCH-LOOP. EXEC SQL WHENEVER NOT FOUND GOTO CLOSE-UP END-EXEC. MOVE SPACES TO EMP-NAM-ARR. EXEC SQL FETCH :EMP-CUR INTO :EMP-NUM:EMP-NUM-IND, :EMP-NAM:EMP-NAM-IND, :EMP-JOB:EMP-JOB-IND, :EMP-MGR:EMP-MGR-IND, :EMP-DAT:EMP-DAT-IND, :EMP-SAL:EMP-SAL-IND, :EMP-COM:EMP-COM-IND, :EMP-DEP:EMP-DEP-IND END-EXEC. MOVE EMP-SAL TO D-EMP-SAL. IF EMP-COM-IND = 0 MOVE EMP-COM TO D-EMP-COM DISPLAY EMP-NAM-ARR, " ", D-EMP-SAL, " ", D-EMP-COM ELSE DISPLAY EMP-NAM-ARR, " ", D-EMP-SAL, " N/A" END-IF. GO TO FETCH-LOOP. LOGON. MOVE "SCOTT" TO USERNAME-ARR. MOVE 5 TO USERNAME-LEN. MOVE "tiger" TO PASSWD-ARR. MOVE 5 TO PASSWD-LEN. MOVE "XE" TO SID-ARR. MOVE 2 TO SID-LEN. EXEC SQL CONNECT :USERNAME IDENTIFIED BY :PASSWD USING :SID END-EXEC. DISPLAY " ". DISPLAY "CONNECTED TO ORACLE AS USER: ", USERNAME-ARR. CLOSE-UP. EXEC SQL CLOSE :EMP-CUR END-EXEC. EXEC SQL FREE :EMP-CUR END-EXEC. SIGN-OFF. DISPLAY " ". DISPLAY "HAVE A GOOD DAY.". DISPLAY " ". EXEC SQL COMMIT WORK RELEASE END-EXEC. STOP RUN. SQL-ERROR. EXEC SQL WHENEVER SQLERROR CONTINUE END-EXEC. DISPLAY " ". DISPLAY "ORACLE ERROR DETECTED:". DISPLAY " ". DISPLAY SQLERRMC. EXEC SQL ROLLBACK WORK RELEASE END-EXEC. STOP RUN.
编译运行：
$ procob iname=sample11.pco SQLCHECK=SEMANTICSPro*COBOL: Release 11.2.0.4.0 - Production on 星期六 5月 26 13:40:32 2018Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.系统默认选项值取自于: /opt/ora11g/instantclient_11_2/precomp/admin/pcbcfg.cfg$ cobc -x -o sample11 sample11.cob -L/opt/ora11g/instantclient_11_2/lib /opt/ora11g/instantclient_11_2/cobsqlintf.o -lpthread -lclntsh$ ./sample11 CONNECTED TO ORACLE AS USER: SCOTT Enter department number (0 to exit): 10 For department 10: EMPLOYEE SALARY COMMISSION---------- ---------- ----------CLARK 2450.00 N/AKING 5000.00 N/AMILLER 1300.00 N/A HAVE A GOOD DAY.