procob handle array-type host variable
来源:互联网 发布:端口和接口的区别 编辑:程序博客网 时间:2024/06/17 05:27
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.
- procob handle array-type host variable
- ThinkPHP5错误解析之variable type error:array
- PHP day9 TP post接受数据 variable type error:array
- ThinkPHP5错误解析之variable type error:array
- TP5接收数组报错variable type error:array
- Box2D: 错误variable length array of non-POD element type 'b2Vec2'
- Box2D: 错误variable length array of non-POD element type 'b2Vec2'
- ThinkPHP5请求参数为数组时报错:variable type error:array
- Variable and basic type
- variable type of buffer_handle_t
- ksh array variable
- Handle showModalDialog in a WebBrowser control host
- Bex Query variable Type 说明
- 【javascript】for(variable in array)
- awk array subscriptor type
- PHP - Manual手册 - Function Reference函数参考 - Variable and Type Related Extensions变量和类型相关扩展 - Arrays数组 - Array Functions数组函数 - ksort
- variable `xxx' has initializer but incomplete type
- * cannot be resolved to a type/variable
- [JS]面向对象OOP-1
- 微信小程序例子——使用text组件实现转义符换行
- 今天做的HTML练习
- c语言之星座运势
- vim python自动补全功能
- procob handle array-type host variable
- html 编码规范
- 使用localResizeIMG-master上传图片
- xshell之类的软件第一次连接不上初次安装kali问题
- Codevs 4373 窗口(线段树 单调队列 st表)
- (五)java开发常用类(包装,数字处理集合等)(下)
- 关于cp命令中拷贝所有的写法
- Android 使用socket实现两个真机之间的通信
- 开发实用整理