1 2 3 Previous Next 36 Replies Latest reply: Nov 28, 2012 3:32 AM by BluShadow Go to original post RSS
      • 30. Re: SQL - NULL vs IS NULL?
        John Spencer
        Using is null:
        SQL> create procedure p (p_id        in number,
          2                      p_old_descr in varchar2,
          3                      p_new_descr in varchar2) as
          4  begin
          5     update t
          6     set descr = p_new_descr
          7     where id = p_id and
          8           (descr = p_old_descr or
          9            (descr is null and
         10             p_old_descr is null));
         11  end;
         12  /
        
        Procedure created.
        Using NVL with a safe value:
        SQL> create procedure p1 (p_id        in number,
          2                       p_old_descr in varchar2,
          3                       p_new_descr in varchar2) as
          4  begin
          5     update t
          6     set descr = p_new_descr
          7     where id = p_id and
          8           NVL(descr, chr(0)) = NVL(p_old_descr, chr(0));
          9  end;
         10  /
        
        Procedure created.
        Demonstration:
        SQL> select * from t;
        
                ID DESCR
        ---------- ----------
                 1 One
                 1
        
        SQL> exec p(1, null, 'Un');
        
        PL/SQL procedure successfully completed.
        
        SQL> select * from t;
        
                ID DESCR
        ---------- ----------
                 1 One
                 1 Un
        
        SQL> rollback;
        
        Rollback complete.
        
        SQL> exec p1(1, null, 'Un');
        
        PL/SQL procedure successfully completed.
        
        SQL> select * from t;
        
                ID DESCR
        ---------- ----------
                 1 One
                 1 Un
        John
        • 31. Re: SQL - NULL vs IS NULL?
          DBA112
          hi, Thanks for the response.

          Yes, I have already used a different variable names than the column names (for eg: used attribute2_2 for attribute2 column etc..).

          In my case, attribute2 values may differ . For instance, it could be IS NULL or some value. Does NVL you referred below works in either cases??

          Could you please clarify or help me modify procedure?

          Thanks a lot..
          • 32. Re: SQL - NULL vs IS NULL?
            DBA112
            Thanks John, Thats all I needed. Appreciate your help!!!
            • 33. Re: SQL - NULL vs IS NULL?
              Stew Ashton
              From the Oracle documentation
              http://docs.oracle.com/cd/E11882_01/server.112/e26088/sql_elements005.htm#SQLRF30037
              Note:
              Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.
              Oracle has been admitting this for a long time.
              • 34. Re: SQL - NULL vs IS NULL?
                Stew Ashton
                Rather than using NVL() with a "safe" value, I prefer to use DECODE because no "safe" value is required.
                ...where decode(a,b,0,1) = 0
                As the Oracle documentation states:
                In a DECODE function, Oracle considers two nulls to be equivalent.
                http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions049.htm#SQLRF00631

                Of course, DECODE only works for equality comparisons. If you are doing greater than or less than, I don't see any alternative to NVL().
                • 35. Re: SQL - NULL vs IS NULL?
                  user346369
                  Stew Ashton wrote:
                  Of course, DECODE only works for equality comparisons. If you are doing greater than or less than, I don't see any alternative to NVL().
                  DECODE, NVL(), ...and John Spencer's   NVL(descr, chr(0)) = NVL(p_old_descr, chr(0))
                  They're all workarounds that slow down a developer's work.

                  Wouldn't it be Sooo much quicker if Ora DBA could have used:
                  AND ATTRIBUTE2 .= ATTRIBUTE2_2;
                  where the "." would tell Oracle to generate code like this (under the hood):
                  AND (    ATTRIBUTE2 = ATTRIBUTE2_2
                        OR (    ATTRIBUTE2   IS NULL
                            AND ATTRIBUTE2_2 IS NULL ) )
                  BluShadow wrote:
                  ...Or maybe these:   .=   .<   .>   .<>   etc.
                  Too dotty, making code messy.
                  What? The dot is messier than NVL() or Decode, or the ANSI SQL COALESCE() ( where you STILL need to invent a never-possible value! )?
                  Why are they cumbersome? They make it readable and clear what it means.
                  I have to disagree. Long, drawn out statements sure take me longer to parse in my simple brain. If that easily recognizable "dotty" period were used many places to simply convert Three-Value logic into Two-Value, I am sure developers all over would latch onto the code immediately.
                  • 36. Re: SQL - NULL vs IS NULL?
                    BluShadow
                    Steve Cosner wrote:
                    Why are they cumbersome? They make it readable and clear what it means.
                    I have to disagree. Long, drawn out statements sure take me longer to parse in my simple brain.
                    Well, if you want short abbreviated code you could go back to coding in assembler... nice short mnemonics, very few instructions to remember... just might take a little longer to achieve exactly what you want though as you'll need more of them. :D
                    If that easily recognizable "dotty" period were used many places to simply convert Three-Value logic into Two-Value, I am sure developers all over would latch onto the code immediately.
                    The dotty stuff just looks messy (like I said, it looks as messy as using pointers and the like in C etc.). It doesn't naturally read as cleanly as the rest of the PL language. I would consider such a thing as a complete fudge, and I wouldn't think Oracle would even consider implementing something like that.
                    1 2 3 Previous Next