Oracle Database/PL/SQL

PL/SQL pour Procedural Language SQL est un langage de programmation d'Oracle, spécialisé dans l'accès à ses bases de données. C'est donc une extension de la norme SQL.

Oracle Database

Introduction
Interfaces de gestion
Gestion des utilisateurs
Objets stockés
- Tablespaces
- Tables
- Vues
- Vues matérialisées
- Index
- Procédures stockées
- Fonctions
- Packages
- Trigger
- Séquences
PL/SQL
- Considérations générales
  - Oracle ignore le type booléen
  - Oracle ignore l'autoincrement
- Quelques requêtes utiles
- Utilisation de fonctions
Sécurité
Net Services
Serveurs partagés
Monitoring des performances
Maintenance proactive
Gestion des annulations
Conflits de verrouillage
Sauvegardes et restaurations
Bases de données multimédia
Bases de données spatiotemporelles
Deferrable constraint
Débogage
Désinstaller

Version imprimable

[ Modifier le sommaire ]

Séquences

Oracle ignore le type booléen

Consultez également ces pages dans d’autres projets Wikimedia :

	Article encyclopédique sur Wikipédia.
	Définition sur Wiktionnaire.

Structure d'un programme

Le code PL/SQL est structuré en "blocs" dont la structure générale est la suivante :

DECLARE
     -- Partie ou l’on déclare les constantes, les variables et les curseurs
 BEGIN
     -- Corps du programme 
 EXCEPTION
     -- Traitement des exceptions 
 END

Commentaires

 /* Exemple de commentaire PL/SQL
    Cette forme de commentaire peut prendre plusieurs lignes
  */
 -- Deuxième exemple, cette forme de commentaire est limitée à une ligne
 rem Troisième exemple (sous Windows)

Fonctions natives

PL/SQL offre plusieurs packages prédéfini :

DBMS_OUTPUT : utilisé pour afficher des messages pendant l'exécution du code
DBMS_JOB : permet de lancer du code en tâche de fond
DBMS_XPLAN : permet d'obtenir le plan d'exécution d'une commande SQL
DBMS_SESSION
DBMS_METADATA
UTL_FILE : permet de gérer les fichiers sur le disque, en dehors de la base
UTL_HTTP
UTL_SMTP

Types du langage

Types natifs

Les principaux types de donnée en PL/SQL sont : NUMBER, INTEGER, CHAR (chaine de longueur fixe), VARCHAR2 (chaine de longueur variable), DATE, TIMESTAMP, TEXT, etc.

Le symbole « := » est utilisé pour l’affectation d'une valeur.

Les types de chaînes de caractères, il existe plusieurs familles de types de textes :

