Oracle PL/SQL Cheat Sheet

Les principaux éléments à connaître de PL/SQL en une page.

Outils

Oracle Express Edition, SQL Developer, SQLPlus, Toad, SQL Navigator…

Syntaxe

/* Assign 42 to ma_variable */
ma_variable := 42;
-- Log to console :
dbms_output.put_line('hello' || ' ' || 'world');
-- Block label. Utile pour imbriquer les blocks.
<<blocklabel>>
-- Portée des variables :
blocklabel.ma_variable
-- Conditions
IF ... THEN ... ELSIF ... THEN ... ELSE ... ENDIF;
CASE ... WHEN ... THEN ... WHEN ... THEN ... ELSE ... END CASE;
-- Boucle LOOP
LOOP ... EXIT || EXIT WHEN || GOTO || RETURN || CONTINUE || CONTINUE WHEN
END LOOP;
-- Boucle WHILE
WHILE ... LOOP ... 
END LOOP;
-- Boucle FOR (du plus petit vers le plus grand, même en reverse)
<<label_for>>
FOR ... IN [REVERSE] ... LOOP ... 
END LOOP <<label_for>>;
-- Exceptions Propager une exception au bloc supérieur :
RAISE xxx
Names : ZERO DIVIDE,NO_DATA_FOUND,TOO_MANY_ROWS,OTHERS
Variables : SQLCODE, SQLERRM

Blocks anonymes

Structure minimale

BEGIN ... END;

Structure complète

DECLARE ... BEGIN ... (COMMIT) EXCEPTION WHEN ... WHEN OTHERS THEN (ROLLBACK) ...  END;

Types de données

NUMBER 1e-130 ~ 1e126
NUMBER(precision, scale) precision<=38 ; scale -84 ~ 127
INTEGER NUMBER(38, 0)
CHAR 32 Ko. En UTF-8 : 8191 caractères.
VARCHAR2 32Ko
CLOB, BLOB, BFILE, … Données de masse
DATE Day Month Year Hour Minute Second
TIMESTAMP DATE + fractional seconds
INTERVAL YEAR TO MONTH Période en mois
INTERVAL DAY TO SECOND Période en secondes
ma_variable%TYPE Type de ma_variable (peut être une colonne de table)
RECORD Structure de données composite
  • Les types spécifiques suivants sont plus performants que « NUMBER » mais moins portables : PLS_INTEGER, BINARY_INTEGER, NATURAL, NATURALN, POSITIVE, POSITIVEN, SIGNTYPE, SIMPLE_INTEGER, BINARY_DOUBLE, BINARY_FLOAT, BOOLEAN, …
  • Pour arrondir 0.9999 à 1 : TO_NUMBER.
  • Fonctions DATE principales : CURRENT_DATE (date de la session) ; SYSDATE (date système) ; TO_DATE(string, ‘DD-MON-RRRR HH24:MI:SS’) (conversion chaîne en date).
  • Autres types : VARRAYS, NESTED_TABLE, ASSOCIATIVE_ARRAY, …

Curseurs

En 3 opérations

  • OPEN : Requête SQL exécutée et cursor positionné avant 1er enregistrement.
  • FETCH: Lit l’enregistrement et déplace le curseur à la ligne suivante.
  • CLOSE : Libère la mémoire

Curseurs implicites

SELECT ... INTO ... WHERE ...; 
IF SQL%FOUND THEN ... 
dbms_output.put_line('Found rows: ' || SQL%ROW_COUNT);
-- Exceptions possibles : 
NO DATA FOUND EXCEPTION EXACT FETCH RETURN MORE THAN REQUESTED NUMBER OF ROWS

Curseurs explicites

DECLARE CURSOR xxx(params) IS ... rowdata ... %ROWTYPE;
BEGIN OPEN xxx(params) LOOP 
FETCH ... INTO ... EXIT WHEN xxx%NOTFOUND;
CLOSE xxx; 
END;

Updates massifs par curseurs

DECLARE CURSOR xxx IS ... FOR UPDATE OF ... NOWAIT;
BEGIN
FOR ... IN xxx LOOP 
UPDATE ... SET ... WHERE CURRENT OF...;
END LOOP;
COMMIT;
END;
  • Attributs de curseurs : %FOUND, %NOTFOUND, %ISOPEN, %ROWCOUNT

Procédures

GRANT CREATE|ALTER [ANY] PROCEDURE TO ...;
GRANT EXECUTE ON ... TO ...;
CREATE OR REPLACE PROCEDURE ... IS|AS ... BEGIN ... EXCEPTION ... END;
  • Terminer par un slash ‘/’ après le END pour compiler la procédure après la création
  • Variables de sessions : PLSQL_CODE_TYPE, PLSQL_OPTIMIZE_LEVEL (table all_plsql_object_settings)
  • Sous programmes locaux : créer des procédures dans des procédures.

Fonctions

-- GRANT : voir PROCEDURE
CREATE OR REPLACE FUNCTION ... (nb IN NUMBER DEFAULT 42, result OUT VARCHAR2) RETURN ... (PARALLEL_ENABLE|AUTHID) IS|AS ... 
BEGIN ... RETURN ... 
END;
EXEC xxx;
  • IN : Paramètre d’entrée. OUT : paramètre de sortie (voir RETURNING … INTO …). IN OUT (NOCOPY) : Entrée et sortie.
  • Paramètres par position (attr1, attr2, …) ou par association (param1=>attr1, param2=>attr2, …).

Packages

CREATE PACKAGE ... IS|AS ... END;
CREATE PACKAGE BODY ... IS|AS ... END;
  • Contient un ensemble logique de variables partagées, procédures et fonctions
  • Spécification + body
  • Slash ‘/’ après le END; pour compiler automatiquement. Sinon : ALTER PACKAGE … COMPILE PACKAGE;

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *