Oracle

Un livre de Wikibooks.

Sections

1 PL / SQL
2 Les curseurs
3 Les fonctions
4 Les procédures
5 Les exceptions

[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
     -- Corp 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_ligne (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

Relever 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 »

Catégorie : Oracle