Frage zu Triggern & Prozeduren in PL/SQL --> dringend

M

Mediax

Grünschnabel
Frage zu Triggern & Prozeduren in PL/SQL Oracle 9.1 --> dringend

Hi,
ich habe momentan das große Problem, dass ich aufgrund meiner Ausbildung zum Medieninformatiker mit dem Fall Trigger und Prozeduren in pl/sql sehr viel auseinander setzen muss, denn diesen Freitag steht in diesem Fach eine Abschlussprüfung an.
Deswegen sind meine Fragen zum thema DB , insbesondere pl/sql, sehr dringend.

Wir haben von unserem Dozenten 2 Aufgaben bekommen, und zwar einen Trigger zu erstellen, der bestimmte neue Werte aus Tabellen ausgibt und eine Prozedur, die bestimmte Dinge ausgibt.
Dazu habe ich folgende Probleme :
Zum Trigger
Beim Trigger ist mein Problem , dass ich nicht genau weiss wie ich an die Informationen "Ergebnis des Spielers" und "Ergebnis des Clans " kommen soll. Ich weiss, das man diese sachen im Grunde berechnen kann, aber wo kann ich die Informationen reintuen (Variable, Array, Cursor?) und wie hole ich mir die Infos überhaupt ( SELECT abfrage, view?). Muss ich dazu auch die Tabellen angeben, aus denen ich die Informationen hole? Kann man Cursor, Array etc überhaupt in Prozeduren verwenden und wenn nein, was verwende ich stattdessen?
Aber habe mir schon den Kopf darüber zerbrochen aber ich komme einfach nicht auf eine sinnvolle Lösung.

Außerdem
Zur Prozedur
Hier weiss ich nicht so recht, wie ich das Ergebnis pro Clan mit den dazugehörigen Spielern ausgeben soll.

Gegeben ist das folgende Datenmodell:

Spieler (snr, vorname, nachname, nickname, cnr) PK: snr
Clan (cnr, bezeichnung) PK: cnr
Wars (wnr, snr, ergebnis) PK: wnr, snr

Aufgaben :
Trigger
Erstellen Sie einen Trigger, der bei jeder Änderung (insert, update(ergebnis), delete) in der Tabelle Wars feuert. Jede Änderung soll sofort angezeigt werden:Vorname, Nachname, Clan, Ergebnis des Spielers, Ergebnis des Clans,
Durchschnitt des Spielers

Meine Lösung ( bzw der versuchte Ansatz :) ):

Code: 
create or replace procedure ergebnisse
is
begin
declare
		wnr		wars.wnr%TYPE;
		bezeichnung	clan.bezeichnung%TYPE;
		ergebnisclan	smallint;
		vorname	spieler.vorname%TYPE;
		nachname	spieler.nachname%TYPE;
		ergebnis		wars.ergebnis%TYPE;
	
		cursor		ergebniscursor1 is select* from ergebnisseclan;
		cursor		ergebniscursor2 is select * from ergebnissespieler;

begin
open		ergebniscursor1;
open		ergebniscursor2;

fetch cursor ergebniscursor 1 into wnr, bezeichnung, ergebnisclan;
fetch cursor ergebniscursor 2 into vorname, nachname, ergebnis;
while		ergebniscursor1 % FOUND and ergebniscursor2 % FOUND
loop
	dbms.output.put_line ( ‘Warnummer: ‘ || wnr);
	dbms.output.put_line ( ‘--------------------------‘);
	dbms.output.put_line( bezeichnung  || ‘    ‘ || ergebnisclan);
		dbms.output.put_line ( vorname || ‘   ‘ || nachname || ‘  ‘  || ergebnis);

	fetch cursor ergebniscursor 1 into wnr, bezeichnung, ergebnisclan;
fetch cursor ergebniscursor 2 into vorname, nachname, ergebnis;
	end loop;
	close ergebniscursor1;
	close ergebniscursor2;
	end;
end;

Prozedur
Aufgabe:
Erstellen Sie eine Prozedur, die jeweils das Ergebnis pro Clan und den jeweils zugehörigen Spielern ausgibt.
Bsp.: Warnummer: 1
-------------------------------
Alpha-Clan 20
-------------------------------
Thomas Alba 15
Jürgen Fert 5

