CODEWITHSUNDEEP
oracle-databaseplsql
Marcus Leon
Marcus Leon

Reputation: 56699

Oracle pl/sql script which increments number

Looking for a way to create an Oracle script using the equivalent of java ++ syntax to increment a variable.

Ie:

int id=10
DELETE MYTABLE;
INSERT INTO MYTABLE(ID, VALUE) VALUES (id++, 'a value');
INSERT INTO MYTABLE(ID, VALUE) VALUES (id++, 'another value');
...

Trying to use a variable and not a sequence so I can rerun this multiple times with the same results.

Upvotes: 8

Views: 53428

Answers (2)

mahi_0707
mahi_0707

Reputation: 1062

You can create a SEQUENCE to increment a number.

----CREATING SEQUENCE:                                                                                                                                                      

    SQL> create sequence seq_name
    2  start with 1
    3  increment by 1
    4  NOCACHE
    5  NOCYCLE
    6  ;

Sequence created. 

----EXECUTION:                                                                                                                                            
SQL> select seq_name.nextval from dual;   

NEXTVAL                                                                                                                                         
1

SQL> select seq_name.nextval from dual;                                                                                                                 

NEXTVAL     
2

Also you can create a function that can be called anywhere: 

----CREATING FUNCTION:     
create or replace function sequence_func(a_number IN Number)
RETURN Number
AS
id Number;                                                                                                                                         
Begin                                                                                                                                              
select seq_name.nextval into id from dual;    
Return id;
end;
/

Function created. 

----EXECUTION:  
SQL> select  sequence_func(1) seq from dual;  
seq
1

P.S : startwith and increment by values below can be set as per your requirement.

Upvotes: 1

Justin Cave
Justin Cave

Reputation: 231851

PL/SQL doesn't have the ++ syntactic sugar. You'd need to explicitly change the value of the variable.

DECLARE
  id integer := 10;
BEGIN
  DELETE FROM myTable;
  INSERT INTO myTable( id, value ) VALUES( id, 'a value' );
  id := id + 1;
  INSERT INTO myTable( id, value ) VALUES( id, 'another value' );
  id := id + 1;
  ...
END;

At that point, and since you want to ensure consistency, you may be better off hard-coding the id values just like you are hard-coding the value values, i.e.

BEGIN
  DELETE FROM myTable;
  INSERT INTO myTable( id, value ) VALUES( 10, 'a value' );
  INSERT INTO myTable( id, value ) VALUES( 11, 'another value' );
  ...
END;

Upvotes: 16

Related Questions