Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2003
    Posts
    14

    Unanswered: need help with procedure

    I have 3 table called List1. List 2, and List3
    The columns in List1 are ID, Name
    The columns in List2 are ID (Foreign Key references List1), companyID (Foreign Key references List1)
    The columns in List3 are companyID, companyName

    I want to write a producere to insert a person to List1 and List2 and then ensure that the companyID is valid (between 1-10), and if its invalid i have to rollback the person insert operation

    PLEASE HELP!
    Last edited by aurel1412; 12-13-03 at 04:35.

  2. #2
    Join Date
    Oct 2003
    Location
    Germany - Stuttgart
    Posts
    14
    I think you should at first evaluate the company-id from table3 and THEN insert the person and the link in table2. so you don't need rollback or delete...

    function get_company_id (p_name in varchar2) return number
    is
    l_n_ret number;
    begin
    select id
    into l_n_ret
    from table3
    where company_name = p_name;

    return l_n_ret;

    exception when others then return 0;
    end;


    procedure insert_person(p_comp_name in varchar2.p_pers_name in varchar2)
    is
    l_comp_id number;
    begin
    l_comp_id:=get_company_id(p_comp_name);
    if l_comp_id > 0
    then
    insert the data in table1 and 2
    commit;
    end if;
    end;

Posting Permissions

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