Hallo,
ich habe ein kleines Problem mit einem PL/SQL-Cursor und hoffe auf Hilfe:
cursor abc (parameter) is
select xxx
from yyy
where yyy.feld1 in (parameter)
feld1 enthält numerische Werte, z.b. 10, 20, 30.
Ich möchte dem Parameter entweder nur einen Wert (20) oder auch mehrere Werte (10,30) so mitgeben, dass die Abfrage die gesuchten Datensätze liefert.
Geht das - und falls ja - wie?
Ratlose Grüsse,
die Lisa
Hallo Lisa,
ja das geht! Sieh Dir mal die SQL-Doku Kapitel 7 „Performing SQL Operations with Native Dynamic SQL“ (falls Du es nicht online hast: http://www.filibeto.org/sun/lib/nonsun/oracle/10.2.0…
) Beispiel 7-8 scheint Dein Problem zu lösen.
MfG Georg V.
P.S.: Falls das die Frage war: Parameter muss ein String sein
Hi Lisa,
ergänzend zur Antwort von Georg:
Ich hoffe, ich habe dein Datenmodell richtig verstanden, und es sieht in etwa so aus:
... | feld1 | ...
----+-------+----
... | 10 | ...
... | 20 | ...
... | 30 | ...
und nicht etwa so:
... | feld1 | ...
----+-------+----
... | 10 | ...
... | 10,20 | ...
... | 20,30 | ...
In letzterem Fall solltest du (allerhöchstwahrscheinlich) dringend das Modell normalisieren, sonst stehst du früher oder später vor einem Problem, das sich aufgrunddessen gar nicht oder nur sehr umständlich lösen lässt.
Zur eigentlichen Frage:
Ich möchte dem Parameter entweder nur einen Wert (20) oder
auch mehrere Werte (10,30) so mitgeben, dass die Abfrage die
gesuchten Datensätze liefert.
Ich würde dynamisches SQL vermeiden, wenn sich das machen lässt. Vielleicht kannst du ja ein wenig mehr zur konkreten Problemstellung verraten.
Geht das - und falls ja - wie?
Auf jeden Fall schon einmal so, wie Georg geschrieben hat (ich glaube übrigens er meint Beispiel 7-6). Aber wie gesagt: Dynamisches SQL ist nur sehr gezielt einzusetzen (nämlich da, wo es anders nicht mehr geht) - Begründung dazu liefere ich bei Interesse gerne nach.
Gruß
Martin
Hallo ,
mein Datenmodell sieht so aus:
... | feld1 | feld2 |...
----+-------+-------+---
... | 10 | AB |...
... | 20 | AB |...
... | 30 | CDE |...
feld1 ist zusammen mit feld2 eindeutig.
Ich will nun - mit einem Select - manchmal genau einen Datensatz holen
…where feld2 = ‚AB‘ and feld1 = 10
Oder aber eben die Bedingung „aufweichen“ mit
…where feld2 = ‚AB‘ and feld1 in (10, 20)
Den Wert für feld1 möchte ich als Parameter an das select schicken, aber eben flexibel für 1-x Werte von feld1.
Ich weiss nicht so recht, wie ich das besser beschreiben soll. Aber dynamisches sql will ich an der Stelle definitiv vermeiden.
Momentan kann ich mich auf max. 3 Werte für feld1 beschränken und das klappt auch, indem man für nicht gebrauchte Parameter einfach null schickt.
…where feld2 = ‚AB‘ and feld1 in (par1, par2, par3)
Aber das finde ich einfach grässlich programmiert…
Gruss,
Lisa
Mahlzeit, Martin!
Dynamisches SQL ist nur sehr gezielt einzusetzen (nämlich da,
wo es anders nicht mehr geht) - Begründung dazu liefere ich
bei Interesse gerne nach.
Und die wäre? Ich bin jetzt mal ziemlich gespannt, da ich in letzter Zeit fast nur noch dynamische Cursor einsetze, um aufgrund verschiedenster Auswahlkriterien das optimalste Statement zusammenzubauen.
Grüße,
Tomh
Servus Tom,
lange nicht mehre geschrieben (und auf ein Bier waren wir auch noch immer nicht 
.
Zum Thema:
Dynamisches SQL ist nur sehr gezielt einzusetzen (nämlich da,
wo es anders nicht mehr geht) - Begründung dazu liefere ich
bei Interesse gerne nach.Und die wäre? Ich bin jetzt mal ziemlich gespannt, da ich in
letzter Zeit fast nur noch dynamische Cursor einsetze, um
aufgrund verschiedenster Auswahlkriterien das optimalste
Statement zusammenzubauen.
Ein Großteil meiner Vorbehalte kommen daher, dass es falsch eingesetzt wird, nämlich dann, wenn es ohne auch ginge. Statisches SQL hat den Vorteil, dass
Der Library Cache tendenziell weniger zugemüllt wird
Hard Parses eher vermieden werden und
Dependencies verwaltet werden können
und 2. sind natürlich sehr knapp zusammen, aber doch nicht ganz das gleiche. Selbstverständlich kommt man an manchen Stellen nicht um dynamisches SQL herum, aber was mir dabei schon an Code untergekommen ist *würg*.
Lieblingsbeispiel:
my_stmnt VARCHAR2(2000) :=
'SELECT my_col '||
'FROM my_table '||
'WHERE pk_id = '||TO_CHAR(numeric_input_parameter,‚09,00‘);
Das beste daran ist, dass der TO_CHAR zusätzlich auch noch Probleme mit den NLS Einstellungen macht.
Noch Fragen?
Gruß
Martin
Hi Lisa,
ich habe jetzt nicht nachgesehen, was für eine Oracle Version Du verwendest, auf 10g jedenfalls funkt das hier problemlos:
CREATE OR REPLACE
TYPE my\_type AS TABLE OF VARCHAR2(2000);
/
CREATE OR REPLACE
FUNCTION my\_list(instring IN VARCHAR2) RETURN my\_type PIPELINED
AS
localstring VARCHAR2(2000) := instring || ',';
localvalue VARCHAR2(2000);
n NUMBER;
BEGIN
LOOP
EXIT WHEN localstring IS NULL;
n := INSTR(localstring, ',');
localvalue := RTRIM(SUBSTR(localstring, 1, n - 1));
PIPE ROW(localvalue);
localstring := SUBSTR(localstring, n + 1);
END LOOP;
END;
/
SELECT \* dba\_users
WHERE username IN (SELECT \* FROM TABLE(my\_list('SYS,SYSTEM')))
/
Damit sollte das Prinzip klar sein, man kann da sicher noch einiges optimieren, aber so schnell aus dem Kopf sollte das hinkommen. Wenn nicht helfe ich natürlich gerne weiter.
Worauf Du noch achten musst: Wenn dein SELECT mit IN(…) einen Full Table Scan macht, dann macht er hier natürlich drei davon, was der Performance eher abträglich ist, aber wenn ich dich richtig verstanden habe, dann geht es ohnehin um eine Primärschlüsselabfrage, sollte also of jeden Fall flott sein.
Gruß
Martin
Hi!
lange nicht mehre geschrieben (und auf ein Bier waren wir auch
noch immer nicht
Vielleicht ergibt sich das ja doch noch mal
Lieblingsbeispiel:
> my\_stmnt VARCHAR2(2000) :=
> 'SELECT my\_col '||
> 'FROM my\_table '||
> 'WHERE pk\_id =
> '||TO\_CHAR(numeric\_input\_parameter,'09,00');
Hehehe, ein Statement von einem Humboldt-„Ich werde DB-Administrator“-Kurs-Teilnehmer?
Das beste daran ist, dass der TO_CHAR zusätzlich auch noch
Probleme mit den NLS Einstellungen macht.
Vor allem den „,“ finde ich recht nett …
Im Ernst: Ich bevorzuge dynamische SQLs _derzeit_ aufgrund der Anforderung: Aufgrund von Parametern werden Einschränkungen über FKs auf andere Tabellen vorgenommen; werden diese Parameter nicht übergeben, will ich die ganze Tabelle nicht mitjoinen - und das können derzeit bis zu 20 unnütze Tabellen werden, die zwischen 3 und 100.000.000 Datensätze beeinhalten, die verknüpften Tabellen sich wieder teilweise untereinander verknüpfen und ich hier die Möglichkeit habe - wieder aufgrund der Parameter -, den Zugriffsplan abzuändern (andere Indizes, hier ein ORDERED, dort ein ALL_ROWS, …)
Oder aber auch ein _klassiches_ Beispiel für ein „verdynamisiertes“ bzw. „wiederverwendbares“ Modell: Der User selber definiert Objekte, die dann in Form von Funktionen, Where-Klauseln, etc. daherkommen - wie hier in dem Beispiel, in dem die Einschränkung in einem(!) String daherkommt …
Und dann kommt meistens der Spezifikateur daher und meint, das das Ganze doch ein bißchen performanter sein soll …
Grüße,
Tomh