IFT2821 Base de donn

1
IFT2821Base de donnéesChapitre 6SQL
2
Plan du cours

• Introduction
• Architecture
• Modèles de données
• Modèle relationnel
• Algèbre relationnelle
• SQL
• Conception
• Fonctions avancées
• Concepts avancées
• PL/SQL

3
Introduction

• Permet de retrouver et de manipuler les données
• Dérivé de SEQUEL 2 (76) lui-même dérivé de SQUARE
  (75)
• Proposé par IBM (82 puis 87)
• Première version normalisée SQL1 (ISO89)
• Deuxième version normalisée SQL2 (ISO92)
• SQL3 en cours de normalisation
• Sert de couche basse aux L4G (par exemple Access)

4
Langage SQL

• Partie LDD
• Conceptuel CREATE SCHEMA, TABLE,
• Externe CREATE VIEW, GRANT,
• Interne CREATE INDEX, CLUSTER,
• Partie LMD
• SELECT, INSERT, DELETE, UPDATE

5
Environnement SQL
6
Environnement SQL (utilisateur)

• Identificateur d utilisateur
• authorizationID
• Mécanisme dauthentification
• e.g. mot de passe
• Utilisateur possède privilèges
• Exemple
• CREATE user joe IDENTIFIED BY jpw
• CONNECT joe/jpw

7
Environnement SQL (Schéma)

• DATABASE catalogue
• une instance Oracle monte une DATABASE à la fois
• Nom du SCHEMA
• authorizationID du propriétaire (Oracle)
• CREATE SCHEMA AUTHORIZATION blair
• CONNECT balir/cmoi_at_blair AS sysDBA

8
Environnement SQL (interprétation de requêtes)

• Création d une table ou schéma
• Transmise à l interprète du LDD
• vérification
• création de la table
• schéma stocké dans dictionnaire de données
• allocation des structures physiques

9
Environnement SQL (Dictionnaire)

• BD relationnelle contient les méta-données
  d un CATALOG
• DEFINITION_SCHEMA
• tables
• INFORMATION_SCHEMA
• VIEWS sur les tables du DEFINITION_SCHEMA

10
Environnement SQL (Dictionnaire)

• SCHEMATA
• les SCHEMA créés par CURRENT_USER
• DOMAINS
• les DOMAIN accessibles par CURRENT_USER ou PUBLIC
• TABLES
• les noms des tables accessibles par CURRENT_USER
  ou PUBLIC
• VIEWS
• les vues accessibles par CURRENT_USER ou PUBLIC
• COLUMNS
• les colonnes des TABLE accessibles par
  CURRENT_USER ou PUBLIC
• TABLE_CONSTRAINTS
• contraintes des TABLE créées par CURRENT_USER
• CHECK_CONSTRAINTS
• contraintes CHECK des TABLE créées par
  CURRENT_USER
• TABLE_PRIVILEGES
• privilèges accordés par CURRENT_USER, à
  CURRENT_USER, ou à PUBLIC

11
Environnement SQL (Dictionnaire)
12
Type SQL

• Numérique exact
• INTEGER (ou INT) 2,3,5,..,299,..
• SMALLINT 2, 3, 459,
• NUMERIC(p, c) (ou DECIMAL(p, c) ou DEC(p, c))
• Nombre décimal avec p chiffres significatifs et c
  chiffres après le point
• Exemples 2.5, 456.342, 6

13
Type SQL

• Numérique approximatif
• REAL Point flottant
• Exemples 3.27E-4, 24E5
• DOUBLE PRECISION Point flottant à double
  précision
• Exemples 3.27265378426E-4, 24E12
• FLOAT(n) Point flottant (précision minimale est
  de n chiffres pour la mantisse)
• Exemples 3.27E-4, 24E5

14
Type SQL

• Chaîne de caractères
• CHARACTER(n) (ou CHAR(n)) Chaîne de caractère de
  taille fixe égale à n
• Exemples  Adresse', 'Paul LeGrand 
• CHARACTER VARYING (n) (ou VARCHAR(n)) Taille
  variable (max de n caractères)
