Porg - Laith SJ Porg - Laith SJ - 6 months ago 10
SQL Question

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

Lets assume that we have a Department Table that has DEPTNO with the values (10,20,30,40...etc )
How can i create a function that get the next department number ?
I was studying it and i think if im not right that the function should contain sequence , still not sure , need your help guys .

Solving this will let me learn how the functions work so if thats ok explain how the code can be , thank you so much .

Answer

To get the numbers like that in the table is not easy. You can select the maximum and add 10. But what if two people working with the database want to insert a new department at the same time? They both get the same maximum number, say 20, add ten, and try to insert the same ID 30.

Oracle has sequences for that. When two people draw the next sequence number, one has to wait for the other, so one gets the next number 30 and the other the number following, i.e. 40. But when one dismisses their insert with rollback, then the ID is not in the table. You could get records 10, 20, 40 for instance.

I think it's also possible to lock the whole table, then select the maximum, insert that value + 10 and commit and release the table.

Most people simply use a sequence. And they don't care about gaps. They don't even care about what the numbers look like as long as they are unique. They use them for technical IDs, which only exist to be able to relate tables; they have no meaning. A department number on the other hand would be a business attribute. It would not be the database finding a value, but some person ("Hey folks, we establish a second buying department and call it B2"). So the wohle case isn't realistic. Anyway:

Here is a sequence you could create:

create sequence seq start with 10 increment by 10;

And here is how to use it:

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

You can also have a trigger get that sequence value on insert, or as of Oracle 12c use it as the default value for deptno.

And here is a function selecting the max deptno and returning this plus 10:

create or replace function next_deptno return integer
as
  v_max_deptno integer;
begin
  select coalesce(max(deptno), 10)
  into v_max_deptno
  from dept;

  return v_max_deptno + 10;
end next_deptno;