Aller au contenu

Oracle Database/PL/SQL

Un livre de Wikilivres.
< Oracle Database
(Redirigé depuis Oracle/PL/SQL)
Séquences Séquences
Oracle ignore le type booléen Oracle ignore le type booléen

[ Modifier le sommaire ]

Séquences Séquences
Oracle ignore le type booléen 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.


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.

Structure d'un programme

[modifier | modifier le wikicode]

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

[modifier | modifier le wikicode]
 /* 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

[modifier | modifier le wikicode]

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

[modifier | modifier le wikicode]

Types natifs

[modifier | modifier le wikicode]

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

[modifier | modifier le wikicode]
 CREATE TYPE ...

Variables Composées

[modifier | modifier le wikicode]

Tableaux

[modifier | modifier le wikicode]
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

[modifier | modifier le wikicode]
  • Tab.first
  • Tab.count
  • Tab.next(indice)
  • Tab.last
  • Tab.prior(indice)
  • Tab.delete(indice)

Enregistrements

[modifier | modifier le wikicode]
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

[modifier | modifier le wikicode]

Conditions

[modifier | modifier le wikicode]
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

[modifier | modifier le wikicode]

FOR

[modifier | modifier le wikicode]
DECLARE 
	NUM	NUMBER(2) := 0
BEGIN
	FOR num IN 0..10
LOOP
		DBMS_OUTPUT.put_line(to_char(num)) ;
END LOOP ;
END ;

WHILE

[modifier | modifier le wikicode]
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

[modifier | modifier le wikicode]
  • 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

[modifier | modifier le wikicode]
  • 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

[modifier | modifier le wikicode]

Exception prédéfinie

[modifier | modifier le wikicode]
  • 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

[modifier | modifier le wikicode]
  • invalid_cursor
  • invalid_number
  • no_data_found
  • too_many_rows
  • value_error
  • zero_divide
  • dup_val_on_index

Exceptions personnalisées

[modifier | modifier le wikicode]
  • 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;
Récupérée de « https://fr.wikibooks.org/w/index.php?title=Oracle_Database/PL/SQL&oldid=663581 »