Как вернуть более одного запроса на выборку в одной процедуре

Я хотел бы спросить, как я могу распечатать вывод в процедуре более чем одного оператора. Предположим, вы хотите показать количество строк dba_objects и segments. Но я не могу использовать dbms_sql.return_result, моя версия 11g.

Что-то вроде,

create or replace procedure get_rows_count
(
  cursor1 out SYS_REFCURSOR,
  cursor2 out SYS_REFCURSOR
)
as
begin
    open cursor1 for select count(*) from dba_objects;
    open cursor2 for select count(*) from dba_segments;
end get_rows_count;
/

🤔 А знаете ли вы, что...
Oracle Database Cloud Service позволяет разработчикам и организациям развертывать базы данных в облаке.


1
65
2

Ответы:

Вы можете использовать знаменитый DBMS_OUTPUT.PUT_LINE() вместе с суффиксом %ROWCOUNT для вашего случая, например

SET serveroutput ON
CREATE OR REPLACE PROCEDURE get_rows_count(
                                            cursor1 OUT SYS_REFCURSOR,
                                            cursor2 OUT SYS_REFCURSOR,
                                            count1  OUT INT,
                                            count2  OUT INT
                                          ) AS
  cur_rec_obj user_objects%ROWTYPE;
  cur_rec_seg user_segments%ROWTYPE;      
BEGIN
    OPEN cursor1 FOR SELECT * FROM user_objects;
    LOOP
      FETCH cursor1 INTO cur_rec_obj;  
      EXIT WHEN cursor1%NOTFOUND;
    END LOOP;

    OPEN cursor2 FOR SELECT * FROM user_segments;
    LOOP
      FETCH cursor2 INTO cur_rec_seg;  
      EXIT WHEN cursor2%NOTFOUND;
    END LOOP;

    count1 := cursor1%ROWCOUNT;
    count2 := cursor2%ROWCOUNT;
    DBMS_OUTPUT.PUT_LINE(count1);
    DBMS_OUTPUT.PUT_LINE(count2);    
      
END;
/

и вы можете вызывать из окна SQL PL/SQL Developer следующим образом:

DECLARE
 v_cursor1   SYS_REFCURSOR;
 v_cursor2   SYS_REFCURSOR;
 v_count1    INT;
 v_count2    INT;
BEGIN
  get_rows_count(v_cursor1, v_cursor2, v_count1, v_count2 );
END;
/

Решено

Предположим, вы хотите показать количество строк dba_objects и segments.

Я предположил это. Вывод: так делать нельзя. Если вы хотите получить количество строк, например. dba_objects, то вам стоит просто

select count(*) from dba_objects;

в любом варианте, который вы хотите (чистый SQL, функция, которая возвращает это число, процедура с параметром OUT (худший вариант), ...). Но создание процедуры, использующей для этой цели ref-курсор,... ну, неправильно.


Если я вас неправильно понял, то: процедура, которую вы написали, в порядке. Вы можете вызвать его из другой процедуры PL/SQL (именованной или анонимной), получить результат в переменную и что-то с ним сделать (например, отобразить).

Ваша процедура (выбирает из таблиц Скотта, у меня нет доступа к представлениям DBA_):

SQL> CREATE OR REPLACE PROCEDURE get_rows_count (cursor1  OUT SYS_REFCURSOR,
  2                                              cursor2  OUT SYS_REFCURSOR)
  3  AS
  4  BEGIN
  5     OPEN cursor1 FOR SELECT * FROM emp;
  6
  7     OPEN cursor2 FOR SELECT * FROM dept;
  8  END get_rows_count;
  9  /

Procedure created.

Как это назвать? См. строку № 8:

SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
  2     rc1  SYS_REFCURSOR;
  3     rc2  SYS_REFCURSOR;
  4     --
  5     rw1  emp%ROWTYPE;
  6     rw2  dept%ROWTYPE;
  7  BEGIN
  8     get_rows_count (rc1, rc2);
  9
 10     DBMS_OUTPUT.put_line ('Employees -----------');
 11
 12     LOOP
 13        FETCH rc1 INTO rw1;
 14
 15        EXIT WHEN rc1%NOTFOUND;
 16
 17        DBMS_OUTPUT.put_line (rw1.ename);
 18     END LOOP;
 19
 20     --
 21     DBMS_OUTPUT.put_line ('Departments ---------');
 22
 23     LOOP
 24        FETCH rc2 INTO rw2;
 25
 26        EXIT WHEN rc2%NOTFOUND;
 27
 28        DBMS_OUTPUT.put_line (rw2.dname);
 29     END LOOP;
 30
 31     DBMS_OUTPUT.put_line ('First ref cursor: ' || rc1%ROWCOUNT);
 32     DBMS_OUTPUT.put_line ('Second ref cursor: ' || rc2%ROWCOUNT);
 33  END;
 34  /

Результат:

Employees -----------
SMITH
ALLEN
WARD
JONES
MARTIN
BLAKE
CLARK
SCOTT
KING
TURNER
ADAMS
JAMES
FORD
MILLER
Departments ---------
ACCOUNTING
RESEARCH
SALES
OPERATIONS
First ref cursor: 14
Second ref cursor: 4

PL/SQL procedure successfully completed.

SQL>