7 Replies Latest reply: Mar 2, 2013 7:55 AM by Frank Kulash RSS

    how to get more than one value in a variable.

    947771
      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
          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
            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
              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.
                Billy~Verreynne
                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
                  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.
                    Billy~Verreynne
                    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
                      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.