Technical Blog Post
Abstract
2011年12月13日 上午5:15
Body
更佳的结果 ——在嵌入式程序中返回丰富的存储过程结果集（二）
Tony Poirier
原文链接: http://www.ibmsystemsmag.com/ibmi/developer/general/Better-Results/?page=1
从一个存储过程中返回结果集
一个存储过程可以返回最多32767个结果集。返回结果集有以下三种方法。
1. 如果在一个存储过程中执行了SET RESULT SETS语句，这个语句就标识了结果集，并使结果集按照其指定的顺序被返回；
2. 如果在一个存储过程中没有执行SET RESULT SETS语句，并且所有的游标都没有明确的WITH RETURN子句，每个由存储过程打开并在存储过程返回时仍然打开的游标都标识了一个结果集。结果集按照游标被打开的顺序返回。注意，任何一个退出时仍保持SQL游标打开的程序，在C、COBOL、或RPG程序中被SQL的CALL语句调用时，都将会返回结果集给调用者。如果本意是不想返回结果集，则该程序应该关闭它所使用的游标。
3. 如果在一个存储过程中没有执行SET RESULT SETS语句，并且任意一个游标都有明确的WITH RETURN子句，则每个被定义了WITH RETURN子句的游标，如果被存储过程打开并在存储过程返回时仍然打开的，则会标识一个结果集。结果集按照游标被打开的顺序返回。
当使用一个打开的游标返回结果集时，返回行的起始位置是当前游标指向的行。如果CALL语句返回466这个SQLCODE，说明这是一个存储过程返回的结果集。
下面的例子中没有使用SET RESULT SETS语句。如果想要获得更多关于SET RESTULT SETS的命令， 请参阅《DB2 for i SQL Reference》。
“示例代码2”是一个使用CREATE PROCEDURE语句定义返回两个结果集的例子。注意，任何打开游标的存储过程都能返回结果集，不仅仅是示例中的SQL存储过程。
CREATE PROCEDURE large_orders (in minimum_order int) LANGUAGE SQL READS SQL DATA DYNAMIC RESULT SETS 2 BEGIN DECLARE cust_curs CURSOR WITH RETURN TO CALLER FOR SELECT custid, orderid from cust_order WHERE ordertotal >= minimum_order; DECLARE item_curs CURSOR WITH RETURN TO CALLER FOR SELECT ORDERID, ITEM, QUANTITY FROM order_item WHERE orderid in (SELECT orderid FROM cust_order WHERE ordertotal >= minimum_order); OPEN cust_curs; /* this will be first result set returned from procedure */ OPEN item_curs; /* this will be second result set returned from procedure */ END |
示例代码 2
“示例代码 3”是一个使用结果集的示例，使用嵌入式C程序调用存储过程，存储过程的结果集数量和格式都是已知的。注意，被调用的存储过程就是前面“示例代码2”中的SQL存储过程，会返回结果集。
/*************************************************************/ /* Declare result set locators and other variables. */ /*************************************************************/ EXEC SQL BEGIN DECLARE SECTION; static SQL TYPE IS RESULT_SET_LOCATOR cust_locator, item_locator; long order_minimum,order_id,item_id,amount, cust_id; EXEC SQL END DECLARE SECTION; /*************************************************************/ /* Call stored procedure P1. */ /*************************************************************/ EXEC SQL CALL LARGE_ORDERS(:order_minimum); if(SQLCODE==466) { /* SQLCODE of +466 indicates that result sets were returned from CALL */ /*************************************************************/ /* Establish a link between each result set and its */ /* locator using the ASSOCIATE LOCATORS. */ /*************************************************************/ EXEC SQL ASSOCIATE LOCATORS (:cust_locator, :item_locator) WITH PROCEDURE LARGE_ORDERS; /*************************************************************/ /* Associate a cursor with each result set. */ /*************************************************************/ EXEC SQL ALLOCATE CUST_CURSOR CURSOR FOR RESULT SET :cust_locator; EXEC SQL ALLOCATE ITEM_CURSOR CURSOR FOR RESULT SET :item_locator; /*************************************************************/ /* Fetch the result set rows into host variables. */ /*************************************************************/ while(SQLCODE==0) { EXEC SQL FETCH CUST_CURSOR INTO :order_id, :cust_id; } EXEC SQL CLOSE CUST_CURSOR; while(SQLCODE==0) { EXEC SQL FETCH ITEM_CURSOR INTO :order_id,:item_id, :amount; } EXEC SQL CLOSE ITEM_CURSOR; } |
示例代码 3
“示例代码 4”是一个调用方不知道会返回何种结果集的例子。
EXEC SQL BEGIN DECLARE SECTION; static volatile SQL TYPE IS RESULT_SET_LOCATOR *locator1; EXEC SQL END DECLARE SECTION; /*************************************************************/ /* Call stored procedure P2. */ /*************************************************************/ EXEC SQL CALL P2(:in1, :out1, ...); if(SQLCODE==466){ /*************************************************************/ /* Determine how many result sets P2 returned, using the */ /* statement DESCRIBE PROCEDURE. */ /*************************************************************/ EXEC SQL ALLOCATE DESCRIPTOR ‘proc_descriptor’; EXEC SQL DESCRIBE PROCEDURE P2 USING SQL DESCRIPTOR ‘proc_descriptor’; EXEC SQL GET DESCRIPTOR ‘proc_descriptor’ :result_set_count = DB2_RESULT_SETS_COUNT; for (i = 0; i < result_set_count; i++){ EXEC SQL GET DESCRIPTOR ‘proc_descriptor’ value :i :locator1 = DB2_RESULT_SET_LOCATOR; /* set result set locator from descriptor used by DESCRIBE PROCEDURE statement. */ EXEC SQL ALLOCATE C1 CURSOR FOR RESULT SET :locator1; /*************************************************************/ /* Use the statement DESCRIBE CURSOR to determine the */ /* format of the result set. */ /*************************************************************/ EXEC SQL ALLOCATE DESCRIPTOR ‘resset_descriptor’; EXEC SQL DESCRIBE CURSOR C1 USING SQL DESCRIPTOR ‘resset_descriptor’; /*************************************************************/ /* Fetch the result set rows into the descriptor used */ /* in the DESCRIBE CURSOR statement. */ /*************************************************************/ while(SQLCODE==0) { EXEC SQL FETCH C1 USING SQL DESCRIPTOR ‘resset_descriptor’; /* Use GET DESCRIPTOR to retrieve the values returned on the */ /* FETCH into host variables. */ } EXEC SQL CLOSE C1; } } |
示例代码 4
返回到客户端 (RETURN TO CLIENT) VS. 返回到调用者(RETURN TO CALLER)
默认情况下，存储过程的结果集会返回给它的调用者（RETURN TO CALLER）。如果结果集被定义成RETURN TO CLIENT，它将会被返回给这个存储过程最外层的调用者。注意，最外层的调用者可能是System i Access Family客户端程序、JDBC或SQL调用级接口。ASSOCIATE LOCATOR和DESCRIBE PROCEDURE语句将只能找到该调用返回的结果集。在图2中，SALES_SUMMARY是最外层的调用者，即客户端（client）。因此，SALES_PROC存储过程返回的RETURN TO CLIENT结果集，只能从SALES_SUMMARY程序中获得。
结果集的限制条件
如果存储过程返回结果集，并且该结果集尚未被处理，有一个限制是，这样的存储过程最多有256个。所有的结果集都需要分配一个游标，并且在结果集处理完成时关闭游标。如果返回结果集的存储过程个数超过256个，SQLCODE为SQL7049的错误码将会被返回。请注意，有可能会遇到这样的问题：在之前的版本中程序没有受到这样的限制，而在IBM i 7.1版本中却受到了限制。这是因为7.1之前的版本，RPG、COBOL和C程序调用存储过程，结果集不能被调用者读取，会被丢弃，所以不会受到上述限制。
在调用方程序中分配的游标
分配给结果集的游标可以和声明于程序中的游标一样使用。在存储过程中，如果结果集游标以相应的方式被声明，则UPDATE WHERE CURRENT语句，DELETE WHERE CURRENT语句以及可回滚的游标操作都可以通过这个已分配的游标完成。
更现代的应用程序
有了i系统7.1版本的DB2，C、C++、ILE COBOL、OPM COBOL以及ILE RPG程序就能使用存储过程返回的结果集了。新增的SQL语句有：ASSOCIATE LOCATORS, ALLOCATE CURSOR, DESCRIBE PROCEDURE以及DESCRIBE CURSOR。由于有了这些新功能的支持，现在，嵌入式程序可以充分利用存储过程，做出模块化程度更高，更现代的应用程序。存储过程中可以包含精巧的SQL逻辑，又能被C、COBOL或RPG程序使用。通过允许调用“黑盒”存储过程，以及使用结果集数据，存储过程也能扩展和提高程序员的能力。同时也允许了不同的程序员和解决方案提供商提供自己的黑盒存储过程。
您可以联系IBM i 实验室服务团队来帮助设计或实现新技术的应用，同样包括上文所提到的技术。您可以查阅实验室服务网站或联系Mike Cain来获取更多的信息与帮助。
作者Tony Poirier是IBM 明尼苏达州罗切斯特实验室的一名资深软件工程师。他从事IBM i系统DB2的SQL开发工作已有21年。
原文链接：http://www.ibmsystemsmag.com/ibmi/developer/general/Better-Results/?page=1
UID
ibm11145824