Проверка результата перед выполнением запроса
При запуске хранимой процедуры для выборки некоторых строк, во-первых, я хочу проверить, вернет ли запрос строку перед отправкой результата, а во-вторых, можно ли проверить, не запуская один и тот же запрос дважды.
Я использую курсор для хранения полученного результата, поэтому я попробовал атрибут курсора %ROWCOUNT и %NOTFOUND. Но не совсем работает. Кроме того, я хочу сделать это без запуска цикла на курсоре.
procedure MODULE_LIST_GK(p_module_Id IN MODULE_LIST.MODULE_ID% TYPE,
p_Error_Code out nvarchar2,
p_Error_Msg out nvarchar2,
p_Cursor out sys_refcursor) IS
BEGIN
OPEN p_Cursor FOR
SELECT A.MODULE_ID,
A.MODULE_NM,
A.AUTH_STATUS_ID
FROM MODULE_LIST A
WHERE A.MODULE_ID=p_module_Id;
SELECT COUNT(MODULE_ID)
INTO v_row_num
FROM MODULE_LIST A
WHERE A.MODULE_ID=p_module_Id;
IF v_row_num=0 THEN
p_Error_Code := SQLCODE;
p_Error_Msg := 'Does not Exists';
Return;
end IF;
EXCEPTION
WHEN OTHERS THEN
p_error_code:= SQLCODE;
p_error_msg := SQLERRM;
END MODULE_LIST_GK;
Ответы 2
В вашей реализации есть несколько моментов, которые можно было бы улучшить.
Первый, если вы ожидаете, что для большого количества параметров возвращаемый курсор будет пустым, чем сначала проверьте пустой курсор и только после этой проверки открывать курсор. Вы делаете это наоборот.
Как проверить, пустой ли курсор? К сожалению, вы должны получить первую строку, чтобы проверить ее.
open l_cur for
select id, status from tab where id = p_id;
fetch l_cur into l_id, l_status;
if l_cur%NOTFOUND then
p_Error_Msg := 'Does not Exists';
Return;
end if;
Эта проверка гораздо более эффективна, чем часто используемая count(*), поскольку она учитывает только первые (несколько) строк и не учитывает все строки в курсоре.
Если проверка не удалась, вы готовы, в противном случае просто откройте курсор и верните его.
open l_cur for
select id, status from tab where id = p_id;
p_Cursor := l_cur;
На ум приходят две дополнительные мысли.
Вам следует изменить общий подход, если база данных очень динамична. Как бы вы поступили в случае, когда другая сессия удаляет какую-то строку между проверкой и вторым открытием курсора?
Наконец, рассмотрите возможность возврата исключения вместо кода возврата.
@ radioactive001 Я полагаю, вы могли бы скопировать результаты во временную таблицу. Однако для вызывающей стороны, вероятно, имеет больше смысла решать, что делать с пустым результирующим набором.
Насколько я знаю, вы должен получить первую строку от курсора, чтобы проверить, пуст ли он. В любом случае вы можете избавиться от него с помощью немного изменив контракт интерфейса. Теперь клиент должен проверить error_msg, и если он пуст, курсор обрабатывается. Вы можете изменить его на извлечь первую строку из курсора, если строки нет, обработайте ее как ошибку, иначе обработайте курсор.
Чтобы узнать, содержит ли курсор строки, вы должны открыть его и получить первую строку. Как только вы это сделаете, больше нет смысла возвращать этот курсор, поскольку получатель не сможет получить эту первую строку, потому что курсор уже указывает за нее.
Таким образом, вы должны выбрать дважды. Что бы вы сделали, так это использовали бы здесь предложение ROWNUM или EXISTS, чтобы показать СУБД, что вас больше не интересуют строки. Это может значительно ускорить запрос.
PROCEDURE module_list_gk(p_module_id IN MODULE_LIST.MODULE_ID%TYPE,
p_error_code OUT NVARCHAR2,
p_error_msg OUT NVARCHAR2,
p_cursor OUT SYS_REFCURSOR) IS
v_count INTEGER;
BEGIN
SELECT COUNT(*)
INTO v_count
FROM module_list
WHERE module_id = p_module_Id
AND ROWNUM = 1;
IF v_count = 0 THEN
p_error_code := 0; -- Or -1403 for NO DATA FOUND if you like
p_error_msg := 'Does not Exists';
RETURN;
END IF;
OPEN p_Cursor FOR
SELECT module_id, module_nm, auth_status_id
FROM module_list
WHERE module_id = p_module_id;
EXCEPTION WHEN OTHERS THEN
p_error_code:= SQLCODE;
p_error_msg := SQLERRM;
END module_list_gk;
Кстати, SQLCODE для первого запроса будет равен 0 (SELECT COUNT(*) возвращает одну строку с количеством найденных записей — следовательно, ошибки нет). Вот почему вы должны либо явно вернуть ноль, либо какой-либо код ошибки, например -1403.
Вот то же самое с EXISTS:
BEGIN
SELECT CASE WHEN EXISTS
(
SELECT NULL
FROM module_list
WHERE module_id = p_module_Id
) THEN 1 ELSE 0 END
INTO v_count
FROM DUAL;
IF v_count = 0 THEN
Итак, в любом случае я должен запросить один раз, чтобы найти 1. Если есть данные? и 2. Если есть данные, получите их. Мне было просто любопытно, есть ли способ добиться этого, запросив один раз?