Les ordres SQL dans PL/SQL

Il existe deux façons d'affecter des valeurs à des variables. La première utilise l'opérateur d'assignation, le signe « := ».La deuxième façon d'attribuer des valeurs à des variables consiste à effectuer un « SELECT » de valeurs en provenance de la base de données.

en savoir

La syntaxe utilisée se présente comme suit :

SELECT EXPRESSION1 [,...] INTO VARIABLE1[,...]

FROM NOM_TABLE [WHERE PREDICAT] ;

SQL> declare

  2     v_employe EMPLOYES%ROWTYPE;

  3  begin

  4     SELECT * INTO v_employe FROM EMPLOYES WHERE NO_EMPLOYE = 5;

  5     dbms_output.put_line( v_employe.NOM ||' '|| v_employe.SALAIRE

  6                           ||' '|| v_employe.COMMISSION  );

  7     v_employe.SALAIRE    := v_employe.SALAIRE * 1.1;                         

  8     v_employe.COMMISSION := 1000;

  9     UPDATE EMPLOYES SET ROW = v_employe WHERE NO_EMPLOYE = 5;

 10     SELECT * INTO v_employe FROM EMPLOYES WHERE NO_EMPLOYE = 5;  

 11     dbms_output.put_line( v_employe.NOM ||' '|| v_employe.SALAIRE  

 12                           ||' '|| v_employe.COMMISSION  );

 13  end;                        

 14  /

Lamarre 7800 200

Lamarre 8580 1000

 

Procédure PL/SQL terminée avec succès.

SQL> declare

  2    v_employe EMPLOYES%ROWTYPE;

  3  begin SELECT * INTO v_employe FROM EMPLOYES WHERE NO_EMPLOYE = 150;

  4  end;

  5  /

declare

*

ERREUR à la ligne 1 :

ORA-01403: aucune donnée trouvée

ORA-06512: à ligne 4

Une requête qui ne renvoie aucun enregistrement génère une erreur PL/SQL.

SQL> declare

  2     v_nb number;

  3  begin

  4     SELECT count(*) INTO v_nb FROM EMPLOYES WHERE NO_EMPLOYE = 15;

  5     dbms_output.put_line('La valeur de v_nb est : '|| v_nb);  

  6  end;

  7  /

La valeur de v_nb est : 0

 

Procédure PL/SQL terminée avec succès.

De la même manière que vous récupérez une variable scalaire ou un enregistrement, vous pouvez récupérer une collection à partir d’une interrogation à l’aide de l’ordre « SELECT … INTO ».

SQL> DESC PAYS

Nom                                NULL ?   Type

 ---------------------------------- -------- -----------------------

 PAYS                                        NVARCHAR2(15)

 ADRESSE                                     T_ADRESSES

 

SQL> DESC T_ADRESSES

 T_ADRESSES TABLE OF R_ADRESSE

 Nom                                NULL ?   Type

 ---------------------------------- -------- -----------------------

 SOCIETE                                     NVARCHAR2(40)

 ADRESSE                                     NVARCHAR2(80)

 VILLE                                       NVARCHAR2(35)

 CODE_POSTAL                                 NVARCHAR2(10)

 

SQL> SELECT ADRESSE FROM PAYS WHERE PAYS = 'France';

 

ADRESSE(SOCIETE, ADRESSE, VILLE, CODE_POSTAL)

--------------------------------------------------------------------

