Oracle12c中PL/SQL(DBMS_SQL)新特性之隐式语句结果（DBMS_SQL.RE

隐式数据结果特性将能简化从其他数据库到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中的游标时也会有一些限制和不足。

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!