This site is currently read-only as we are migrating to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 31st, when you will be able to use this site as normal.

    Forum Stats

  • 3,890,723 Users
  • 2,269,776 Discussions
  • 7,916,827 Comments

Discussions

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

TPD-Opitz
TPD-Opitz Dipl.-Ing, Dipl.-InfGermanyMember 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

  • Pradeep Agrawal
    Pradeep Agrawal CPQ Solution Architect United KingdomMember Posts: 19

    Hi,

    I think you posted this idea in the wrong place.

    This community is for BigMachines.

    Regards,

    Pradeep

  • TPD-Opitz
    TPD-Opitz Dipl.-Ing, Dipl.-Inf GermanyMember 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

  • Pradeep Agrawal
    Pradeep Agrawal CPQ Solution Architect United KingdomMember Posts: 19

    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 Dipl.-Ing, Dipl.-Inf GermanyMember 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 Dipl.-Ing, Dipl.-Inf GermanyMember 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 London, UKMember Posts: 9,579 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 Señor Developer AustriaMember 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 Dipl.-Ing, Dipl.-Inf GermanyMember 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