Oracle

Un livre de Wikibooks.

Sections

[modifier] PL / SQL

PL/SQL (Procedural Language/Structured Query Language) est un langage procédural propriétaire créé par Oracle et utilisé dans le cadre de bases de données relationnelles. Il permet de combiner des requêtes SQL et des instructions procédurales (boucles, conditions...), dans le but de créer des traitements complexes destinés à être stockés sur le serveur de base de données (objets serveur), comme par exemple des procédures stockées ou des déclencheurs(trigger). Les dernières évolutions proposées par Oracle reposent sur un moteur permettant dorénavant de créer et gérer des objets contenant des méthodes et des propriétés. PostgreSQL est une alternative libre aux PL/SQL, le PL/pgSQL. Les principaux types de donnée en PL/SQL sont : NUMBER, INTEGER, CHAR, VARCHAR2, DATE, TIMESTAMP, TEXT etc. Le symbole « := » est utilisé pour l’affectation

DECLARE
     -- Partie ou l’on déclare les variables
 BEGIN
     -- Corps du programme 
 EXCEPTION
     -- Manipulation des exceptions 
 END
 /* exemple de commentaire PL/SQL*/
 -- Autre exemple

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

  • DBMS_OUTPUT
  • DBMS_JOB
  • DBMS_XPLAN
  • DBMS_SESSION
  • DBMS_METADATA
  • UTL_FILE
  • UTL_HTTP
  • UTL_SMTP

Variables Composées :

[modifier] Les tableaux

DECLARE 
	TYPE nom_type_tableau IS TABLE OF VARCHAR(20) INDEX BY BINARY
	tab    nom_type_tableau ;
tab2 nom_type_tableau := nom_type_tableau(‘l1’, ‘l2’,…) ;
BEGIN 
	tab(1) := ‘ligne 1’ ;
	tab(2) := ‘ligne 2’ ;
	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 ;

[modifier] Methode disponibles pour avec les variables tableau

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

[modifier] Enregistrements

DECLARE 
	TYPE time_type IS RECHORD
	(
		MIN SMALLINT,
		heure SMALLINT
	) ;
	Temps time_type ;
BEGIN 
	Temps.MIN := 30 ;
	Temps.heure :=13 ;
END ;

[modifier] Condition

DECLARE 
	Emp_rec  employe %ROWTYPE ;
BEGIN 
	SELECT * INTO emp_rec
	FROM employe
	WHERE em = & 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

[modifier] Boucle

[modifier] FOR

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

[modifier] 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 ;


[modifier] 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% tout 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 ;

[modifier] Les curseurs

  • Explicite :
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 :
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;

[modifier] Les fonctions

CREATE OR REPLACE FUNCTION loginExist(Param1 tableName.champName%TYPE)
RETURN BOOLEAN
IS
-- declare
       retVal BOOLEAN := TRUE;
BEGIN 
--Instructions
       RETURN retVal;
END;

[modifier] Les procédures

CREATE OR REPLACE PROCEDURE nomProc(
	param1 IN DATE, 
	param2 IN OUT DATE
	param3 OUT DATE)
IS 
-- declare
BEGIN
--Instructions
END;

[modifier] Les exceptions

[modifier] 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;

[modifier] Types d'exceptions prédéfinie

  • invalid_cursor
  • invalid_number
  • no_data_found
  • too_many_rows
  • value_error
  • zero_divide
  • dup_val_on_index

[modifier] exception personnalisée

  • 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 « http://fr.wikibooks.org/wiki/Oracle »