PL/SQL Exercise 4

Practice 8
1. Write a PL/SQL block to select the name of the employee with a given salary value.
Use the DEFINE command to provide the salary.
Pass the value to the PL/SQL block through a iSQL*Plus substitution variable. If the salary entered returns more than one row, handle the exception with an appropriate exception handler and insert into the MESSAGES table the message “More than one employee with a salary of .”

SET SERVEROUTPUT ON
DEFINE myvar NUMBER(8)
DECLARE
inpvar NUMBER(8):='&myvar';
empname EMPLOYEES.FIRST_NAME%type;
BEGIN
select concat(FIRST_NAME,LAST_NAME) INTO empname FROM Employees where salary=inpvar;
DBMS_OUTPUT.PUT_LINE('Employee with Salary '||empname||inpvar);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE (' Your select statement retrieved multiple rows. Consider using a cursor.');
END;


Test this with input as 3000

c. If the salary entered does not return any rows, handle the exception with an appropriate
exception handler and insert into the MESSAGES table the message “No employee with a salary of .”

SET SERVEROUTPUT ON
DEFINE myvar NUMBER(8)
DECLARE
inpvar NUMBER(8):='&myvar';
empname EMPLOYEES.FIRST_NAME%type;
BEGIN
select concat(FIRST_NAME,LAST_NAME) INTO empname FROM Employees where salary=inpvar;
DBMS_OUTPUT.PUT_LINE('Employee with Salary '||empname||inpvar);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('No employee with a salary of '||inpvar);
END;

Test this with input 123t


d. If the salary entered returns only one row, insert into the MESSAGES table the employee’s name and the salary amount.
e. Handle any other exception with an appropriate exception handler and insert into the MESSAGES table the message “Some other error occurred.”
f. Test the block for a variety of test cases. Display the rows from the MESSAGES table to check whether the PL/SQL block has executed successfully. Some sample output is shown below.


SET SERVEROUTPUT ON
DEFINE myvar NUMBER(8)
DECLARE
inpvar NUMBER(8):='&myvar';
empname EMPLOYEES.FIRST_NAME%type;
BEGIN
select concat(FIRST_NAME,LAST_NAME) INTO empname FROM Employees where salary=inpvar;
DBMS_OUTPUT.PUT_LINE('Employee with Salary '||empname||inpvar);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('No employee with a salary of '||inpvar);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Some other exception occurred');
END;

Test this first with random salary such as 123 then with 3000.There are two records with salary 3000 but since we are not handling it ,it will go to other exception section.





2. Modify the code in p3q3.sql to add an exception handler.
Use the DEFINE command to provide the department ID and department location. Pass the values to the PL/SQL block through a iSQL*Plus substitution variables.
b. Write an exception handler for the error to pass a message to the user that the specified
department does not exist. Use a bind variable to pass the message to the user.
c. Execute the PL/SQL block by entering a department that does not exist.



SET SERVEROUTPUT ON
DEFINE myvar1 NUMBER(8)
DEFINE myvar2 VARCHAR(15)
ACCEPT myvar1 PROMPT 'Enter department no'
ACCEPT myvar2 PROMPT 'Enter department Location'
DECLARE
inpvar1 NUMBER(8):='&myvar1';
inpvar2 VARCHAR(15):='&myvar2';
empname EMPLOYEES.FIRST_NAME%type;
TestDisplay Varchar2(50) :='Department Doesnot exist';
BEGIN
select concat(FIRST_NAME,LAST_NAME) INTO empname FROM Employees where department_id=inpvar1;
DBMS_OUTPUT.PUT_LINE('Employee with Salary '||empname||inpvar1);
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE (TestDisplay);
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE ('Some other exception occurred');
END;

Pass 12 and anything to test the same.










3. Write a PL/SQL block that prints the number of employees who earn plus or minus $100
of the salary value set for an iSQL*Plus substitution variable.
Use the DEFINE command to provide the salary value. Pass the value to the PL/SQL block through a iSQL*Plus substitution variable.

SET SERVEROUTPUT ON
DEFINE EmpSal NUMBER(4)
ACCEPT EmpSal PROMPT 'Enter the Salary'
DECLARE
sal NUMBER(4):='&EmpSal';
EmpNo NUMBER(3);
BEGIN
SELECT count(*) INTO EmpNo FROM EMPLOYEES where SALARY =(sal-100) or SALARY=(sal+100);
DBMS_OUTPUT.PUT_LINE(EmpNo);
END;




a. If there is no employee within that salary range, print a message to the user indicating
that is the case. Use an exception for this case.

SET ECHO OFF
SET SERVEROUTPUT ON
DEFINE EmpSal NUMBER(4)
ACCEPT EmpSal PROMPT 'Enter the Salary'
declare
SAL number(5):='&EmpSal';
Empno NUMBER(4);
begin
SELECT count(*) INTO Empno FROM EMPLOYEES where SALARY BETWEEN (sal-100) and (sal+100);
If Empno =0 THEN
RAISE NO_DATA_FOUND;
ELSE
DBMS_OUTPUT.PUT_LINE(EmpNo);
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('There is no employee in that salary range');
END;

b. If there are one or more employees within that range, the message should indicate how many employees are in that salary range.
c. Handle any other exception with an appropriate exception handler. The message should indicate that some other error occurred.

SET ECHO OFF
SET SERVEROUTPUT ON
DEFINE EmpSal NUMBER(4)
ACCEPT EmpSal PROMPT 'Enter the Salary'
declare
NO_DATA_FOUNDING EXCEPTION;
sal NUMBER(5):='&EmpSal';
Empno NUMBER(3);
begin
SELECT count(*) INTO Empno FROM EMPLOYEES where SALARY BETWEEN (sal-100) and (sal+100);
if EMPNO =0 then
RAISE NO_DATA_FOUNDING;
ELSE
DBMS_OUTPUT.PUT_LINE(EmpNo||' Employees are in the salary range');
END IF;
EXCEPTION
WHEN NO_DATA_FOUNDING THEN
DBMS_OUTPUT.PUT_LINE('There is no employee in that salary range');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('SOME OTHER ERRORS');
END;