Forum Stats

  • 3,768,293 Users
  • 2,252,772 Discussions
  • 7,874,519 Comments

Discussions

PLSQL - How to store multiple values into a variable and return those stored values

Jasper Tanglib
Jasper Tanglib Member Posts: 67 Green Ribbon

Hi,

I have a sample code below. This will obviously result to an error but the idea is, I want to store values depending on IF conditions into a variable by appending whatever value is already there ,and then use that variable in the end to show all the accumulated values that were stored. How can I achieve this?

I have already tried bulk collect and cursor, but if you see my sample below, bulk collect and cursor does not work since they will overwrite the values in the variable instead of adding new values.


DECLARE

    v_var varchar2(100);

BEGIN


    SELECT xt 

    INTO v_var

    FROM data_source

    WHERE xt = 'myData';


    IF v_var IS NOT NULL THEN

        -- store myData value into v_var

    END IF;


    SELECT xt 

    INTO v_var

    FROM data_source

    WHERE xt = 'myData2';


    IF v_var IS NOT NULL THEN

        -- store myData2 value into v_var

    END IF;


    SELECT xt 

    INTO v_var

    FROM data_source

    WHERE xt = 'myData3';

    

    IF v_var IS NOT NULL THEN

        -- store myData3 value into v_var

    END IF;


FOR i IN v_var.count

    LOOP

        dbms_output.put_line('Values from v_var that are not null' || v_var(i));

    END LOOP;

END;


Any ideas or suggestions are appreciated!

- Jazz

Tagged:

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,908 Red Diamond
    Accepted Answer

    Anyway:

    DECLARE
        CURSOR V_CUR
          IS
            SELECT  XT
              FROM  DATA_SOURCE
              WHERE XT IN (
                           NVL2(file_one,'myData',NULL),
                           NVL2(file_two,'myData2',NULL),
                           NVL2(file_three,'myData3',NULL)
                          );
    BEGIN
        FOR V_REC IN V_CUR LOOP
          DBMS_OUTPUT.PUT_LINE('Values from v_var that are not null' || V_REC.XT);
        END LOOP;
    END;
    /
    


    SY.

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,908 Red Diamond

    Why not simply using cursor?

    DECLARE
        CURSOR V_CUR
          IS
            SELECT  XT
              FROM  DATA_SOURCE
              WHERE XT IN (
                           'myData',
                           'myData2',
                           'myData3'
                          );
    BEGIN
        FOR V_REC IN V_CUR LOOP
          DBMS_OUTPUT.PUT_LINE('Values from v_var that are not null' || V_REC.XT);
        END LOOP;
    END;
    /
    
    

    SY.

  • Jasper Tanglib
    Jasper Tanglib Member Posts: 67 Green Ribbon

    Because I need to do some sort of preprocessing, it should only store values that have passed a specific condition. Something like this.

    DECLARE

        v_var varchar2(100);

    BEGIN

        IF file_one IS NULL THEN

            NULL;

        ELSIF NOT NULL THEN

            -- add value myData1

            SELECT XT

            INTO v_var

            FROM DATA_SOURCE

            WHERE XT = 'myData1'

        END IF;


        IF file_two IS NULL THEN

            NULL;

        ELSIF NOT NULL THEN

            -- add value myData2

            SELECT XT

            INTO v_var

            FROM DATA_SOURCE

            WHERE XT = 'myData2'

        END IF;


        IF file_three IS NULL THEN

            NULL;

        ELSIF NOT NULL THEN

            -- store value myData3

            SELECT XT

            INTO v_var

            FROM DATA_SOURCE

            WHERE XT = 'myData3'

        END IF;

        

        FOR i IN v_var LOOP

          DBMS_OUTPUT.PUT_LINE('Values from v_var that are not null: ' || v_var(i);

        END LOOP;

    END;

    Based from the code, assuming file_one and file_three are not null while file_two is null, then output should only be myData1 and myData3.



    Not related but, how did you paste your code like that. It looks neat

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,908 Red Diamond

    And what is file_one, file_two,... where do they come from?

    SY.

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,908 Red Diamond
    Accepted Answer

    Anyway:

    DECLARE
        CURSOR V_CUR
          IS
            SELECT  XT
              FROM  DATA_SOURCE
              WHERE XT IN (
                           NVL2(file_one,'myData',NULL),
                           NVL2(file_two,'myData2',NULL),
                           NVL2(file_three,'myData3',NULL)
                          );
    BEGIN
        FOR V_REC IN V_CUR LOOP
          DBMS_OUTPUT.PUT_LINE('Values from v_var that are not null' || V_REC.XT);
        END LOOP;
    END;
    /
    


    SY.