Gérer des variables PL/SQL dans un block WITH en debug

Petit rappel, la clause WITH permet de créer des sous requêtes. Elle est utilisée pour optimiser le temps d’exécution de la requête.

Copier le code
1
2
3
4
5
6
7
WITH ONE AS ( 
 SELECT ...
), TWO AS (
 SELECT ..
)
SELECT * FROM ONE 
INNER JOIN TWO ...

Elle trouve son intérêt si le WITH est utilisé plusieurs fois par la suite. Oracle prend la décision de créer une table temporaire ou la mettre en mémoire en fonction de la complexité (si trop complexe Oracle utilise la 1ère option).

On peut forcer cette décision en utilisant MATERIALIZE pour stocker les informations dans une table temporaire globale

Copier le code
1
2
3
4
5
6
WITH ART_COUNT AS (
 SELECT /*+ MATERIALIZE */ 
  COUNT(*) AS NB_ART
 FROM article
)
SELECT ...

ou INLINE pour mettre en mémoire

Copier le code
1
2
3
4
5
6
WITH ART_COUNT AS (
 SELECT /*+ INLINE */ 
  COUNT(*) AS NB_ART
 FROM article
)
SELECT ...

Quand j'utilise une clause WITH avec des variables à l'intérieur j'aimerai affecter une valeur à ces variables.

Hors mes WITH ne peuvent pas être dans un bloc BEGIN/END, donc pas de DECLARE possible.

Il faut initialiser au préalable la variable

DEF myVar = 1337; # Ou remplacer DEF par DEFINE 

SELECT &myVar FROM dual

Attention si myVar est une chaine de caractère il faut entourer la variable de simple quote

DEF myVar = 'Content'; 

DEF myVar ; 

>>DEFINE myVar = "Content" (CHAR) 

SELECT '&myVar' FROM dual

Donc en résumé

• copiez votre code PL/SQL dans SqlDeveloper
• ajoutez en haut du script l'initialisation des variables

  DEF myVar = 'Content';

• ajoutez & devant vos variables (entourez les de simple quote si la variable est une chaine de caractère)

  ... SELECT '&myVar' FROM ...

Normalement le caractère & permet d'obtenir une pop up de SqlDeveloper afin de saisir le contenu de la variable.

Mais comme on initialise son contenu, SqlDeveloper ne nous demande pas d’interaction