如何在选择查询中编写 “in” 子句,只要输入参数中有一些值..?

huangapple go评论100阅读模式
英文:

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, &#39;externalattendees&#39;, &#39;External&#39;, &#39;Internal&#39;) 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 = &#39;CONFIRMED&#39;
                        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 = &#39;MAIN_ROOM&#39;
                        AND country IN (
                            SELECT
                                column_value
                            FROM
                                TABLE ( json_doc )
                        )
                    
                )
                WHERE
                1=1

                ) a
            WHERE
                ROWNUM &lt;= l_end_row
        )
    WHERE
        rn &gt;= 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(&#39;here in first insert&#39;);

        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(&#39;Inside cursor   &#39;  );
            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 &gt; 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(&#39;Error occurred : &#39; || sqlerrm);
            END;
        END LOOP;

    END LOOP;

EXCEPTION
    WHEN OTHERS THEN
        dbms_output.put_line(&#39;HERE INSIIDE OTHERS&#39; || 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 || &#39;,&#39;;
      l_comma_index  PLS_INTEGER;
      l_index        PLS_INTEGER := 1;
      l_tab          CHAR_ARRAY := CHAR_ARRAY();
    BEGIN
      LOOP
       l_comma_index := INSTR(l_string, &#39;,&#39;, 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!!

huangapple
  • 本文由 发表于 2020年1月6日 14:45:59
  • 转载请务必保留本文链接:https://go.coder-hub.com/59607782.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定