• NATIONAL CHARACTER(n) Ensemble de caractères
  alternatif spécifique à l'implémentation
• NATIONAL CHARACTER VARYING(n) Taille variable

15
Type SQL

• Date et temps
• DATE année (quatre chiffres), mois (2 chiffres)
  et jour (2 chiffres)
• Exemple DATE '1998-08-25 
• ALTER SESSION SET NLS_DATE_FORMAT
   DD/MM/YYYY 
• TIME(p) heure (2 chiffres), minutes (2
  chiffres), secondes (2 p chiffres)
• Exemple TIME '140432.25 
• TIMESTAMP(p) DATE TIME
• Exemple TIMESTAMP '1998-08-25 140432.25 
• INTERVAL Représente un intervalle de temps
• Exemple  INTERVAL '2' DAY

16
Type SQL

• Booléen
• BIT (n) Vecteur de n bits.
• Exemples B'00100110', X'9F 
• BIT VARYING (n) taille variable (max n)
• Données de grande taille
• BINARY LARGE OBJECT (n) (BLOB(n)) n taille en
  octets (ex 1024, 5K, 3M, 2G)
• Exemple X 52CF4  (hexadecimal)
• CHARACTER LARGE OBJECT (n) (CLOB(n))

17
Type SQL (Oracle)

• NUMBER(p,c)
• numérique exact p entre 1 et 38, c doit être
  entre -84 et 127 (défaut, c 0)
• VARCHAR2(n) n 4000
• RAW(n)
• Binaire de taille n octets (n 2000)
• LONG(n)
• Chaîne de caractères de taille variable (n 2G).
  Maximum une colonne LONG par table
• LONG RAW(n)
• Binaire de taille variable (n 2G). Maximum une
  colonne de type LONG RAW par table

18
Type SQL (Oracle)

• ROWID identifiant de ligne composé de
• identificateur de fichier
• identificateur de bloc relatif au fichier
• identificateur de ligne relatif au bloc
• Conversions implicites

19
Définition des données

• Domaine
• Exemple
• CREATE DOMAIN SSS_TYPE AS CHAR(9) (sql2)
• CREATE TYPE COULEUR AS OBJECT (Oracle)
• nomCouleur CHAR(6) DEFAULT 'vert'
  CONSTRAINT COULEUR_VALIDE
  
  CHECK (VALUE IN
  'rouge', 'blanc', 'vert', 'bleu', 'noir')
• Destruction dun domaine
• DROP TYPE domaine RESTRICT CASCADE

20
Définition des données

• Définition dune table (1)
• CREATE TABLE lttable namegt
• (ltcolumn namegt ltcolumn typegt ltattribute
  constraintgt
• , ltcolumn namegt ltcolumn typegt ltattribute
  constraintsgt
• lttable constraintgt ,lttable constraintgt)
• Contraintes dintégrité sur une table
• NOT NULL
• UNIQUE ou PRIMARY KEY
• FOREIGN KEY
• REFERENCES
• CHECK

21
Définition des données

• Définition dune table (2)

22
Définition des données

• Exemple
• CREATE TABLE DEPARTMENT
• (DNAME VARCHAR(15) NOT NULL,
• DNUMBER INT,
• MGRSSN CHAR(9) DEFAULT '888665555',
• MGRSTARTDATE DATE,
• CONSTRAINT DEPTPK PRIMARY KEY(DNUMBER),
• CONSTRAINT DEPTSK UNIQUE (DNAME)
• CONSTRAINT DEPTMGRFK FOREIGN KEY(MGRSSN)
  REFERENCES EMPLOYE(SSN) ON DELETE CASCADE)

23
Définition des données

• Table
• Avec contraintes d intégrité

