oracle 12c pl/sql语言,Oracle12c中PL/SQL(DBMS_SQL)新特性之隐式语句结果

隐式数据结果特性将能简化从其他数据库到Oracle12c存储过程迁移。

1. 背景

T-SQL中允许查询结果的隐式返回。例如：下面T-SQL存储过程隐式返回查询结果。

CREATE PROCEDURE Get_Results

( @p_id  int )

AS

SELECT dscpt, crt_date FROM t1 WHERE id = @p_id

RETURN 0

GO

通过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 #1

DESCRIPTION                    CREATED_DATE

------------------------------ --------------------

The value 1                    06-JUL-2013 21:19:45

1 row selected.

ResultSet #2

COUNT(*)

----------

3

1 row selected.

SQL> EXEC get_my_results;

PL/SQL procedure successfully completed.

ResultSet #1

COUNT(*)

----------

3

1 row selected.

SQL>

通过RETURN_RESULT过程返回DBMS_SQL的游标时也会存在一些限制和不足。

3. GET_NEXT_RESULT

一般来说，我们希望通过客户端应用来处理这些结果集，这可以通过DBMS_SQL包的 GET_NEXT_RESULT过程来解决。

下例通过DBMS_SQL包来执行该过程。由于过程返回不同记录结构的结果集，我们必须描述这些结果集以便进行处理。这可以通过结果集的列数来进行判断。

SET SERVEROUTPUT ON

DECLARE

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-2013

The column is COUNT:

l_count=3

PL/SQL procedure successfully completed.

SQL>

通过过程GET_NEXT_RESULT返回DBMS_SQL中的游标时也会有一些限制和不足。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/8484829/viewspace-2120969/，如需转载，请注明出处，否则将追究法律责任。