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
    • Dictionnaire de données
    • Paramètres système
    • Utiliser les dates
    • Modifier une ligne
    • Analyse d'une table
  • Utilisation de fonctions
    • Fonction UNPIVOT
    • Fonction PIVOT
    • Fonction LISTAGG
    • Fonction TRIM
    • Fonction LENGTH
    • Fonction SUBSTR
    • Fonction DECODE
• 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

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;