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
| create or replace
FUNCTION CalcTxPLoc (mag IN VARCHAR2, per IN VARCHAR2)
RETURN NUMBER IS taux_porteurs_loc NUMBER;
nb_p NUMBER;
nb_ploc NUMBER;
req VARCHAR2(1000);
req2 VARCHAR2(1000);
BEGIN
req := 'SELECT sum(a.pa_nb) '||
'FROM grfid_h_200607 a , arcsde.gr_taxcodes_2001 b '||
'WHERE a.codmag = '''||mag||''' '||
'AND a.periode = '''||per||''' '||
'AND a.codgeo = b.ID '||
'AND a.codgeo NOT IN (''NP'',''PNL'',''GNE'',''XXXX'') '||
'AND a.pa_nb > 0 '
;
EXECUTE IMMEDIATE req INTO nb_ploc;
req2 := 'SELECT sum(pa_nb) '||
'FROM grfid_h_200607 '||
'WHERE codmag = '''||mag||''' '||
'AND periode = '''||per||''''
;
EXECUTE IMMEDIATE req2 INTO nb_p;
taux_porteurs_loc := nb_ploc/nb_p;
RETURN(taux_porteurs_loc);
END; |
