#oracle #plsql
Есть запрос, который возвращает найденные route по парам id, разделенных точкой с
запятой.
Например, он вернет route для входных данных "12;13;". Также я ожидал, что для нескольких
таких пар будет возвращен результат с найденными route. Но это не так. Если на его
входе есть несколько id от заведомо не существующих записей, то не возвращается ничего.
Например , заведомо правильная пара "12;13;". Запрос "12;13;14;15" возвращает мне
нужный route. Запрос же "14;15;12;13" не возвратит ни одного, если для пары "14;15;"
не существует значения.
Сам запрос выглядит примерно так:
function get_list(p_ids clob)
return obj_list pipelined --(obj_list это table of rec_obj)
is
v_rec_obj rec_obj; --(rec_obj это record)
v_id_list id_list;
begin
select column_value
bulk collect into v_id_list
from table(split2(p_ids, ';')); --split2 возвращает разделенные `id`
for i in 1..v_id_list.count - 1 loop
select r.origin_id, r.dest_id, r.data_object
into v_rec_obj
from route r
where r.origin_id = v_id_list(i)
and r.dest_id = v_id_list(i+1);
pipe row(v_rec_obj);
end loop;
end;
Ну и достается это все потом по типу:
select * from table(get_list(p_ids => p_ids));
Ответы
Ответ 1
Поведение некоторых исключений в PL/SQL и SQL контекстах могут существенно различаться. Например, ORA-01403: no data found в PL/SQL контексте должно быть обработано: exception when no_data_found then <действие> А в SQL контексте это же исключение возникает при любом запросе, будет отловлено в SQL движке и означает всего лишь, что открытый курсор не имеет больше данных для fetch. Другими словами, необработанное исключение no_data_found из PL/SQL функции вызванной в SQL контексте будет понято: нет больше данных и запрос завершится нормально. На упрощённом и воизпроизводимом примере можно подробнее увидеть, что не так в функции из вопроса (подготовку схемы см. ниже). Такой запрос для тестовых данных: select * from route; ORIGIN_ID DEST_ID DATA ---------- ---------- ---------------------------------------------------------------- 11 12 route 1 12 13 route 2 13 14 route 3 3 rows selected. select * from table (getRoutes (routeIdList (11,12,15,12,13))) ; ORIGIN_ID DEST_ID DATA ---------- ---------- ---------------------------------------------------------------- 11 12 route 1 1 row selected. ORA-01403: no data found завершится без ошибок, но ожидаемая строка к существуюшей паре значений 12,13 не будет получена из-за необработанного исключения к паре значений 12,15. В pipelined функциях не стоит забывать обработать NO_DATA_NEEDED. И в зaключение, как упомянуто в документации, любая функция должна быть заершена выражением RETURN: As in every function, every execution path in a pipelined table function must lead to a RETURN statement, which returns control to the invoker. However, in a pipelined table function, a RETURN statement need not return a value to the invoker. И хотя в последних версиях выглядит, что это не является необходимым и работает также и без RETURN, но пока трудно предсказать, где будет исправлена ошибка в следующих релизах, в документации или в поведении функции. Подготовка схемы для примера: create table route (origin_id number, dest_id number, data varchar2 (64)) / create or replace type routeRow force is object (origin_id number, dest_id number, data varchar2 (64)); / create or replace type routeList is table of routeRow; / create or replace type routeIdList is table of number; / create or replace function getRoutes (ids routeIdList) return routeList pipelined is route routeRow; begin for i in 1..ids.count-1 loop select routeRow (r.origin_id, r.dest_id, r.data) into route from route r where r.origin_id = ids (i) and r.dest_id = ids (i+1) ; pipe row (route); end loop; return; exception when others then dbms_output.put_line (sqlerrm); raise; end; / insert into route select rownum+10, rownum+11, 'route '||rownum from xmlTable ('1 to 3') ;Ответ 2
Как и предположил @Viktorov, проблема заключалась в ошибке no_data_found. При её возникновении останавливалось выполнение функции. Решение конкретно моей проблемы состояло в обработке этого исключения. К примеру, приведенная в вопросе функция стала бы выглядеть так: function get_list(p_ids clob) return obj_list pipelined is v_rec_obj rec_obj; v_id_list id_list; begin select column_value bulk collect into v_id_list from table(split2(p_ids, ';')); for i in 1..v_id_list.count - 1 loop begin select r.origin_id, r.dest_id, r.data_object into v_rec_obj from route r where r.origin_id = v_id_list(i) and r.dest_id = v_id_list(i+1); pipe row(v_rec_obj); exception when no_data_found then null; end; end loop; end;Ответ 3
проверьте demo create table route ( origin_id NUMBER, dest_id NUMBER, data_object VARCHAR2(100) ) // CREATE OR REPLACE PACKAGE D_OBJ IS type obj_rec is record( origin_id NUMBER, dest_id NUMBER, data_object VARCHAR2(100) ); type obj_list is table of obj_rec; function get_list(p_ids in clob) return obj_list pipelined; end D_OBJ; // -- create package body -- create package body CREATE OR REPLACE PACKAGE BODY D_OBJ IS function get_list(p_ids in clob) return obj_list pipelined is TYPE t_num_col_typ is table of number; l_origin_id_col t_num_col_typ; l_dest_id_col t_num_col_typ; l_result obj_rec; l_result_tbl obj_list; begin WITH T AS ( SELECT p_ids AS STR FROM DUAL ) SELECT to_number(REGEXP_SUBSTR(T.STR, '[0-9]+', 1, LEVEL, 'm')), to_number(REGEXP_SUBSTR(T.STR, '[0-9]+', 1, LEVEL+1, 'm')) bulk collect into l_origin_id_col, l_dest_id_col FROM T CONNECT BY LEVEL < REGEXP_COUNT(T.STR, '[0-9]+', 1, 'm'); for i in 1..l_origin_id_col.count loop select r.origin_id, r.dest_id, r.data_object BULK COLLECT INTO l_result_tbl from route r where r.origin_id = l_origin_id_col(i) and r.dest_id = l_dest_id_col(i); for j IN 1..l_result_tbl.count loop pipe row(l_result_tbl(j)); end loop; end loop; end get_list; end D_OBJ; // -- execute the public procedure of the package begin insert into route( origin_id, dest_id, data_object) VALUES (21, 201, 'obj 1'); insert into route( origin_id, dest_id, data_object) VALUES (31, 301, 'obj 2'); insert into route( origin_id, dest_id, data_object) VALUES (41, 401, 'obj 2'); end; // select * from route // -- dbms_output doesn't work, so we log into a table -- (just like in real life) and select all records from it here select * from table(D_OBJ.get_list(p_ids => '21;201; 31;302; 41;401')) //
Комментариев нет:
Отправить комментарий