OraclePLSQL--DATA MANIPULATION IN BEGIN CLAUSE BY SQL FUNCTION

DATA MANIPULATION IN BEGIN CLAUSE

BY SQL FUNCTION

PL/SQL

By SQL function

•      UPPER() character function
•        ROUND() numeric function
•         MAX() function

WE USING THREE TABLES

 EMPLOYEE, DEPARTMENT, WORKS_ON

Task

we want to extract the best employee name and his bdate,gender,ssn,dept name,hours to do work as well as bonus from these three tables.

DECLARE

TYPE EMPLOYEERecord

IS RECORD

(ssn           employee.ssn%TYPE,

LName         employee.LName%TYPE,

DName         department.DName%TYPE,

SEX           employee.SEX%TYPE,

BDATE         employee.BDATE%TYPE,

BonusPayment  NUMBER(10),

HOURS         WORKS_ON.HOURS%TYPE

);

BestEMPLOYEE  EMPLOYEERecord;

BEGIN

SELECT essn, LName, DName,SEX,BDATE, 799,HOURS

INTO BestEMPLOYEE

FROM employee, department, works_on

WHERE employee.dno = department.dnumber

AND employee.ssn = works_on.essn

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

dbms_output.put_line ('Best employee name: ' ||

UPPER(BestEMPLOYEE.LName));

dbms_output.put_line ('Best employee name dept: ' ||

UPPER(BestEMPLOYEE.DName));

dbms_output.put_line ('Best employee gender: ' ||

UPPER(BestEMPLOYEE.SEX));

dbms_output.put_line ('Best employee BDATE: ' ||

UPPER(BestEMPLOYEE.BDATE));

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

ROUND(BestEMPLOYEE.BonusPayment * 1.24, 4));

dbms_output.put_line ('Best employee SSN: ' ||

UPPER(BestEMPLOYEE.SSN));

dbms_output.put_line ('Best employee HOURS: ' ||

UPPER(BestEMPLOYEE.HOURS));

END;

anonymous block completed

Best employee name: NARAAN

Best employee name dept: RESEARCH

Best employee gender: M

Best employee BDATE: 15-SEP-52

Best employee bonus: 990.76

Best employee SSN: 666884444

Best employee HOURS: 40