Oracle PL/SQL Stored Procedures

[skizZ]

Hi zusammen,

sitze gerade an einer Aufgabe:

1a. Schreiben Sie eine Stored Procedure, die die Anzahl der Flugstunden eines Piloten (STUNDENANZAHL in der Tabelle PILOT) um die Anzahl der Flugstunden eines Fluges (FLUGZEIT in der Tabelle FLUGTYP) erhöht in Abhängigkeit von der PERSONALNUMMER des Piloten und der FLUGNUMMER.
1b. Überzeugen Sie sich von der Richtigkeit der Stored Procedure durch interaktiven Test in SQL*PLUS mit geeigneten Übergabeparametern.


Meine Überlegung dazu ist, wenn ich die Aufgabe richtig verstanden habe, ich soll also die Prozedur aufrufen mit z.b. updateflugstunden(personalnummer, flugnummer) und die entsprechenden Variablen dann benutzen. Also bekommt der Pilot mit der Nummer X dann die Flugzeit des Fluges Y gutgeschrieben. Sehe ich das so richtig?

CREATE OR REPLACE
PROCEDURE UPDATEFLUGSTUNDEN (ANR INT, FNR CHAR(6)) IS
DECLARE
  CURSOR FlugzeitCursor IS SELECT flugzeit FROM FLUGTYP F WHERE F.flugnummer = FNR;
  flugzeitRecord FlugzeitCursor%ROWTYPE;
BEGIN
IF NOT FlugzeitCursor%ISOPEN
    THEN
        OPEN FlugzeitCursor;  
    END IF;
FETCH FlugzeitCursor INTO FlugzeitRecord;
UPDATE PILOT SET STUNDENANZAHL = STUNDENANZAHL+FlugzeitRecord.Flugzeit WHERE Personalnummer = ANR;
END;

Sollte doch so klappen, oder?


Und zum zweiten Teil:

Nutzen Sie die Stored Procedure aus Teil 1. um einen Insert Trigger für die Tabelle FLIEGT zu schreiben, der bei jedem Insert auf einem FLIEGT-Datensatz die Flugstunden dem entsprechenden Piloten gutschreibt bzw. in Abzug bringt.

create or replace trigger test 
after
 insert of Flugstunden
 on Fliegt
 begin
  SELECT * FROM
  (SELECT ROWNUM rnum, flugnummer, personalnummer FROM 
    (SELECT f.flugnummer, f.personalnummer FROM FLUGTYP f 
    oder by f.flugnummer desc)
  WHERE ROWNUM <= 1) 
WHERE rnum > 0;
UPDATEFLUGSTUNDEN(f.personalnummer, f.flugnummer);
end;

Macht das Sinn?
Kann das alles leider nicht testen.

[TomT]

Sollte doch so klappen, oder?

1. Grundsatz: Nie PL/SQL verwenden wenn man es mit einfachem SQL machen kann.

update pilot  
   set stundenanzahl=stundenanzahl+(select sum(flugzeit) 
                                     from  flugtyp 
                                     where flugnummer = fnr)
   where personalnummer=anr;

Wenn die flugnummer eindeutig ist, kannst Du das SUM auch weglassen. Das Update packst dann in eine Procedure, den Cursor kannst weglassen.

Den COMMIT nach dem Aufruf nicht vergessen.

Zum Thema Trigger:
Ich versteh nicht was du mit dem vorhandenen Code machen möchtest, aber es ist auf jeden Fall nicht richtig
Du kannst mit der Variablen :new.spaltenname innerhalb eines Rowlevel Triggers auf die Werte des neuen Datensatzes zugreifen.

[skizZ]

Hallo Tom,

danke für die Antwort.
Was meinst du mit Commit?

Also muss dies dann so aussehen:

CREATE OR REPLACE
PROCEDURE UPDATEFLUGSTUNDEN (ANR INT, FNR CHAR(6)) IS
update pilot  
   set stundenanzahl=stundenanzahl+(select sum(flugzeit) 
                                     from  flugtyp 
                                     where flugnummer = fnr)
   where personalnummer=anr;
END;

Im zweiten Teil, sollen wir jedes Mal, wenn ein weiterer Datensatz in die Tabelle "FLIEGT" gespeichert wird, eben diese Funktion aufrufen.

Somit sollte doch folgendes dann funktionieren?

create or replace trigger test 
after
 insert of Flugstunden
 on Fliegt
 begin
UPDATEFLUGSTUNDEN(:new.personalnummer, :new.flugnummer);
end;

[TomT]

Also muss dies dann so aussehen:

Hmm hast Du keine lokale Oracle Installation? Deine Prozedur kompiliert nämlich nicht. Bei der Parameterübergabe werden keine Längenangaben gemacht. Anstelle CHAR(6) muss es CHAR heißen. Ob es fachlich richtig ist hier CHAR und nicht VARCHAR2 zu verwenden kann ich nicht beurteilen - dazu müsste ich den Datentyp der Spalte kennen.

m zweiten Teil, sollen wir jedes Mal, wenn ein weiterer Datensatz in die Tabelle "FLIEGT" gespeichert wird, eben diese Funktion aufrufen.

Und dann? Bei jedem Insert rust Du die Procedur auf, die die Summe aus flugtyp ermittelt. In dieser Tabelle hat sich aber überhaupt nichts verändert.
Wenn dann müsstest Du doch aus FLIEGT selektieren. In einem row level trigger wiederum kannst Du aber nicht auf die eigene Tabelle selektieren.

Ergo: Technisch mag das ganze kompilieren, fachlich ist es immer noch falsch. Bist Du eigentlich sicher, dass Du die Aufgabe hier richtig eingestellt hast? Die Flugzeit in der Tabelle FLUGTYP hören sich etwas seltsam an.

[dbwizard]

Hallo Tom,

Also muss dies dann so aussehen:

CREATE OR REPLACE
PROCEDURE UPDATEFLUGSTUNDEN (ANR INT, FNR CHAR(6)) IS
update pilot  
   set stundenanzahl=stundenanzahl+(select sum(flugzeit) 
                                     from  flugtyp 
                                     where flugnummer = fnr)
   where personalnummer=anr;
END;

Hallo,

Dies ist syntaktisch falsch:
- Du musst die Parameter der Prozedur als IN oder OUT (oder beides) definieren¨
- es fehlt das Schlüsselwort BEGIN

Anbei ein Beispiel für eine syntaktisch richtige Prozedur :

PROCEDURE test
   ( param1 IN datatype DEFAULT default_value,
     param2 IN OUT datatype)
   IS
    
   variable_name                 datatype;
   -- Declare program variables as shown above
BEGIN
    statements;
EXCEPTION
    WHEN exception_name THEN
        statements ;
END; -- Procedure