This discussion is archived
7 Replies Latest reply: Mar 2, 2013 5:55 AM by Frank Kulash RSS

how to get more than one value in a variable.

947771 Newbie
Currently Being Moderated
Hi,


in mssql i use to get all values like following

select @a = @a + col from abc;

please tel me how to get the same result in oracle 11g express.


yours sincerely

Edited by: 944768 on Feb 26, 2013 8:37 AM

Edited by: 944768 on Feb 26, 2013 7:34 PM
  • 1. Re: how to get more than value in a variable.
    Solomon Yakobson Guru
    Currently Being Moderated
    Formal equivalent would be:
    DECLARE
        a number;
    BEGIN
        FOR v_rec IN (select col from abc) LOOP
          a := a + v_rec.col;
        END LOOP;
    END;
    /
    Or, it col is a string:
    DECLARE
        a varchar2(...); -- or CLOB
    BEGIN
        FOR v_rec IN (select col from abc) LOOP
          a := a || v_rec.col;
        END LOOP;
    END;
    /
    However, if col is numeric, use:
    DECLARE
        a number;
    BEGIN
        SELECT  sum(col)
          INTO  a
          FROM  abc;
    END;
    /
    And if col is a string:
    DECLARE
        a varchar2(...); -- or CLOB
    BEGIN
        SELECT  listagg(col) within group(order by col)
          INTO  a
          FROM  abc;
    END;
    /
    SY.
  • 2. Re: how to get more than value in a variable.
    947771 Newbie
    Currently Being Moderated
    please tel me which one is more fast as i have to use it in function and SP both.

    yours sincerely.

    Edited by: 944768 on Mar 1, 2013 2:29 AM
  • 3. Re: how to get more than value in a variable.
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    944768 wrote:
    please tel me which one is more fast as i have to use it in function and SP both.
    As Solomon said, it depends on whether col is a NUMBER or a string.
    Solomon Yakobson wrote:
    ... if col is numeric, use:
    DECLARE
    a number;
    BEGIN
    SELECT  sum(col)
    INTO  a
    FROM  abc;
    END;
    /
    That's the faster way for numbers.
    And if col is a string:
    DECLARE
    a varchar2(...); -- or CLOB
    BEGIN
    SELECT  listagg(col) within group(order by col)
    INTO  a
    FROM  abc;
    END;
    /
    That's the faster way for strings.

    If you're concerend about speed, try to avoid the function or procedure altogether. Use SUM or LISTAGG directly in your SQL statements, and don't use PL/SQL at all. Exactly how depends on your data and what results you want from that data. If you'd like help, post CREATE TABLE and INSERT statements for a little sample data, and the results you want from that data. Always say which version of Oracle you're using (e.g. 11.2.0.3.0).
    See the forum FAQ {message:id=9360002}
  • 4. Re: how to get more than value in a variable.
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    944768 wrote:
    please tel me which one is more fast as i have to use it in function and SP both.
    How about which one is right? And robust? And will always work?

    In that case, none of the above options.

    Why not?

    Because technically the concatenated string can exceed 4000 bytes (max SQL string size).

    Because functionally it violates the concepts of structured data and relational data modelling, by changing structured data into a raw text string.

    Because architecturally it violates client-server principles that state that the client deals with formatting structured data for rendering, and not server-side SQL.
  • 5. Re: how to get more than value in a variable.
    947771 Newbie
    Currently Being Moderated
    they are hand full of numbers which we keeping in variable of varchar2 type seprated by ,
    for intermidiate use.

    and i show i do not have to convert the number into varchar , so finialy i have number which i want to get , seprated .

    yours sincerly

    Edited by: 944768 on Mar 2, 2013 2:05 AM

    Edited by: 944768 on Mar 2, 2013 2:25 AM
  • 6. Re: how to get more than value in a variable.
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    944768 wrote:
    they are hand full of numbers which we keeping in variable of varchar2 type seprated by ,
    for intermidiate use.
    Why exactly? If we understand the reasons behind doing this, we are in a position to hopefully offer better solutions.
    and i show i do not have to convert the number into varchar , so finialy i have number which i want to get , seprated .
    Keep in mind that tokenising means stuffing multiple values into a string. What happens when there are too many values and the max string size is reached?

    Using those values in a string, means parsing the string. This requires extra CPU cycles. And what happens when the parser chokes on something unexpected in the string and throws an exception - at runtime?
  • 7. Re: how to get more than value in a variable.
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    944768 wrote:
    they are hand full of numbers which we keeping in variable of varchar2 type seprated by ,
    for intermidiate use.
    If you have a handful of numbers, the correct way to store them is in a handful of rows. Relational databases, like Oracle, can do that.
    and i show i do not have to convert the number into varchar ,
    I agree completely; you do not have to convert NUMBERs into VARCHAR2s.
    so finialy i have number which i want to get , seprated .
    So now you're talking about a list of several numbers, delimited somehow (by commas, perhaps). That sounds like a string. A delimited list might be an appropriate way to display data sometimes, but it's the wrong way to store data. Each column of each row should contain only 1 value. That rule is so basic to database design that it's called First Normal Form. See the answers above for some of the reasons.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points