OraclePLSQL--DATA MANIPULATION IN BEGIN CLAUSE pl/sql 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 and we want to evaluate whether or not lived in a big city or a small town and also evaluate his/her address.

DATA MANIPULATION IN BEGIN CLAUSE

BY SQL FUNCTION and REGULAR EXPRESSIONS

PL/SQL

By SQL function

·         UPPER() character function

·         ROUND() numeric function

·         MAX() function

REGULAR EXPRESSIONS

·         REGEXP_LIKE()

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 and we want to evaluate whether or not lived in a big city or a small town  and also evaluate his/her address.

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,

address       employee.address%TYPE,

BonusPayment  NUMBER(10),

HOURS         WORKS_ON.HOURS%TYPE

);

BestEMPLOYEE  EMPLOYEERecord;

BEGIN

SELECT essn, LName, DName,SEX,BDATE,address, 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));

IF REGEXP_LIKE(BestEMPLOYEE.Address, '[humble|spring]', 'i') THEN

dbms_output.put_line ('Best employee does not live in a big city');

END IF;

dbms_output.put_line ('Best employee lives at ' || BestEMPLOYEE.Address);

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

Best employee does not live in a big city

Best employee lives at 975 Fire Oak, Humble, TX