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 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96
| PROCEDURE get_datas (p_id_do IN VARCHAR2,
p_id_sonde IN NUMBER,
p_tpe IN VARCHAR2,
p_date_debut IN DATE,
p_date_fin IN DATE,
p_cur OUT r_cursor)
IS
v_sql VARCHAR2 (4000);
v_sql_surverses VARCHAR2 (4000);
v_sql_mesures VARCHAR2 (4000);
v_sql_debits VARCHAR2 (4000);
v_sql_dual VARCHAR2 (4000);
v_sql_dates VARCHAR2 (4000);
BEGIN
v_sql_surverses :=
'SELECT sur.dte, ROUND (sur.val, 2) AS val, son.id_pos '
|| 'FROM t_surverse sur, t_sondes son '
|| 'WHERE sur.id_do = '''
|| p_id_do
|| ''' '
|| 'AND son.id_sonde = sur.id_sonde ';
v_sql_mesures :=
'SELECT m.dte, ROUND (m.val, 2) AS val, son.id_pos '
|| 'FROM t_mesures m, t_sondes son '
|| 'WHERE m.id_do = '''
|| p_id_do
|| ''' '
|| 'AND son.id_sonde = m.id_sonde '
|| 'AND son.id_sonde NOT IN (SELECT id_sonde FROM t_sondes WHERE etat = 0) ';
v_sql_debits :=
'SELECT d.dte, ROUND (d.val, 2) AS val, '
|| 'DECODE (d.source, '
|| '''PACKDO'', ''Débit calculé'', '
|| '''MAESTRO'', (SELECT id_pos FROM t_sondes '
|| 'WHERE id_do = '''
|| p_id_do
|| ''' '
|| 'AND id_sonde = d.id_sonde)) id_pos '
|| 'FROM t_debits d '
|| 'WHERE d.id_do = '''
|| p_id_do
|| ''' ';
v_sql_dual :=
'SELECT TO_DATE (NULL) AS DTE, '
|| 'TO_NUMBER (NULL) AS VAL, '
|| 'NULL AS POS '
|| 'FROM DUAL ';
FOR r
IN (SELECT *
FROM t_periodes
WHERE id_do = p_id_do
AND dte_debut BETWEEN TO_DATE (p_date_debut,
'DD/MM/YYYY HH24:MI:SS')
AND TO_DATE (p_date_fin,
'DD/MM/YYYY HH24:MI:SS'))
LOOP
v_sql_dates :=
v_sql_dates
|| 'AND dte NOT BETWEEN TO_DATE('''
|| TO_CHAR (r.dte_debut, 'DD/MM/YYYY HH24:MI:SS')
|| ''', ''DD/MM/YYYY HH24:MI:SS'') '
|| 'AND TO_DATE('''
|| TO_CHAR (r.dte_fin, 'DD/MM/YYYY HH24:MI:SS')
|| ''', ''DD/MM/YYYY HH24:MI:SS'') ';
END LOOP;
IF p_tpe = 'TOUT'
THEN
v_sql := v_sql || v_sql_surverses;
v_sql := v_sql || v_sql_dates;
v_sql := v_sql || ' UNION ';
v_sql := v_sql || v_sql_mesures;
v_sql := v_sql || v_sql_dates;
v_sql := v_sql || ' UNION ';
v_sql := v_sql || v_sql_debits;
v_sql := v_sql || v_sql_dates;
v_sql := v_sql || ' ORDER BY dte';
ELSIF p_tpe = 'DEBIT'
THEN
v_sql := v_sql || v_sql_debits;
v_sql := v_sql || v_sql_dates;
v_sql := v_sql || ' ORDER BY dte';
ELSE
/* Ne fait rien, c'est juste pour récupérer la structure de la
requête dans le DataSet côté VB */
v_sql := v_sql || v_sql_dual;
END IF;
DBMS_OUTPUT.put_line (v_sql);
OPEN p_cur FOR v_sql;
END; |
Partager