Exemple (SQL 86) CREATE TABLE COMMANDE ( NC
NUMBER UNIQUE NOT NULL, NV NUMBER NOT
NULL QUANTITE NUMBER(6))
Exemple (SQL 89) CREATE TABLE COMMANDE ( NC
NUMBER PRIMARY KEY, NV NUMBER NOT NULL
REFERENCES VIN, QUANTITE NUMBER(6)
CHECK(QUANTITE gt 0)) -----------------------------
--------------------------------------------------
------------------- NC NUMBER, PRIMARY KEY
(NC), NV NUMBER NOT NULL, FOREIGN KEY (NV)
REFERENCES VIN,
SQL 92
24
Définition des données

• Modification du schéma dune table (1)
• ALTER TABLE lttablegt ADD ltattributgt lttypegt,
  ltattributgt lttypegt, ...
• Exemple
• ALTER TABLE DEPARTEMENT ADD DATE_CREATION DATE
• ALTER TABLE EMPLOYE DROP ADDRESS CASCADE
• ALTER TABLE EMPLOYE DROP CONSTRAINT EMPSUPERFK
  CASCADE
• Suppression dune table
• DROP TABLE lttablegt
• Exemple
• DROP TABLE EMPLOYE
• DROP TABLE DEPENDENT CASCADE

25
Définition des données

• Modification du schéma dune table (2)

ALTER TABLE nomTable ADD COLUMN
spécificationColonne DROP COLUMN nomColonne
RESTRICTCASCADE ADD spécificationContrainte D
ROP nomContrainte RESTRICTCASCADE ALTER
nomColonne SET DEFAULT valeurDéfaut ALTER
nomColonne DROP DEFAULT
26
Accès aux données

• Clause SELECT
• SELECT ltattributsgt
• FROM ltliste de tablesgt
• WHERE ltconditiongt
• GROUP BY ltgroupe(s) attribut(s) gt
• HAVING ltcondition de groupegt
• ORDER BY ltliste attributsgt
• Exemple
• SELECT BDATE, ADRESS
• FROM EMPLOYE
• WHERE FNAME'Jhon' AND LNAME'Smith'
• BDATE ADRESS
• --------- --------------------------------------
  -----------
• 01-SEP-65 731Fondren Huston, TX

27
Accès aux données

• Exemple
• Donner les noms et adresses des employés?
• SELECT BDATE, ADRESS
• FROM EMPLOYE
• BDATE ADRESS
• --------- --------------------------------------
  ----------
• 10-OCT-37 450 Stone, Huston, TX
• 08-DEC-55 638 Voss, Huston , TX
• 20-JUN-41 291 Berry, Bellaire, TX
• 15-SEP-62 975FireOak, Humble,TX
• 31-JUL-72 5631Rice, Humble, TX
• 19-JUL-68 3321 Castle,Spring, TX
• 29-MAR-69 980, Dallas, Huston, TX
• 01-SEP-65 731Fondren Huston, TX

28
Accès aux données

• Clause Where non spécifiée
• Que fait la requête suivante ?
• SELECT
• FROM EMPLOYE, DEPARTMENT
• Clause Where mal spécifiée !!!
• Que fait la requête suivante ?
• SELECT LNAME, FNAME, DNUMDER
• FROM EMPLOYE, DEPARTMENT
• WHERE DNUMBER 5

Produit cartésien des tables EMPLOYE et DEPARTMENT
? ? ? ? ? ? ? ?
29
Accès aux données

• Exemple
• Donner les noms et adresses des employés du
  département de recherche ?
• SELECT FNAME, LNAME, ADRESS
• FROM EMPLOYE, DEPARTMENT
• WHERE DNAME'Research' AND DNUMBERDNO
• FNAME LNAME ADRESS
• --------------- ---------------
  ------------------------------
• Franklin Wong 638 Voss,
  Huston , TX
• Ramesh Narayan 975FireOak,
  Humble,TX
• Joyce English 5631Rice,
  Humble, TX
• Jhon Smith 731Fondren
  Huston, TX

30
Accès aux données

• Attributs ambiguës
• Exemple
• Donner les noms et adresses des employés qui
  travaillent au département de recherche ?
  (Supposons que lattribut DNO de la table EMPLOYE
  sappelle DNUMBER)
