Forum Stats

  • 3,824,845 Users
  • 2,260,430 Discussions
  • 7,896,330 Comments

Discussions

introduce increment/decrement operator (++/--) in SQL and PL/SQL for integer types

TPD-Opitz
TPD-Opitz Member Posts: 2,465 Silver Trophy
edited Jan 11, 2016 5:53PM in Database Ideas - Ideas

I'd like to have increment/decrement operator (++/--) in SQL and PL/SQL.for integer types

in SQL this Operator should be available for updates like:

UPDATE some_table
SET integer_arrtibute ++,
,another_integer_Attribute--,
where ...


leading Operator or combination with assingments should not be allowed.

In PL/SQL the Operator should work as in Java, mening that the leading Operator is evalueated bevore an assingment and the trailing Operator is evaluated after an assingment:

declare
  integer v_test1 := 1;
  integer v_test2 := 5; begin v_test2 := v_test1++; if(6=v_test2) then dbms_Output.put_line('success'); end if; if(2=v_test1) then dbms_Output.put_line('success'); end if; v_test2 := ++v_test1; if(9=v_test2) then dbms_Output.put_line('success'); end if; if(3=v_test1) then dbms_Output.put_line('success'); end if; end;

bye

TPD

TPD-OpitzctriebBPeaslandDBAManish Chaturvediabhinivesh.jainKiran Pawarpankajrangavinaykumar2ulohmannWilliam RobertsonSven W.dherzhauberxRaderarnjbbarretoJeff Kemp-Oracleandre.psantos
25 votes

Active · Last Updated

«1

Comments

  • Hi,

    I think you posted this idea in the wrong place.

    This community is for BigMachines.

    Regards,

    Pradeep

  • TPD-Opitz
    TPD-Opitz Member Posts: 2,465 Silver Trophy

    Hi,

    I think you posted this idea in the wrong place.

    This community is for BigMachines.

    Regards,

    Pradeep

    Thanks for the tipp,

    I moved it to database.

    bye

    TPD

  • Thanks for the tipp,

    I moved it to database.

    bye

    TPD

    welcome

  • abhinivesh.jain
    abhinivesh.jain Member Posts: 307 Blue Ribbon

    Is this operation part of ANSI standards? Also what specific purpose will it solve?

  • TPD-Opitz
    TPD-Opitz Member Posts: 2,465 Silver Trophy

    Is this operation part of ANSI standards? Also what specific purpose will it solve?

    abhinivesh.jain wrote:
    
    Is this operation part of ANSI standards?
    

    is select * from tab_a, tab_b where a_attrib=b_attrib(+) ANSI standard?

    Why am I restricted to ANSI standard with my enhancement suggestions?

    Also what specific purpose will it solve? 
    

    In SQL: easier handling of version numbering.

    In PL/SQL: easier handling of counter variables in loops, especially when looping trough XML.

    bye

    TPD

  • TPD-Opitz
    TPD-Opitz Member Posts: 2,465 Silver Trophy

    I just thought about the SQL operator and now I feel a bit uncomfortable with the trailing increment/decrement operator since this is kind of inconsistent:

    It is some kind of assignment and the general convention for the trailing increment/decrement operator in other languages (like Java) is that the increment/decrement occurs after the assignment when the operator is trailing.

    In conequence the SQL-proposal should be leading only instead of trailing only.

    bye

    TPD

  • So, what the result of

    select rownum++ from dual

    connect by rownum++ < 10

    is supposed to be?

  • William Robertson
    William Robertson Member Posts: 9,567 Bronze Crown

    I'm voting ++ for this as a PL/SQL feature. I can't see how it makes sense in SQL, though.

    Sven W.
  • Christian Erlinger
    Christian Erlinger Member Posts: 3,244 Gold Trophy
    edited Oct 28, 2015 11:22AM

    Is it just me, but the last time I checked at least '--' is already part of the SQL and PL/SQL syntax - it indicates a single line comment...Of course one could argue that

    i--;
    
    

    clearly shows that you want to decrement i whereas

    -- some comment
    
    

    certainly is a comment.

    however; what about this this

    if --i = 5 then
      [...]
    
    

    is this a comment?

    or this

    begin
      if --haha
        1 = 0 then
        dbms_output.put_line('blah');
      else --huhu;
        dbms_output.put_line('bleh');
      end if;
    end;
    
    

    ? I haven't studied building compilers, but at least to me the introduction of a -- operator could possibly make existing, valid code invalid.

    I have to deal with several languages from Java to PL/SQL in my day to day job, so I can sympathize as I frequently add '--' in my Java code to add comments and wonder about compiler errors

    Maybe the related operators += and -= might prove more powerful and readable then ++ and --; e.g.

    update some_table
    set some_column +=5;
    
    

    Or maybe

    update some_table
    set some_column -= another_column;
    
    

    and of course the same in PL/SQL.

    cheers

    William RobertsonTPD-OpitzVadim Tropashko-Oracle
  • TPD-Opitz
    TPD-Opitz Member Posts: 2,465 Silver Trophy

    So, what the result of

    select rownum++ from dual

    connect by rownum++ < 10

    is supposed to be?

    You may have noticed that i requested the SQL ++/-- operator for the set part in updates only.

    bye

    TPD