-------------------------------
Beta-Clan 10
-------------------------------
Jonny Depp 8
Tom Cruise 2

Mein Lösungsansatz:

Code: 
create or replace view ergebnisseclan ( wnr, bezeichnung, ergebnisclan) as
select	wnr, bezeichnung, sum (snr*ergebnis) as ergebnisclan 
from		wars, clan,spieler
where		spieler .snr = clan.snr 		
group by 	cnr
order by		cnr desc;

create or replace view ergebnissespieler (vorname, nachname,ergebnis) as
select		vorname, nachname, ergebnis
from		clan, spieler
where		clan.cnr = spieler.cnr 
group by	cnr
order by		cnr desc;

create or replace procedure ergebnisse
is
begin
declare
		wnr		wars.wnr%TYPE;
		bezeichnung	clan.bezeichnung%TYPE;
		ergebnisclan	smallint;
		vorname	spieler.vorname%TYPE;
		nachname	spieler.nachname%TYPE;
		ergebnis		wars.ergebnis%TYPE;
	
		cursor		ergebniscursor1 is select* from ergebnisseclan;
		cursor		ergebniscursor2 is select * from ergebnissespieler;

begin
open		ergebniscursor1;
open		ergebniscursor2;

fetch cursor ergebniscursor 1 into wnr, bezeichnung, ergebnisclan;
fetch cursor ergebniscursor 2 into vorname, nachname, ergebnis;
while		ergebniscursor1 % FOUND and ergebniscursor2 % FOUND
loop
	dbms.output.put_line ( ‘Warnummer: ‘ || wnr);
	dbms.output.put_line ( ‘--------------------------‘);
	dbms.output.put_line( bezeichnung  || ‘    ‘ || ergebnisclan);
		dbms.output.put_line ( vorname || ‘   ‘ || nachname || ‘  ‘  || ergebnis);

	fetch cursor ergebniscursor 1 into wnr, bezeichnung, ergebnisclan;
fetch cursor ergebniscursor 2 into vorname, nachname, ergebnis;
	end loop;
	close ergebniscursor1;
	close ergebniscursor2;
	end;
end;

Vielen Dank schonmal im Vorraus für eure Hilfe :)
Mediax
 
Zuletzt bearbeitet:
Ähm .. ich seh bei deinem Datenmodell nicht, das noch mehr Daten über einzelne Spieler existieren, als Vorname und Nachname?!

Zu dem Trigger : Ein Trigger ist nicht nur eine Prozedur. Ein Trigger wird automatisch ausgelöst, wenn er für die entsprechenden Aktionen deklariert ist.

Siehe beispielsweise hier ..

Also in deinem Fall:
create trigger trigger-name after delete or update or insert on table-name ...

Ansonsten kann ich mal ein bißchen Quelltext aus meinen PL/SQL Prozeduren aus meinem Studienprojekt letztes Semester posten. Vielleicht hilft dir davon was weiter ..

Code: 
Create or replace procedure k_means1(anzahlCluster in Number) is

  -- Clustering von Startpreis (x)  <-> Differenz (y)
  
    
  --   ANFANG VARIABLENDEKLARATION
  
  i number := 0;

  cursor init1 is
         Select "Startpreis", "Differenz" from DM_KMEANS1_BASE group by "Startpreis", "Differenz";
    
  cursor centers is
         Select "center_id","x","y" from center;
         
  cursor clusterMitte is
           select sum("Val1"), sum("Val2"), "center_id", count(*) as clusterCount from temp_Cluster group by "center_id";
           
  cursor clusterAuslesen IS 
         Select "tmp_cluster_id", "Val1", "Val2", "V1V2Dist2Center", "center_id" from temp_cluster;
           

  startPreis DM_KMEANS1_BASE."Startpreis"%TYPE;
  myDifferenz DM_KMEANS1_BASE."Differenz"%TYPE;
  
  stMaximum Number;
  stMinimum Number;
  diffMaximum Number;
  diffMinimum Number;
  
  minCenterID Number;
  
  minCenterX Number;
  minCenterY Number;
  
  maxCenterX Number;
  maxCenterY Number; 
  
  newCenterX Number;
  newCenterY Number; 
  
  oldCenterID Number;
  newCenterID Number;
 
  distanz Number;
  distance2center Number;
  newMinDistance Number;
  minDistance Number;
  anzahlTupel Number;
  newSchwelle Number;
  Schwellwert Number;

  iterations Number;
  doChanges Number;
  doneChanges Number;
  updates Number;
  

  
  -- Datenstruktur für schnelle Centerabfrage  
  type centerTableType is table of Number INDEX BY BINARY_INTEGER;
  centerTable centerTableType;
    
  -- temporäre Variablen
  merke1 Number;
  merke2 Number;
  merke3 Number;
  merke4 Number;
  merke5 Number;
  
  counter Number;
  
  
  -- ENDE VARIABLENDEKLARATION
  
  

