OraclePLSQL--LABELS -----Scenario we want to extract the most busy employee and least busy employee with their information.

LABELS

 Labels name are located any where within the block and aregenerally denoted by the <<lablesname>>

Scenario

 we  want to extract  the most busy employee and least busy employee with their information.

DECLARE

TYPE EmployeeRecord

IS RECORD (ssn           employee.ssn%TYPE,

LName         employee.LName%TYPE,

DName         department.DName%TYPE,

sex           employee.sex%TYPE,

BonusPayment  NUMBER(6)

);

ActiveEmployee  EmployeeRecord;

InactiveEmployee EmployeeRecord;

BEGIN

<<LocateActive>>

SELECT ssn, LName, DName,sex, 5000

INTO ActiveEmployee

FROM employee, department, works_on

WHERE employee.dno = department.dnumber

AND employee.ssn = works_on.essn

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

AND ROWNUM <= 1;

<<OutputActive>>

dbms_output.put_line ('Active employee name: ' || ActiveEmployee.LName);

dbms_output.put_line ('Active employee name: ' || ActiveEmployee.SSN);

dbms_output.put_line ('Active employee department: ' || ActiveEmployee.DName);

dbms_output.put_line ('Active employee gender: ' || ActiveEmployee.sex);

dbms_output.put_line ('Active employee bonus: ' || ActiveEmployee.BonusPayment);

<<LocateInactive>>

SELECT ssn, LName, DName,sex, 0

INTO InactiveEmployee

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;

<<OutputInactive>>

dbms_output.put_line ('Inactive employee name: ' || InactiveEmployee.LName);

dbms_output.put_line ('Inactive employee name: ' || ActiveEmployee.SSN);

dbms_output.put_line ('Inactive employee department: ' ||

InactiveEmployee.DName);

dbms_output.put_line ('Inactive employee gender: ' || ActiveEmployee.sex);

dbms_output.put_line ('Inactive employee bonus: ' ||

InactiveEmployee.BonusPayment);

END;

anonymous block completed

Active employee name: Naraan

Active employee name: 666884444

Active employee department: Research

Active employee gender: M

Active employee bonus: 5000

Inactive employee name: Wallace

Inactive employee name: 666884444

Inactive employee department: Administration

Inactive employee gender: M

Inactive employee bonus: 0