Création table de temps

La requête suivante permet de créer une table d’heure avec SQL server :

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
CREATE TABLE [Clock]
(
    ClTime time,
    ClHourMinuteStr VARCHAR(5),
    ClHourStr VARCHAR(2),
    ClHour int,
    ClMinuteStr VARCHAR(2),
    ClMinute int
);
 
 
BEGIN
    DECLARE @heureStr VARCHAR(2);
    DECLARE @minuteStr VARCHAR(2);
    DECLARE @heureMinute VARCHAR(5);
    DECLARE @idHeureMinute INTEGER;
    DECLARE @hour integer = 0;
    DECLARE @hourMax integer = 23;
    DECLARE @minute integer = 0;
    DECLARE @minuteMax integer = 59;
 
    BEGIN
 
        WHILE @hour <= @hourMax
        BEGIN
   
            SET @heureStr = REPLACE(STR(@hour, 2), SPACE(1), '0')
   
            WHILE @minute <= @minuteMax
            BEGIN
       
                SET @minuteStr = REPLACE(STR(@minute, 2), SPACE(1), '0')
           
                SET @heureMinute = @heureStr + ':' + @minuteStr;

                INSERT INTO [Clock]
                SELECT convert(time, @heureMinute), @heureMinute, @heureStr, @hour, @minuteStr, @minute;
       
           
              SET @minute = @minute + 1 ;
       
            END

            SET @minute = 0;
            SET @hour = @hour+1;
   
        END
   
 
    END

END;

Création calendrier

La requête suivante permet de générer une table « Calendar » avec Sql Server du 01/01/1900 au 12/31/2999 :

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
SELECT
    convert(date , d ) as CalDate,
    DATEPART(YEAR, d) as CalYear,
    DATEPART(QUARTER,  d) as CalQuarter,
    DATEPART(MONTH,  d) as CalMonth,
    DATENAME(MONTH,    d) as CalMonthName,
    DATEPART(WEEK,     d) as CalWeekOfYear,
    CONVERT(CHAR(10),  d, 101) as CalDateStr,
    DATEPART(WEEKDAY,  d) as CalDayOfWeek,
    DATEPART(DAY,  d) as CalDayOfMonth,
    DATEPART(dy, d) as CalDayOfYear,
    CONVERT(VARCHAR(10), DATENAME(WEEKDAY, d)) as CalDayName,
    DATEPART(YEAR, d)*100+REPLACE(STR(DATEPART(MONTH,  d), 4), SPACE(1), '0') as CalYearMonth,
    DATEPART(YEAR, d)*10000+REPLACE(STR(DATEPART(MONTH,  d), 4), SPACE(1), '0')*100+DATEPART(DAY,  d) as CalYearDay,
    CASE WHEN REPLACE(STR(DATEPART(MONTH,  d), 4), SPACE(1), '0')*100+DATEPART(DAY,  d) IN (101, 501, 508, 714,815, 1101, 1225) THEN 1 ELSE 0  END as CalIsPublicHoliday,
    case when DATEPART(WEEKDAY,  d) in (6, 7) THEN 1 else 0 end as CalIsWeekEnd
    into dbo.Calendar
from
(
SELECT d = DATEADD(DAY, rn - 1, '19000101')
  FROM
  (
    SELECT TOP (DATEDIFF(DAY, '19000101', '29991231'))
      rn = ROW_NUMBER() OVER (ORDER BY s1.[object_id])
    FROM sys.all_objects AS s1
    CROSS JOIN sys.all_objects AS s2
    ORDER BY s1.[object_id]
  ) A
) b;

create index IdxRefCalendar on dbo.[REF-Calendar](CalDate);

Export des procédures/fonctions/package d’une instance Oracle

Après avoir galéré plusieurs heures afin de pouvoir exporter facilement chaque procédure / package / fonction d’une instance Oracle dans un fichier il me semblait nécessaire de créer un post sur le sujet.

Première chose à savoir : où se trouve le contenu de ces objets ?
2 réponse à cette question :

  • dans la table USER_SOURCE pour le schéma courant
  • dans la table DBA_SOURCE pour le schéma SYSTEM afin d’avoir un listing exhaustif de tous les objets

Lire la suite

Activation/désactivation d’indexe

Avant quand je voulais désactivé un indexe, je le supprimai car il me semblait que s’était impossible de le désactiver (comme une contrainte avec l’option DISABLE).

Et puis finalement il y avait bien une option pour ne pas avoir à supprimer un indexe juste pour le désactiver.
Lire la suite

Compter les lignes des tables d’un schéma en PL/SQL

Pour compter le nombre de lignes de toutes les tables d’un schéma Oracle il est nécessaire d’utiliser le bloc anonyme suivant :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
DECLARE
    nbLignes NUMBER := 0;
BEGIN
 
    FOR C1 IN (
        select TABLE_NAME
        from user_tables
    ) LOOP
     
    EXECUTE IMMEDIATE 'SELECT COUNT(*) FROM ' || C1.TABLE_NAME INTO nbLignes;
     
    DBMS_OUTPUT.PUT_LINE(C1.TABLE_NAME || ': ' || nbLignes);
             
    END LOOP;
END;

Il est bien évidement possible de filtrer les tables en ajoutant une instruction WHERE dans la requête du curseur C1

Création et alimentation d’une dimension DATE

Lors de la création d’un entrepôt de données, bien souvent, il contient une dimension DATE contenant la liste des dates depuis l’année 1900 jusqu’à l’année 2999. Cette table contenant plusieurs attributs comme par exemple :

  • La date au format date
  • La date au format chaine de caractère sous la forme DD/MM/YYYY
  • La date au format numérique sous la forme YYYYMMDD
  • Le jour de l’année

Voici donc un exemple de création de DIM_DATE en Oracle.
Lire la suite

Modification d’une séquence Oracle en PL/SQL

Pour modifier une séquence existante il est nécessaire de la supprimer puis de la recréer. Pour effectuer cela facilement, il est possible de tester son existence dans un bloc PL/SQL anonyme et en fonction de son existence de la supprimer puis la créer :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
DECLARE
    existe NUMBER(1) := 0;
    seqName VARCHAR2(30) := 'MA_SEQUENCE';

BEGIN
 
    select COUNT(*) INTO existe
    from user_sequences
    WHERE SEQUENCE_NAME = seqName;
     
    IF existe = 1 THEN
     
        EXECUTE IMMEDIATE 'DROP SEQUENCE ' || seqName;
     
    END IF;
     
    EXECUTE IMMEDIATE 'create sequence ' || seqName || ' increment by 1 start with 1 minvalue -2 nocycle noorder nocache';
             
END;