begin

  --  K-Means Algorithmus Stefan Lehmann
  --  
  --  02.06.2006
  --  
  --
  -- temporäre Tabelleninhalte löschen
  
  DELETE FROM center;
  DELETE FROM temp_cluster;
  
  counter := 0;

  -- Erstellung der ersten Zufallszentren
  
  open init1;
  
  loop
        
        fetch init1 into merke1, merke2;
        
        EXIT WHEN init1%NOTFOUND;
        
        anzahlTupel := init1%ROWCOUNT;
  
  end loop;
  
  close init1;
  
  open init1;
  
          --dbms_output.put_line('Anzahl: ' || anzahlTupel);
          
          Schwellwert := anzahlTupel / (anzahlCluster-1);
          
          --dbms_output.put_line('Schwellwert: ' || Schwellwert);
          
          newSchwelle := Schwellwert;
  
          fetch init1 into merke1, merke2;
       
          minCenterX := merke1;
          minCenterY := merke2;
           
          INSERT INTO center VALUES ( test_seq.nextval, minCenterX, minCenterY ); 
          
          counter := 1;
          
          loop
          
              fetch init1 into merke1, merke2;
              
              EXIT WHEN init1%NOTFOUND;
              
              IF counter > newSchwelle + 1 THEN
              
                 INSERT INTO center VALUES ( test_seq.nextval, merke1, merke2 ); 
                 
                 --dbms_output.put_line('newSchwelle: ' || newSchwelle);
                 --dbms_output.put_line('counter: ' || counter);
                 newSchwelle := newSchwelle + Schwellwert; 
              
              END IF;
             
             counter := counter + 1;
            
          end loop;
              
       INSERT INTO center VALUES ( test_seq.nextval, merke1, merke2 ); 
        
  close init1;
  
  -- Array mit Centerdaten füllen
  
  open centers;
  
  counter := 0;
  
  loop
  
      fetch centers into merke1,merke2,merke3;
      EXIT WHEN centers%NOTFOUND; 
      
        centerTable(counter) := merke1;
        centerTable(counter+1) := merke2;
        centerTable(counter+2) := merke3;
        dbms_output.put_line('1: ' || centerTable(counter));
        dbms_output.put_line('2: ' || centerTable(counter+1));
        dbms_output.put_line('3: ' || centerTable(counter+2));
       
      counter := counter + 3;    
     
  end loop;

  close centers;
  
  -- Ende Array füllen
  
  -- Initiierende Distanzberechnungen durchführen --
 
  open init1;
  
  loop
      minDistance := 999999999999;
      minCenterID := 0; 
      counter := 0;
  
      fetch init1 into startPreis,myDifferenz;
      EXIT WHEN init1%NOTFOUND;
      
      loop       
                 
                 EXIT WHEN counter = anzahlCluster * 3;
                 
                 merke1 := SQRT(POWER((startPreis - centerTable(counter+1)),2) + POWER((myDifferenz - (centerTable(counter+2))),2));
                 
                 -- Center mit geringster Entfernung merken
                 
                 IF merke1 < minDistance THEN
                    
                    minDistance := merke1;
                    
                    minCenterID := centerTable(counter); 
                    
                 END IF;
       
                 counter := counter + 3;
    
     end loop;
     
     -- dbms_output.put_line('MinDistanz: ' || minDistance);
     
     INSERT into temp_Cluster VALUES (test_seq.nextval, startPreis, myDifferenz, minDistance, minCenterID); 
     
  end loop;
  
  close init1;
  
  -- ENDE Initialisierung ----------> Daten sind vorbereitet ....
  
  
  
  
  iterations := 0; -- Zähler für die durchgelaufenen Algorithmusdurchläufe 
    
  LOOP -- ANFANG HAUPTSCHLEIFE 
  
  -- Neue Clustermittelpunkte erzeugen
  
  doneChanges := 0;
  
  updates := 0;
    
  open clusterMitte;
  
       loop
          --  select sum("Val1"), sum("Val2"), "center_id", count(*) as clusterCount from temp_Cluster group by "center_id";
          fetch clusterMitte INTO merke1, merke2, merke3, merke4;
          
          EXIT When clusterMitte%NOTFOUND;
          
          newCenterX := merke1 / merke4;
          newCenterY := merke2 / merke4;
          
          UPDATE center SET "x" = newCenterX, "y" = newCenterY WHERE "center_id" = merke3;
          
          dbms_output.put_line('Neues ZentrumX: ' || newCenterX);
          dbms_output.put_line('Neues ZentrumY: ' || newCenterY);
       
       end loop;
    
  close clusterMitte;
  
  -- Array mit Centerdaten füllen
  
  open centers;
  
  counter := 0;
  
  loop
  
      fetch centers into merke1,merke2,merke3;
      EXIT WHEN centers%NOTFOUND; 
      
        centerTable(counter) := merke1;
        centerTable(counter+1) := merke2;
        centerTable(counter+2) := merke3;
        dbms_output.put_line('Zentrum ID : ' || centerTable(counter));
        dbms_output.put_line('Zentrum X : ' || centerTable(counter+1));
        dbms_output.put_line('Zentrum X : ' || centerTable(counter+2));
       
      counter := counter + 3;    
     
  end loop;

  close centers;
  
  -- Ende Array füllen 
  
  -- Bestimmung der Abstände zu den neuen Clustermittelpunkten
  
  -- Select "tmp_cluster_id", "Val1", "Val2", "V1V2Dist2Center", "center_id" from temp_cluster;
  
  open clusterAuslesen;
  
       loop
       
           fetch clusterAuslesen into merke1, merke2, merke3, merke4, merke5;
           
           EXIT WHEN clusterAuslesen%NOTFOUND;
           
           counter := 0;
           
           minDistance := 9999999999;
           
           newMinDistance := 0;
           
           doChanges :=0;
           
           loop       
                 
                 EXIT WHEN counter = anzahlCluster * 3;
                 
                 distance2center := SQRT(POWER((merke2 - centerTable(counter+1)),2) + POWER((merke3 - (centerTable(counter+2))),2));
                 
                 -- Center mit geringster Entfernung merken
                 
                 IF distance2center < minDistance THEN
                 
                    minDistance := distance2center;
                 
                    newMinDistance := distance2center;
                    
                    minCenterID := centerTable(counter);
                    
                    IF minCenterID <> merke5 THEN 
                          
                          doChanges := 1;
                          
                          dbms_output.put_line('neu: ' || minCenterID);
                          dbms_output.put_line('alt: ' || merke5);
                          
                    END IF;
                    
                 END IF;
       
                 counter := counter + 3;
    
            end loop;
            
            if doChanges = 1 THEN
            
               updates := updates + 1;
               
               UPDATE temp_Cluster Set "V1V2Dist2Center" = newMinDistance, "center_id" = minCenterID WHERE "tmp_cluster_id" = merke1;
               
               doneChanges := 1;
               
            END IF;
  
       end loop;
 
  close clusterAuslesen;
  
  iterations := iterations + 1;   
  
  dbms_output.put_line('Updates: ' || updates);
  
  EXIT WHEN doneChanges = 0 OR iterations > 20;
  
  END LOOP; -- ENDE HAUPTSCHLEIFE
  
  dbms_output.put_line('Endebedingungen: ' || doneChanges || ' ' || iterations);
  
  commit;
  
end k_means1;

Gruß Stefan ..
 
Zuletzt bearbeitet:
Um antworten zu können musst du eingeloggt sein.

Neue Beiträge

Zurück