Results 1 to 8 of 8

Thread: Procedures

  1. #1
    Join Date
    Dec 2010
    Posts
    10

    Unanswered: Procedures

    Hi there i am trying to answer the following question:
    2.2 Create a public procedure called fire_emp that accepts one parameter – either an emp_id or an
    employee email. Using either the ID or the email, delete a record from the emp table.

    I have written the following code and it works, but it will only work if the parameter is the emp_id. I need to modify it to have the ability to accept the parameter of email or id and still function.

    Any ideas?

    Code:
      PROCEDURE fire_emp (p_emp_id NUMBER) IS
      BEGIN                         --Deletes a record for an employee from their employee ID
        DELETE FROM emp
        WHERE employee_id = p_emp_id;
      END fire_emp;
    Thanks

  2. #2
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Well, it depends on table description. If both E_MAIL and EMP_ID are characters - no problem. Otherwise, datatype conversion might be needed, exception handlers used, etc. It means that you should have provided some more information.

    The simplest solution (where both columns' datatypes are CHAR): create sample table and insert some records:
    Code:
    SQL> create table test
      2    (emp_id varchar2(5),
      3     e_mail varchar2(30),
      4     ename  varchar2(30));
    
    Table created.
    
    SQL> insert all
      2    into test (emp_id, e_mail, ename) values (1, 'l@mail.com', 'Little')
      3    into test (emp_id, e_mail, ename) values (2, 'f@mail.com', 'Foot')
      4    into test (emp_id, e_mail, ename) values (3, 'b@mail.com', 'Balistic')
      5  select * from dual;
    
    3 rows created.
    
    SQL>
    Create a procedure:
    Code:
    SQL> create or replace procedure fire_emp (par_emp in char) is
      2  begin
      3    delete from test
      4      where emp_id = par_emp
      5         or e_mail = par_emp;
      6  end;
      7  /
    
    Procedure created.
    
    SQL>
    Test it:
    Code:
    SQL> select * from test;
    
    EMP_I E_MAIL                         ENAME
    ----- ------------------------------ ------------------------------
    1     l@mail.com                     Little
    2     f@mail.com                     Foot
    3     b@mail.com                     Balistic
    
    SQL> exec fire_emp(1);
    
    PL/SQL procedure successfully completed.
    
    SQL> select * from test;
    
    EMP_I E_MAIL                         ENAME
    ----- ------------------------------ ------------------------------
    2     f@mail.com                     Foot
    3     b@mail.com                     Balistic
    
    SQL> exec fire_emp('b@mail.com');
    
    PL/SQL procedure successfully completed.
    
    SQL> select * from test;
    
    EMP_I E_MAIL                         ENAME
    ----- ------------------------------ ------------------------------
    2     f@mail.com                     Foot
    
    SQL>

  3. #3
    Join Date
    Dec 2010
    Posts
    10
    Sorry forgot to put that information down.

    Email is VARCHAR and employee_id is NUMBER

    How do i handle the different datatypes?

  4. #4
    Join Date
    Dec 2010
    Posts
    10
    I wrote the following and thought it was working, but it turned out i was still running the ID not the email. It comes up with a converstion error.

    Please help

    Code:
      PROCEDURE fire_emp (p_emp NUMBER) IS
      v_emp VARCHAR2(25);
      BEGIN                         --Deletes a record for an employee from their employee ID
        v_emp:= TO_CHAR(p_emp);
        DELETE FROM emp
        WHERE v_emp = employee_id;
        EXCEPTION
        WHEN VALUE_ERROR THEN
        DELETE FROM emp
        WHERE v_emp = email;
      END fire_emp;
    I have attached all my file so people can see it
    Attached Files Attached Files
    Last edited by bbalistic8; 02-03-11 at 06:05.

  5. #5
    Join Date
    Mar 2010
    Location
    Vienna, Austria
    Posts
    149
    I woul use a package with overlay technique for this sort of problem:

    Code:
    create or replace package employee
    is
     procedure fire (pEmpNo   in number);
     procedure fire (pEmpMail in varchar2);
    end employee;
    /
    
    create or replace package body employee
    is
    
      procedure fire (pEmpNo   in number)
      is
      begin
        delete from emp where employee_id = pEmpNo;
      end fire;
      
      procedure fire (pEmpMail varchar2)
      is
      begin
        delete from emp where email = pEmpMail;
      end fire;
      
    end employee;
    /
    "There is always an easy solution to every problem - neat, plausible, and wrong."
    -- H.L. Mencken

  6. #6
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    I like the idea, Magicwand!

    As Balistic tried the procedure on his own, here's how it should look like:
    Code:
        create or replace procedure fire_emp (par_emp in char) is
          l_id number;
        begin
          l_id := to_number(par_emp);
          
          delete from test
            where emp_id = par_emp;
            
        exception
          when value_error then
            delete from test
              where e_mail = par_emp;
        end;
        /

  7. #7
    Join Date
    Feb 2005
    Posts
    57
    or you could create a procedure that takes two parameters. Then test which is not null and proceed accordingly

  8. #8
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Sure, but problem, as stated in the first message, is that he has to use a single parameter.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •