当参数有一些值时in_put如何在选择查询中编写"in" claus..？

Question

每当用户传递值列表时，我们需要暂停"in"条件。 jin_son_doc会像这样"印度，美属萨摩亚">

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" 为空时，我们需要跳过 in 子句。这可能吗，如果我们有任何解决方案，请告诉我。

用户将通过jin_son_doc会像这个"印度，美国"。

通过使用以下函数，我将字符串转换为数组，

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;

Answer 1

您可以使用OR条件，如下所示：

....
AND (json_doc is null
OR country IN (
SELECT
column_value
FROM
TABLE ( json_doc )
)
)
...

干杯！！