Как я могу получить общее количество записей, выбранных курсором sysref?

ФОН

Я работаю над веб-приложением, которое вызывает хранимые процедуры PLSQL для извлечения и обработки информации. В одном таком случае база данных имеет две хранимые процедуры; тот, который выбирает общее количество записей для данного набора параметров, и тот, который возвращает фактические записи с теми же параметрами, плюс параметры разбиения на страницы (min и max rownum).

EX (не фактический код):

PROCEDURE get_records_count(
    p_first_name IN record_table.first_name%TYPE,
    p_last_name IN record_table.last_name%TYPE,
    p_resultCursor OUT sys_refcursor
)
IS BEGIN 
    OPEN p_resultCursor FOR 
    SELECT count(*) from record_table 
        WHERE first_name LIKE (p_first_name || '%') 
        OR last_name LIKE (p_last_name || '%');
END;


PROCEDURE get_records(
    p_first_name IN record_table.first_name%TYPE,
    p_last_name IN record_table.last_name%TYPE,
    p_min IN NUMBER,
    p_max IN NUMBER,
    p_resultCursor OUT sys_refcursor
)
IS BEGIN 
    OPEN p_resultCursor FOR 
    SELECT * from record_table 
        WHERE first_name LIKE (p_first_name || '%') 
        OR last_name LIKE (p_last_name || '%')
        AND rownum >= p_min AND rownum <= p_max;
END;

Считать или нет, что это хорошая идея, выходит за рамки моей позиции. Проблема заключается в том, что при изменении любой хранимой процедуры результаты не совпадают. Краткосрочное исправление состоит в том, чтобы просмотреть обе хранимые процедуры, определить, какие критерии выбора из какой хранимой процедуры подходят, а затем отредактировать другую хранимую процедуру, чтобы они обе совпадали.

В качестве долгосрочного исправления я хотел бы изменить процедуру get_records_count, чтобы она вызывала процедуру get_records, а затем возвращала общее количество записей, которые будут возвращены из полученного sys_refcursor.

EX:

PROCEDURE get_records_count(
    p_first_name IN record_table.first_name%TYPE,
    p_last_name IN record_table.last_name%TYPE,
    p_resultCursor OUT sys_refcursor
)
AS
v_recordsSelectedCursor sys_refcursor; 
BEGIN 
    /*I understand that I will need to change some logic in get_records to
      handle the case in which p_max is set to zero. 
      It should take this case to not apply an upper limit.*/ 
    get_records(p_first_name,p_last_name,0,0,v_recordsSelectedCursor);
    /*This is where I really have NO idea what I'm doing.
      Hopefully, you can infer what I'm trying to do. */ 
    OPEN p_resultCursor FOR
        SELECT count(*) FROM v_recordsSelectedCursor;  
END;

АКТУАЛЬНЫЙ ВОПРОС

Как я могу выбрать количество записей, которые будут возвращены для sys_refcursor? Полученное число должно быть возвращено внутри sys_refcursor

Источник

2 ответа

Решение

Курсор - это просто спецификация для извлечения строк - он не знает, сколько строк будет возвращено, пока не получит их все.

Любой метод, включающий повторный вызов, может привести к противоречивым результатам, если вы не используете dbms_flashback.enable_at_time в начале процедуры (и отключите ее в конце). Конечно, это также влияет на производительность.

Единственный способ получить курсор, чтобы включить его общее количество строк, это включить аналитические count(*) over () выражение в select список.

Источник

Как я могу выбрать количество записей, которые будут возвращены для sys_refcursor? Полученное число должно быть возвращено внутри sys_refcursor.

Вы можете изменить свою процедуру, как показано ниже:

PROCEDURE get_records_count(
    p_first_name IN record_table.first_name%TYPE,
    p_last_name IN record_table.last_name%TYPE,
    p_resultCursor OUT sys_refcursor
)
AS
v_recordsSelectedCursor sys_refcursor; 
type y is table of record_table%rowtype;
z y; 
num number:=0;
BEGIN 

    get_records(p_first_name,p_last_name,0,0,v_recordsSelectedCursor);

    fetch  v_recordsSelectedCursor bulk collect into z;

    ---taking the count of refcursor
    num:=z.count;

    OPEN p_resultCursor FOR
    select num from dual;         
    dbms_output.put_line(num);

END;

Посмотреть демо:

 SQL> SELECT count(*)
     FROM emp;

     COUNT(*)
     ----------
      14

SQL> CREATE OR REPLACE PROCEDURE sys_ref_rec_cnt (var OUT sys_refcursor)
  2  AS
  3  BEGIN
  4  OPEN var FOR
  5  SELECT *
  6    FROM emp;
  7  END;
  8  /

Procedure created.    

 SQL> DECLARE
  2  x     sys_refcursor;
  3  k     sys_refcursor;
  4  TYPE y IS TABLE OF emp%ROWTYPE;
  5  z      y;
  6  num   NUMBER        := 0;
  7  BEGIN
  8  sys_ref_rec_cnt (x);
  9  
 10  FETCH x
 11  BULK COLLECT INTO z;
 12  
 13  num :=z.count;
 14  
 15  open k for
 16   select num from dual;
 17   
 18  DBMS_OUTPUT.put_line ('No. Of records-->'||num);
 19  END;
 20  /

No. Of records-->14

PL/SQL procedure successfully completed.
Источник
Другие вопросы по тегам