Les crashs dev d'un passionné

Le PL/SQL (Procedural Language / Structured Query Language) est un langage de programmation procédurale de Oracle.
Il est composé de 3 sections :

• Déclaration (facultatif)
• Exécution
• Exception (facultatif)

DECLARE
  -- Initialisation des variables
BEGIN
  -- Code
EXCEPTION
WHEN OTHER THEN
  -- Code lancé si une erreur se produit
END;
/

Ne pas oublier

/

à la fin car il permet de lancer la requête qui est placée dans le buffer.

Dans la partie exécution il est possible de mettre des instructions SQL et procédurales.

Il existe 2 types de commandes :

• DML (Data Manipulation Language) : SELECT, INSERT, UPDATE, DELETE, MERGE, CALL, EXPLAIN PLAN, LOCK TABLE
• DDL (Data Definition Language) : CREATE, ALTER, DROP, TRUNCATE, COMMENT, RENAME

Cependant il est impossible de lancer des DDL dans un bloc PL/SQL, en effet ces commandes modifient la structure de la BD et nécessite une ré-compilation du package.
Il faut passer par un EXECUTE IMMEDIATE.

EXECUTE IMMEDIATE permet d'exécuter des blocs PL/SQL ou de lancer une requête dynamique

# insère des valeurs
EXECUTE IMMEDIATE 'BEGIN MyProc(:1,:2); END;' USING param1,param2; 

# retourne une valeur
EXECUTE IMMEDIATE 'SELECT COL1 FROM MY_TABLE ...' INTO my_var; 

EXECUTE IMMEDIATE fait un commit automatique par besoin de le préciser.

Attention après chaque commande DML il faut faire un commit explicite contrairement au DDL qui a un commit implicite. Le résultat est placé dans une zone temporaire afin de vérifier les modifications appliquées.

Un bloc PL/SQL anonyme est compilé à la volé alors que l'appel de EXECUTE IMMEDIATE compile ce qui est passé en paramètre lors de l’exécution (donc plus long la première fois).
Une fois compilé le code réside en mémoire.
Sur la version 11g les scripts PL/SQL sont compilés en code machine directement grâce à

PLSQL_CODE_TYPE = NATIVE

Si

PLSQL_CODE_TYPE = INTERPRETED

ils seront compilés en byte code et stockés dans le tablespace SYSTEM.
Quand le code est appelé il est placé dans une mémoire partagée afin qu'il soit accessible pour toutes les sessions de l'instance

Après la fin de la session Oracle, si le commit n’a pas été lancé toutes les modifications seront perdues.

Pour faire un long bloc anonyme vous pouvez utiliser q'[]' qui permet d'échapper les simples quotes dans le texte placé entre les crochets. Les crochets peuvent être aussi { } () ou <> du moment qu'il ne soit pas présent dans le texte.

Consulter cet article sur la création d'un package PL/SQL performant si vous voulez aller plus loin dans la création d'un package traitant une volumétrie importante.