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

身边躺着一条狗

于 2024-07-24 04:10:03 发布

阅读量49

点赞数

文章标签： sql oracle 数据库

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

背景

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

通过DBMS_SQL包中的RETURN_RESULT过程，Oracle 12c目前支持类似的功能。这在实施迁移时非常有用。

CREATE PROCEDURE Get_Results 

 ( @p_id  int ) 

 AS 

 SELECT dscpt, crt_date FROM t1 WHERE id = @p_id 

 RETURN 0 

 GO

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>

1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
29.
30.
31.
32.
33.
34.
35.
36.
37.
38.
39.
40.
41.
42.
43.
44.
45.
46.
47.
48.
49.
50.
51.
52.
53.
54.
55.
56.
57.
58.
59.
60.
61.
62.
63.
64.
65.
66.
67.
68.
69.
70.
71.
72.
73.
74.
75.
76.
77.
78.
79.
80.
81.
82.
83.
84.
85.
86.
87.
88.
89.
90.
91.
92.
93.
94.
95.
96.
97.
98.
99.
100.
101.
102.
103.
104.
105.
106.
107.
108.
109.

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

一般来说，我们希望通过客户端应用来处理这些结果集，这可以通过DBMS_SQL包的 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>