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.
&lt;<blocklabel>&gt;
-- 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)
&lt;<label_for>&gt;
FOR ... IN [REVERSE] ... LOOP ...
END LOOP &lt;<label_for>&gt;;
-- 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;

Fichiers externes

• Exécuter un script à partir d’un fichier

@"path/to/file.sql"

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;

Debugging

• Equivalent de EXPLAIN de MySQL :

EXPLAIN PLAN FOR select ...;
SELECT * FROM table(DBMS_XPLAN.DISPLAY);