1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36
| create or replace
FUNCTION HMAX
(polu_st in varchar2) return number
AS
I number(2);
TYPE TYPE_TAB IS VARRAY (25) OF journalier.h_m01%type; /*--varray tabelau se commence de 1*/
tab TYPE_TAB := TYPE_TAB(0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0); /*-- il faut l'initialiser*/
hrmax number(6);
indice number(6);
pol varchar2(6);
begin
pol := polu_st;
DBMS_OUTPUT.PUT_LINE (pol);
select h_m01,h_m02,h_m03 ,h_m04 ,h_m05,h_m06,h_m07,h_m08,h_m09,h_m10,h_m11,
h_m12,h_m13,h_m14,h_m15,h_m16,h_m17,h_m18,h_m19,h_m20,h_m21,h_m22 ,
h_m23 ,h_m24 ,nom_court_mes
into tab(1),tab(2),tab(3),tab(4),tab(5),tab(6),tab(7),tab(8),tab(9),tab(10),tab(11)
,tab(12),tab(13),tab(14),tab(15),tab(16),tab(17),tab(18),tab(19),tab(20),tab(21)
,tab(22),tab(23),tab(24),pol
from journalier
where j_date like (select sysdate-4 from dual)
and nom_court_mes = pol;
--(select sysdate-4 jj from dual)
DBMS_OUTPUT.PUT_LINE ('I m here' );
hrmax := tab(1);
FOR I IN 2..24
LOOP
if hrmax < tab(I) then
hrmax := tab(I);
end if ;
END LOOP;
indice := SOUSINDICE(hrmax,polu_st);
return indice;
end HMAX; |
Partager