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
|
DEFINE ValAnnee = TO_CHAR(2007)
DEFINE TableLots = Schema.TableLots
VARIABLE ValMois VARCHAR2(2);
VARIABLE ValPeriode VARCHAR2(6);
VARIABLE TableTemp VARCHAR2(28);
VARIABLE iDateCalcul VARCHAR2(19);
BEGIN
IF sysdate BETWEEN TO_DATE('02/2007','mm/yyyy') AND TO_DATE('12/2007','mm/yyyy') THEN
:ValMois := LPAD(TO_CHAR(ADD_MONTHS(sysdate,-1),'mm'),2,'0');
:ValPeriode := TO_CHAR(&ValAnnee||:ValMois);
ELSIF sysdate >= TO_DATE('01/2008','mm/yyyy') THEN
:ValMois := '12';
:ValPeriode := TO_CHAR(&ValAnnee||TO_CHAR(sysdate,'mm')+11);
END IF;
:TableTemp :=TO_CHAR('Schema.Table_'||SUBSTR(:ValPeriode,5,2));
:ValMois := TO_NUMBER(:ValMois);
END;
/
DECLARE vsql VARCHAR2(800);
BEGIN
vsql:= 'INSERT INTO &TableLots (champ1, champ2, ...) '||
'SELECT champ1, champ2, ...'||
'FROM '||:TableTemp||' WHERE annee = &ValAnnee AND periode = '||:ValPeriode;
EXECUTE IMMEDIATE (vsql);
END;
/ |
Partager