• SELECT FNAME, EMPLOYE.LNAME, ADRESS
• FROM EMPLOYE, DEPARTMENT
• WHERE DEPARTMENT.DNAME 'Research' AND
  DEPARTMENT.DNUMBEREMPLOYE.DNUMBER

31
Accès aux données

• Attributs ambiguës
• Exemple
• Donner le nom et prénom de chaque employé ainsi
  que le nom et le prénom de son superviseur
  immédiat ?
• SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME
• FROM EMPLOYE E, EMPLOYE S
• WHERE E.SUPERSSNS.SSN
• FNAME LNAME FNAME
  LNAME
• --------------- --------------- ---------------
  ---------------
• Franklin Wong James
  Borg
• Jennifer Wallace James
  Borg
• Ramesh Narayan Franklin
  Wong
• Joyce English Franklin
  Wong
• Alicia Zelaya Jennifer
  Wallace
• Ahmad Jabbar Jennifer
  Wallace
• Jhon Smith Franklin
  Wong

32
Accès aux données

• Tables Ensembles
• Duplication des uplets
• Duplication permise des uplets
• SELECT ALL SALARY
• FROM EMPLOYE
• Duplication non permise des uplets
• SELECT DISTINCT SALARY
• FROM EMPLOYE

SALARY ---------- 55000 40000
43000 38000 25000 25000
25000 30000
SALARY --------- 25000 30000 38000
40000 43000 55000
33
Accès aux données

• Tables Ensembles
• Union Donner la liste des projets dont Smith
  est implique comme employé ou comme directeur du
  département qui contrôle ces projets ?
• (SELECT DISTINCT PNUMBER
• FROM PROJECT, DEPARTMENT, EMPLOYE
• WHERE DNUM DNUMBER AND MGRSSN SSN AND
  LNAME 'Smith')
• UNION
• (SELECT DISTINCT PNUMBER
• FROM PROJECT, WORKS_ON, EMPLOYE
• WHERE PNUMBER PNO AND ESSN SSN AND LNAME
  'Smith')

PNUMBER ------------- 1 2
34
Accès aux données

• Fonctions de calcul
• SUM, AVG, MAX, MIN
• SELECT SUM (SALARY), MAX (SALARY), MIN (SALARY),
  
• AVG (SALARY)
• FROM EMPLOYE
• SUM(SALARY) MAX(SALARY) MIN(SALARY)
  AVG(SALARY)
• ------------------- ------------------- --------
  ------------ --------------------
• 281000 55000 25000
  35125
• Quel est le nombre des employés ?
• SELECT COUNT ()
• FROM EMPLOYE

COUNT() ---------- 8
35
Accès aux données

• Fonctions de calcul
• Que fait la requête suivante ?
• SELECT LNAME, FNAME
• FROM EMPLOYE
• WHERE (SELECT COUNT ()
• FROM DEPENDENT
• WHERE SSNESSN) gt 2

Retourne nom et prénom des employés qui ont deux
dépendants et plus. LNAME
FNAME --------------- --------------- Smith
Jhon
36
Accès aux données

• Fonctions dagrégation
• GROUP BY, HAVING
• Pour chaque projet retrouver son nom, son numéro
  et le nombre des employés qui y ont participé ?
• SELECT PNUMBER, PNAME, COUNT ()
• FROM PROJECT, WORKS_ON
• WHERE PNUMBERPNO
• GROUP BY PNUMBER, PNAME

PNUMBER PNAME COUNT() ---------------
----------- ---------- 1
ProductX 2 2
ProductY 3 3
ProductZ 2 10
Computerization 3 20
Reorganization 3 30
Newbenefits 3
37
Accès aux données

• Fonctions dagrégation
• GROUP BY, HAVING
• Pour chaque projet qui implique plus de deux
  employés retrouver son nom, son numéro et le
  nombre demployés qui y participent ?
• SELECT PNUMBER, PNAME, COUNT ()
• FROM PROJECT, WORKS_ON
• WHERE PNUMBER PNO
• GROUP BY PNUMBER, PNAME
• HAVING COUNT () gt 2

