Write a script which examines table Employees. It displays the id gaps in the employee_id column as follows:
anonymous block completed
EmployeeId column has values starting from 100 to 208
There is no employee with code: 178
There is no employee with code: 198
Solutions
SET serveroutput ON;
DECLARE
i NUMBER ;
min_id NUMBER;
max_id NUMBER;
nr NUMBER;
fname VARCHAR2(100);
BEGIN
SELECT MIN(employee_id) INTO min_id FROM employees;
SELECT MAX(employee_id) INTO max_id FROM employees;
dbms_output.put_line('EmployeeId column has values starting from '||min_id||' to '||max_id);
FOR i IN min_id..max_id
LOOP
SELECT COUNT(employee_id) INTO nr FROM employees WHERE employee_id=i;
if(nr=0) then
dbms_output.put_line('There is no employee with code: '||i);
end if;
END LOOP;
END;
Exercice 2
Modify the script at exercice 1 in order to count the number of gaps in the employees table.
After that the script will add as many raws as counted gaps.
For each new employee copy the data of the employee with the smallest ID in the table; Insert only the columns which do not accept NULL values (employee_Id,First_name,last_name,hire_date,job_id).
Use EMPLOYEES_SEQ sequence to generate correct IDs for the employee.
For each inserted employee the script should display a message similar to:
A new employee with employee_id=336 is inserted;
The script should submit the results before it ends;
Solution
SET serveroutput ON;
DECLARE
i NUMBER ;
min_id NUMBER;
max_id NUMBER;
nr NUMBER;
fname VARCHAR2(100);
missing number :=0;
current_id number;
BEGIN
SELECT MIN(employee_id) INTO min_id FROM employees;
SELECT MAX(employee_id) INTO max_id FROM employees;
dbms_output.put_line('EmployeeId column has values starting from '||min_id||' to '||max_id);
FOR i IN min_id..max_id
LOOP
SELECT COUNT(employee_id) INTO nr FROM employees WHERE employee_id=i;
if(nr=0) then
dbms_output.put_line('There is no employee with code: '||i);
missing:=missing+1;
end if;
END LOOP;
FOR i in 1..missing Loop
insert into employees(employee_id,first_name,last_name,hire_date,job_id)
