Tabelle mir Cursor durchsuchen?

[hep83]

Hallo,

ich habe ein paar - wahrscheinlich einfache - Fragen:

Ich möchte mit Oracle folgendes machen:

1. Ein Tabelle durchsuchen und für jede Zeile ein Datum überprüfen

2. Wenn Überprüfung positiv, will ich eine E-Mail verschicken.

Mein Plan ist bis jetzt:

- Eine Prozedur überprüft jede Zeile mit einem Cursor und ruft bei
positivem Datum eine Prozedur auf, die eine E-Mail verschickt.

Jetzt meine Fragen dazu?

1. Geht das einfacher, oder ist das ne gute Lösung?
2. Muss ich einen implizieten oder expliziten Cursor verwenden?
3. Wartet die Prüf-Prozedur darauf, ob die Mail-Prozedur fertig ist, oder macht
die sofort weiter? -> Serverüberlastung?

Wie man sieht bin in ein Newbie... vielen Dank für jede Hilfe!!

[TomT]

Mach das einfach so:

create or repace procedure checkDate(p_date DATE) IS
BEGIN
  FOR i IN (SELECT col1,col2 FROM tabelle WHERE datum =p_date) LOOP
    --Hier Dein Mailversendecode
  END LOOP;
END;

Auf die Werte kannst Du mit i.<Spaltenname> zugreifen. Übergeben musst Du dann beim Aufruf nur das datum auf das Du prüfen möchtest. Wie das Versenden der Mails passiert ist unterschiedlich. Wenn Du direkt eine EMail schickst, dann ist es syncron, es gibt aber auch Möglichkeiten Sie in eine Warteschlange zu stellen (Bei ApplicationExpress wird das z.B. so gemacht) wo sie dann in gewissen Intervallen asynchron abgearbeitet werden können.

[hep83]

Danke TomT!
Sorry, dass ich mich jetzt erst melde, aber ich war im Urlaub...

Dein Tip hat mir sehr geholfen, doch nun tun sich weitere Fragen auf:

Ich möchte aus einer Tabelle einzelne Datentensätze herausfiltern und dann
zusammen als E-Mail verschicken, nicht einzeln wie eigentlich geplant.

Das verschicken an sich ist einfache, aber wie sammel ich die Datensätze? Meine Überlegungen:

1. Alle in ein Array schreiben und dann in eine E-Mail packen oder
2. Eine view verwenden -> problem: die Prozedur soll täglich gestartet werden, also müsste die view vorher stets aktualisiert werden. Geht das?

Gruß

[fanderlf]

Eine View ist immer aktuell, da sie nur eine Sicht auf Daten ist (im Endeffekt ist es nur ein SQL Kommando was ausgeführt wird sobald Du auf das View zugreifst). Ausser ihr dupliziert die Daten in eine zweite Tabelle (macht man manchmal aus Performance gründen), dann musst Du eben das Kommando zum Updaten der Daten vor die Schleife in Deiner PL/SQL Prozedur setzen.

[dbwizard]

Eine View ist immer aktuell, da sie nur eine Sicht auf Daten ist (im Endeffekt ist es nur ein SQL Kommando was ausgeführt wird sobald Du auf das View zugreifst)....

- Na ja , immer aktuell nicht gerade, wie du schreibst, muss das SQL ja jeweils ausgeführt werden. In diesem Fall wäre unter Umständen eine Materialized View einen Blick wert....die ist "immer" aktuell, solange du die entsprechenden Attribute korrekt setzt


Gruss

[fanderlf]

Na aber wenn ich doch in einer SQL Prozedur eine View verwende, also darauf zugreife, dann wird die SQL Abfrage dahinter ausgeführt sobald ich den Cursor öffne - die SQL Abfrage ausführe. Ein View ist in meinen Augen eine vorgefertigte SQL Abfrage - mehr nicht.
Oder kann man in der Datenbank Views verwenden die einmal erstellt werden und dann nur auf Bedarf geupdatet werden? Korrigiere mich wenn ich mich täusche, aber das würde man dann nicht über eine View machen sondern über CREATE TABLE AS ...

[dbwizard]

Na aber wenn ich doch in einer SQL Prozedur eine View verwende, also darauf zugreife, dann wird die SQL Abfrage dahinter ausgeführt sobald ich den Cursor öffne - die SQL Abfrage ausführe. Ein View ist in meinen Augen eine vorgefertigte SQL Abfrage - mehr nicht.
Oder kann man in der Datenbank Views verwenden die einmal erstellt werden und dann nur auf Bedarf geupdatet werden? Korrigiere mich wenn ich mich täusche, aber das würde man dann nicht über eine View machen sondern über CREATE TABLE AS ...

- Deine Aussage gem. View sind schon richtig, aber eben, du musst die View jeweils ausführen, um die aktuellen Daten zu sehen. Mit einer Materialized View ist dies nicht der Fall. Wenn du die MV korrekt erstellt hast, ist sie quasi wie eine Tabelle zu verwenden, die Aktualierung erfolgt immer dann, wenn sich die der MV zugrundeliegenden Tabellen ändern.


gruss

[hep83]

Hallo!
Ich habe es endlich geschafft mein Vorhaben in Code zu gießen. Es funktioniert soweit, nur frage ich mich, wie ich den Code optimieren kann. Er macht folgendes:

