Chumma

LAB 4
STUDENT NAME: Rajendhar Dayalan Student ID:12667690

Q1 Please write down the modified script for creating the add_jobs procedure into your report.
CREATE OR REPLACE PROCEDURE ADD_JOBS
(p_jobid IN jobs.job_id%TYPE, p_jobtitle IN jobs.job_title%TYPE, p_minsal IN jobs.min_salary%TYPE
)
is v_maxsal jobs.max_salary%TYPE;
BEGIN
v_maxsal:= 2* p_minsal; Insert into jobs values(p_jobid,p_jobtitle,p_minsal,v_maxsal); DBMS_OUTPUT.PUT_LINE ('Added the following row into the JOBS table ...');
DBMS_OUTPUT.PUT_LINE (p_jobid || ' ' || p_jobtitle ||
' '|| p_minsal || ' ' || v_maxsal);

END ADD_JOBS;

Q2 Please write down the modified script for creating the get_service_days function into your report. (2 pts)

CREATE OR REPLACE FUNCTION GET_SERVICE_DAYS
(p_empid IN employees.employee_id%TYPE)
RETURN NUMBER IS v_days NUMBER(8);
BEGIN
select SYSDATE - HIRE_DATE INTO v_days from employees where EMPLOYEE_ID=p_empid; DBMS_OUTPUT.PUT_LINE ('no. of days'||' '|| v_days); RETURN NULL; EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('There is no employee with the specified ID');
END GET_SERVICE_DAYS;

Q3 Please write down the script for creating the get_emp_count function into your report. (3pts)

CREATE OR REPLACE FUNCTION GET_EMP_COUNT
(
V_DEPTID IN NUMBER
) RETURN NUMBER IS v_count NUMBER; p_depname varchar2(30);
BEGIN
select count(*) into v_count from departments where department_id=v_deptid; if(v_count = 0)then DBMS_OUTPUT.PUT_LINE ('There is no Department with the specified Department ID'); RETURN NULL; else select count (*) into v_count from employees where department_id=v_deptid; select DEPARTMENT_NAME into p_depname from departments where department_id=v_deptid; DBMS_OUTPUT.PUT_LINE (v_count || ' ' || 'employee(s) work for the' ||' ' ||p_depname); return v_count; end if;
END GET_EMP_COUNT;

Q4 Please write down the script for creating the