PL/SQL Tabelle zurückgeben

[Helios co.]

Hallo @all,

ich versuche mich aktuell in PL/SQL und habe just eine Frage. Ich habe mir die Grundlagen so weit angeschaut, bin aber teilweise noch bissel konfus.

Was ich im Prinzip machen will, ist folgendes:
1. Ich habe eine verschachtelte SQL QUERY, mit einem inneren und einem äußeren SELECT.
2. Nun möchte ich, dass der innere SELECT in einer Funktion ausgeführt wird.
3. D.h. der Äußere SELECT bleibt wie gehabt, doch der innere SELECT wird ersetzt durch einen Funktionsaufruf (innerhalb eines Packages).

Ich wäre dankbar, wenn mir jemand die grundlegende Vorgehensweise erläutern könnte.

Aktuell gehe ich so vor, dass ich einen Select - Cursor (wenn man das so sagen kann) definieren:

CURSOR test_cur IS SELECT test_id, test_value FROM test_table;

D.h. ich will nur die Werte zweier Spalten haben.

Anschließend habe ich folgendes gemacht:

 --Definition des Records.
  TYPE my_record_type IS RECORD(test_id NUMBER, test_value NUMBER);
  my_record_var my_record_type;

Ab hier weiß ich aber nicht mehr wirklich weiter. Im Prinzip müsste ich doch in einer Schleife die Werte aus dem Cursor holen und diese entsprechend in my_record_var schreiben und hier zunächst ganz nach Wunsch und Laune die Einträge bearbeiten?

Wie aber schreibe ich die Werte aus dem Cursor in den Record und wie gebe ich eine SQL Tabelle aus, mit der der äußere SELECT weiterarbeiten kann?

Bin dankbar für jeden Rat!

 

[Rossibaer]

mit "PIPE ROW" kannst du die Zeilen von der Tablefunktion an die Außenwelt liefern ala

PIPE ROW(my_record_var);

Oder war das ohne Klammern?! Ist lang her aber schau einfach mal da nach:

http://it.toolbox.com/blogs/database-solutions/returning-rows-through-a-table-function-in-oracle-7802

Viel Erfolg

 

[Helios co.]

Hm, das verstehe ich nicht. Bzw. nin ich mir nicht sicher, ob das nicht auch deutlich weniger aufwändig geht.

Ich kann doch theoretisch so etwas machen:

Select * from (test.my_test_function);

Das müsste doch bereits funktionieren, wenn my_test_function eine Tabelle liefert.
Ich weiss, dass das so gehen kann, da ich ein Beispiel, jedoch mit einer einspaltigen Tabelle, gesehen habe.

Ich brauche so etwas jedoch für mehrspaltige Tabellen.

Hier der Code dazu:

TYPE genericcursor IS REF CURSOR;


function myfunction return int_nested_table
IS
   tmp int_nested_table;
   res genericcursor;
   results INTARRAYTYPE;
   q varchar2(1000);

begin
   q := 'select id from mytable';

   OPEN res FOR q;


   FOR i IN 1..3 LOOP
        fetch res BULK COLLECT INTO results LIMIT 2;
   END LOOP;

   CLOSE res;

   tmp:= int_nested_table();

   FOR i IN 1..results.COUNT LOOP
     tmp.extend();
     tmp(tmp.count) := intEntry(results(i));
   END LOOP;

   return tmp;

end myfunction;

Mir sind hier einige Statements nicht ganz klar. Dieses int_nested_table beispielsweise oder intEntry und INTARRAYTYPE.

tmp ist vom Typ int_nested_table und ist der Rückgabewert. D.h. das muss die einspaltige Tabelle sein. Wie definiert man so etwas? Und wie definiert man das für mehrspaltige Tabellen.


@Rossibaer: Vielen Dank für deine Antwort!

 

[AlbertLast]

such mal nach "Oracle Table Function"
dazu gibts schon vieles

 

[Rossibaer]

Das ist nuin die Frage ob du eine Stored Table Function erstellen willst, die du wie eine Tabelle im SELECT verwenden kannst oder ob du eine Stored Function erstellen willst, die sich wie ein Array verhält und eben nicht im SELECT anstelle einer Tabelle verwendet werden kann.

Die Table Function wäre (inkl. der 2 notwendigen Typen):

CREATE TYPE MyRow AS OBJECT(id NUMBER, name VARCHAR(20), regon DATE)
/
CREATE TYPE MyTable AS TABLE OF MyRow
/
CREATE FUNCTION MyFunction RETURN MyTable PIPELINED
IS
  row MyRow;
BEGIN
  FOR rec IN (SELECT id, name, regon FROM users) LOOP
    row.id := rec.id;
    row.name := rec.name;
    row.regon := rec.regon;
    PIPE ROW(row);
  END LOOP;
  RETURN;
END MyFunction;
/

Damit kannst du dann z.B. folgendes machen:

SELECT * FROM TABLE(MyFunction)

Die "normale" Stored Function wäre wahrscheinlich (wieder inkl. der 2 notwendigen Typen) so:

CREATE TYPE MyRow AS OBJECT(id NUMBER, name VARCHAR(20), regon DATE)
/
CREATE TYPE MyTable AS TABLE OF MyRow
/
CREATE FUNCTION MyFunction() RETURN MyTable
IS
  data MyTable;
BEGIN
  SELECT id, name, regon BULK COLLECT INTO data FROM users;
  RETURN data;
END MyFunction;
/

Damit könntest du dann sicher nur folgendes in einem beliebigen SQL Block machen:

BEGIN DECLARE data MyTable;
  BEGIN
    data := MyFunction;
    FOR i IN data.FIRST..data.LAST LOOP
      -- do something
    END LOOP;
  END;
END;

Jedoch gilt für beide, dass du das mal gegen deine DB selber testen solltest. Ich habe zur Zeit keine Oracle DB installiert und habs nur aus dem Gedächtnis runter getippt!

Also viel Erfolg!

 

[Helios co.]

Habs dank deiner Hilfe hinbekommen!

Vielen dank für die sehr kompetente Beratung!