Aide: Fonction à partir d'une requête JOIN

Version imprimable

Bonjouir,

pourriez-vous me dire si c'est possible? Écrire une fonction PL/SQL qui retourne les résultats de cette requête?
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 <div style="margin-left:40px">select "DOSSIER"."ID" as "DOSSIERID", "DOSSIER"."TITLE" as "TITLE", "DVD"."ID" as "DVDID", "DVD"."NAME" as "DVDNAME", "DVD"."ID_DOSSIER" as "DVD_ID_DOSSIER", "ELEMENTS"."ID" as "ELEMENTSID", "ELEMENTS"."NAME" as "ELEMENTSNAME", "ELEMENTS"."ID_DVD" as "ELEMENTS_ID_DVD" from "DOSSIER" "DOSSIER", "DVD" "DVD", "ELEMENTS" "ELEMENTS" where "DVD"."ID_DOSSIER"="DOSSIER"."ID" and "ELEMENTS"."ID_DVD"="DVD"."ID" and "DOSSIER"."TITLE" = :P3_SEARCH ORDER BY "DVD".NAME</div>
On m'a dit que pour obtenir le template voulu (dans APEX), je devais écrire une fonction PL/SQL qui retourne 1 seul résultat. Par la suite, je pourrai modifier le template et obtenir le résultats que j'ai mis plus bas.:

RÉSULTATS

TITLE: FIGARO

DVD: C2008-203
Elements: 12l10201.tif, 12l10202.tif, 12l11101.tif, 12l11102.tif, 12l11201.tif
12l11202.tif, 12l12101.tif, 12l12102.tif

DVD C2008-204
Elements: 12l12202.tif, 12l13101.tif, 12l13102.tif, 12l13201.tif, 12l13202.tif,
12l14101.tif, 12l14102.tif, 12l14201.tif, 12l14202.tif, 12l15101.tif,
12l15102.tif, 12l15201.tif

DVD: C2008-205
Elements: 12l15202.tif, 12l16101.tif, 12l16102.tif, 12l1
6201.tif, 12l16202.tif,
12m01101.tif, 12m01102.tif, 12m01201.tif

merci de votre aide,

Roseline

Quelque chose comme ca?

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE FUNCTION my_super_function (passed_parm IN varchar2)   <-- declare the passing variable here
RETURN VARCHAR(2000);
IS 
details VARCHAR(2000); 
BEGIN 
select	 
	 "DOSSIER"."ID" || ',' ||     <<-- concat the columns with a comma between em to make a long string
	 "DOSSIER"."TITLE" || ',' ||
	 "DVD"."ID" || ',' || 
	 ...
 from	 "DOSSIER" "DOSSIER",
		 "DVD" "DVD",
		 "ELEMENTS" "ELEMENTS"
  INTO  details     <-- do something with the data being returned by SQL.  Put it into here 
 where   "DVD"."ID_DOSSIER"="DOSSIER"."ID"
 and	 "ELEMENTS"."ID_DVD"="DVD"."ID"
  and 	 "DOSSIER"."TITLE" = passed_parm <-- this is where the parameter gets used
  and          ROWNUM < 2 <-- ensure that a max of 1 row can be returned, 'cause we only got one variable to fill
;  <-- a SQL statement is a single statement in PL/SQL and needs it's own terminator.
 
RETURN details;
END;
/

21/09/2009, 17h51
GoLDoZ

Bonjour.
Le "INTO details" doit être placé avant le "FROM" dans la requête.