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 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58
|
FUNCTION fc_getarbreconsultable (pi_prefixe IN re_region.re_prefixe%TYPE)
RETURN ref_cursor
AS
cur_rubrique ref_cursor; -- curseur pour retourner les rubriques
nbrubcons NUMBER;
i NUMBER:=0;
nbrows NUMBER;
req VARCHAR2 (500); -- variable pour construction de la requete
CURSOR cur_rubcons IS SELECT ru.ru_id, ru.ru_idpere, ru.ru_libelle, ru.ru_niveau, ru.ru_lien, ru.ru_type, ru.ru_magasin, ru.ru_bg, ru.ru_bd
FROM ru_rubrique ru, dr_droit dr, re_region re
WHERE dr.ru_id = ru.ru_id
AND ru.ru_id = '2'
AND re.re_prefixe = pi_prefixe
AND dr.re_id = re.re_id
--AND ru_consultable = '1'
ORDER BY ru_bg ASC;
rubrique ref_cursor;
BEGIN
req := 'SELECT ru.ru_id, ru.ru_idpere, ru.ru_libelle, ru.ru_niveau, ru.ru_lien, ru.ru_type, ru.ru_magasin ';
req := req || 'FROM ru_rubrique ru, dr_droit dr, re_region re ';
req := req || 'WHERE ru.ru_id = dr.ru_id AND re.re_prefixe = ''' || pi_prefixe || ''' AND dr.re_id = re.re_id AND (';
--nbrubcons := cur_rubcons%ROWCOUNT;
-- boucle sur toutes les rubriques consultables
--WHILE cur_rubcons IS NOT NULL
FOR rubrique IN cur_rubcons
--Open cur_rubcons ; -- ouverture du curseur
LOOP
--fetch cur_rubcons into rubrique;
--exit when rubrique%notfound;
i := i+1;
req := req || '(ru.ru_bg <= ' || rubrique.ru_bg || ' AND ru.ru_bd >= ' || rubrique.ru_bd || ')';
IF i < nbrubcons THEN
req := req || ' OR ';
END IF;
-- si il s'agit de la derniere rubrique, on n'ajoute pas le OR
/*IF (rubrique IS NOT NULL) THEN
req := req || ' OR ';
END IF;*/
END LOOP;
--CLOSE cur_rubcons ;
req := req || ') ORDER BY ru.ru_bg ASC';
OPEN cur_rubrique
FOR req;
RETURN cur_rubrique;
END fc_getarbreconsultable; |
Partager