T_ADRESSES(R_ADRESSE('Du monde entier', '67, rue des Cinquante Otage

s', 'Nantes', '44000'), R_ADRESSE('Folies gourmandes', '184, chaussé

...

 

SQL> declare

  2   adresses t_adresses;

  3   i        NUMBER(3);

  4  begin

  5   SELECT ADRESSE INTO adresses FROM PAYS WHERE PAYS = 'France';

  6   dbms_output.put_line( 'Éléments : '||adresses.COUNT);

  7   i := adresses.FIRST;

  8   loop

  9      dbms_output.put_line( i||' '||adresses(i).SOCIETE||'--'||

 10               adresses(i).ADRESSE||'--'||adresses(i).VILLE);

 11      i := adresses.NEXT(i);

 12   exit when i is null;

 13   end loop;

 14  end;

 15  /

Éléments : 11

1 Du monde entier--67, rue des Cinquante Otages--Nantes

2 Folies gourmandes--184, chaussée de Tournai--Lille

3 France restauration--54, rue Royale--Nantes

4 La corne d'abondance--67, avenue de l'Europe--Versailles

5 La maison d'Asie--1 rue Alsace-Lorraine--Toulouse

6 Paris spécialités--265, boulevard Charonne--Paris

7 Spécialités du monde--25, rue Lauriston--Paris

8 Victuailles en stock--2, rue du Commerce--Lyon

9 Vins et alcools Chevalier--59 rue de l'Abbaye--Reims

10 Blondel père et fils--24, place Kléber--Strasbourg

11 Bon app'--12, rue des Bouchers--Marseille

BULK COLLECT

L’inconvénient de la commande « SELECT … INTO » est que si elle renvoie plusieurs enregistrements une erreur PL/SQL est générée.

SQL> declare

  2     v_employe EMPLOYES%ROWTYPE;

  3  begin

  4     SELECT * INTO v_employe FROM EMPLOYES WHERE FONCTION LIKE 'Rep%';

  5  end;  

  6  /

declare

*

ERREUR à la ligne 1 :

ORA-01422: l'extraction exacte ramène plus que le nombre de lignes demandé

ORA-06512: à ligne 4

La clause « BULK COLLECT » vous permet d’extraire plusieurs enregistrements, en un seul aller-retour vers la base de données. Il s’agit de demander au moteur SQL de traiter par lots l'ensemble des lignes ramenées par la requête dans les collections spécifiées ce qui améliore les performances de votre requête.

La syntaxe utilisée se présente comme suit :

SELECT EXPRESSION1 [,...]BULK COLLECT INTO TABLEAU1[,...]

FROM NOM_TABLE ... [WHERE PREDICAT] ;

Le moteur SQL initialise et étend automatiquement les collections référencées dans la clause « BULK COLLECT ». Il remplit les collections à partir de l'indice, insère les éléments séquentiellement et remplace les valeurs de tout élément préalablement affecté.

SQL> declare

  2       TYPE EMPLOYE IS  TABLE OF EMPLOYES%ROWTYPE;

  3       TYPE TABLEAU_NOM IS TABLE OF EMPLOYES.NOM%TYPE

  4                    INDEX BY BINARY_INTEGER;

  5       TYPE TABLEAU_PRENOM IS TABLE OF EMPLOYES.PRENOM%TYPE

  6                    INDEX BY BINARY_INTEGER;

  7       T_NOM        TABLEAU_NOM;

  8       T_PRENOM     TABLEAU_PRENOM;

  9  begin

 10     T_NOM(1)    := 'BIZOÏ';

 11     T_PRENOM(1) := 'Razvan';

 12     dbms_output.put_line(T_NOM(1)||' '||T_PRENOM(1));

 13     SELECT NOM, PRENOM

 14     BULK COLLECT INTO T_NOM, T_PRENOM

 15     FROM EMPLOYES

 16     WHERE ROWNUM < 4;

 17     dbms_output.put_line(T_NOM(1)||' '||T_PRENOM(1));

 18     dbms_output.put_line(T_NOM(2)||' '||T_PRENOM(2));

 19     dbms_output.put_line(T_NOM(3)||' '||T_PRENOM(3));

 20  end;

 21  /

BIZOÏ Razvan

Callahan Laura

Buchanan Steven

Peacock Margaret

 

Procédure PL/SQL terminée avec succès.

Les deux tableaux ont le premier élément affecté avec le nom ‘BIZOÏ’ et le prénom ‘Razvan’. La clause « BULK COLLECT » remplit les deux collections séquentiellement et remplace les valeurs de tout élément préalablement affecté.

La collection ou les collections acceptées ne peuvent stocker que des valeurs scalaires, ou directement des enregistrements, à condition qu’ils soient déclarés par une référence « %ROWTYPE ».

SQL> declare

  2       TYPE EMPLOYE IS  TABLE OF EMPLOYES%ROWTYPE;

  3       TYPE TABLEAU_EMPLOYE IS TABLE OF EMPLOYE NOT NULL

  4            INDEX BY BINARY_INTEGER;

  5       T_EMP TABLEAU_EMPLOYE;

  6  begin

  7     SELECT * BULK COLLECT INTO T_EMP FROM EMPLOYES

  8     WHERE FONCTION LIKE 'Rep%';

  9  end;

 10  /

   SELECT * BULK COLLECT INTO T_EMP FROM EMPLOYES

                              *

ERREUR à la ligne 7 :

ORA-06550: Ligne 7, colonne 31 :

PLS-00642: types de collecte locale interdite dans les instructions SQL

ORA-06550: Ligne 7, colonne 37 :

PL/SQL: ORA-00947: nombre de valeurs insuffisant

ORA-06550: Ligne 7, colonne 4 :

PL/SQL: SQL Statement ignored

 

SQL> declare

  2       TYPE TABLEAU_EMPLOYE IS TABLE OF EMPLOYES%ROWTYPE 

  3            INDEX BY BINARY_INTEGER;

  4       T_EMP TABLEAU_EMPLOYE;

  5  begin

  6     SELECT * BULK COLLECT INTO T_EMP FROM EMPLOYES

  7     WHERE FONCTION LIKE 'Rep%';

  8     dbms_output.put_line(T_EMP(1).NOM||' '||T_EMP(1).PRENOM);

  9  end;

 10  /

Peacock Margaret

 

Procédure PL/SQL terminée avec succès.

Les deux tableaux associatifs déclarés dans les blocs sont identiques du point de vue de leurs structures. Cependant la première déclaration fait référence à un type prédéfinit EMPLOYE que le moteur SQL n’arrive pas à vérifier s’il est ou non du même type que l’enregistrement renvoyé par la requête SQL. Dans le deuxième bloc, le tableau associatif est déclaré comme une collection d’enregistrements de la table EMPLOYES ainsi reconnu par le moteur SQL.

Dans le cas d’une table qui stocke un ou plusieurs tableaux imbriqués le tableau des enregistrements récupère à l’aide de la clause « BULK COLLECT » dans un ordre « SELECT » va être dans ce cas une collection a deux dimensions.

SQL> DESC PAYS

Nom                                NULL ?   Type

 ---------------------------------- -------- ---------------

 PAYS                                        NVARCHAR2(15)

 ADRESSE                                     T_ADRESSES

 

SQL> DESC T_ADRESSES

 T_ADRESSES TABLE OF R_ADRESSE

 Nom                                NULL ?   Type

 ---------------------------------- -------- ---------------

 SOCIETE                                     NVARCHAR2(40)

 ADRESSE                                     NVARCHAR2(80)

 VILLE                                       NVARCHAR2(35)

 CODE_POSTAL                                 NVARCHAR2(10)

 

SQL>  declare

  2   TYPE TABLEAU_PAYS IS TABLE OF PAYS%ROWTYPE INDEX BY BINARY_INTEGER;

  3   l_pays   TABLEAU_PAYS;           

  4   i        NUMBER(3);

  5   j        NUMBER(3);

  6  begin

  7   SELECT * BULK COLLECT INTO l_pays FROM PAYS

  8   WHERE PAYS IN ('France','Suède','Espagne');

  9   dbms_output.put_line( 'l_pays éléments: '||l_pays.COUNT);

 10   i := l_pays.FIRST;

 11   loop

 12      dbms_output.put_line( i||' '||l_pays(i).PAYS);

 13      j := l_pays(i).adresse.FIRST;

 14      dbms_output.put_line( i||'    '||

 15           'l_pays.adresse éléments: '||l_pays(i).adresse.COUNT);

 16      loop

 17          dbms_output.put_line( i||'    '|| l_pays(i).adresse(j).SOCIETE

 18                 ||'--'||l_pays(i).adresse(j).ADRESSE

 19                 ||'--'||UPPER(l_pays(i).adresse(j).VILLE));

 20          j := l_pays(i).adresse.NEXT(j);              

 21          exit when j is null;

 22      end loop;

 23      i := l_pays.NEXT(i);

 24   exit when i is null;

 25   end loop;

 26  end;

 27  /

l_pays éléments: 3

1 Suède

1    l_pays.adresse éléments: 2

1    Folk och fä HB--Åkergatan 24--BRÄCKE

1    Berglunds snabbköp--Berguvsvägen  8--LULEÅ

2 Espagne

2    l_pays.adresse éléments: 5

2    FISSA Fabrica Inter. Salchichas S.A.--Moralzarzal, 86--MADRID

2    Galería del gastrónomo--Rambla de Cataluña, 23--BARCELONA

2    Godos Cocina Típica--Romero, 33--SEVILLA

2    Romero y tomillo--Gran Vía, 1--MADRID

2    Bólido Comidas preparadas--Araquil, 67--MADRID

3 France

3    l_pays.adresse éléments: 11

3    Du monde entier--67, rue des Cinquante Otages--NANTES

3    Folies gourmandes--184, chaussée de Tournai--LILLE

3    France restauration--54, rue Royale--NANTES

3    La corne d'abondance--67, avenue de l'Europe--VERSAILLES

3    La maison d'Asie--1 rue Alsace-Lorraine--TOULOUSE

3    Paris spécialités--265, boulevard Charonne--PARIS

3    Spécialités du monde--25, rue Lauriston--PARIS

3    Victuailles en stock--2, rue du Commerce--LYON

3    Vins et alcools Chevalier--59 rue de l'Abbaye--REIMS

3    Blondel père et fils--24, place Kléber--STRASBOURG

3    Bon app'--12, rue des Bouchers--MARSEILLE

 

Procédure PL/SQL terminée avec succès.

Dans l’exemple précédent, pour pouvoir afficher les éléments du tableau que nous avons récupérés, nous avons utilisé deux boucles imbriquées pour parcourir la matrice constituée.

Insertion des lignes

Vous pouvez utiliser la commande « INSERT » avec toutes les syntaxes étudiées lors de la mise à jour des données. Les variables PL/SQL sont utilisées aussi bien pour insérer des valeurs ainsi que pour les comparaisons dans la clause « WHERE ».

A partir de la version Oracle 11g il est possible d’affecter une variable à l’aide d’une séquence. Dans les versions antérieures il faut utiliser l’ordre « SELECT … INTO » pour affecter une variable à l’aide d’une séquence.

SQL> CREATE SEQUENCE S_CAT START WITH 11;

 

Séquence créée.

 

SQL> declare

  2    v_code CATEGORIES.CODE_CATEGORIE%TYPE := S_CAT.NEXTVAL;

  3    v_catg CATEGORIES.NOM_CATEGORIE%TYPE

  4               := 'Fruits et légumes frais';

  5    v_desc CATEGORIES.DESCRIPTION%TYPE

  6               := 'Fruits et légumes frais';

  7    ve_cat CATEGORIES%ROWTYPE;

  8    v_ret  NUMBER(2);

  9  begin

 10    SELECT COUNT(*) INTO v_ret FROM CATEGORIES

 11    WHERE CODE_CATEGORIE = v_code;

 12    dbms_output.put_line('Le nombre d''enregistrements est : '

 13                          ||v_ret);

 14    INSERT INTO CATEGORIES VALUES ( v_code, v_catg, v_desc);

 15    SELECT * INTO ve_cat FROM CATEGORIES

 16    WHERE CODE_CATEGORIE = v_code;

 17    dbms_output.put_line(ve_cat.CODE_CATEGORIE||'--'||

 18        ve_cat.NOM_CATEGORIE||'--'||ve_cat.DESCRIPTION);

 19  end;

 20  /

Le nombre d'enregistrements est : 0

11--Fruits et légumes frais--Fruits et légumes frais

 

Procédure PL/SQL terminée avec succès.

Vous pouvez utiliser la commande SQL « INSERT » basée sur un enregistrement avec la structure de la table dans laquelle on veut effectuer les insertions. La syntaxe de la commande « INSERT » est la suivante :

INSERT INTO NOM_TABLE VALUES VARIABLE_ENREGISTREMENT;

SQL> declare

  2       v_client CLIENTS%ROWTYPE;

  3  begin

  4       v_client.CODE_CLIENT := 'ETELI';

  5       v_client.SOCIETE     := 'ETELIA';

  6       v_client.ADRESSE     := '44, Paul Claudel';

  7       v_client.VILLE       := 'STRASBOURG';

  8       v_client.CODE_POSTAL := '67000';

  9       v_client.PAYS        := 'FRANCE';

 10       v_client.TELEPHONE   := '03.88.27.13.35';

 11       INSERT INTO CLIENTS VALUES v_client;

 12  end;

 13  /

 

Procédure PL/SQL terminée avec succès.

 

SQL> SELECT CODE_CLIENT, SOCIETE, ADRESSE,

  2         VILLE, CODE_POSTAL, PAYS, TELEPHONE

  3  FROM CLIENTS

  4  WHERE CODE_CLIENT = 'ETELI';

 

CODE_ SOCIETE ADRESSE          VILLE      CODE_ PAYS  

----- ------- ---------------- ---------- ----- -------

ETELI ETELIA  104, rue Mélanie STRASBOURG 67000 FRANCE

Dans l’exemple suivant, vous pouvez voir la création d’une table CLIENTS_CONTACTS qui stocke une liste de sociétés et la liste des contacts de ces sociétés. La liste de contacts est un tableau imbriqué qui stocke les enregistrements des clients et pour chaque client un tableau imbriqué avec une liste des numéros de téléphones où il peut être contacté. Dans le bloc PL/SQL, une requête est conçue pour retrouver un enregistrement qui peut alimenter la table CLIENTS_CONTACTS. Tous les enregistrements de cette requête sont affectés à un tableau associatif. La lecture séquentielle de ce tableau permet d’insérer chaque élément dans la table CLIENTS_CONTACTS.

SQL> CREATE TYPE l_telephones AS TABLE OF VARCHAR2(24);

  2  /

 

Type créé.

 

SQL> CREATE TYPE e_client AS OBJECT (

  2     NOM                  NVARCHAR2(40),

  3     PRENOM               NVARCHAR2(30),

  4     FONCTION             VARCHAR2(30) ,

  5     telephones           l_telephones);

  6  /

 

Type créé.

 

SQL> CREATE TYPE l_clients AS TABLE OF e_client;

  2  /

 

Type créé.

 

SQL> CREATE TABLE CLIENTS_CONTACTS (

  2     ID                 NUMBER(2) CONSTRAINT CLI_PK PRIMARY KEY,

  3     SOCIETE            NVARCHAR2(40),

  4     contacts           l_clients)

  5  NESTED TABLE contacts STORE AS seg_contacts

  6        (NESTED TABLE telephones STORE AS seg_telephones);

 

Table créée.

 

SQL> CREATE SEQUENCE S_CLI START WITH 1;

 

Séquence créée.

 

SQL> declare

  2   TYPE T_CLIENTS_CONTACTS IS TABLE OF CLIENTS_CONTACTS%ROWTYPE

  3              INDEX BY BINARY_INTEGER;

  4   l_cli_cont   T_CLIENTS_CONTACTS;

  5   i            NUMBER(3);

  6  begin

  7      SELECT S_CLI.NEXTVAL, C1.SOCIETE,

  8             ( SELECT CAST(COLLECT(

  9                           e_client( NOM, PRENOM, FONCTION,

 10                             ( SELECT CAST( COLLECT(TELEPHONE)

 11                                            AS l_telephones)

 12                               FROM CLIENTS C2

 13                               WHERE C2.PAYS = C1.PAYS )

 14                                           )) AS l_clients)

 15               FROM EMPLOYES E

 16               WHERE E.PAYS = C1.PAYS)

 17      BULK COLLECT INTO l_cli_cont

 18      FROM ( SELECT PAYS, SOCIETE, ROW_NUMBER()

 19             OVER (PARTITION BY PAYS ORDER BY SOCIETE) RN

 20             FROM CLIENTS) C1

 21      WHERE C1.RN = 1;

 22      dbms_output.put_line( 'l_cli_cont éléments: '||

 23                             l_cli_cont.COUNT);

 24   i := l_cli_cont.FIRST;

 25   loop

 26      dbms_output.put_line( i||' '||l_cli_cont(i).SOCIETE);

 27      INSERT INTO CLIENTS_CONTACTS VALUES l_cli_cont(i);

 28      i := l_cli_cont.NEXT(i);

 29   exit when i is null;

 30   end loop;

 31  end;

 32  /

l_cli_cont éléments: 21

1 Alfreds Futterkiste

2 Cactus Comidas para llevar

3 Ernst Handel

4 Maison Dewey

5 Comércio Mineiro

6 Bottom-Dollar Markets

7 Simons bistro

8 Bólido Comidas preparadas

9 Great Lakes Food Market

10 Wartian Herkku

...

Modification des données

Vous pouvez utiliser la commande « UPDATE » avec toutes les syntaxes étudiées lors de la mise à jour des données. Les variables PL/SQL sont utilisées aussi bien pour la mise à jour des valeurs que pour les comparaisons dans la clause « WHERE ».

SQL> SELECT NOM, DATE_NAISSANCE, SALAIRE FROM EMPLOYES

  2  WHERE DATE_NAISSANCE < '01/01/1966';

 

NOM                                      DATE_NAISS    SALAIRE

---------------------------------------- ---------- ----------

Maurer                                   19/10/1965       1400

Maillard                                 20/02/1965       6600

 

SQL> declare

  2     v_date   DATE := '01/01/1966';

  3     v_pct    NUMBER(4,3) := 1.05;

  4  begin

  5     UPDATE EMPLOYES SET SALAIRE = SALAIRE * v_pct

  6     WHERE DATE_NAISSANCE < v_date;

  7  end;

  8  /

 

Procédure PL/SQL terminée avec succès.

 

SQL> SELECT NOM, DATE_NAISSANCE, SALAIRE FROM EMPLOYES

  2  WHERE DATE_NAISSANCE < '01/01/1966';

 

NOM                                      DATE_NAISS    SALAIRE

---------------------------------------- ---------- ----------

Maurer                                   19/10/1965       1470

Maillard                                 20/02/1965       6930

Vous pouvez également mettre à jour une ligne complète avec un enregistrement. Pour les mises à jour basées sur des enregistrements, vous avez besoin d'un nouveau mot-clé, « ROW », qui indique que je mets à jour la totalité de la ligne avec un enregistrement.

La syntaxe de la commande « UPDATE » est la suivante :

UPDATE NOM_TABLE SET ROW = VARIABLE_ENREGISTREMENT[WHERE PREDICAT];

SQL> declare

  2       v_client CLIENTS%ROWTYPE;

  3  begin

  4       SELECT * INTO v_client FROM  CLIENTS WHERE CODE_CLIENT = 'SPECD';

  5       v_client.ADRESSE     := '104, rue Mélanie';    

  6       v_client.TELEPHONE   := '01.41.27.13.35';    

  7       v_client.FAX         := '01.41.27.13.35';

  8       UPDATE CLIENTS SET ROW = v_client WHERE CODE_CLIENT = 'SPECD';

  9  end;    

 10  /    

 

Procédure PL/SQL terminée avec succès.

 

SQL> SELECT SOCIETE, ADRESSE, TELEPHONE, FAX FROM CLIENTS

  2  WHERE CODE_CLIENT = 'SPECD';

 

SOCIETE              ADRESSE          TELEPHONE      FAX

-------------------- ---------------- -------------- --------------

Spécialités du monde 104, rue Mélanie 01.41.27.13.35 01.41.27.13.35

Vous devez mettre à jour la totalité d'une ligne en utilisant la syntaxe « ROW » à l’aide d’une variable de type enregistrement. Si vous utilisez le mot-clé « ROW », il n’est pas possible d’employer une sous-requête pour effectuer la mise à jour.

Suppression des données

Vous pouvez utiliser la commande « DELETE » avec la syntaxe étudiée lors de la mise à jour des données. Les variables PL/SQL sont utilisées pour les comparaisons dans la clause « WHERE ».

SQL> SELECT COUNT(*) FROM COMMANDES NATURAL JOIN DETAILS_COMMANDES

  2  WHERE DATE_ENVOI < '01/01/1997';

 

  COUNT(*)

----------

       379

 

SQL> declare

  2       v_date COMMANDES.DATE_ENVOI%TYPE := '01/01/1997';

  4  begin

  5       DELETE DETAILS_COMMANDES

  6       WHERE NO_COMMANDE IN (

  7              SELECT NO_COMMANDE FROM COMMANDES

  8              WHERE DATE_ENVOI < v_date);

  9       DELETE COMMANDES

 10       WHERE DATE_ENVOI < v_date;

 11  end;

 12  /

 

Procédure PL/SQL terminée avec succès.

 

SQL> SELECT COUNT(*) FROM COMMANDES NATURAL JOIN DETAILS_COMMANDES

  2  WHERE DATE_ENVOI < '01/01/1997';

 

  COUNT(*)

----------

         0

Attributs des ordres LMD

Le langage PL/SQL vous permet d'obtenir des informations sur l'exécution d'un ordre « INSERT », « UPDATE » ou « DELETE ».

Les attributs d'exécution d'un ordre LMD se réfèrent toujours à l'ordre SQL le plus récent, quelque soit le bloc dans lequel l’ordre est exécuté. Ainsi il est très fortement conseillé de récupérer ces attributs volatils dans une variable pour les exploiter

SQL%FOUND

Attribut de type « BOOLEAN », il renvoie « TRUE » si un ou plusieurs enregistrements ont été créés, mis à jour ou supprimés avec succès.

SQL> begin

  2       UPDATE DETAILS_COMMANDES

  3           SET PRIX_UNITAIRE = PRIX_UNITAIRE * 1.05

  4       WHERE NO_COMMANDE = 11077;

  5       if SQL%FOUND then 

  6           COMMIT;

  7       end if;

  8  end;

  9  /

 

Procédure PL/SQL terminée avec succès.

On effectue la validation de la transaction uniquement si on a mis à jour un ou plusieurs enregistrements. L’ordre « COMMIT » ou « ROLLBACK » ne doit pas être lancé sans raison car il augmente les volumes d’écritures du serveur de base de données et ainsi augmente le temps de traitements. (Pour plus d’informations sur les blocs de contrôle « IF » voir le module correspondant.)

SQL%NOTFOUND

Attribut de type « BOOLEAN » il renvoie « TRUE » si aucun enregistrement n'a été modifié par l'ordre LMD.

SQL> begin

  2       DELETE DETAILS_COMMANDES NO_COMMANDE = 11080

  3       if SQL%FOUND then

  4             dbms_output.put_line('Trouvé');

  5       else             

  6             dbms_output.put_line('Non trouvé');

  7       end if;

  8  end;

  9  /

Non trouvé

 

Procédure PL/SQL terminée avec succès.

SQL%ROWCOUNT

Attribut de numérique, il renvoie le nombre d’enregistrements modifiés par l'ordre LMD.

SQL> begin

  2       UPDATE DETAILS_COMMANDES

  3           SET PRIX_UNITAIRE = PRIX_UNITAIRE * 1.05

  4       WHERE NO_COMMANDE = 11077;

  5       if SQL%FOUND then

  6          COMMIT;

  7          dbms_output.put_line('Enregistrements modifiés : '

  8                                ||SQL%ROWCOUNT);

  9       else

 10          dbms_output.put_line('Aucun enregistrement trouvé');

 11       end if;

 12  end;

 13  /

Enregistrements modifiés : 25

 

Procédure PL/SQL terminée avec succès.

INSERT RETURNING

Vous pouvez utiliser la clause « RETURNING » avec vos ordres LMD, « INSERT », « UPDATE » ou « DELETE » pour renvoyer les valeurs de champs des enregistrements affectés.

La syntaxe de la clause « RETURNING » pour l’ordre « INSERT » est la suivante :

INSERT INTO NOM_TABLE [( COLONNE1[,...])]

 { VALUES {( EXPRESSION1[,...]) | VARIABLE_ENREGISTREMENT}

      [RETURNING EXPRESSION1[,...] INTO VARIABLE1[,...]]

  | SOUS-REQUETE };

La clause « RETURNING » ne permet pas d’utiliser « * » pour retourner l’ensemble des expressions insérées dans l’enregistrement, il faut préciser chaque expression. Par contre vous pouvez utiliser une liste de variables pour récupérer les valeurs de retour ou tout simplement une variable de type enregistrement.

SQL> declare

  2      v_rowid  ROWID;

  3  begin

  4     INSERT INTO CATEGORIES VALUES ( 9,'Fruits et légumes frais',

  5        'Fruits et légumes frais') RETURNING ROWID INTO v_rowid;

  7     dbms_output.put_line('Le rowid est : '||v_rowid);

  8  end;

  9  /

Le rowid est : AAAMy2AAFAAAAAOAAA

 

Procédure PL/SQL terminée avec succès.

RETURNING

Les deux autres ordres LMD « UPDATE » ou « DELETE » permettent également l’utilisation de la clause « RETURNING » ainsi que la possibilité de la combiner avec la clause « BULK COLLECT » pour renvoyer une collection d'enregistrements.

La syntaxe de la clause « RETURNING » pour les deux ordres est la suivante :

...

     RETURNING EXPRESSION1[,...]

           { INTO VARIABLE1[,...]

           | BULK COLLECT INTO VARIABLE_ENREGISTREMENT  }

...

La clause « RETURNING » ne permet pas d’utiliser « * » pour retourner l’ensemble des expressions insérées dans l’enregistrement, il faut préciser chaque expression.

SQL> declare

  2      v_rowid  ROWID;

  3      v_cat CATEGORIES%ROWTYPE;

  4  begin

  5     INSERT INTO CATEGORIES VALUES ( 9,'Fruits et légumes',

  6                                    'Fruits et légumes frais')

  7     RETURNING  ROWID INTO v_rowid;

  8     UPDATE CATEGORIES SET NOM_CATEGORIE = DESCRIPTION

  9     WHERE ROWID = v_rowid

 10     RETURNING  NOM_CATEGORIE INTO v_cat.NOM_CATEGORIE;

 11     dbms_output.put_line(v_rowid||' '||v_cat.NOM_CATEGORIE);

 12     DELETE CATEGORIES

 13        WHERE ROWID = v_rowid

 14     RETURNING  CODE_CATEGORIE INTO v_cat.CODE_CATEGORIE;

 15     dbms_output.put_line(v_cat.CODE_CATEGORIE);

 16  end;

 17  /

AAAMy2AAFAAAAAQAAA Fruits et légumes frais

9

 

Procédure PL/SQL terminée avec succès.

La pseudo-colonne « ROWID » retourne l’adresse physique de chaque enregistrement et elle peut être utilisée directement dans la clause « WHERE » pour retrouver l’enregistrement correspondant.

Cette opération est la plus rapide manière d’accéder a un enregistrement dans une table mais elle n’est pas sans risques. Si l’administrateur de votre base de données réorganise à chaud cette table votre interrogation n’aboutira pas.

Ainsi si vous décidez d’employer cette manière de travail il ne faut pas stocker les « ROWID », il s’agit des traitements ponctuels, et il faut communiquer avec les administrateurs des vos base de données.

SQL> declare

  2      TYPE TABLEAU_DETAILS_COMMANDES IS TABLE OF

  3           DETAILS_COMMANDES%ROWTYPE INDEX BY BINARY_INTEGER;

  4      v_det_comm TABLEAU_DETAILS_COMMANDES;

  5  begin

  6      UPDATE DETAILS_COMMANDES SET PRIX_UNITAIRE = PRIX_UNITAIRE * 1.05

  7      WHERE NO_COMMANDE = 11077 AND REF_PRODUIT in ( 2, 3 ,6)

  8      RETURNING NO_COMMANDE, REF_PRODUIT, PRIX_UNITAIRE,   

  9                QUANTITE, REMISE         

 10      BULK COLLECT INTO v_det_comm;

 11      dbms_output.put_line( v_det_comm(1).NO_COMMANDE||' '||

 12          v_det_comm(1).REF_PRODUIT||' '|| v_det_comm(1).PRIX_UNITAIRE);

 13      dbms_output.put_line( v_det_comm(2).NO_COMMANDE||' '||                     

 14          v_det_comm(2).REF_PRODUIT||' '|| v_det_comm(2).PRIX_UNITAIRE);

 15      dbms_output.put_line( v_det_comm(3).NO_COMMANDE||' '||           

 16          v_det_comm(3).REF_PRODUIT||' '|| v_det_comm(3).PRIX_UNITAIRE);         

 17  end;   

 18  /   

11077 2 99,75

11077 3 52,5

11077 6 131,25

 

Procédure PL/SQL terminée avec succès.