DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: need help in rewriting sql into plsql

  1. #1
    Join Date
    Aug 2000
    Location
    Chicago IL
    Posts
    586

    need help in rewriting sql into plsql

    Hello,
    I wanted to take the following sql and put it in a plsql precedure so that a user can just execute that procedure when ever they wanted to do the following operation.


    spool delete_one_employee
    delete from HRW_HEALTH_PLAN_DEPENDENTS where
    health_plan_key in
    (select health_plan_key from hrw_dependents where employee_id = '&&emp_id');

    delete from HRW_INSURANCE_BENEFICIARIES where
    insurance_key in
    (select insurance_key from hrw_insurances where employee_id = '&&emp_id');

    delete from HRW_INSURANCE_OFFERS where employee_id = '&&emp_id';

    spool off
    thanks
    Jigar
    "High Salaries = Happiness = Project Success."

  2. #2
    Join Date
    Aug 2000
    Location
    Chicago IL
    Posts
    586
    would this work?

    create or replace procedure delete_one_employee as

    /*THIS SCRIPT DELETES AN ENTIRE EMPLOYEE'S "RECORD".
    I.E. THE EMPLOYEE'S ROWS FROM ALL TABLES THAT
    * HAVE AN EMPLOYEE_ID COLUMN.*/

    BEGIN

    delete from HRW_HEALTH_PLAN_DEPENDENTS where
    health_plan_key in
    (select health_plan_key from hrw_dependents where employee_id = '&&emp_id');

    delete from HRW_INSURANCE_BENEFICIARIES where
    insurance_key in
    (select insurance_key from hrw_insurances where employee_id = '&&emp_id');

    delete from HRW_INSURANCE_OFFERS where employee_id = '&&emp_id';
    delete from HRW_MISC_OFFERS where employee_id = '&&emp_id';
    delete from HRW_FLEX_SPEND_OFFERS where employee_id = '&&emp_id';

    end;
    "High Salaries = Happiness = Project Success."

  3. #3
    Join Date
    May 2002
    Posts
    2,645
    Wouldn't it be better coding practice for the procedure to have the employee ID passed in as a parameter and remove the prompts?

    create or replace procedure delete_one_employee (emp_id IN NUMBER) as...

  4. #4
    Join Date
    Aug 2000
    Location
    Chicago IL
    Posts
    586
    stecal..i am a new at plsql..thats why i was askinng for help...
    "High Salaries = Happiness = Project Success."

Posting Permissions

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


Click Here to Expand Forum to Full Width