SQL Abfrage on AS Werte parameter cursor

PL/SQL: Frage zu Cursor mit Parameter

Von: , Frage gestellt am Fr, 2. Mai 2008

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

7 Antworten zu dieser Frage

Antwort von nach 3 Stunden 0 hilfreich

Re: PL/SQL: Frage zu Cursor mit Parameter

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
Antwort von nach 3 Stunden 0 hilfreich

Re: PL/SQL: Frage zu Cursor mit Parameter

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
- Antwort von nach 2 Tagen 0 hilfreich
  
  Re^2: PL/SQL: Frage zu Cursor mit Parameter
  
  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
  - Antwort von nach 3 Tagen 0 hilfreich
    
    Re^3: PL/SQL: Frage zu Cursor mit Parameter
    
    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
- Antwort von nach 3 Tagen 0 hilfreich
  
  Re^2: PL/SQL: Frage zu Cursor mit Parameter
  
  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
  - Antwort von nach 3 Tagen 0 hilfreich
    
    Re^3: PL/SQL: Frage zu Cursor mit Parameter
    
    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
    1. Der Library Cache tendenziell weniger zugemüllt wird
    2. Hard Parses eher vermieden werden und
    3. Dependencies verwaltet werden können
    
    1. 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
    - Antwort von nach 3 Tagen 0 hilfreich
      
      Re^4: PL/SQL: Frage zu Cursor mit Parameter
      
      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

Keine passende Antwort gefunden? Jetzt eigene Frage stellen!