PL/SQL Exercise 5

Practice 10
1. Create and invoke the Q_JOB function to return a job title.

CREATE FUNCTION Q_JOBS RETURN VARCHAR2 IS
title jobs.job_title%type;
BEGIN
select job_title INTO title from jobs where job_id='SA_REP';
Return title;
END;
/

SET SERVEROUTPUT ON
DECLARE
employee_name VARCHAR2(35);
BEGIN
employee_name := Q_JOBS;
DBMS_OUTPUT.PUT_LINE(employee_name);
END;
/
a. Create a function called Q_JOB to return a job title to a host variable.
b. Compile the code; create a host variable G_TITLE and invoke the function with job ID SA_REP. Query the host variable to view the result.

DROP FUNCTION Q_JOBS

CREATE FUNCTION Q_JOBS(G_TITLE jobs.job_id%type) RETURN VARCHAR2 IS
title jobs.job_title%type;
BEGIN
select job_title INTO title from jobs where job_id=G_TITLE;
Return title;
END;
/

SET SERVEROUTPUT ON
DECLARE
employee_name VARCHAR2(35);
BEGIN
employee_name := Q_JOBS('SA_REP');
DBMS_OUTPUT.PUT_LINE(employee_name);
END;
/


2. Create a function called ANNUAL_COMP to return the annual salary by accepting two parameters:
an employee’s monthly salary and commission. The function should address NULL values.
a. Create and invoke the function ANNUAL_COMP, passing in values for monthly salary and commission.
Either or both values passed can be NULL, but the function should still return an annual salary, which is not NULL.
The annual salary is defined by the basic formula:
(salary*12) + (commission_pct*salary*12)
b. Use the function in a SELECT statement against the EMPLOYEES table for department 80.


CREATE FUNCTION ANNUAL_COMP(EMPSalary employees.salary%type,EmpComission employees.salary%type)
RETURN NUMBER IS AnnualSalary employees.salary%type;
BEGIN
AnnualSalary:=EMPSALARY*12+EMPCOMISSION*EMPSALARY*12+0;
RETURN AnnualSalary;
END;
/



SET SERVEROUTPUT ON
DECLARE
EmployeeSalary employees.salary%type;
MonSalary employees.salary%type;
Comm employees.salary%type;
CURSOR C1 IS SELECT SALARY,COMMISSION_PCT FROM Employees where DEPARTMENT_ID=80;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO MonSalary,Comm;
EmployeeSalary :=ANNUAL_COMP(MonSalary,Comm);
DBMS_OUTPUT.PUT_LINE(EMPLOYEESALARY);
EXIT WHEN C1%NOTFOUND;
END LOOP;
CLOSE C1;
END;









3. Create a procedure, NEW_EMP, to insert a new employee into the EMPLOYEES table.
The procedure should contain a call to the VALID_DEPTID function to check whether the department ID
specified for the new employee exists in the DEPARTMENTS table.
a. Create the function VALID_DEPTID to validate a specified department ID. The function should return a BOOLEAN value.

CREATE OR REPLACE FUNCTION VALID_DEPTID(dept employees.department_id%type) RETURN BOOLEAN IS
dept_id employees.department_id%type;
BEGIN
select count(*) INTO dept_id from employees where department_id=dept;
IF
dept_id =0 THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END IF;
END;
/

SET SERVEROUTPUT ON
DECLARE
vale BOOLEAN;
BEGIN
vale :=VALID_DEPTID(12);
IF vale=FALSE THEN
DBMS_OUTPUT.PUT_LINE('false');
ELSE
DBMS_OUTPUT.PUT_LINE('true');
END IF;
END;


b. Create the procedure NEW_EMP to add an employee to the EMPLOYEES table.
A new row should be added to the EMPLOYEES table if the function returns TRUE.
If the function returns FALSE, the procedure should alert the user with an appropriate message.
Define default values for most parameters. The default commission is 0, the default salary is 1000,
the default department number is 30, the default job is SA_REP, and the default manager ID is 145.
For the employee’s ID, use the sequence EMPLOYEES_SEQ. Provide the last name, first name, and e-mail address of the employee.
c. Test your NEW_EMP procedure by adding a new employee named Jane Harris to department 15. Allow all other parameters to default. What was the result?
d. Test your NEW_EMP procedure by adding a new employee named Joe Harris to department 80. Allow all other parameters to default. What was the result?

CREATE OR REPLACE FUNCTION VALID_DEPTID(dept employees.department_id%type) RETURN BOOLEAN IS
dept_id employees.department_id%type;
BEGIN
select count(*) INTO dept_id from employees where department_id=dept;
IF
dept_id =0 THEN
RETURN FALSE;
ELSE
RETURN TRUE;
END IF;
END;
/


CREATE SEQUENCE EMPLOYEES_SEQ
INCREMENT BY 1
START WITH 1
MAXVALUE 99999
NOCACHE
NOCYCLE;

DROP PROCEDURE NEW_EMP

CREATE PROCEDURE NEW_EMP IS
vale BOOLEAN;
Invalid_Dept_ID EXCEPTION;
BEGIN
vale :=VALID_DEPTID(60);
IF vale=FALSE THEN
RAISE Invalid_Dept_ID;
ELSE
DBMS_OUTPUT.PUT_LINE('true');
INSERT INTO employees values(EMPLOYEES_SEQ.nextVal,'arpit','rahi','arahi',7316117,sysdate,'SA_REP',1000,'',145,30);
DBMS_OUTPUT.PUT_LINE('true');
END IF;
EXCEPTION
WHEN Invalid_Dept_ID THEN
DBMS_OUTPUT.PUT_LINE('Invalid department id');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('SOME OTHER ERRORS');
END;
/

SET SERVEROUTPUT ON
EXECUTE NEW_EMP