Как я могу получить общее количество записей, выбранных курсором 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.