|Help with PL Packages [message #612301]
||Tue, 15 April 2014 18:22
Registered: April 2014
Hello, i am currently trying to go through oracle academy to teach myself pl sql. I'm having some trouble in some of the lab questions. for example i have: |
. Create the specification for the check_emp_pkg that 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.
so i did this:
CREATE OR REPLACE PACKAGE check_emp_pkg AS
g_max_length_of_service CONSTANT NUMBER := 100;
PROCEDURE chk_hiredate (p_date IN employees.hire_date%TYPE);
(p_empid IN employees.employee_id%TYPE,
p_mgr IN employees.manager_id%TYPE);
that went ok, but the next part:
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.
this is where i'm getting stuck- i'm starting with this
CREATE OR REPLACE PACKAGE BODY check_emp_pkg IS
PROCEDURE chk_hiredate (p_date IN employees.hire_date%TYPE)
IF MONTHS_BETWEEN(SYSDATE, p_date) > g_max_length_of_service * 12 THEN RAISE_APPLICATION_ERROR(-20200, 'Invalid Hiredate');
PROCEDURE chk_dept_mgr (p_empid IN employees.employee_id%TYPE, p_mgr IN employees.manager_id%TYPE)
If p_mgr THEN
if p_mgr THEN
but i'm just not sure what to write in the logic to get it to do what the question is asking it to do.....
sorry for the long question, can anyone help?
|Re: Help with PL Packages [message #612323 is a reply to message #612301]
||Wed, 16 April 2014 01:13
Registered: March 2014
Location: Cape Town
cman348powah wrote on Wed, 16 April 2014 01:22|
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
The fundamental rule of database processing is to use SQL. It is the most powerful, most flexible, most performant, language for crunching database data.
So rephrase that question from a SQL perspective. Craft a SQL that
a) finds the manager of an employee
b) compares that find with the supplied manager id
If the SQL fails (managers are different), then no data will be returned. This is thus what you check for from the process control/flow language (aka PL/SQL) - does the SQL return data? This answer determines whether the managers are the same or not.
As an example (do not want to code the answer for you):
// does employee exist?
select 1 from emp where empid = :1
// does employee exist with a salary of $500?
select 1 from emp where empid = :1 and sal = 500
As for the instructions you are working from - sounds to like poorly designed and written education material. A check typically means a getter (function) and not a setter (procedure). So the code unit should be a boolean function along the lines of:
create or replace function ExistEmp( empID integer ) return boolean is
select 1 into i from emp e
where e.empid = ExistEmp.empID;
return( true );
exception when NO_DATA_FOUND then
return( false );
[Updated on: Wed, 16 April 2014 01:14]
Report message to a moderator