Function Hr

CREATE OR REPLACE function HR.calc_sal (p_salary number) return number is v_raise number(6,2) default 1.08; begin return v_raise*p_salary; end calc_sal; / CREATE OR RETURN as title BEGIN SELECT INTO FROM WHERE

REPLACE FUNCTION HR.get_job (ID employees.employee_id%TYPE) varchar2 employees.job_id%TYPE; job_id title employees employee_id = ID;

RETURN title; END get_job; / CREATE OR REPLACE FUNCTION HR.get_sal (ID employees.employee_id%TYPE) RETURN NUMBER IS sal employees.salary%TYPE; BEGIN SELECT salary INTO sal FROM employees WHERE employee_id = ID; RETURN sal; END get_sal; /

CREATE OR REPLACE PROCEDURE HR.add_job_history ( p_emp_id job_history.employee_id%type , p_start_date job_history.start_date%type , p_end_date job_history.end_date%type , p_job_id job_history.job_id%type , p_department_id job_history.department_id%type ) IS BEGIN INSERT INTO job_history (employee_id, start_date, end_date, job_id, department_id) VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id); END add_job_history; / CREATE OR REPLACE procedure HR.loops(v_p number) is BEGIN FOR j IN 1 .. v_p

LOOP DBMS_OUTPUT.put_line ('hello world'); END LOOP; END; / CREATE OR REPLACE procedure HR.mgr_num (mgr number) as v_fname employees.first_name%type; begin select first_name into v_fname from employees where manager_id=mgr; dbms_output.put_line(v_fname); end; / CREATE OR REPLACE PROCEDURE HR.new_job ( jobid jobs.job_id%TYPE, jobtlt jobs.job_title%TYPE, minsal jobs.min_salary%TYPE ) IS BEGIN INSERT INTO jobs (job_id, job_title, min_salary,max_salary ) VALUES (jobid, jobtlt, minsal,minsal*2 ); COMMIT; END; / CREATE OR REPLACE procedure HR.raise_sal(id employees.employee_id%type,percent number) is begin update employees set salary = salary*(1+percent/100) where employee_id = id; end raise_sal; / CREATE OR REPLACE PROCEDURE HR.secure_dml IS BEGIN IF TO_CHAR (SYSDATE, 'HH24:MI') NOT BETWEEN '08:00' AND '18:00' OR TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN') THEN RAISE_APPLICATION_ERROR (-20205, 'You may only make changes during normal office hours'); END IF; END secure_dml; ////////////////////////////////////////////////////////////////////////////////// ////////

CREATE OR REPLACE TRIGGER sec_emp BEFORE INSERT ON employees BEGIN IF (TO_CHAR (SYSDATE, 'dy') IN ('sun', 'fri')) OR (TO_CHAR (SYSDATE, 'hh24:mi') NOT BETWEEN '08:00' AND '18:00') THEN raise_application_error (-20500, 'you may insert' || ' into employees table only during' || ' business hours.' ); END IF; END; / CREATE OR REPLACE TRIGGER update_job_history AFTER UPDATE OF job_id, department_id ON employees FOR EACH ROW BEGIN add_job_history(:old.employee_id, :old.hire_date, sysdate, :old.job_id, :old.department_id); END; /