PNUMBER PNAME COUNT() ---------------
----------- ---------- 2
ProductY 3 10
Computerization 3 20
Reorganization 3 30
Newbenefits 3
38
Accès aux données

• Chaînes de caractères, opérateurs arithmétiques
  et ordonnancement
• LIKE, BETWEEN, ORDER BY
• Retrouver tous les employés qui ont une adresse
  a Huston, Texas?
• SELECT FNAME, LNAME
• FROM EMPLOYE
• WHERE ADRESS LIKE 'Huston, TX'
• Retrouver tous les employés qui sont nés durant
  les années
• 1950 ?
• SELECT FNAME, LNAME
• FROM EMPLOYE
• WHERE BDATE LIKE'________5_'

39
Accès aux données

• Chaînes de caractères, opérateurs arithmétiques
  et ordonnancement
• Retrouver le salaire de chaque employé si on
  augmente de 10 les employés qui ont participé
  dans le projet ProjectX?
• SELECT FNAME, LNAME, 1.1SALARY
• FROM EMPLOYE, WORKS_ON, PROJECT
• WHERE SSNESSN AND PNOPNUMBER AND
  PNAME'ProjectX'
• Que fait la requête suivante ?
• SELECT
• FROM EMPLOYE
• WHERE (SALARY BETWEEN 30000 AND 40000) AND DNO
  5

FNAME LNAME
1.1SALARY --------------- ---------------
---------- Jhon Smith
33000 Joyce English
27500
40
Accès aux données

• Chaînes de caractères, opérateurs arithmétiques
  et ordonnancement
• Retrouver la liste des employés qui sont
  impliqués dans des projets Cette liste doit
  triée par département (descendant), nom et
  prénom(ascendant).
• SELECT DNAME, LNAME, FNAME, PNAME
• FROM DEPARTMENT, EMPLOYE, WORKS_ON, PROJECT
• WHERE DNUMBERDNO AND SSNESSN AND PNOPNUMBER
  
• ORDER BY DNAME DESC, LNAME ASC, FNAME ASC

41
Accès aux données

• Chaînes de caractères, opérateurs arithmétiques
  et ordonnancement

DNAME LNAME FNAME
PNAME --------------- ---------------
--------------- --------------- Research
English Joyce
ProductX Research English Joyce
ProductY Research Narayan
Ramesh ProductZ Research Smith
Jhon ProductX Research
Smith Jhon
ProductY Research Wong
Franklin ProductY Research Wong
Franklin ProductZ Research
Wong Franklin Computerization Rese
arch Wong Franklin
Reorganization Headquarters Borg
James Reorganization Administration
Jabbar Ahmad Computerization Admi
nistration Jabbar Ahmad
Newbenefits Administration Wallace
Jennifer Newbenefits Administration
Wallace Jennifer Reorganization Admin
istration Zelaya Alicia
Newbenefits Administration Zelaya
Alicia Computerization
42
Accès aux données

• Sous requêtes et comparaison densembles
• IN, SOME, ANY, ALL, EXIST, UNIQUE, NOT
• Retrouver le NAS des employé qui ont travaillé
  dans les mêmes projets et les mêmes nombres
  d heures que John Smith (NAS 123456789) ?
• SELECT DISTINCT ESSN
• FROM WORKS_ON
• WHERE (PNO, HOURS) IN
• (SELECT PNO, HOURS FROM WORKS_ON WHERE
  ESSN'123456789')
• Quobtient-on si on remplacer IN par SOME puis
  par ANY ?

43
Accès aux données

• Sous requêtes et comparaison densembles
• (IN, NOT) SOME, ANY, ALL, (EXIST, NOT)
• Retrouver les noms des employé qui gagnent un
  salaire supérieur a tous ceux qui travaillent
  dans le département de recherche?
• SELECT LNAME, FNAME
• FROM EMPLOYEE
• WHERE SALARY gt ALL
• (SELECT SALARY FROM EMPLOYEE WHERE DNO5)

LNAME FNAME ---------------
--------------- Borg James Wallace
Jennifer
44
Accès aux données