1. Es wird überprüft, ob Softwarelizenzen abgelaufen sind
2. Falls ja, werden Infos über ablaufende Lizenzen per E-Mail an mehrere Adressen verschickt

Ich mache mehrmals die gleiche SELECT Abfrage :

where sysdate+reminddaysbefore>=bestbefore and mail_send=0 and reminder=1

Ich überlege eine VIEW oder einen RECORDER zu benuzten. Macht das Sinn? Würden beim Verwenden einer VIEW die vielen SELECTs durch eine Abfrage ersetzt werden?

create or replace PROCEDURE PROCEDURE3 AS
BEGIN
DECLARE
v_From VARCHAR2(80) := '[email protected]';
v_Subject VARCHAR2(80) := 'Lizenzverwaltung';
v_Mail_Host VARCHAR2(30) := 'mail.gmx.de';
v_Mail_Conn utl_smtp.Connection;
crlf VARCHAR2(2) := chr(13)||chr(10);
counter int;

BEGIN

select count (*) into counter
from  licence 
where sysdate+reminddaysbefore>=bestbefore and mail_send=0 and reminder=1;

IF counter=0
THEN 
GOTO leer;
ELSE 
GOTO voll;
END IF;

<<voll>>
dbms_output.put_line('email wird versendet');
FOR i IN (
  SELECT
    name,
    mail
  FROM
    Benutzer
  ) LOOP
  

  
v_Mail_Conn := utl_smtp.Open_Connection(v_Mail_Host, 25);
utl_smtp.Helo(v_Mail_Conn, v_Mail_Host);
utl_smtp.Mail(v_Mail_Conn, v_From);
utl_smtp.Rcpt(v_Mail_Conn, i.mail);

UTL_SMTP.open_data(v_mail_conn);


utl_smtp.write_data(v_Mail_Conn,
'Date: ' || to_char(sysdate, 'Dy, DD Mon YYYY hh24:mi:ss') || crlf ||
'From: ' || v_From || crlf ||
'Subject: '|| v_Subject || crlf ||
'To: ' || i.mail || crlf ||
crlf ||
'Hallo Frau/Herr '|| i.name || ',' || crlf || crlf
|| 'folgende Lizenzen laufe bald aus:' || crlf || crlf
);

FOR n IN 
  ( SELECT
    id,
    key,
    software,
    version,
    bestbefore,
    reminder,
    mail_send
  FROM
    Licence 
  WHERE sysdate+reminddaysbefore>=bestbefore and mail_send=0 and reminder=1
    
  ) LOOP
    UTL_SMTP.write_data(v_mail_conn, n.id || '. ' || 'Key: ' || n.key || '   ' || n.software || '   '  || n.version || crlf );
  END LOOP;

UTL_SMTP.close_data(v_mail_conn);
utl_smtp.Quit(v_mail_conn);



END LOOP;

for j in ( select
id,
mail_send 
from  licence 
where sysdate+reminddaysbefore>=bestbefore and mail_send=0 and reminder=1)
loop 
UPDATE licence set mail_send=1 where id=j.id;
end loop;

<<leer>>
null;
END;
END PROCEDURE3;

Gruß

[TomT]

Da sind nicht wirklich GOTOs drinnen oder? Gibt's echt noch jemanden der das heute noch verwendet?

Wo willst Du denn eine View verwenden und warum? Past doch, dann sieht jeder sofort was Du selektieren möchtest. Nur wegen der GOTOs solltest eigentlich mit Tomaten beworfen werden

[Falk Prüfer]

...Nur wegen der GOTOs solltest eigentlich mit Tomaten beworfen werden

Aber in die Tomaten kleine Steine reintun, damits auch weht tut! *gg*

[hep83]

Aber ohne GOTOs kann ich diesen vorzüglichen Spaghetti-Code nicht schreiben

Ne, auf die GOTOs komme ich eigentlich klar... auch wenn's da sicher ne andere Möglichkeit gibt. Aber was ich doof finde:

Ich tätige 3 mal die gleiche SELECT Abfrage. Das scheint mir nicht sinnvoll zu sein. Wenn ich dafür ne VIEW verwende läuft die Prozedur doch schon mal schneller, da "angeblich" ne VIEW schon fertig kompiliert in der Datenbank liegt.
ABER wäre ein "Record" bei sowas nicht sinnvoll? Damit kenne ich mich leider nicht aus, aber wenn ihr sagt das wäre ganz toll und könnte rein theoretisch funktionieren, dann probiere ich das mal.

gruß.. und weck mit den tomaten!

[dimitri]

da "angeblich" ne VIEW schon fertig kompiliert in der Datenbank liegt.

Wenn Du ein Statement ausführst, dann wird ebenfalls ein Ausführungsplan erstellt und im Shared Pool abgelegt. Das ist das, was Du unter kompliliert bezeichnest. Der Zeitgewinn dadurch sollte sich also in engen Grenzen halten

Was Du machen kannst ist, dass Du dir eine temporäre Tabelle anlegst die Du am Anfang befüllst und dann nur noch daraus deine Daten beziehst.

Alles in eine nested table zu laden halte ich nicht für sinnvoll, da Du dann ganz schnell mal Speicherprobleme bekommst wenn die Datenmenge größer wird. Ist die Datenmenge aber nur relativ gering, dann reicht auch die GTT bei deinen drei Zugriffen.

Dim

[hep83]

Danke!

Werde mir die GTT mal anschauen und ausprobieren.

Gruß