Technical Blog Post
Abstract
更佳的结果 ——在嵌入式程序中返回丰富的存储过程结果集
Body
更佳的结果 ——在嵌入式程序中返回丰富的存储过程结果集（一）
Tony Poirier
原文链接: http://www.ibmsystemsmag.com/ibmi/developer/general/Better-Results/?page=1
存储过程可以用于封装代码，允许逻辑保持一贯性，并且可以被多个应用程序共享。在分布式环境中，存储过程特别有助于减轻网络流量。它还可以把逻辑处理集中到一段代码中，并被多个程序使用。
存储过程可以通过出参把数据返回给调用者，但这并不是存储过程返回信息的唯一方式。存储过程可以返回结果集（通过select语句得到的结果）给调用者，可以和调用者共享数据。由于调用者可以通过存储过程访问结果集，这就增加了一个存储过程可以提供的信息量。复杂逻辑和复杂查询可以被封装在一个单独的存储过程中，任何应用程序都可以调用这个存储过程，并且处理返回的结果集。一个存储过程甚至可以返回多个结果集给调用者。关于存储过程结果集的逻辑流程，请参见下图1。
一个存储过程既可以是SQL存储过程，也可以是外部存储过程（一段用宿主语言编写的代码，并且通过CREATE PROCEDURE语句定义成为存储过程），还可以是任意程序。在IBM i操作系统上的DB2中，只要是可以通过SQL语言中的CALL语句调用的代码段或程序都可以被认为是存储过程。并且只要程序使用了OPEN或SET RESULT SETS语句，都可以返回结果集给调用者。 从某种程度上说，使用一个存储过程返回结果集给调用它的应用程序，和使用CL命令OPNQYF返回结果集给应用程序是类似的。但是存储过程的能力要远远强于OPQRYF，并且功能也更丰富。 在已有的在几个版本中，可以使用System i Access Family客户端程序（如ODBC），SQL调用级接口（Call Level Interface），JDBC，或是远程系统访问时用到的DRDA协议，对结果集进行访问；而RPG、COBOL以及C程序就没有那么幸运了，它们还不可以访问结果集。然而在IBM i 7.1版本中，这一点发生了变化。7.1版本增加了对嵌入式C、C++、ILE COBOL、OPM COBOL，以及ILE RPG程序访问结果集的支持。新的SQL语句ASSOCIATE LOCATOR、 ALLOCATE CURSOR、 DESCRIBE PROCEDURE，以及DESCRIBE CURSOR就是嵌入式程序用于定位结果集并使用其数据的工具。这篇文章介绍了如何使用这些新的SQL语句，来获取嵌入式程序中存储过程返回的结果集的方法。 一个存储过程可以返回两种不同类型的结果集：游标结果集和数组结果集。这篇文章主要举了游标结果集的例子。值得注意的是，存储过程的调用者并不需要知道返回的是游标结果集还是数组结果集。如果想了解关于数组结果集的信息，请参见《DB2 for i SQL Reference》手册。 本文将按大家在后面使用这些SQL语句的一般顺序，逐一进行介绍。说起获得更优结果这个话题，大家可以从回答如下问题开始：结果集数据的数据类型是众所周知的，还是说结果集的使用者需要获取并了解结果集数据的大小和数据结构？这些问题的答案将会引领我们决定使用上述那些新SQL语句中的哪一条。 结果集定位器是一个调用程序中的宿主变量（host variable），它在存储过程的特定结果集和调用方程序的游标之间建立了一个链接。如： 用C声明一个结果集定位器：my_locator Static SQL TYPE IS RESULT_SET_LOCATOR my_locator; 用COBOL声明一个结果集定位器： MY-LOCATOR 01 MY-LOCATOR SQL TYPE IS RESULT_SET_LOCATOR. 用RPG声明一个结果集定位器：MYLOCATOR D MYLOCATOR S SQLTYPE(RESULT_SET_LOCATOR)
结果集定位器（Result Set Locators）
通过ASSOCIATE LOCATOR语句，或是通过一个跟在DESCRIBE PROCEDURE语句后面的GET DESCRIPTOR语句，结果集定位器就会被分配到一个结果集的特定实例中。
ASSOCIATE LOCATOR语句
ASSOCIATE LOCATOR语句用来获取存储过程返回的每个结果集的结果集值。下面是一个关于返回3个结果集的存储过程inventory_proc的例子。
ASSOCIATE LOCATORS (:inventory_locator1, :inventory_locator2, :inventory_locator3) WITH PROCEDURE inventory_proc; 这三个定位器中每一个，在后面都可能被用于单独的ALLOCATE CURSOR语句中。 |
当使用ASSOCIATE LOCATOR语句时，请注意以下几点：
1. ASSOCIATE LOCATOR语句必须在CALL语句调用存储过程的同一个调用中完成；
2. 如果存储过程返回多个结果集，所有的结果集定位器都要在同一个ASSOCIATE LOCATOR语句中设定，不能用多个ASSOCIATE LOCATOR进行设定；
3. 如果有多个CALL语句调用存储过程，ASSOCIATE LOCATOR语句返回最后一个CALL语句的结果集定位器；
4. 当使用DRDA连到另一个系统时，在ASSOCIATE LOCATOR语句中，最好在存储过程名称前加上其所在模式（schema）的名称进行限定；否则i系统上的DB2数据库需要搜索远端系统的系统表来确定哪个才是要用的有效的存储过程；
5. 如果在ASSOCIATE LOCATOR语句中，设定的结果集定位器变量的个数少于存储过程返回的结果集个数，将会返回一个错误码为SQL0494的告警；
6. 如果在ASSOCIATE LOCATOR语句中，设定的结果集个数多于存储过程返回结果集的个数，将会返回一个错误码为SQL0387的告警；
7. 不论上述两种告警（SQL0494/SQL0387）哪个发生，分配给定位器的结果集顺序和返回结果集的顺序是一致的。
ALLOCATE CURSOR语句
ALLOCATE CURSOR语句用来定义一个SQL游标，并且将其和一个结果集定位器变量关联。这种游标的使用方式和在程序中声明并打开的一般游标的使用方式完全相同。下面是一个使用ALLOCATE CURSOR语句的例子。
ALLOCATE INVENTORY_CURSOR CURSOR FOR RESULT SET :inventory_locateor1; |
当使用ALOCATE CURSOR语句时，请注意这几点：
1. 结果集定位器变量必须是一个由ASSOCIATE LOCATORS或DESCRIBE PROCEDURE语句返回的、有效的，定位器变量值；
2. 分配游标时，结果集定位器变量的值会被使用到。但如果定位器变量的值随后再发生变化，不会影响已分配的游标；
3. 源程序中，与不同游标关联的结果集定位器变量的值应各异；
4. 关闭一个已分配的游标（allocated cursor），也会关闭在存储过程中定义的相关的结果集的游标。
DESCRIBE PROCEDURE语句
DESCRIBE PROCEDURE语句用来获取有关存储过程返回的结果集的信息。这些信息，如结果集个数，会被放到一个描述符（descriptor）或SQLDA中。如果应用程序已知道存储过程返回的结果集都是什么，就不需要使用DESCRIBE PROCEDURE语句了。
下面是一些使用DESCRIBE PROCEDURE语句来获取存储过程SALES_PROC结果集信息的例子。第一个例子使用了描述符；第二个例子使用了SQLDA。
1. DESCRIPTOR的例子及可用值： DESCRIBE PROCEDURE SALES_PROC USING SQL DESCRIPTOR ‘SALES_DESCRIPTOR’; DB2_RESULT_SETS_COUNT表明结果集的总数。0表示没有结果集被返回。 每个结果集都有一个描述符区域项： DB2_RESULT_SET_LOCATOR包含了和结果集关联的结果集定位器的值； DB2_RESULT_SET_ROWS包含了结果集中估算的行数。-1表示在结果集中没有可用的行数估算值； DB2_CURSOR_NAME包含了存储过程用于返回结果集的游标名称。 2. SQLDA的例子及可用值： DESCRIBE PROCEDURE SALES_PROC USING :sales_sqlda; SQLD表示结果集的总数。0表示没有结果集被返回。 对于每一个SQLVAR： SQLDATA字段包含了和结果集关联的结果集定位器的值； SQLD_RESULT_SET_ROWS字段（利用了SQLIND字段）包含了结果集中估算的行数。-1表示在结果集中没有可用的行数估算值； SQLNAME字段包含了存储过程用于返回结果集的游标名称。 |
在使用DESCRIBE PROCEDURE语句时，这几点请注意：
1. DESCRIBE PROCEDURE必须在CALL语句调用存储过程的同一个调用中完成；
2. DESCRIBE PROCEDURE语句不会返回程序所期望的参数信息；
3. 当使用DRDA连到另一个系统时，在DESCRIBE PROCEDURE语句中，最好在存储过程名称前加上其所在模式（schema）的名称对其进行限定；否则i系统上的DB2数据库需要搜索远端系统的系统表来确定哪个才是要用的有效的存储过程。
下面的“示例代码1”中给出了使用DESCRIBE PROCEDURE语句的示例代码段。
EXEC SQL CALL LARGE_ORDERS(:order_threshold); if (SQLCODE==466){ EXEC SQL ALLOCATE DESCRIPTOR 'large_order_descriptor'; EXEC SQL DESCRIBE PROCEDURE LARGE_ORDERS USING SQL DESCRIPTOR 'large_order_descriptor'; EXEC SQL GET DESCRIPTOR 'large_order_descriptor' :result_sets_count = DB2_RESULT_SETS_COUNT; for (i = 1; i <= result_sets_count; i++) { EXEC SQL GET DESCRIPTOR 'large_order_descriptor' VALUE :i :curs_name = DB2_CURSOR_NAME, :large_order_locator = DB2_RESULT_SET_LOCATOR, :num_rows = DB2_RESULT_SET_ROWS; } } |
示例代码 1
DESCRIBE CURSOR语句
DESCRIBE CURSOR语句用来获取为结果集而打开或分配的游标的信息。这些信息，如列信息，会被放到一个描述符（descriptor）或SQLDA中。
下面是一些使用DESCRIBE CURSOR语句来获取游标INVENTORY_CURSOR信息的例子。第一个例子使用了描述符；第二个例子使用了SQLDA。
1. DESCRIPTOR的例子： DESCRIBE CURSOR INVENTORY_CURSOR USING SQL DESCRIPTOR ‘INVENTORY_DESCRIPTOR’; 执行DESCRIBE CURSOR语句后的描述符区域的内容，和执行DESCRIBE一条SELECT语句后的描述如区域的内容是一样的，但增加了以下的内容，这些内容可以通过GET DESCRIPTION语句获得： DB2_CURSOR_HOLDABILITY表示游标是否将会被一个commit操作关闭； DB2_CURSOR_RETURNABILITY表示游标指向的结果集是否可被返回； DB2_CURSOR_SCROLLABILITY表示游标是否是可回滚的； DB2_CURSOR_SENSITIVITY表示游标的灵敏级别； DB2_CURSOR_UPDATEABLITY表示游标是否能被用于UPDATE WHERE CURRENT语句中； DB2_RESULT_SET_ROWS包含了结果集中估算的行数。-1表示在结果集中没有可用的行数估算值。注意，这个变量在item级别有效，每一个item包含了相同的DB2_RESULT_SET_ROWS值。 2. SQLDA的例子： DESCRIBE CURSOR INVENTORY_CURSOR USING :inventory_sqlda; 执行DESCRIBE CURSOR语句后SQLDA的内容，和执行DESCRIBE一条SELECT语句后的SQLDA的内容是一样的，但增加了以下的内容： SQLD_RESULT_SET_ROWS字段（利用了SQLIND字段）包含了结果集中估算的行数。-1表示在结果集中没有可用的行数估算值。注意每一个SQLVAR entry都有相同的SQLD_RESULT_SET_ROWS值。 |
使用DESCRIBE CURSOR语句时，同样需要注意以下这几点：
1. DESCRIBE CURSOR必须在游标被打开或分配的同一个调用中完成，这个游标不能已被关闭；
2. DESCRIBE CURSOR可以在一个游标被声明并打开时完成，或在一个游标被分配给一个结果集时完成。
3. 如果应用程序已经知道了游标的格式，则不需要使用DESCRIBE CURSOR来获取游标信息了。
UID
ibm11145830