• Sous requêtes et comparaison densembles
• IN, SOME, ANY, ALL, EXIST, NOT
• Retrouver les noms des employé qui nont pas de
  dépendant?
• SELECT FNAME, LNAME
• FROM EMPLOYE
• WHERE NOT EXISTS
• (SELECT
• FROM DEPENDENT, EMPLOYE
• WHERE SSNESSN)
• Retrouver les noms des managers qui ont plus
  dun dépendant?

FNAME LNAME ---------------
-------- James Borg Ramesh
Narayan Joyce English Alicia
Zelaya Ahmad Jabbar
45
Accès aux données

• Jointure de tables
• JOIN, NATURAL JOIN
• Retrouver les noms et les adresses des employé
  qui travaillent dans le département de recherche
  ?
• SELECT FNAME, LNAME, ADDRESS
• FROM (EMPLOYE JOIN DEPARTMENT ON DNODNUMBER)
• WHERE NAMEResearch
• SELECT FNAME, LNAME, ADDRESS
• FROM (EMPLOYE NATURAL JOIN (DEPARTMENT AS DEPT
  (DNAME, DNO, MSSN, MSDATE)))
• WHERE DNAMEResearch

46
Manipulation des données

• Insertion
• INSERT INTO lttablegt (ltliste_colonnesgt)
• VALUES (ltliste_valeursgt) ltrequetegt
• INSERT INTO EMPLOYEE
• VALUES ('Richard','Marini', '653298653',
  '30-dec-1962','98 Oak
  Forest,Katy,TX','M', 37000, '987654321', 4)
• INSERT INTO EMPLOYE (FNAME, LNAME, DNO, SSN)
• VALUES ('Richard', 'Marini', 4, '653298653')
• INSERT INTO DEPTS_INFO (DEPT_NAME, NO_OF_EMPS,
  TOTAL_SAL)
• SELECT DNAME, COUNT (), SUM (SALARY)
• FROM (DEPARTMENT JOIN EMPLOYE ON DNUMBERDNO)
• GROUP BY DNAME

47
Manipulation des données

• Mise à jour
• UPDATE lttablegt
• SET ltnom_colonnegt ltexpressiongt
• WHERE ltconditiongt
• UPDATE PROJECT
• SET PLOCATION Bellaire, DNUM 5
• WHERE PNUMBER10
• UPDATE EMPLOYE SET SALARY SALARY 1.1
• WHERE DNO IN
• (SELECT DNUMBER
• FROM DEPARTMENT
• WHERE DNAME 'Research')

48
Manipulation des données

• Suppression
• DELETE FROM lttablegt
• WHERE ltconditiongt
• DELETE FROM EMPLOYE
• WHERE SSN123456789
• DELETE FROM EMPLOYE
• WHERE DNO IN
• (SELECT DNUMBER
• FROM DEPARTMENT
• WHERE DNAME'Research')
• Que fait la requête suivante ?
• DELETE FROM EMPLOYE

49
Opérations ensemblistes

• Différence
• Les employés qui n ont pas de dépendants.
• select ssn as Nass from employe
• minus
• select essn from dependent
• Nass
• ---------
• 453453453
• 666884444
• 888665555
• 987987987
• 999887777
• - Que fait la requête suivante ?
• select essn from dependent
• minus
• select ssn as Nass from employe

50
Opérations ensemblistes

• Différence
• Les noms et prénoms des employés qui nont pas de
  dépendants.
• select lname as prenom
• from employe
• where ssn in
• (select ssn as Nass from employe
• minus
• select essn from dependent)
• ou
• select lname as prenom
• from employe, (select ssn as Nass from employe
• minus
• select essn from
  dependent)
• where ssn Nass

PRENOM ----------- English Narayan
51
Opérations ensemblistes

• Intersection
• Les employés qui ont des dépendants.
• select ssn as Nass from employe
• intersect
• select essn from dependent
• Nass
• ---------
• 123456789
• 334455559
• 87654321

52
Opérations dérivées

• Division (1)
• Exemple quel sont les employées qui ont
  participé au mêmes projets que  John Smith 

?

53
Opérations ensemblistes

• Division (2)
• select fname as Prenom, lname as Nom
• from employe e
• where not exists
• (select pno from works_on
• where essn '123456789 
• minus
• select pno
• from works_on where essn e.ssn)
• PRENOM NOM
• --------------- ---------------
• Joyce English
• Jhon Smith

54
Opérations ensemblistes

• Division (2 Ayoye !!!)
• select fname as Prenom, lname as Nom
• from employe
• where not exists
• (select pno
• from works_on w1
• where essn '123456789 
• and not exists
• (select pno
• from works_on w2
• where essn ssn and w1.pno w2.pno))

55
SQL intégré

• Introduction
• SQL peut être intégré dans un langage hôte (C,
  COBOL, PL/1, PASCAL, JAVA, etc.)
• Étude du cas du langage C

56
SQL intégré

• Principes
• Tout instruction SQL commence par lexpression
  EXEC SQL pour la distinguer des autres
  instructions du langage hôte
• Différents types dinstructions
• déclarations
• connexion
• traitement

57
SQL intégré

• Déclarations (variables de communication)
• Elle se fait dans la DECLARE SECTION. Celle ci
  commence par l'ordre
• EXEC SQL BEGIN DECLARE SECTION
• et se termine par
• EXEC SQL END DECLARE SECTION
• Exemple
• EXEC SQL BEGIN DECLARE SECTION
• int pempno
• char pname11
• int pdeptno
• EXEC SQL END DECLARE SECTION

58
SQL intégré

• Déclarations (variables de communication)
• Utilisation dans SQL
• EXEC SQL SELECT DEPTO, ENAME
• INTO pdeptno, pname FROM EMP
• WHERE EMPNO pempno
• Variables précédées de "" pour les distinguer
  des noms des attributs
• Utilisation dans C
• strcpy(pname,"Martin")
• Les types possibles pour ces variables sont ceux
  compatibles avec ORACLE (entiers, réels, chaînes
  de caractères)

59
SQL intégré

• Connexion
• La connexion à une base ORACLE se fait par
  l'ordre SQL
• EXEC SQL CONNECT username IDENTIFIED BY
  password
• username et password sont des variables déclarées
  dans la section déclaration
• Exemple
• EXEC SQL BEGIN DECLARE SECTION
• VARCHAR username20
• VARCHAR password20
• EXEC SQL END DECLARE SECTION
• EXEC SQL INCLUDE sqlca.h

60
SQL intégré

• Connexion
• Exemple (suite)
• main()
• strcpy(username,"login_oracle")
• / Copie du username/
• strcpy(password,"motdepasse_oracle")
• / Copie du mot de passe /
• EXEC SQL CONNECT username IDENTIFIED BY
  password

61
SQL intégré

• Traitements
• Mise à jour
• "mettre à jour le salaire dans la relation
  employé"
• EXEC SQL UPDATE EMP
• SET SAL salaire
• WHERE EMPNO301
• Suppression
• EXEC SQL DELETE FROM EMP WHERE EMPNO empno
• Création d une table
• EXEC SQL CREATE TABLE EMP_TEST
• (EMPNO NUMBER, ENAME CHAR(15), JOB CHAR(10))

62
SQL intégré

• Traitements
• Sélection, cas du INTO
• S applique quand le SELECT retourne un seul
  n-uplet
• EXEC SQL SELECT job, sal
• INTO fonction, salaire
• FROM EMP
• WHERE empno301
• Sélection, utilisation dun curseur
• S applique quand le SELECT retourne un ensemble
  de n-uplets
• Un curseur est une structure de données contenant
  tous les n-uplets retournés par la commande
  SELECT
• Cette structure se manipule comme un fichier
  séquentiel

63
SQL intégré

• Traitements
• Association du curseur à un SELECT
• EXEC SQL DECLARE C CURSOR FOR
• SELECT job, salaire
• FROM EMP
• Ouverture du curseur
• EXEC SQL OPEN C
• A l ouverture, le premier n-uplet est pointé
• Fermeture du curseur
• EXEC SQL CLOSE C

64
SQL intégré

• Traitements
• Accès aux autres n-uplets de manière séquentielle
• Se fait par l instruction FETCH
• EXEC SQL FETCH C INTO fonction, salaire
• On ne peut pas reculer dans le curseur
• Pour accéder de nouveau aux n-uplets, il faut
  fermer et rouvrir le curseur

65
SQL intégré

• Commandes dynamiques
• Il est possible dexécuter des commandes SQL
  inconnues au moment de lécriture du programme
• Il existe pour cela quatre méthode
• Commandes sauf SELECT sans variables (EXECUTE
  IMMEDIATE)
• Commandes sauf SELECT avec un nombre de variables
  fixe (PREPARE, EXECUTE)
• Commandes avec un nombre de variables variable
  (PREPARE, DECLARE, OPEN, FETCH)
• SELECT complètement dynamique

66
SQL intégré

• Commandes dynamiques
• Commandes sans variables
• EXEC SQL EXECUTE IMMEDIATE modif
• Commandes avec un nombre de variables fixe
• EXEC SQL PREPARE S1 FROM chaîne
• EXEC SQL EXECUTE S1 USING variable1,variable2,
• ...

67
SQL intégré

• Directives de traitement d'erreur
• EXEC SQL WHENEVER SQLERROR / SQLWARNING / NOT
  FOUND STOP / CONTINUE / GO TO étiquette
• Ces directives correspondent donc à 3 événements
  ORACLE
• SQLERROR erreur ORACLE
• SQLWARNING "warning" ORACLE
• NOT FOUND curseur vide ou fini

68
SQL intégré

• Directives de traitement d'erreur
• EXEC SQL WHENEVER SQLERROR / SQLWARNING / NOT
  FOUND STOP / CONTINUE / GO TO étiquette
• Les actions possibles sont
• STOP le programme se termine et la transaction
  est  abortée ,
• CONTINUE le programme continue en séquence,
• GO TO le programme se branche à l'adresse
  indiquée.

69
SQL intégré

• Directives de traitement d'erreur
• La portée d'une directive WHENEVER va jusqu'à la
  directive WHENEVER suivante (ou la fin de
  programme) dans l'ordre du texte source PROC (et
  non pas dans l'ordre d'exécution).
