Need a PL/SQL procedure for master detail tables

[barnard90]

Hi

I need to populate data into Master detail tables :-
Department ( Master ) and Employee ( Detail )


They get data from a table called " EmpData " . I need a PL/SQL procedure that fills the data simultaneously into Department and Employee at the same time .

The Data of " Emp Data " is

Empno EmpName Sal DeptNo DeptName DeptLoc
--------------------------------------------------------
101 John 1000 10 Finance London
102 David 1500 10 Finance London
103 Alice 5000 20 Sales Paris
104 Mike 3000 20 Sales Paris
105 Greg 2000 30 Production Rome
106 John 6000 40 Personnel Berlin

I need to a PL/SQL procedure to populate data like this

" Employee " Table (Detail )

Empno EmpName Sal DeptNo
--------------------------------
101 John 1000 10
102 David 1500 10
103 Alice 5000 20
104 Mike 3000 20
105 Greg 2000 30
106 John 6000 40


"Department " Table (Master)

DeptNo DeptName DeptLoc
---------------------------------
10 Finance London
20 Sales Paris
30 Production Rome
40 Personnel Berlin

Could some one please suggest the code

thanks

 

[feherke]

Hi

Why procedure ?

[b]insert into[/b] Detail [b]select[/b] Empno,EmpName,Sal,DeptNo [b]from[/b] EmpData;
[b]insert into[/b] Master [b]select distinct[/b] DeptNo,DeptName,DeptLoc [b]from[/b] EmpData;

Feherke.

http://rootshell.be/~feherke/

 

[BJCooperIT]

If there are constraints on the Detail table you want to insert into the master table first, then the detail.

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
My website: www.EmuProductsPlus.com

 

[feherke]

Hi

You are right. Thanks.

Feherke.

http://rootshell.be/~feherke/

 

[feherke]

Hi

While you asked for a [tt]procedure[/tt], here it is. But please do not use it. The above two [tt]insert[/tt]s are much more efficient.

[b]create or replace procedure[/b] spreadempdata [b]is[/b]
  [b]cursor[/b] cur [b]is select[/b] * [b]from[/b] empdata;
  [b]cursor[/b] mas (no [b]number[/b]) [b]is select[/b] count(*) [b]from[/b] master [b]where[/b] deptno=no;
  nr [b]number[/b];
[b]begin[/b]
  [b]for[/b] rec [b]in[/b] cur [b]loop[/b]
    [b]open[/b] mas(rec.deptno);
    [b]fetch[/b] mas [b]into[/b] nr;
    [b]if[/b] nr=0 [b]then[/b]
      [b]insert into[/b] master [b]values[/b] (rec.deptno,rec.deptname,rec.deptloc);
    [b]end if[/b];
    [b]close[/b] mas;
    [b]insert into[/b] detail [b]values[/b] (rec.empno,rec.empname,rec.sal,rec.deptno);
  [b]end loop[/b];
  [b]commit[/b];
[b]end[/b];

Feherke.

http://rootshell.be/~feherke/