oracle - Trying to Get the next department number from dept table using SQL Function -

lets assume have department table has deptno values (10,20,30,40...etc ) how can create function next department number ? studying , think if im not right function should contain sequence , still not sure , need guys .

solving let me learn how functions work if thats ok explain how code can , thank .

to numbers in table not easy. can select maximum , add 10. if 2 people working database want insert new department @ same time? both same maximum number, 20, add ten, , try insert same id 30.

oracle has sequences that. when 2 people draw next sequence number, 1 has wait other, 1 gets next number 30 , other number following, i.e. 40. when 1 dismisses insert rollback, id not in table. records 10, 20, 40 instance.

i think it's possible lock whole table, select maximum, insert value + 10 , commit , release table.

most people use sequence. , don't care gaps. don't care numbers long unique. use them technical ids, exist able relate tables; have no meaning. department number on other hand business attribute. not database finding value, person ("hey folks, establish second buying department , call b2"). wohle case isn't realistic. anyway:

here sequence create:

create sequence seq start 10 increment 10; 

and here how use it:

insert dept (deptno, name) values (seq.nextval, 'buying 2'); 

you can have trigger sequence value on insert, or of oracle 12c use default value deptno.

and here function selecting max deptno , returning plus 10:

create or replace function next_deptno return integer   v_max_deptno integer; begin   select coalesce(max(deptno), 10)   v_max_deptno   dept;    return v_max_deptno + 10; end next_deptno;