Voila comment s y prendre
-----------------
Step 1 : creation d un type qui correspond a ce qu il faut renvoyer
-----------------
CREATE TYPE TRC_MEASURE AS OBJECT
(
PROGRAM VARCHAR2(255),
ACTION VARCHAR2(255),
ELLAPSED_TIME NUMBER(11)
)
;
-----------------
Step 2 : creation d un type qui correspond a une structure de type table
-----------------
CREATE TYPE TRC_MEASURE_RESULTS IS TABLE OF TRC_MEASURE
;
-----------------
Step 3 : creation de la fonction
-----------------
Points importants
Il faut rajouter PIPELINED a la declaration de la fonction
Il faut renvoyer les records a l aide de l instruction PIPE ROW
Il ne faut pas faire de return d une structure ou d une valeur
--
FUNCTION TRC_GET_RESULT (p_Camp IN NUMBER) RETURN TRC_MEASURE_RESULTS PIPELINED
IS
TYPE MyCursorType IS REF CURSOR;
MyCursor MyCursorType;
MyCounter NUMBER DEFAULT 1;
COL1 Varchar2(255);
COL2 Varchar2(255);
COL3 Number(11);
MySQLstmt VARCHAR(200);
BEGIN
MySQLstmt := ;
MySQLstmt := MySQLstmt || select prg,lib,deltassd*86400+deltassh*3600+deltassm*60+deltasss ellapsed_time from TRC_DATA ;
MySQLstmt := MySQLstmt || where campid= ;
MySQLstmt := MySQLstmt || p_Camp;
MySQLstmt := MySQLstmt || and deltassd*86400+deltassh*3600+deltassm*60+deltasss is not null ;
MySQLstmt := MySQLstmt || order by id ;
OPEN MyCursor FOR MySQLstmt;
LOOP
FETCH MyCursor INTO COL1,COL2,COL3 ;
EXIT WHEN MyCursor%NOTFOUND;
MyCounter := MyCounter+1;
PIPE ROW(TRC_MEASURE (COL1,COL2,COL3) );
END LOOP;
CLOSE MyCursor;
-- RETURN instruction should not be used as it has been done through the pipe
-- RETURN MyListOfRecords;
RETURN;
END
;
/
show errors;
/
-----------------
Step 4 : Utilisation dans ton outil SQL prefere (TOAD !!!!)
-----------------
A l aide de l instruction TABLE on peut alors recuperer le resultat de la fonction
SELECT * from TABLE(TRC_GET_RESULT(1))
|