procob handle array-type host variable

2019-04-15 17:48发布

站内文章 / PIC单片机
14768 0 1233

l  Use select statement when we know the max rows will bereturned. WORKING-STORAGE SECTION. 01 TABLE-ROWS .   03S-SEQ-NUM-S    PIC S9(8) COMP-3    OCCURS 3 TIMES.   03S-NAME-S       PIC X(8)            OCCURS 3 TIMES.   03S-ID-S          PIC S9(4) COMP-3    OCCURS 3 TIMES.   03SCORE-Y-S      PIC X(4)            OCCURS 3 TIMES.
PROCEDURE DIVISION.   EXECSQL     SELECTS_SEQ_NUM, S_NAME, S_ID, SCORE_Y      INTO :TABLE-ROWS:I-TABLE-ROWS      FROM V16TABLE   END-EXEC.   IFSQLCODE = 0     DISPLAY"RETURN OK"     DISPLAY"RETURN ROWS: " SQLERRD(3)                  *>exactly 3 rows data are selected.   ELSEIF SQLCODE = 100     DISPLAY"NO DATA ROW IS FOUND"     DISPLAY"RETURN ROWS: " SQLERRD(3)                  *>SQLERRD(3) indicate how many real data rows are selected; host array size 3 meanswe wish exactly 3 rows are selected, but the real data rows can be less than 3,e.g., 2, 1, 0   ELSEIF SQLCODE = -2112     DISPLAY"RETURN TOO MANY ROWS"                      *>There should have more than 3 rows, but array size is 3, so first 3 rows arereturned, and SQLCODE is set to -2112     DISPLAY"RETURN ROWS: " SQLERRD(3)   ELSE     DISPLAY"ORACLE ERROR DETECTED:"     DISPLAY"SQLCODE = " SQLCODE     DISPLAY"SQLERRM = " SQLERRMC   END-IF.
Limitation: we cannot indicate the expectedrows size returned, except the array size.
l  Use fetch statement when we don’t know the max rows will bereturned.
WORKING-STORAGE SECTION. 01 SELECT-ROW-REQ                            PIC S9(09)  COMP. 01 TABLE-ROWS.    03S-SEQ-NUM-S            OCCURS  00005  PIC S9(8) COMP-3.    03S-NAME-S               OCCURS  00005  PIC X(8).    03S-ID-S                 OCCURS  00005  PIC S9(4) COMP-3.    03SCORE-Y-S              OCCURS  00005  PIC X(4).
PROCEDURE DIVISION.    EXEC SQL     DECLARE CUR_SQL SCROLL CURSOR WITH HOLD FOR     SELECT S_SEQ_NUM, S_NAME, S_ID, SCORE_Y     FROM V16TABLE     WHERE S_SEQ_NUM = :TABLE-KEYS END-EXEC.
EXEC SQLOPEN  CUR_SQL END-EXEC.
MOVE 2 TOSELECT-ROW-REQ. EXEC SQL         FETCH NEXT ROWSET CUR_SQL       FOR :SELECT-ROW-REQ ROWS                                                            *>we expect to fetch 2 rows   INTO :S-SEQ-NUM-S, :S-NAME-S, :S-ID-S,:SCORE-Y-S END-EXEC. IF SQLCODE = 0   DISPLAY "RETURN OK"   DISPLAY "RETURN ROWS: " SQLERRD(3) ELSE IF SQLCODE= 100                                                                              *>SQLERRD(3) indicate how many real data rows are selected; SELECT-ROW-REQ=2 meanswe wish exactly 2 rows are selected, but the real data rows can be less than 2,e.g., 1, 0   DISPLAY "NO DATA ROW IS FOUND"   DISPLAY "RETURN ROWS: " SQLERRD(3) ELSE   DISPLAY "ORACLE ERROR DETECTED:"   DISPLAY "SQLCODE = " SQLCODE   DISPLAY "SQLERRM = " SQLERRMC END-IF.
EXEC SQL CLOSECUR_SQL END-EXEC.
l  Host array limitation -         Simple host variables cannot bemixed with host arrays in the INTO clause of a SELECT or FETCH statement. Ifany of the host variables is an array, all must be arrays. -         Using host arrays in the WHEREclause of a SELECT statement is not allowed except in a sub-query, only simplehost variable in WHERE clause of a SELECT statement is allowed, so this is theonly time that you can mix array host variables and simple host variables.
Following code snippet will have same result (the host arrayin WHERE clause is treated as simple host variable): WORKING-STORAGE SECTION.  01 SELECT-ROW-REQ                            PIC S9(09)  COMP.  01 TABLE-ROWS.     03 S-SEQ-NUM-S            OCCURS  00005  PIC S9(8) COMP-3.     03 S-NAME-S               OCCURS  00005  PIC X(8).     03 S-ID-S                 OCCURS  00005  PIC S9(4) COMP-3.     03 SCORE-Y-S              OCCURS  00005  PIC X(4). 01 TABLE-KEYS                OCCURS  00005  PIC S9(8) COMP-3.
PROCEDURE DIVISION.      EXEC SQL       DECLARE CUR_SQLSCROLL CURSOR WITH HOLD FOR       SELECT S_SEQ_NUM,S_NAME, S_ID, SCORE_Y       FROM V16TABLE       WHERE S_SEQ_NUM = :TABLE-KEYS      END-EXEC.
     MOVE 2 TOSELECT-ROW-REQ.      MOVE 4 TO TABLE-KEYS(1).     MOVE5 TO TABLE-KEYS(2).      EXEC SQL OPEN  CUR_SQL END-EXEC.
     EXEC SQL            FETCH NEXT ROWSETCUR_SQL          FOR:SELECT-ROW-REQ ROWS       INTO:S-SEQ-NUM-S, :S-NAME-S, :S-ID-S, :SCORE-Y-S      END-EXEC.
vs. WORKING-STORAGE SECTION.  01 SELECT-ROW-REQ                            PIC S9(09)  COMP.  01 TABLE-ROWS.     03 S-SEQ-NUM-S            OCCURS  00005  PIC S9(8) COMP-3.     03 S-NAME-S               OCCURS  00005  PIC X(8).     03 S-ID-S                 OCCURS  00005  PIC S9(4) COMP-3.     03 SCORE-Y-S              OCCURS  00005  PIC X(4). 01 TABLE-KEYS                PIC S9(8) COMP-3.
PROCEDURE DIVISION.      EXEC SQL       DECLARE CUR_SQLSCROLL CURSOR WITH HOLD FOR       SELECT S_SEQ_NUM,S_NAME, S_ID, SCORE_Y       FROM V16TABLE       WHERE S_SEQ_NUM = :TABLE-KEYS      END-EXEC.
     MOVE 2 TOSELECT-ROW-REQ.      MOVE 4 TO TABLE-KEYS.      EXEC SQL OPEN  CUR_SQL END-EXEC.
     EXEC SQL            FETCH NEXT ROWSETCUR_SQL          FOR:SELECT-ROW-REQ ROWS       INTO:S-SEQ-NUM-S, :S-NAME-S, :S-ID-S, :SCORE-Y-S      END-EXEC.

热门文章