英文:
How to write "in" claus in select query when ever in_put param has some values..?
问题
每当用户传递一个值列表时,我们需要暂停“in”条件。
jin_son_doc将会是这样的 "India, American Samoa"
create or replace PROCEDURE test_attendee (
out_chr_err_code OUT VARCHAR2,
out_chr_err_msg OUT VARCHAR2,
out_attendee_tab OUT return_attendee_arr_result,
in_json_doc IN VARCHAR2
) IS
l_chr_srcstage VARCHAR2(200);
l_chr_biqtab VARCHAR2(200);
l_chr_srctab VARCHAR2(200);
l_chr_bistagtab VARCHAR2(200);
l_chr_err_code VARCHAR2(255);
l_chr_err_msg VARCHAR2(255);
l_out_chr_errbuf VARCHAR2(2000);
lrec return_attendee_report;
l_num_counter NUMBER := 0;
json_doc CHAR_ARRAY(1000) := in_json_doc;
CURSOR cur_attendee_data IS
SELECT
*
FROM
(
SELECT
a.*,
ROWNUM rn
FROM
(SELECT * FROM (
SELECT
r.id request_id,
c.designation ext_att_title,
DECODE(c.attendee_type, 'externalattendees', 'External', 'Internal') attendee_type
FROM
bi_request r
LEFT JOIN bi_request_activity_day a ON a.request_id = r.id
LEFT JOIN bi_request_catering_activity b ON b.request_activity_day_id = a.id
LEFT JOIN bi_request_attendees c ON c.request_id = r.id
LEFT JOIN bi_request_act_day_room d ON d.request_activity_day_id = a.id
AND d.room = b.room
WHERE
r.state = 'CONFIRMED'
AND a.event_date BETWEEN l_start_date AND l_end_date
AND r.location_id = (
SELECT UNIQUE
( id )
FROM
bi_location
WHERE
unique_id = l_location_id
)
AND d.room_type = 'MAIN_ROOM'
AND country IN (
SELECT
column_value
FROM
TABLE ( json_doc )
)
)
WHERE
1=1
) a
WHERE
ROWNUM <= l_end_row
)
WHERE
rn >= l_start_row;
TYPE rec_attendee_data IS
TABLE OF cur_attendee_data%rowtype INDEX BY PLS_INTEGER;
l_cur_attendee_data rec_attendee_data;
BEGIN
dbms_output.put_line(l_country_array.count);
out_attendee_tab := return_attendee_arr_result();
OPEN cur_attendee_data;
LOOP
FETCH cur_attendee_data BULK COLLECT INTO l_cur_attendee_data;
EXIT WHEN l_cur_attendee_data.count = 0;
dbms_output.put_line('here in first insert');
lrec := return_attendee_report();
out_attendee_tab := return_attendee_arr_result(return_attendee_report());
out_attendee_tab.DELETE;
FOR i IN 1..l_cur_attendee_data.count LOOP
-- dbms_output.put_line('Inside cursor ' );
BEGIN
l_num_counter := l_num_counter + 1;
lrec := return_attendee_report();
lrec.requestid := l_cur_attendee_data(i).request_id;
lrec.attendeetype := l_cur_attendee_data(i).attendee_type;
lrec.attendeetype := json_doc;
IF l_num_counter > 1 THEN
out_attendee_tab.extend();
out_attendee_tab(l_num_counter) := return_attendee_report();
ELSE
out_attendee_tab := return_attendee_arr_result(return_attendee_report());
END IF;
out_attendee_tab(l_num_counter) := lrec;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error occurred : ' || sqlerrm);
END;
END LOOP;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('HERE INSIIDE OTHERS' || sqlerrm);
END;
每当 j"son_doc" 为 null 时,我们需要跳过 in 子句。如果有解决方案,请告诉我。
用户将传递的 jin_son_doc 将会是这样的 "India, America"。
通过以下函数,我正在将字符串转换为数组,
create or replace FUNCTION fn_varchar_to_array(p_list IN VARCHAR2)
RETURN CHAR_ARRAY
AS
l_string VARCHAR2(32767) := p_list || ',';
l_comma_index PLS_INTEGER;
l_index PLS_INTEGER := 1;
l_tab CHAR_ARRAY := CHAR_ARRAY();
BEGIN
LOOP
l_comma_index := INSTR(l_string, ',', l_index);
EXIT WHEN l_comma_index = 0;
l_tab.EXTEND;
l_tab(l_tab.COUNT) := SUBSTR(l_string, l_index, l_comma_index - l_index);
l_index := l_comma_index + 1;
END LOOP;
RETURN l_tab;
END fn_varchar_to_array;
英文:
whenever users pass a list of values then we need to pause "in" condition.
the jin_son_doc will be like this "India,American Samoa"
create or replace PROCEDURE test_attendee (
out_chr_err_code OUT VARCHAR2,
out_chr_err_msg OUT VARCHAR2,
out_attendee_tab OUT return_attendee_arr_result,
in_json_doc IN VARCHAR2
) IS
l_chr_srcstage VARCHAR2(200);
l_chr_biqtab VARCHAR2(200);
l_chr_srctab VARCHAR2(200);
l_chr_bistagtab VARCHAR2(200);
l_chr_err_code VARCHAR2(255);
l_chr_err_msg VARCHAR2(255);
l_out_chr_errbuf VARCHAR2(2000);
lrec return_attendee_report;
l_num_counter NUMBER := 0;
json_doc CHAR_ARRAY(1000) := in_json_doc;
CURSOR cur_attendee_data IS
SELECT
*
FROM
(
SELECT
a.*,
ROWNUM rn
FROM
(SELECT * FROM (
SELECT
r.id request_id,
c.designation ext_att_title,
DECODE(c.attendee_type, 'externalattendees', 'External', 'Internal') attendee_type
FROM
bi_request r
LEFT JOIN bi_request_activity_day a ON a.request_id = r.id
LEFT JOIN bi_request_catering_activity b ON b.request_activity_day_id = a.id
LEFT JOIN bi_request_attendees c ON c.request_id = r.id
LEFT JOIN bi_request_act_day_room d ON d.request_activity_day_id = a.id
AND d.room = b.room
WHERE
r.state = 'CONFIRMED'
AND a.event_date BETWEEN l_start_date AND l_end_date
AND r.location_id = (
SELECT UNIQUE
( id )
FROM
bi_location
WHERE
unique_id = l_location_id
)
AND d.room_type = 'MAIN_ROOM'
AND country IN (
SELECT
column_value
FROM
TABLE ( json_doc )
)
)
WHERE
1=1
) a
WHERE
ROWNUM <= l_end_row
)
WHERE
rn >= l_start_row;
TYPE rec_attendee_data IS
TABLE OF cur_attendee_data%rowtype INDEX BY PLS_INTEGER;
l_cur_attendee_data rec_attendee_data;
BEGIN
dbms_output.put_line(l_country_array.count);
out_attendee_tab := return_attendee_arr_result();
OPEN cur_attendee_data;
LOOP
FETCH cur_attendee_data BULK COLLECT INTO l_cur_attendee_data;
EXIT WHEN l_cur_attendee_data.count = 0;
dbms_output.put_line('here in first insert');
lrec := return_attendee_report();
out_attendee_tab := return_attendee_arr_result(return_attendee_report());
out_attendee_tab.DELETE;
FOR i IN 1..l_cur_attendee_data.count LOOP
-- dbms_output.put_line('Inside cursor ' );
BEGIN
l_num_counter := l_num_counter + 1;
lrec := return_attendee_report();
lrec.requestid := l_cur_attendee_data(i).request_id;
lrec.attendeetype := l_cur_attendee_data(i).attendee_type;
lrec.attendeetype := json_doc;
IF l_num_counter > 1 THEN
out_attendee_tab.extend();
out_attendee_tab(l_num_counter) := return_attendee_report();
ELSE
out_attendee_tab := return_attendee_arr_result(return_attendee_report());
END IF;
out_attendee_tab(l_num_counter) := lrec;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Error occurred : ' || sqlerrm);
END;
END LOOP;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('HERE INSIIDE OTHERS' || sqlerrm);
END;
Whenever j"son_doc" is null we need to skip the in clause. Is this possible, let me know if we have any solution.
user will pass jin_son_doc will be like this "India, America".
by using the following function i'm converting string to array,
create or replace FUNCTION fn_varchar_to_array(p_list IN VARCHAR2)
RETURN CHAR_ARRAY
AS
l_string VARCHAR2(32767) := p_list || ',';
l_comma_index PLS_INTEGER;
l_index PLS_INTEGER := 1;
l_tab CHAR_ARRAY := CHAR_ARRAY();
BEGIN
LOOP
l_comma_index := INSTR(l_string, ',', l_index);
EXIT WHEN l_comma_index = 0;
l_tab.EXTEND;
l_tab(l_tab.COUNT) := SUBSTR(l_string, l_index, l_comma_index - l_index);
l_index := l_comma_index + 1;
END LOOP;
RETURN l_tab;
END fn_varchar_to_array;
答案1
得分: 1
你可以如下使用OR
条件:
....
AND (json_doc 为空
或者 country 在 (
从
TABLE ( json_doc )
中选择
)
)
...
祝好运!
英文:
You can use OR
condition as following:
....
AND (json_doc is null
OR country IN (
SELECT
column_value
FROM
TABLE ( json_doc )
)
)
...
Cheers!!
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论