U0100082：GaussDB不支持 DBMS_SQL.GET_NEXT_RESULT 函数

描述

Oracle:DBMS_SQL.GET_NEXT_RESULT介绍：处理动态 SQL 查询返回的多个结果集 ， 用于从递归语句中获取下一个返回的结果集。

GaussDB:不支持DBMS_SQL.GET_NEXT_RESULT。

数据库类型与版本

• 源库类型与版本：UGO 支持的 Oracle 版本。
• 目标库类型与版本：UGO 支持的 GaussDB 版本。

语法示例

create or replace procedure DBMS_SQL_GET_NEXT_RESULT as
DECLARE
    l_cursor_id  INTEGER;       
    l_return     INTEGER;        
    l_id         NUMBER;          
    l_name       VARCHAR2(100); 
BEGIN
    l_cursor_id := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(
        l_cursor_id,
		 'SELECT id, name FROM test_table WHERE id <= 2 union all SELECT id, name FROM test_table WHERE id > 2' ,
        DBMS_SQL.NATIVE
    );
    l_return := DBMS_SQL.EXECUTE(l_cursor_id);
    LOOP
        BEGIN
            DBMS_SQL.GET_NEXT_RESULT(l_cursor_id, l_return);
            EXIT WHEN l_return = 0;
            DBMS_SQL.DEFINE_COLUMN(l_cursor_id, 1, l_id);
            DBMS_SQL.DEFINE_COLUMN(l_cursor_id, 2, l_name, 100);
            DBMS_OUTPUT.PUT_LINE('--- Result Set ---');
            LOOP
                 IF DBMS_SQL.FETCH_ROWS(l_cursor_id) > 0 THEN
                           DBMS_SQL.COLUMN_VALUE(l_cursor_id, 1, l_id);
                    DBMS_SQL.COLUMN_VALUE(l_cursor_id, 2, l_name);
                    DBMS_OUTPUT.PUT_LINE('ID: ' || l_id || ', NAME: ' || l_name);
                ELSE
                    EXIT;
                END IF;
            END LOOP;
        EXCEPTION
            WHEN OTHERS THEN
                EXIT; 
        END;
    END LOOP;
end ;
/

改造建议

直接注释掉DBMS_SQL.GET_NEXT_RESULT循环这部分逻辑。

GaussDB改写后为：

create or replace procedure DBMS_SQL_GET_NEXT_RESULT1() as
DECLARE
    l_cursor_id  NUMBER(38);
    l_return     NUMBER(38);  
    l_id         NUMBER; 
    l_name       VARCHAR2(100);  
BEGIN
    l_cursor_id := DBE_SQL.REGISTER_CONTEXT();
      DBE_SQL.SQL_SET_SQL(
        l_cursor_id,
         'SELECT id, name FROM test_table WHERE id <= 2 union all SELECT id, name FROM test_table WHERE id > 2' ,
        2
    );
        l_return := DBE_SQL.SQL_RUN(l_cursor_id);
   LOOP
        BEGIN
          DBE_SQL.RUN_AND_NEXT(l_cursor_id/*, l_return*/);
	  l_return := DBE_SQL.SQL_RUN(l_cursor_id);
          EXIT WHEN l_return = 0;*/
            DBE_SQL.SET_RESULT_TYPE(l_cursor_id, 1, l_id);
            DBE_SQL.SET_RESULT_TYPE(l_cursor_id, 2, l_name, 100);
            DBE_OUTPUT.PRINT_LINE('--- Result Set ---');
            LOOP
                              IF DBE_SQL.NEXT_ROW(l_cursor_id) > 0 THEN
                              DBE_SQL.GET_RESULTS(l_cursor_id, 1, l_id);
                    DBE_SQL.GET_RESULTS(l_cursor_id, 2, l_name);
                    DBE_OUTPUT.PRINT_LINE('ID: ' || l_id || ', NAME: ' || l_name);
                ELSE
                    EXIT;
                END IF;
            END LOOP;
        EXCEPTION
            WHEN OTHERS THEN
                EXIT;
        END;
   END LOOP;
   
END;
/