codage	nombre fixé de caractères	nombre variable de caractères
caractère	CHAR	VARCHAR2
caractère Unicode (cf À la découverte d'Unicode )	NCHAR	NVARCHAR2

Créer un type

 CREATE TYPE ...

Variables Composées

Tableaux

DECLARE 
	-- Type Tableau de chaines de 20 caractères maxi
	TYPE nom_type_tableau is TABLE OF VARCHAR(20) INDEX BY BINARY
        TYPE nom_type_tableau2d is TABLE OF nom_type_tableau INDEX BY BINARY
	-- Déclaration de la variable tab de type nom_type_tableau
	tab    nom_type_tableau ;
        matrice  nom_type_tableau2d;
	-- Variable tab2 de type nom_type_tableau initialisée avec des valeurs
	tab2 nom_type_tableau := nom_type_tableau(‘l1’, ‘l2’,…) ;
BEGIN 
	tab(1) := ‘ligne 1’ ;
	tab(2) := ‘ligne 2’ ;
	-- Affichage du premier élément de la variable tab, c'est à dire "ligne 1"
	DBMS_OUTPUT.put_ligne(tab(1)) ;
END ;

DECLARE 
	TYPE nom_type_tableau IS VARRAY(2) OF VARCHAR2(30) ;
	tab nom_type_tableau := nom_type_tableau(‘l1’,’l2’) ;
BEGIN 
	DBMS_OUTPUT.put_ligne(tab(1)) ;
END ;

Méthodes disponibles pour/avec les variables tableau

Tab.first
Tab.count
Tab.next(indice)
Tab.last
Tab.prior(indice)
Tab.delete(indice)

Enregistrements

DECLARE 
	-- Un RECORD est une variable structurée, comme une struct en C
	TYPE time_type IS RECORD
	(
		min SMALLINT,
		heure SMALLINT
	) ;
	-- Déclararion de la variable Temps
	Temps time_type ;
BEGIN 
	Temps.min := 30 ;
	Temps.heure :=13 ;
END ;

Structures de contrôle

Conditions

DECLARE 
	-- Déclaration de la variable emp_rec qui sera structurée avec les mêmes champs
	-- qu'un enregistrement (%ROWTYPE) de la table ''employe''
	Emp_rec  employe%ROWTYPE ;
BEGIN 
	-- Il est possible de récupérer le résultat du SELECT directement dans la variable
	-- La valeur &temp sera demandée à l'utilisateur lors de l'exécution du script dans
	-- l'outil Sql*Plus d'Oracle 
	SELECT * INTO emp_rec
	FROM employe
	WHERE emp_num = &temp ;
	DBMS_output.put_line (emp_rec.nom) ;
END ;

DECLARE 
	Age NUMBER(3) := &temp ;
BEGIN
	IF age < 18 THEN 
		DBMS_OUTPUT.put_line(‘7’) ;
	ELSIF age > 65 THEN
		DBMS_OUTPUT.put_line(‘6,5’) ;
ELSE	
END IF ;
END

Boucles

FOR

DECLARE 
	NUM	NUMBER(2) := 0
BEGIN
	FOR num IN 0..10
LOOP
		DBMS_OUTPUT.put_line(to_char(num)) ;
END LOOP ;
END ;

WHILE

LOOP 
	DBMS_OUTPUT.put_line(to_char(num)) ;
	Num := num+1 ;
	EXIT WHEN num = 10 ;  // if num = 10 THEN EXIT ;
END LOOP ;
	WHILE num < 11 AND (cool OR pascool)
		LOOP
			Instructions …
END LOOP ;

Exemples

Exemples 1

Mettre la date à jour dans la db

DECLARE 
	TYPE DATE IS RECORD (
		Jour NUMBER(2),
		Mois NUMBER(2),
		Annees NUMBER(4));
TYPE DATE_SEVEN VARRAY(7) OF DATE 
	DATE DATE_SEVEN
BEGIN 
	DATE_NOW(1).Jour   := &temp1 ;
	DATE_NOW(1).Mois   := &temp2 ;
	DATE_NOW(1).Annees := &temp3 ;

Exemples 2

Créer un record qui contient matricule, nom, prénom

DECLARE 
	TYPE eleves IS RECORD(
		Matricule number(10),
		Nom varchar(20),
		Prenom varchar(20) )
	TYPE LesEleves TABLE OF eleves INDEX BY BINARY INTEGER ;
	Tab LesEleves ;
BEGIN 
	Tab(1).Matricule := 001 ;
	Tab(1).Nom       := ‘Bonjean’ ;
	Tab(1).Prenom    := ‘Simon’ ;

Exemples 3

Chercher dans une table ListeDeCourse

DECLARE 
	TYPE Course IS RECORD(
		NumArt number(10),
		Prix varchar(20),
		NomArt varchar(20) )
	TYPE ListeDeCourses TABLE OF Course INDEX BY BINARY INTEGER ;
	LesCourses ListeDeCourse;
BEGIN 
	SELECT * 
INTO LesCourses(1)
FROM tCourse 
	WHERE numArticle =1

Exemples 4

Relevé méteo

DECLARE 
	TYPE MeteoType IS RECORD(
		temp number(4,2),
		tx varchar(2),
		lieu varchar(10) )
	TYPE tabMeteoTypeTABLE OF MeteoType INDEX BY BINARY INTEGER ;
	tabMeteo tabMeteoType;
BEGIN 
	DBMS_output.putline(tabMeteo.first.lieu.tochar) ;
	DBMS_output.putline(tabMeteo.last.temp.tochar) ;

Exemples 5

Créer un tableau de 10 nombres

DECLARE 
	TYPE unAdix varray(10)
	Tab unAdix := unAdix(1,2,3,4,5,6,7,8,9,10) ;
	Compteur number(2) ;
BEGIN 
	FOR Compteur in 1..10
	LOOP
		IF MOD(tab(Compteur), 2) =0 THEN
			DBMS_output.putline(‘C est pair’) ;
		ELSE 
			DBMS_output.putline(‘C est pas pair’) ;
		END IF ;
	END LOOP
END ;

Exemples 6

Augmenter de 10 % tous les logiciels Photoshop

DECLARE 
	Intitule Logiciel.nom % type := &temp ;
BEGIN 
	IF intitule = ‘photoshop’ THEN 
		UPDATE logiciel
		SET prix = prix * 1,1
		WHERE  nom = intitule ;
	END IF ;
END;

Exemples 7

Insérer un élément dans la table locaux

DECLARE
localType is record (n° number(1), etage varchar2(4), type varchar2(10)) ;
	 // locate locaux % ROWTYPE;
locate localType ;
BEGIN
	Locate.n° := 4 ;
	Locate.etage := ‘2eme’ ;
	Locate.type := ‘linux’ ;
	INSERT INTO locaux VALUES (locate.n°,locate.etage, locate.type) ;
END ;

Exemples 8

Vérifie si le prix du logiciel encodé est supérieur à la moyenne

DECLARE
Log logiciel % ROWTYPE
Intitule Logiciel.nom % type := &temp ;
Prix2 	 Logiciel.prix % type ;
Moyenne Logiciel.prix % type ;
BEGIN
	SELECT prix INTO Prix2
FROM Logiciel
	WHERE nom = Intitule;
	SELECT avg(prix) INTO Moyenne 
	FROM Logiciel ;
	IF prix2 > Moyenne THEN
	UPDATE Logiciel set prix = prix -100 ;
	WHERE Logiciel.nom = Intitule ;
END IF
END ;

Curseurs

Explicite :

Un curseur explicite est un curseur déclaré explicitement avec le mot-clef CURSOR dans le bloc PLSQL.

DECLARE 
  CURSOR c1 IS SELECT nom FROM EMP;  
  nomEmp EMP.nom %type;
BEGIN
  OPEN c1;
  FETCH c1 INTO nomEmp;
  dbms_output.putline(nomEmp);
  --FETCH c1 INTO nomEmp;
  CLOSE c1;
END;

Implicite :

Un curseur implicite est un curseur généré automatiquement par Oracle pour une commande SQL incluse généralement dans un bloc PLSQL.

UPDATE EMP SET sol = sol *1.1;

SELECT SUM(sol) INTO total FROM EMP WHERE deptNo = 10;

Exemples :

DECLARE 
  bonus NUMBER(8,8) := 1000;
  CURSOR sol_cur IS SELECT sol, sol + bonus nouveauSol FROM emp
  WHERE dateEmbauche < SYSDATE;
BEGIN;

Explicite :

DECLARE 
  CURSOR salleCur IS SELECT * FROM SALLE
  maSalle SALLE%ROWTYPE;
BEGIN 
  FOR maSalle IN salleCur
  LOOP
     dbms_output.putline(maSalle.nSalle);
  END LOOP;
END;

Implicite :

DECLARE 
  maSalle SALLE%ROWTYPE;
BEGIN 
  FOR maSalle IN (SELECT * FROM SALLE)
  LOOP
     dbms_output.putline(maSalle.nSalle);
  END LOOP;
END;

Exceptions

Exception prédéfinie

Exemples 1

declare
begin
	insert into pilote values(1, 'CHARLIE', 'PARIS', 07);
exception
	when dup_val_on_index
	then dbms_output.put_line('Doublon');
end;

Exemples 2

Cherche l'employé n°555 et prévois le cas ou il n'existe pas.

declare
	employe_rec emp%rowtype;
begin
	select * into employe_rec from emp
	where emp = 555;
exception
	when no_data_found 	then dbms_output.put_line('Donnée non trouvée');
	when others			then null;
end;

Exemples 3

demander un nom à l'utilisateur et prévoir le cas où il inscrit trop de lettres

declare
	nom varchar2(5) := '&temp';
begin
	dbms_output.put_line(nom);
exception
	when value_error then dbms_output.put_line('chaine de caractères trop longue');
end;

Types d'exceptions prédéfinie

invalid_cursor
invalid_number
no_data_found
too_many_rows
value_error
zero_divide
dup_val_on_index

Exceptions personnalisées

Exemple 1 :

declare
	joueur_max exception;
	temp number(3);
begin
	select count(*) into temp from joueur
	if (temp = 100) then
		raise joueur_max;
	end if;
	insert into joueur values (1, 'test', 'test');
exception
	when dup_val_on_index 	then dbms_output.put_line('Le joueur existe déjà');
	when joueur_max 	then dbms_output.put_line('Nombre de joueurs max atteint');
end;