Avec cette petite requête SQL que vous pouvez rediriger vers une table, il sera facile de créer 10 ans de données pour une table de temps sans avoir recours au PL/SQL.
requête calendrier Oracle
SELECT TRUNC(SYSDATE - ROWNUM,'DD') AS cod_dat, TO_NUMBER(TO_CHAR(SYSDATE - ROWNUM,'D')) AS cod_jou_sem, TRUNC(sysdate-ROWNUM,'IW') AS cod_sem, TRUNC(SYSDATE - ROWNUM,'MM') AS cod_moi, TO_NUMBER(TO_CHAR(sysdate-ROWNUM,'IW')) AS cod_sem_ann, TO_NUMBER(TO_CHAR(sysdate-ROWNUM,'MM')) AS cod_moi_ann, TO_NUMBER(TO_CHAR(sysdate-ROWNUM,'Q')) AS cod_tri_ann, FLOOR(TO_NUMBER(TO_CHAR(sysdate-ROWNUM,'Q'))/3)+1 AS cod_str_ann, TO_CHAR(sysdate-ROWNUM,'DD/MM/YY') AS lib_crt_dat, TO_CHAR(sysdate-ROWNUM,'DD/MM/YYYY') AS lib_moy_dat, RTRIM(TO_CHAR(sysdate-ROWNUM,'day'))||' '|| RTRIM(TO_CHAR(sysdate-ROWNUM,'DD')) ||' '|| RTRIM(TO_CHAR(sysdate-ROWNUM,'month'))||' '|| TO_CHAR(sysdate-ROWNUM,'YYYY') AS lib_lng_dat, ADD_MONTHS(TRUNC(SYSDATE - ROWNUM,'DD'),-12) AS cod_dat_ap1, ADD_MONTHS(TRUNC(SYSDATE - ROWNUM,'DD'),-24) AS cod_dat_ap2, TRUNC(SYSDATE - ROWNUM,'YY') AS cod_ann, TRUNC(SYSDATE - ROWNUM,'Q') AS cod_tri, CASE WHEN TO_NUMBER(TO_CHAR(sysdate-ROWNUM,'MM')) < 7 THEN TRUNC(SYSDATE - ROWNUM,'YY') ELSE TO_DATE(TO_CHAR(sysdate-ROWNUM,'YYYY')||'0601','YYYYMMDD') END AS cod_str , TO_CHAR(SYSDATE - ROWNUM,'YY-MM') AS LIB_CRT_MOI, TO_CHAR(SYSDATE - ROWNUM,'mon YYYY') AS LIB_MOY_MOI, TO_CHAR(SYSDATE - ROWNUM,'month YYYY') AS LIB_LNG_MOI, TO_CHAR(SYSDATE - ROWNUM,'day') AS LIB_JOU_SEM, TO_CHAR(SYSDATE - ROWNUM,'mon') AS LIB_MOY_MOI_ANN, TO_CHAR(SYSDATE - ROWNUM,'month') AS LIB_LNG_MOI_ANN FROM all_objects WHERE TRUNC(SYSDATE - ROWNUM,'DD')>= TO_DATE(&v_start_date_yyyymmdd,'YYYYMMDD') UNION ALL SELECT TRUNC(SYSDATE + ROWNUM-1,'DD') AS cod_dat, TO_NUMBER(TO_CHAR(SYSDATE + ROWNUM-1,'D')) AS cod_jou_sem, TRUNC(SYSDATE+ROWNUM-1,'IW') AS cod_sem, TRUNC(SYSDATE + ROWNUM-1,'MM') AS cod_moi, TO_NUMBER(TO_CHAR(SYSDATE+ROWNUM-1,'IW')) AS cod_sem_ann, TO_NUMBER(TO_CHAR(SYSDATE+ROWNUM-1,'MM')) AS cod_moi_ann, TO_NUMBER(TO_CHAR(SYSDATE+ROWNUM-1,'Q')) AS cod_tri_ann, FLOOR(TO_NUMBER(TO_CHAR(SYSDATE+ROWNUM-1,'Q'))/3)+1 AS cod_str_ann, TO_CHAR(SYSDATE+ROWNUM-1,'DD/MM/YY') AS lib_crt_dat, TO_CHAR(SYSDATE+ROWNUM-1,'DD/MM/YYYY') AS lib_moy_dat, RTRIM(TO_CHAR(SYSDATE+ROWNUM-1,'day'))||' '|| RTRIM(TO_CHAR(SYSDATE+ROWNUM-1,'DD')) ||' '|| RTRIM(TO_CHAR(SYSDATE+ROWNUM-1,'month'))||' '|| TO_CHAR(SYSDATE+ROWNUM-1,'YYYY') AS lib_lng_dat, ADD_MONTHS(TRUNC(SYSDATE + ROWNUM-1,'DD'),+12) AS cod_dat_ap1, ADD_MONTHS(TRUNC(SYSDATE + ROWNUM-1,'DD'),+24) AS cod_dat_ap2, TRUNC(SYSDATE + ROWNUM-1,'YY') AS cod_ann, TRUNC(SYSDATE + ROWNUM-1,'Q') AS cod_tri, CASE WHEN TO_NUMBER(TO_CHAR(SYSDATE+ROWNUM-1,'MM')) < 7 THEN TRUNC(SYSDATE + ROWNUM-1,'YY') ELSE TO_DATE(TO_CHAR(SYSDATE+ROWNUM-1,'YYYY')||'0601','YYYYMMDD') END AS cod_str , RTRIM(TO_CHAR(SYSDATE + ROWNUM-1,'YY-MM')) AS LIB_CRT_MOI, RTRIM(TO_CHAR(SYSDATE + ROWNUM-1,'mon YYYY')) AS LIB_MOY_MOI, RTRIM(TO_CHAR(SYSDATE + ROWNUM-1,'month YYYY')) AS LIB_LNG_MOI, TO_CHAR(SYSDATE + ROWNUM-1,'day') AS LIB_JOU_SEM, TO_CHAR(SYSDATE + ROWNUM-1,'mon') AS LIB_MOY_MOI_ANN, TO_CHAR(SYSDATE + ROWNUM-1,'month') AS LIB_LNG_MOI_ANN FROM all_objects WHERE TRUNC(SYSDATE + ROWNUM-1,'DD')<= TO_DATE(&v_end_date_YYYYMMDD,'YYYYMMDD') ORDER BY 1 ASC
Il suffit de modifier les dates de début et de fin (&v_start_date_yyyymmdd et &v_end_date_yyyymmdd) pour la période à insérer et le tour est joué.
Pour le calcul des jours ouvrés, une procedure ou une fonction sera la meilleure alliée.
Calendrier simple sur x années pour Mysql
SELECT date_add(current_date ,INTERVAL -1*@rownum:=@rownum+1 DAY) 'COD_DAY', CAST(DATE_FORMAT(date_add(current_date ,INTERVAL -1*@rownum DAY) ,'%Y-%m-01') AS DATE) 'COD_MTH', CAST(DATE_FORMAT(date_add(current_date ,INTERVAL -1*@rownum DAY) ,'%Y-01-01') AS DATE) 'COD_YRD' FROM (SELECT * FROM INFORMATION_SCHEMA.COLUMNS LIMIT 366) p, (SELECT * FROM INFORMATION_SCHEMA.COLUMNS LIMIT 10) q, (SELECT @rownum:=0) r WHERE date_add(current_date ,INTERVAL -1*@rownum DAY) > 20090101 UNION ALL SELECT date_add(current_date ,INTERVAL @rownumup:=@rownumup+1 DAY) 'COD_DAY', CAST(DATE_FORMAT(date_add(current_date ,INTERVAL @rownumup DAY) ,'%Y-%m-01') AS DATE) 'COD_MTH', CAST(DATE_FORMAT(date_add(current_date ,INTERVAL @rownumup DAY) ,'%Y-01-01') AS DATE) 'COD_YRD' FROM (SELECT * FROM INFORMATION_SCHEMA.COLUMNS LIMIT 366) p, (SELECT * FROM INFORMATION_SCHEMA.COLUMNS LIMIT 10) q, (SELECT @rownumup:=0) r WHERE date_add(current_date ,INTERVAL @rownumup+1 DAY) < '2013-01-01' ORDER BY 1 ASC