Question

(PL/SQL Programming) Consider the table EMPLOYEE with attributes ID, Name, and Hours, and the table PAYINFO with attributes Regular, Overtime, and HourLimit, defined and populated by the following script:

DROP TABLE EMPLOYEE CASCADE CONSTRAINTS;

CREATE TABLE EMPLOYEE

(

       ID            CHAR(5),

       Name          VARCHAR2(16),

       Hours         NUMBER(2),

       CONSTRAINT PK_EMPLOYEE

              PRIMARY KEY (ID)

);

INSERT INTO EMPLOYEE VALUES ('22144', 'Anderson', 30);

INSERT INTO EMPLOYEE VALUES ('31902', 'Bruford', 45);

INSERT INTO EMPLOYEE VALUES ('42771', 'Wakeman', 20);

INSERT INTO EMPLOYEE VALUES ('82889', 'Howe', 40);

SELECT * FROM EMPLOYEE;

DROP TABLE PAYINFO;

CREATE TABLE PAYINFO

(

       Regular       NUMBER(5,2),

       Overtime     NUMBER(5,2),

       HourLimit    NUMBER(2)

);

INSERT INTO PAYINFO VALUES (12.00, 18.50, 30);

SELECT * FROM PAYINFO;

COMMIT;

Write a script file Problem1.sql containing an anonymous PL/SQL block that will do the following:

First, report three values found in the PAYINFO table and store them in variables. (You may assume that the PAYINFO table contains only one record.) Next, output the ID and Name of each employee in the EMPLOYEE table and their total pay, computed as:

Regular * Hours                                                                                               if Hours <= HourLimit

(Regular * HourLimit) + (Overtime * (Hours – HourLimit))                                if Hours > HourLimit

(In other words, each employee is paid at the regular rate for hours up to HourLimit, and is paid at the higher overtime rate for any hours beyond HourLimit.) Output each employee’s information on a separate line, and then the total pay for all employees. For the sample data given above, the output should be:

Payroll data is: 12, 18.5, 30

22144 Anderson 360

31902 Bruford 637.5

42771 Wakeman 240

82889 Howe 545

Payroll total is 1782.5

Answer 1

SET SERVEROUTPUT ON SIZE 200000

DECLARE
/*Declaring 3 variables to store payinfo data*/
V_Regular PAYINFO.Regular%type,
V_OverTime PAYINFO.Overtime%type
V_HourLimit PAYINFO.HourLimit%type;
  
BEGIN
/*Inserting data from payinfo to variables*/
SELECT Regular,Overtime,HourLimit INTO V_Regular,V_OverTime,V_HourLimit FROM PAYINFO;
/*Displaying payinfo data*/
dbms_output.put_line('Payroll data is: '||V_Regular ||','||V_OverTime||','||V_HourLimit);
/*Creating cursor to loop data of employee table and display using DBMS_Output*/
FOR Cur_C1 IN
(SELECT ID,Name, CASE WHEN hours > ((V_Regular * V_HourLimit) + (V_Overtime * (hours – V_HourLimit)) THEN (V_Regular* hours )
ELSE (V_Regular * hours) END AS hoursCal
FROM EMPLOYEE
)
LOOP
dbms_output.put_line(Cur_C1.Name|| ' ' ||Cur_C1.Name || ' '||Cur_C1.hoursCal)
END LOOP;

END;

/