DB2 / 400替代打开光标 [英] DB2/400 Alternative to Opening a Cursor

问题描述

我正在运行DB2 for i，V7R2 TR3。

我被告知打开游标提供了大量的开销，尽可能避免。从我所看到的，使用 EXECUTE INTO var1 USING var2 是一种替代方案，但我无法使其工作。我收到一个 SQL0104错误。

这是我的存储过程：

  BEGIN 
 DECLARE STMT1 VARCHAR（500）; 
 
 SET STMT1 ='SELECT SUBSTR（'''|| TRIM（ITEM）||'''，（LENGTH（TRIM（PREFIX））+ 1），（20  -  LENGTH（TRIM ））））'|| 
'FROM MYLIB.MYTABLE'|| 
'WHERE PREFIX = SUBSTR（'''|| TRIM（ITEM）||'''，0，LENGTH（TRIM（PREFIX））+ 1）'|| 
'AND SEQ1 ='|| TYPE || ''|| 
'ORDER BY LENGTH（TRIM（PREFIX））DESC，TRIM（PREFIX）DESC'|| 
'FETCH FIRST 1 ROWS ONLY'; 
 
来自STMT1的PREPARE S1; 
 EXECUTE S1 INTO BASEITEM; 
 
 --OPEN C1; 
  -  FETCH C1 INTO BASEITEM; 
 --CLOSE C1; 
 
 IF（TRIM（BASEITEM）=''）THEN 
 SET BASEITEM = ITEM; 
 END IF; 
 END; 
  

它有三个变量定义：

  IN ITEM CHAR（20）CCSID 37 DEFAULT''，
 IN TYPE INT DEFAULT 1，
 INOUT BASEITEM CHAR（20）DEFAULT''
  

当我有 EXECUTE INTO ... 这不会编译，如果我使用 EXECUTE USING ... 它将编译，但$ code> BASEITEM 最终为空白，$ code> IF 语句解析为true。

我试过按照 EXECUTE 文档，显然是 INTO 可以仅适用于 CALL 或 VALUES INTO 语句。所以我试图按照 VALUES INTO 文档，但无法弄清楚如何使用查询。

注意：我想最终更改连接的变量以使用参数标记，但我下一步要担心。

我想我应该张贴正在使用的表：
MYLIB.MYTABLE

 列|前缀| SEQ1 | SEQ2 | 
第1行| aaa | 1 | 3 | 
第2行| aab | 1 | 3 | 
第3行| aabd | 2 | 4 |我基本上传递一个字符串，然后从字符串中删除最长的前缀。然后我返回了新的字符串。  SEQ1 只是前缀的类型（1或2），SEQ2是前缀的长度。 
解决方案
  EXECUTE 只能用于DML语句， ，所以真的没有办法声明一个动态游标。话虽如此，没有打开游标的开销，而不是以任何其他方式执行查询，因为对于任何查询，游标仍将被隐式地创建和打开，如果没有明确的。
 
 
 
游标效率低于基于集合的操作，例如什么时候，而不是像 INSERT INTO table1 SELECT something FROM table2 WHERE ... 你打开一个光标在 table2 并在循环中逐个插入行到 table1 中。在这种情况下，效率低下是由执行单行插入（或更新）与多次更新而不是光标本身产生的，并且与打开游标提供了大量开销完全不同。 
 
 
 
由于在您的情况下，您只会获取一条记录，所以此循环与设置参数不适用。
 
I'm running DB2 for i, V7R2 TR3.

I was told opening a cursor provides a lot of overhead and should be avoided whenever possible.  From what I've read, using EXECUTE INTO var1 USING var2 is an alternative, but I can't get it working.  I am getting a SQL0104 error.

This is my Stored Procedure:
BEGIN 
DECLARE STMT1 VARCHAR ( 500 ) ;     

SET STMT1 =     'SELECT SUBSTR (''' || TRIM(ITEM) || ''' , ( LENGTH ( TRIM ( PREFIX ) ) + 1 ) , ( 20 - LENGTH ( TRIM ( PREFIX ) ) ) ) ' ||
                'FROM   MYLIB.MYTABLE ' ||
                'WHERE  PREFIX = SUBSTR(''' || TRIM(ITEM) || ''', 0,LENGTH ( TRIM ( PREFIX ) ) + 1 ) ' ||
                'AND    SEQ1 = ' || TYPE || ' ' ||
                'ORDER BY   LENGTH ( TRIM ( PREFIX) ) DESC , TRIM (PREFIX) DESC ' ||
                'FETCH FIRST 1 ROWS ONLY ';

PREPARE S1 FROM STMT1; 
EXECUTE S1 INTO BASEITEM;

--OPEN C1 ; 
--  FETCH C1 INTO BASEITEM ; 
--CLOSE C1 ; 

IF(TRIM(BASEITEM) = '') THEN
    SET BASEITEM = ITEM;
END IF;
END  ; 
It has three variables defined as such:
IN ITEM CHAR(20) CCSID 37 DEFAULT  ''  , 
IN TYPE INT DEFAULT  1  , 
INOUT BASEITEM CHAR(20) DEFAULT  '' 
When I have EXECUTE INTO... this will not compile, if I use EXECUTE USING... it will compile but BASEITEM ends up being blank and the IF statements resolves as true.

I've tried following the the EXECUTE documentation and apparently the INTO can only work for CALL or VALUES INTO statements.  So I then tried to follow the VALUES INTO documentation, but can't figure out how to use a query with it.

Note*: I'd like to eventually change the concatenated variables to use parameter markers, but I was going to worry about that next.

I figured I should post the table being used:
MYLIB.MYTABLE
Column |Prefix|SEQ1|SEQ2|
Row 1  |aaa   |   1|   3|
Row 2  |aab   |   1|   3|
Row 3  |aabd  |   2|   4|
I'm essentially passing in a string and then removing the longest prefix from the string.  Then I returned the new string.  SEQ1 is just the type of prefix (either 1 or 2), SEQ2 is the length of the prefix.  
解决方案
EXECUTE can only be used for DML statements, not for queries, so there is really no alternative to declaring a dynamic cursor. Having said that, there is no "overhead in opening a cursor", no more than in executing a query in any other manner, because for any query a cursor will still be created and opened implicitly, if not explicitly.

Cursors are less efficient than set-based operations, e.g. when, instead of doing something like INSERT INTO table1 SELECT something FROM table2 WHERE... you open a cursor over table2 and insert rows into table1 one by one in a loop. Inefficiency in such case arises from performing single row inserts (or updates) vs. multirow updates, not from the cursor itself, and that is completely different from stating that "opening a cursor provides a lot of overhead". 

Since in your case you only fetch one record anyway, this loop vs. set argument does not apply.

                        
这篇关于DB2 / 400替代打开光标的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！