PL/SQL insert record using max function

Solution 1:

The way you described it, it would look like this:

SQL> declare
  2    l_department_id      department.department_id%type;
  3    l_department_name    department.department_name%type  := 'TESTING';
  4    l_location_id        department.location_id%type      := 'CHN-102';
  5  begin
  6    select nvl(max(department_id), 10)
  7      into l_department_id
  8      from department;
  9
 10    insert into department (department_id, department_name, location_id)
 11      values (l_department_id + 10, l_department_name, l_location_id);
 12  end;
 13  /

PL/SQL procedure successfully completed.

SQL> select * From department;

DEPARTMENT_ID DEPARTMENT_NAME      LOCATION_I
------------- -------------------- ----------
           20 TESTING              CHN-102

SQL>

Note, though, that MAX + 10 is a wrong approach. If two (or more) users run the same procedure at the same time, only the first one who commits changes will be able to do that; other user(s) will violate the primary key constraint because that department_id already exists (as it was inserted moments ago by someone else). Use a sequence instead.