OraclePLSQL--DML Update & Transaction Control in PL/SQL Scenario: Let suppose we want to transfer the employee who is the least busy and we want to transfer into another task so we write the following program

DML Update & Transaction Control in PL/SQL

Scenario:

Let suppose we want to transfer the employee who is the least busy and we want to transfer into another task so we write the following program.

Tables

Ø  employee,

Ø  department

Ø  works_on

Ø  dependent

DECLARE

TYPE EmpolyeeRecord

IS RECORD (ssn           employee.ssn%TYPE,

LName         employee.LName%TYPE,

DName         department.DName%TYPE,

BonusPayment  NUMBER(6));

InactiveEmpolyee EmpolyeeRecord;

BEGIN

/*

Identify the one employee who has been the least active, based upon the number

of hours they have been working on projects. This will be the first employee

we want to remove from the existing COMPANY database and transfer them into

the new division.

*/

SELECT ssn, LName, DName, 0

INTO InactiveEmpolyee

FROM employee, department, works_on

WHERE employee.dno = department.dnumber

AND employee.ssn = works_on.essn

AND hours = (SELECT MIN(hours) FROM works_on)

AND ROWNUM <= 1;

-- Remove this employee as a manager of any department.

UPDATE department

SET MgrSSN = NULL

WHERE MgrSSN = InactiveEmpolyee.ssn;

---Next, remove this employee as a supervisor of other employees.

UPDATE employee

SET SuperSSN = NULL

WHERE SuperSSN = InactiveEmpolyee.ssn;

-- Delete any dependents and all WORKS_ON rows.

DELETE FROM dependent

WHERE essn = InactiveEmpolyee.ssn;

DELETE FROM works_on

WHERE essn = InactiveEmpolyee.ssn;

-- Finally, delete this employee from the EMPLOYEE table itself.

DELETE FROM employee

WHERE ssn = InactiveEmpolyee.ssn;

-- Transaction control statement to complete the transaction

COMMIT;

dbms_output.put_line ('Least active employee has been transferred: ' ||

InactiveEmpolyee.LName);

END;

Executing this procedure several times then we will get the names of employee in the order of least busy

anonymous block completed

Least active employee has been transferred: Jabour

anonymous block completed

Least active employee has been transferred: Smith