• L'erreur classique dans la manipulation des SQL
  WHENEVER est la suivante

70
SQL intégré

• routine a
• ...
• EXEC SQL WHENEVER ERROR GOTO toto
• ...
• toto ...
• routine b
• ...
• EXEC SQL INSERT ...
• / donc rien est dit dans b pour SQL WHENEVER
  /
• Par conséquent, au sein de la routine b, on garde
  les dernières directives rencontrées, donc celles
  de la routine a. Or l'étiquette toto est locale à
  a et donc inconnue dans b.

71
SQL intégré

• Solutions
• par exemple avoir systématiquement des étiquettes
  globales
• définir localement dans chaque routine les
  directives d'erreurs.

72
SQL intégré

• Gestion des transactions
• Une transaction est assimilée à une exécution
  d'un programme. Le début de transaction est
  implicite (c'est le début de programme), et la
  fin est soit implicite
• (erreur non récupérée par un WHENEVER
  annulation, ou fin de programme validation)
• soit explicite. Les ordres de fin de transaction
  explicites sont

73
SQL intégré

• Gestion des transactions
• EXEC SQL COMMIT WORK RELEASE
• Valide les mises à jour. L'option RELEASE
  désalloue toutes les ressources ORACLE et réalise
  la déconnexion de la base
• EXEC SQL ROLLBACK WORK RELEASE
• Annule les mises à jour

74
SQL intégré

• Gestion des transactions
• Pour éviter les problèmes de conflit entre le
  ROLLBACK et les directives WHENEVER, il est
  prudent d'utiliser le ROLLBACK comme suit
• EXEC SQL WHENEVER SQLERROR CONTINUE
• EXEC SQL WHENEVER SQLWARNING CONTINUE
• EXEC SQL ROLLBACK WORK