Home » SQL & PL/SQL » SQL & PL/SQL » Help with PL Packages (Oracle SQL)
Help with PL Packages [message #612301] Tue, 15 April 2014 18:22 Go to next message
cman348powah
Messages: 1
Registered: April 2014
Junior Member
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);
PROCEDURE chk_dept_mgr
(p_empid IN employees.employee_id%TYPE,
p_mgr IN employees.manager_id%TYPE);
END check_emp_pkg;


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)
IS BEGIN
IF MONTHS_BETWEEN(SYSDATE, p_date) > g_max_length_of_service * 12 THEN RAISE_APPLICATION_ERROR(-20200, 'Invalid Hiredate');
END IF;
END chk_hiredate;
PROCEDURE chk_dept_mgr (p_empid IN employees.employee_id%TYPE, p_mgr IN employees.manager_id%TYPE)
IS BEGIN
If p_mgr THEN
dbms_output.put_line('success');
if p_mgr THEN
RAISE_APPLICATION_ERROR(-20200, 'No_Data_Found');

END chk_dept_mgr;
END check_emp_pkg;


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 Go to previous message
vslabs
Messages: 26
Registered: March 2014
Location: Cape Town
Junior Member
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
  i integer;
begin
  select 1 into i from emp e
  where e.empid = ExistEmp.empID;
  return( true );
exception when NO_DATA_FOUND then
  return( false );
end;

[Updated on: Wed, 16 April 2014 01:14]

Report message to a moderator

Previous Topic: Create table in sqlplus script
Next Topic: Date in Words
Goto Forum:
  


Current Time: Thu Mar 28 16:02:54 CDT 2024