PL/SQL , exemples pas à pas

attention ! dans tous ces exemples pour que les sorties s'affichent à l'écran il faudra positionner une variable dans votre environnement SQL*Plus ou iSQL*Plus :
set serveroutput on

Mise à jour simple

Les mises à jours (UPDATE, INSERT, DELETE) ou ordre du langage de définition de données (CREATE, ALTER, DROP, GRANT,...) sont les plus simples puisque ne nécessitent même pas forcément de variables.

Il suffira de mettre le code SQL au sein du bloc en le COMMITant si nécessaire.

BEGIN
INSERT INTO emp(empno, ename) VALUES (1111,'Deleglise');
COMMIT;
CREATE TABLE test (n INTEGER);
END;

rappelons que les ordres du LDD sont auto COMMITés.
De plus les ordres du LDD ne sont supportés de manière aussi simple que dans les versions récentes d'Oracle. Auparavant il fallait utiliser des packages spéciaux fournis avec le logiciel pour les exécuter.

Select simple (monoligne sans curseur)

Dans la syntaxe normale d'un SELECT en SQL on rajoute une clause 'INTO' avant la clause 'FROM' en précisant une (ou des) variable(s) de réception :

SQL> SELECT col1, col2,... INTO var_recep1, var_recep2 FROM ma_table

les variables doivent bien sur être déclarées. Voici un exemple

DECLARE
salaire emp.SAL%TYPE;
nom emp.ename%TYPE;
BEGIN
SELECT ename, sal INTO nom,salaire FROM EMP
WHERE empno=7902;
END;

la clause WHERE est très importante ici et on doit s'assurer qu'elle ramène bien exactement 1 ligne. Sinon on obtient une erreur :
"ORA-01422": exact fetch returns more than requested number of rows" si il ya trop de lignes
"ORA-01403": no data found , si le SELECT ne ramène aucune ligne

Ordres SQL simples avec paramètres d'entrée

LEs ordres SQL précédents peuvent être paramétrés, ce qui dans la pratique ets un peu plus utile. Il suffit de déclarer la variable et de la faire apparaitre dans l'ordre SQL sans délimiteur particulier.

DECLARE
salaire emp.SAL%TYPE;
nom emp.ename%TYPE;

PROCEDURE lit_emp ( no IN INTEGER)
IS
BEGIN
SELECT ename, sal INTO nom,salaire FROM EMP
WHERE empno = no;
END lit_emp;

BEGIN
lit_emp(7902);
DBMS_OUTPUT.PUT_LINE('nom : '||nom|| ' salaire : ' || salaire);
END;

Select avec curseur explicite, boucle simple et sortie conditionnelle

Les curseurs sont des zones tampon utilisées pour tout SELECT multiligne.
A chaque ordre SELECT on associe un curseur nommé et on y fait référence dans la suite du programme pour traiter les lignes les unes après les autres jusqu'à la fin de la table. Chaque accès à un curseur incrémente un pointeur dans l'ensemble des lignes résultats (resul set).

On peut utiliser des types de variables prédéfinis et des attributs de curseur prédéfinis pour faciliter le codage :

types de variables prédéfinis utiles

type prédéfini	Signification	Exemple
%ROWTYPE	le type enregistrement d'une table	DECLARE ligne fournisseur%ROWTYPE (fournisseur est un nom de table...)
%TYPE	le type d'une colonne	DECLARE v_nom nom_fournisseur%TYPE (nom_fournisseur est une colonne de la table fournisseur...)

attributs de curseurs prédéfinis utiles

Attribut	TYPE	Signification
SQL%NOTFOUND	booléen	renvoie TRUE lorsque l'ordre SQL précédent n'a affecté aucune ligne.
SQL%ROWCOUNT	entier	renvoie le nombre de lignes traitées par l'ordre SQL précédent.
SQL%FOUND	booléen	négation logique de SQL%NOTFOUND, donc vrai dès qu'une ligne a été affectée par l'ordre SQL.
SQL%ISOPEN	booléen	vrai si curseur ouvert

Les différentes étapes :

déclaration, où l'on associe, dans la zone de déclaration un curseur avec un ordre SQL :
CURSOR nom_curseur IS ordre_select
ouverture du curseur, où l'on analyse l'ordre et l'on identifie les lignes candidates :
OPEN nom_curseur
rapatriement des valeurs dans les variables de réception :
FETCH nom_curseur INTO liste_variables
fermeture du curseur :
CLOSE nom_curseur

DECLARE
nom emp.ename%TYPE;
salaire emp.sal%TYPE;
CURSOR C1 IS SELECT ename, NVL(sal,0) FROM emp;
BEGIN
OPEN C1;
LOOP
FETCH C1 INTO nom, salaire;
EXIT WHEN C1%NOTFOUND;
DBMS_OUTPUT.PUT_LINE ( nom||' gagne '||salaire || ' dollars');
END LOOP;
CLOSE C1;
END;

Select avec curseur implicite et boucle FOR

Il existe une notation très compacte où l'on n'utilise pas de déclaration de curseur explicite, mais où l'on associe un ordre SELECT à une boucle FOR.

On utilisera une variable de réception de type implicite (oui encore) RECORD. Et l'on fera référence à chaque champ par son nom préfixé par le nom du record sus évoqué. Un exemple vaut mieux qu'un court discours :

BEGIN
FOR record_emp IN (SELECT * FROM EMP)
LOOP
DBMS_OUTPUT.PUT_LINE(record_emp.ename||' gagne '||record_emp.sal || ' Dollars');
END LOOP;
END;

on doit bien sûr ici utiliser les noms exacts des colonnes de la table

modifié
le 20/11/2006