Two procedures.txt

PROCEDURE I BONUS SYSTEM FOR EMPLOYEE: THIS PROCEDURE IS CREATED FOR CALCULATING THE EXTRA BONUS EARNED BY AN EMPLOYEE THROUGH DEALING WITH CUSTOMER’S ORDERS. IF AN EMPLOYEE HELP TO PLACE MORE THAN 100 ORDERS PER MONTH, HE/SHE WILL EARN AN EXTRA BONUS AS 10 PERCENTS OF HIS/HER SALARY. 
CREATE OR REPLACE PROCEDURE EXTRA_BONUS (PSSN IN EMPLOY- EE.SSN%TYPE) AS 
COUNT_ORDER INTEGER; ENAME EMPLOYEE.NAME%TYPE; EBONUS INTEGER; 
BEGIN 
SELECT COUNT (O.ORDERNUMBER) INTO COUNT_ORDER FROM ORDERINFORMATION O, EMPLOYEE E
WHERE E.SSN = PSSN AND E.SSN = O.SSN
GROUP BY E.SSN; 
IF (COUNT_ORDER > 100) THEN 
SELECT E.SALARY, E.NAME INTO EBONUS, ENAME FROM EMPLOYEE E
WHERE E.SSN = PSSN;
EBONUS := EBONUS*0.1; 
UPDATE EMPLOYEEBONUSACCOUNT A SET A.BONUS = A.BONUS + EBONUS WHERE A.SSN = E.SSN; 
ELSE
EBONUS := 0; 
END IF; 
END; 
DBMS_OUTPUT.PUT_LINE (ENAME|| ‘’ is the star employee with placed more than 100 
orders. He/She got an extra bonus in his/her reward account as 10% of his salary.’); 
PROCEDURE II EMPLOYEE ON SPECIAL POSITION’S TOTAL SALARY AND THEIR TOTAL REVENUE CREATED FOR COMPANY: THIS PROCEDURE IS CREATED FOR CALCULATING THE TOTAL SALARY AND THE TOTAL CONTRIBUTION TO COMPANY OF DIFFERENT GROUPS (WITH DIFFERENT DUTIES) OF EMPLOYEES. 
CREATE OR REPLACE PROCEDURE SALARY_GP (PT IN EMPLOYEE.POSI- TION%TYPE) AS 
POSIT EMPLOYEE.POSITION%TYPE; TOTAL_SALARY INTEGER; TOTAL_SELLING INTEGER; 
BEGIN 
SELECT E.POSITION, SUM(SALARY) INTO POSIT, TOTAL_SALARY FROM EMPLOYEE E
WHERE E.POSITION = PT
GROUP BY POSITION 
ORDER BY SUM(SALARY) DESC; 
SELECT SUM (O.ORDERPRICE) INTO TOTAL_SELLING FROM ORDERINFORMATION O, EMPLOYEE E WHERE O.SSN = E.SSN AND E.POSITION = PT; 
DBMS_OUTPUT.PUT_LINE (‘THE TOTAL_SALARY OF EMPLOYEES WORKING ON THE POSITION OF ‘||POSIT||’
is ‘||TOTAL_SALARY||’. THEY ALSO CREATED ‘||TOTAL_SELLING||’ 
DOLLARS EARNING FOR COMPANY.’); 
END;