PL/SQL Functie om meerdere records terug te geven

[ACM]

Ik wil eigenlijk de volgende set queries in een functie bouwen, dit om het makkelijker/beter mogelijk te maken gebruik te maken van temporary tables.

1. Creeer temporary table
2. Vul de tabel (select into's met verschillende parameters etc)
3. Wijzig wat van de data indien nodig
4. Doe een select op de temptabel

De eerste 3 maak ik me niet eens druk om, die werken zowiezo/vast wel.

Het probleem zit hem in de 4e, dat is in principe een query die een heel stel records terug moet gaan geven.

Na een heel stel documenten in technet.oracle.com doorgekeken/doorgelezen te hebben heb ik geen mogelijkheid gevonden om records terug te geven.
Wat ik dus eigenlijk zoek is een manier om een heel resultset terug te geven aan de query/client die de functie aanroept.

(bijvoorbeeld het resultaat van 'select veld1, veld2, veld3 from temporary_created_table' wat dan 300 records zijn)

[Darkvater]

dat doet ie standaard al.
Als je op perl.com leest zie je zoiets

code:

while ($row= mysql_fetchrow($result)) {
  print "veld1: $row[0]\n";
  print "veld2: $row[1]\n";
}

etc.....

Dan gaat ie alle gevonden records af en print alles uit

[ACM]

Op woensdag 14 november 2001 13:41 schreef Darkvater het volgende:
dat doet ie standaard al.
Als je op perl.com leest zie je zoiets

code:

while ($row= mysql_fetchrow($result)) {
  print "veld1: $row[0]\n";
  print "veld2: $row[1]\n";
}

etc.....

Dan gaat ie alle gevonden records af en print alles uit

Euh, perl != PL/SQL

Dat zouden ze bij oracle niet leuk vinden om te horen iig

[justmental]

Ik zou niet de resultset maar een ref cursor teruggeven uit de functie.
Eventueel zou je de resultset in een pl/sql table terug kunnen geven, maar dat is niet zo netjes.

[ACM]

Op woensdag 14 november 2001 15:02 schreef justmental het volgende:
Ik zou niet de resultset maar een ref cursor teruggeven uit de functie.
Eventueel zou je de resultset in een pl/sql table terug kunnen geven, maar dat is niet zo netjes.

Sja, daar zat ik ook aan te denken.

Het liefst geef ik een resultset terug, zodat de (php)code eromheen niet zoveel aangepast hoeft te worden.

Ik denk dat ik dan maar statement 1-3 in een procedure doe en dan statement 4 "los".

[justmental]

Als je met 8i of 9i werkt dan kan dit waarschijnlijk ook met een SQL object type.
Volgens mij staan daar een paar mooie voorbeelden van op die site van Tom Kyte.
Hier heb ik alleen zelf nog geen ervaring mee.

edit:
linkje erbij

[Verwijderd]

hee, PL/SQL, dat zie je hier zelden

Tom heeft idd wat voorbeelden op zijn site staan : zie hier

Ikzelf gebruik het via JDBC : zie oa Oracle8i JDBC Developer's Guide and Reference

[Darkvater]

Oeps sorry, ik dacht PL = Perl; SQL = SQL.
Mijn fout.

[JaQ]

Het is per definitie onmogelijk om meer dan 1 waarde terug te laten geven door een functie uit pl/sql. *sry*

wat je kan doen is een grote lap code schrijven, die m.b.v. memory-tables de waarden teruggeeft en hercodeerd en daarna pas wegschrijft (performance to the max! )

Als je vervolgens een query maakt, waarin je deze tijdelijke tabel opneemt die je noemde, is het toch geen enkel probleem meer?

Ik snap dus eigenlijk niet helemaal waarom je een functie zou willen gebruiken die meerdere waarden teruggeeft..

[ACM]

Op donderdag 15 november 2001 14:18 schreef DrFrankenstoner het volgende:
Ik snap dus eigenlijk niet helemaal waarom je een functie zou willen gebruiken die meerdere waarden teruggeeft..

Waarom niet?
Als het simpel met een functie had gekund, dan had ik simpelweg de huidige set queries (allemaal losse statements, niet door mij gemaakt) in een functie kunnen stoppen en dan was ik klaar (tenminste, op het gebied van aanpassingen aan de gebruikende applicaties (wederom niet van mij, maar aanpassen is nou ook weer niet zo'n vreselijk groot probleem))

Had het gekund dan was het leuk geweest, maar echt handig kan het dus niet (tenminste, hangt er vanaf wat je handig vindt) dus zal ik het ook anders op moeten lossen.

[JaQ]

als ik het goed begrijp wil je dus code genereren?
of zit ik nu helemaal krom te denken.. (dat kan namelijk wel erg goed met een functie, danwel package

[whoami]

Met behulp van een Cursor kan je dat doen.

Zo ongeveer (dit werkt op Informix maar ik denk dat dit ook standaard SQL is)

code:

DECLARE blaat CURSOR FOR
SELECT .....

FOREACH blaat INTO struct_variable
   ....
END FOREACH

Of, als je gewoon het resultaat van een query in een table wilt inserten, kan je dat toch ook gewoon met een query doen:

code:

INSERT INTO table
  SELECT * FROM ....

_{... geen haakjes , geen values ...}

[whoami]

Woeps, heb nu ook gelezen dat 1 tem 3 geen probleem zijn, dus die insert - select query zal je wel niet nodig hebben.

Die cursor is wel handig voor uw 4de punt.

[ACM]

Op donderdag 15 november 2001 19:37 schreef DrFrankenstoner het volgende:
als ik het goed begrijp wil je dus code genereren?
of zit ik nu helemaal krom te denken.. (dat kan namelijk wel erg goed met een functie, danwel package

Nee, ik heb het onduidelijk uitgelegd

Ik heb nu '4 losse queries' in een stuk (php, is verder niet relevant) code/app.

Daarvan wilde ik eigenlijk 1 statement maken die uiteindelijk hetzelfde teruggeeft als de huidige 4 queries.
In zo'n geval moet je in principe natuurlijk een function bouwen.

Waardoor de aanroepen binnen de app/code nauwelijks veranderen (3 regels code minder )

Op donderdag 15 november 2001 20:12 schreef whoami het volgende:
Woeps, heb nu ook gelezen dat 1 tem 3 geen probleem zijn, dus die insert - select query zal je wel niet nodig hebben.

Die cursor is wel handig voor uw 4de punt.

Ja, dat is denk ik idd de beste optie.

[ACM]

Ok, *kuch*, bedankt voor jullie tips.

Ik wist alleen niet dat Outer joins (het simpelweg toevoegen van (+) dus achter de kolomnaam in de where clause) binnen oracle zo makkelijk hiervoor toepasbaar waren.

En als dan iemand anders al een ontwikkeling in een bepaalde richting heeft ingezet wil je daar nog wel es overheen kijken.

Ik heb dus de complete temporary table weggesmeten en het hele insert-idee eruit gegooid.

Het bleek op te lossen te zijn met een outer join in een query van 20 woorden sql

Die ene query vervangt alle 4 de onderdelen.


Het is me zelfs gelukt de query aan de collega's uit te leggen

[JaQ]

eventueel kan je ook nog union (of union all) gebruiken.

Sorry.. had je draaidje beter moeten lezen

[whoami]

Op vrijdag 16 november 2001 16:08 schreef ACM het volgende:
Die ene query vervangt alle 4 de onderdelen.


Het is me zelfs gelukt de query aan de collega's uit te leggen

Wauw, proficiat.