Tema nr. 7 Observație! Scrieți rezolvarea direct în acest document!
Creating Packages 1. Create the specification for the check_emp_pkg which you studied in this lesson. The specification should declare a constant and two procedures, as follows:
g_max_length_of_service, datatype NUMBER, initialized to 100 chk_hiredate with one input parameter having the same datatype as employees.hire_date chk_dept_mgr with two input parameters having the same datatypes as employees.employee_id and employees.manager_id.
CREATE OR REPLACE PACKAGE check_emp_pkg IS g_max_length_of_service CONSTANT NUMBER:=100; PROCEDURE chk_hiredate (p_date IN emloyees.hiredate%TYPE); PROCEDURE chk_dept_mgr (p_empid IN employees.employee_id%TYPE, p_mgr IN employees.manager_id%TYPE); END check_emp_pkg; 2. Create the package body for check_emp_pkg. Remember that the names and parameters of the procedures in the body must be identical to those in the specification, or the body will not compile. The code for chk_hiredate should RAISE_APPLICATION_ERROR if the employee was hired more than 100 years ago (hint: use MONTHS_BETWEEN, comparing with g_max_length_of_service * 12). The second procedure, chk_dept_mgr, accepts two input parameters: an employee_id and a manager_id. The code should find the manager of the employee’s department and check whether this manager has the same manager_id as the second parameter. If the manager_id is the same, display a suitable “success” message; if they are different, raise an application error. Include an exception handler for NO_DATA_FOUND. CREATE OR REPLACE PACKAGE BODY check_emp_pkg IS PROCEDURE chk_hiredate (p_date IN employees.hire_date%TYPE) IS BEGIN IF MONTHS_BETWEEN(SYSDATE, p_date)> g_max_length_of_service * 12 THEN RAISE_APPLICATION_ERROR(-20200, 'Invalid Hire Date');
END IF; END chk_hiredate; PROCEDURE chk_dept_mgr (p_empid IN employees.employee_id%TYPE, p_mgr IN employees.manager_id%TYPE) IS v_dept employees.department_id%TYPE; v_mgr departments.manager_id%TYPE; BEGIN SELECT department_id INTO v_dept FROM employees WHERE employee_id = p_empid; SELECT manager_id INTO v_mgr FROM departments WHERE department_id = v_dept; IF v_mgr = p_mgr THEN DBMS_OUTPUT.PUT_LINE ('Success. This really is the manager of the employee'); ELSE RAISE_APPLICATION_ERROR(-20202, 'Error. Manager not valid for this employee'); END IF; EXCEPTION WHEN NO_DATA_FOUND THEN RAISE_APPLICATION_ERROR(-20203, 'Wrong employee or department!'); END chk_dept_mgr; END check_emp_pkg; The following sample data from the employees and departments tables may help you: Departments:
Employees:
Passing parameters (174,149) would be successful, while (174,176) would raise an error.
3. Procedure chk_hiredate: A. Test the chk_hiredate procedure using input value 17-Jan-87 (it should succeed). BEGIN check_emp_pkg.chk_hiredate('17-Jan-87'); END; B. Test the chk_dept_mgr procedure twice using input values (174,149) and (174,176). The first should succeed while the second should fail. BEGIN check_emp_pkg.chk_dept_mgr(174, 149); END; BEGIN check_emp_pkg.chk_dept_mgr(174,176); END; 4. Now you want to modify the package so that the chk_dept_mgr procedure displays a different error message if the two manager_ids are different. What do you need to recreate: the Specification, the Body, or both? Make the change and test it again, using (174,176) as before. Only to the body of package
Advanced Package Concepts 1. Create a package called overload. The package should contain three procedures all called what_am_i. The first procedure should accept a VARCHAR2 as an IN parameter, the second a NUMBER and the third a DATE. Each procedure should display a simple message to show which datatype was passed to it. For example, the first procedure could display “Here I am a Varchar2”. Save your work for later. When you are done, describe the package. CREATE OR REPLACE PACKAGE overload IS PROCEDURE what_am_i (p_chr IN VARCHAR2); PROCEDURE what_am_i (p_nr IN NUMBER); PROCEDURE what_am_i (p_nr IN DATE); END overload; CREATE OR REPLACE PACKAGE BODY overload IS PROCEDURE what_am_i (p_chr IN VARCHAR2) IS BEGIN DBMS_OUTPUT.PUT_LINE('Input is VARCHAR2');
END what_am_i; PROCEDURE what_am_i (p_nr IN NUMBER) IS BEGIN DBMS_OUTPUT.PUT_LINE('Input is NUMBER'); END what_am_i; PROCEDURE what_am_i (p_nr IN DATE) IS BEGIN DBMS_OUTPUT.PUT_LINE('Input is DATE'); END what_am_i; END overload; 2. Test the overload package by calling it and passing in a character string, a number and a date respectively. You should see the different messages returned. 3. Now modify the overload package to have a fourth procedure in it, again called what_am_i, accepting two parameters: p_in NUMBER and p_in2 VARCHAR2. Test the new procedure to verify that it works.
CREATE OR REPLACE PACKAGE overload IS PROCEDURE what_am_i (p_chr IN VARCHAR2); PROCEDURE what_am_i (p_nr IN NUMBER); PROCEDURE what_am_i (p_nr IN DATE); PROCEDURE what_am_i( p_in NUMBER, p_in2 VARCHAR2); END overload; CREATE OR REPLACE PACKAGE BODY overload IS PROCEDURE what_am_i (p_chr IN VARCHAR2) IS BEGIN DBMS_OUTPUT.PUT_LINE('Input is VARCHAR2'); END what_am_i; PROCEDURE what_am_i (p_nr IN NUMBER) IS BEGIN DBMS_OUTPUT.PUT_LINE('Input is NUMBER'); END what_am_i; PROCEDURE what_am_i (p_nr IN DATE) IS BEGIN DBMS_OUTPUT.PUT_LINE('Input is DATE'); END what_am_i; PROCEDURE what_am_i ( p_in NUMBER, p_in2 VARCHAR2)
IS BEGIN DBMS_OUTPUT.PUT_LINE('Input is number and varchar2'); END what_am_i; END overload; 4. Modify the overload package specification again to add a fifth procedure called what_am_i with the same two IN parameters as the fourth procedure. Try to recreate the specification. What happens and why? 5. Package init_pkg A. Create a package init_pkg as follows: CREATE OR REPLACE PACKAGE init_pkg IS g_max_sal employees.salary%TYPE; PROCEDURE get_emp_sal (p_emp_id IN employees.employee_id%TYPE); END init_pkg; CREATE OR REPLACE PACKAGE BODY init_pkg IS PROCEDURE get_emp_sal (p_emp_id IN employees.employee_id%TYPE) IS v_salary employees.salary%TYPE; BEGIN SELECT salary INTO v_salary FROM employees WHERE employee_id = p_emp_id; IF v_salary > (g_max_sal / 2) THEN dbms_output.put_line('This employee earns more than half the maximum salary'); ELSE dbms_output.put_line('This employee earns less than half'); END IF; END get_emp_sal; BEGIN SELECT MAX(salary) INTO g_max_sal FROM employees; END init_pkg; B. Now execute the following anonymous block. Explain the output. BEGIN init_pkg.get_emp_sal(101);
init_pkg.g_max_sal := 40000; init_pkg.get_emp_sal(101); END; This employee earns more than half the maximum salary This employee earns less than half Statement processed.
Este afisat faptul ca salariul sau este mai mult decat jumatate din salariul maxim iar dupa schimbarea salariului maxim, angajatul va castiga mai putin de jumatate din salariul maxim
