隐式数据结果特性将能简化从其他数据库到Oracle12c存储过程迁移。1. 背景T-sql中允许查询结果的隐式返回。例如：下面T-sql存储过程隐式返回查询结果。CREATE PROCEDURE Get_Results( @p_id int )ASSELECT dscpt,crt_date FROM t1 WHERE id = @p_idRETURN 0GO通过DBMS_sql包中的RETURN_RESULT过程，Oracle 12c目前支持类似的功能。这在实施迁移时非常有用。2. RETURN_RESULT不是显式定义参照游标（ref cursor）输出参数，DBMS_sql包中的RETURN_RESULT过程允许结果隐式传出，看下例。CREATE table t1 ( id NUMBER, dscpt VARCHAR2(30), crt_date DATE);INSERT INTO t1 VALUES (1,‘The value 1‘,SYSDATE-2);INSERT INTO t1 VALUES (2,‘The value 2‘,SYSDATE-1);INSERT INTO t1 VALUES (3,‘The value 3‘,SYSDATE);COMMIT;现在我们创建一个过程来返回一个或多个结果集。CREATE OR REPLACE PROCEDURE get_results (p_id IN NUMBER DEFAULT NULL)AS l_cursor_1 SYS_REFCURSOR; l_cursor_2 SYS_REFCURSOR;BEGIN IF p_id IS NOT NULL THEN OPEN l_cursor_1 FOR SELECT dscpt,crt_date FROM t1 WHERE id = p_id; DBMS_sql.RETURN_RESULT(l_cursor_1); END IF; OPEN l_cursor_2 FOR SELECT COUNT(*) FROM t1; DBMS_sql.RETURN_RESULT(l_cursor_2);END;/我们从sql*plus中执行该过程时，将会自动显式显示语句结果。sql> EXEC get_results(1);PL/sql procedure successfully completed.ResultSet #1DESCRIPTION CREATED_DATE------------------------------ --------------------The value 1 06-JUL-2013 21:19:451 row selected.ResultSet #2 COUNT(*)---------- 31 row selected.sql> EXEC get_my_results;PL/sql procedure successfully completed.ResultSet #1 COUNT(*)---------- 31 row selected.sql>通过RETURN_RESULT过程返回DBMS_sql的游标时也会存在一些限制和不足。3. GET_NEXT_RESULT一般来说，我们希望通过客户端应用来处理这些结果集，这可以通过DBMS_sql包的 GET_NEXT_RESULT过程来解决。下例通过DBMS_sql包来执行该过程。由于过程返回不同记录结构的结果集，我们必须描述这些结果集以便进行处理。这可以通过结果集的列数来进行判断。SET SERVEROUTPUT ONDECLARE l_sql_cursor PLS_INTEGER; l_ref_cursor SYS_REFCURSOR; l_return PLS_INTEGER; l_col_cnt PLS_INTEGER; l_desc_tab DBMS_sql.desc_tab; l_count NUMBER; l_dscpt t1.dscpt%TYPE; l_crt_date t1.crt_date%TYPE;BEGIN -- 执行过程 l_sql_cursor := DBMS_sql.open_cursor(treat_as_client_for_results => TRUE); DBMS_sql.parse(c => l_sql_cursor, statement => ‘BEGIN get_results(1); END;‘, language_flag => DBMS_sql.native); l_return := DBMS_sql.execute(l_sql_cursor); -- 循环遍历每个结果集 LOOP -- 获取下个结果集 BEGIN DBMS_sql.get_next_result(l_sql_cursor,l_ref_cursor); EXCEPTION WHEN NO_DATA_FOUND THEN EXIT; END; -- 检查结果集列数 l_return := DBMS_sql.to_cursor_number(l_ref_cursor); DBMS_sql.describe_columns (l_return,l_col_cnt,l_desc_tab); l_ref_cursor := DBMS_sql.to_refcursor(l_return); -- 根据列数处理结果集 CASE l_col_cnt WHEN 1 THEN DBMS_OUTPUT.put_line(‘The column is COUNT:‘); FETCH l_ref_cursor INTO l_count; DBMS_OUTPUT.put_line(‘l_count=‘ || l_count); CLOSE l_ref_cursor; WHEN 2 THEN DBMS_OUTPUT.put_line(‘The columns are DSCPT and CRT_DATE:‘); LOOP FETCH l_ref_cursor INTO l_dscpt,l_crt_date; EXIT WHEN l_ref_cursor%NOTFOUND; DBMS_OUTPUT.put_line(‘l_dscpt=‘ || l_dscpt || ‘ ‘ || ‘l_crt_date=‘ || TO_CHAR(l_crt_date,‘DD-MON-YYYY‘)); END LOOP; CLOSE l_ref_cursor; ELSE DBMS_OUTPUT.put_Line(‘I wasn‘‘t expecting that!‘); END CASE; END LOOP;END;/The columns are DSCPT and CRT_DATE:l_dscpt=The value 1 l_crt_date=06-JUL-2013The column is COUNT:l_count=3PL/sql procedure successfully completed.sql>通过过程GET_NEXT_RESULT返回DBMS_sql中的游标时也会有一些限制和不足。
