Le langage SQL avancé - PDF

Description
Chapitre 9 9 Le langage SQL avancé Ce document reprend les requêtes SQL du chapitre 9 de l ouvrage Bases de données - Concepts, utilisation et développement. 9.1 INTRODUCTION 9.2 LE CONTRÔLE D ACCÈS grant

Please download to get full document.

View again

of 18
All materials on our website are shared by users. If you have any questions about copyright issues, please report us to resolve them. We are always happy to assist you.
Information
Category:

Home & Garden

Publish on:

Views: 147 | Pages: 18

Extension: PDF | Download: 0

Share
Transcript
Chapitre 9 9 Le langage SQL avancé Ce document reprend les requêtes SQL du chapitre 9 de l ouvrage Bases de données - Concepts, utilisation et développement. 9.1 INTRODUCTION 9.2 LE CONTRÔLE D ACCÈS grant select, update(qstock,prix) on PRODUIT to P_MERCIER, S_FINANCIERS; grant all privileges on CLIENT to P_MERCIER, S_FINANCIERS with grant option; grant select on COM_COMPLETE to public; grant run on SUP_DETAIL to public; grant run on COMPTA01 to S_FINANCIERS Chapitre 9 Le langage SQL avancé with grant option; revoke update(prix) on PRODUIT from P_MERCIER; revoke run on COMPTA01 from P_MERCIER; revoke grant option for update (COMPTE) on CLIENT from P_MERCIER; a) Les rôles create role CONSULTANT; grant select on CLIENT to CONSULTANT; grant update (ADRESSE, LOCALITE) on CLIENT to CONSULTANT; grant CONSULTANT to P_MERCIER; revoke select (LOCALITE) from CONSULTANT; revoke CONSULTANT from P_MERCIER; drop role CONSULTANT; 9.3 LES VUES SQL Principes et objectifs des vues Définition et utilisation d une vue create view COM_COMPLETE(NCOM,NCLI,NOMCLI,LOC,DATECOM) as select NCOM,COM.NCLI,NOM,LOCALITE,DATECOM CLI,COMMANDE COM where COM.NCLI = CLI.NCLI; select NOMCLI,NCOM,DATECOM from COM_COMPLETE where LOC = 'Toulouse'; select NPRO from DETAIL where NCOM in ( select NCOM from COM_COMPLETE where LOC = 'Toulouse'); select LOC, count(*) from COM_COMPLETE CC, DETAIL D where CC.NCOM = D.NCOM group by LOC; 9.3 Les vues SQL select NOM,NCOM,DATECOM CLI,COMMANDE COM where COM.NCLI = CLI.NCLI and LOC = 'Toulouse'; drop view COM_COMPLETE; Les vues comme interface pour des besoins particuliers create view HABITUDE_ACHAT(LOCALITE,NPRO,VOLUME) as select LOCALITE,P.NPRO,sum(QCOM*PRIX) CLI,COMMANDE COM,DETAIL D,PRODUIT P where COM.NCLI = CLI.NCLI and D.NCOM = COM.NCOM and P.NPRO = D.NPRO group by LOCALITE, P.NPRO; Les vues comme mécanisme de contrôle d accès create view ANALYSE(LOCALITE, CAT, DATE, NPRO, QCOM) as select LOCALITE, CAT, DATECOM, NPRO, QCOM C, COMMANDE M, DETAIL D where C.NCLI = M.NCLI and M.NCOM = D.NCOM; Les vues comme mécanisme d évolution de la base de données Les vues comme aide à l expression de requêtes complexes create view VAL_STOCK(STOCK,VALEUR) as select P.NPRO, (QSTOCK - sum(d.qcom))*prix from DETAIL D, PRODUIT P where D.NPRO = P.NPRO group by P.NPRO, QSTOCK, PRIX; 1 select sum(valeur) from VAL_STOCK; Dunod La photocopie non autorisée est un délit Mise à jour des données via une vue create view CLI(NCLI,NOM,ADRESSE,LOCALITE,CAT,COMPTE) as select * where CAT is null or CAT in ('B1','B2','C1','C2') with check option; insert into CLI values ('B313','DURAND','place Monge','Mons','D7',0); 1. Pourquoi QSTOCK et PRIX? Chapitre 9 Le langage SQL avancé 9.4 EXTENSION DE LA STRUCTURE DES REQUÊTES SFW Extension de la clause select select NCOM, (select sum(qcom*prix) from DETAIL D, PRODUIT P where D.NPRO = P.NPRO and D.NCOM = M.NCOM) as MONTANT from COMMANDE M where MONTANT 1000; Extension de la clause from select NCLI, NOM from ((select NCLI, NOM, LOCALITE ) except (select NCLI, NOM, LOCALITE from BON_CLIENT) union (select NCLI, NOM, LOCALITE from PROSPECT)) where LOCALITE = 'Poitiers'; select avg(montant) from (select NCOM, sum(qcom*prix) as MONTANT from DETAIL D, PRODUIT P where D.NPRO = P.NPRO group by NCOM); select NPRO, TOTAL_QTE from ( (select NPRO, sum(qcom) from PRODUIT P, DETAIL D where P.NPRO=D.NPRO group by NPRO) union (select NPRO, 0 from PRODUIT where NPRO not in (select NPRO from DETAIL)) ) as DP(NPRO,TOTAL_QTE) where TOTAL_QTE 1000; select * cross join COMMANDE where... select *, COMMANDE where... select * natural join COMMANDE where... select * 9.4 Extension de la structure des requêtes SFW C, COMMANDE M where C.NCLI = M.NCLI and... select * C join COMMANDE M on (C.NCLI = M.NCLI) where... select * C join COMMANDE M on (C.NCLI = M.NCLI) join DETAIL D on (M.NCOM = D.NCOM) join PRODUIT P on (D.NPRO = P.NPRO) where... select * C,COMMANDE M, DETAIL D,PRODUIT P where C.NCLI = M.NCLI and M.NCOM = D.NCOM and D.NPRO = P.NPRO; select * join COMMANDE using (NCLI) where... from CLIENT inner join COMMANDE using (NCLI) select NCOM, C.NCLI, DATECOM, NOM, LOCALITE from COMMANDE M, CLIENT C where M.NCLI = C.NCLI union select null, NCLI, null, NOM, ADRESSE where NCLI not in (select NCLI from COMMANDE); select NCOM, C.NCLI, DATECOM, NOM, LOCALITE from COMMANDE M right outer join CLIENT C on (M.NCLI = C.NCLI); Dunod La photocopie non autorisée est un délit. select NCOM, C.NCLI, DATECOM, NOM, LOCALITE from COMMANDE M, CLIENT C where M.NCLI = (+) C.NCLI; select NCLI,NOM where NCLI not in (select NCLI from COMMANDE); select NCLI,NOM C where not exists (select * from COMMANDE where NCLI = C.NCLI); Chapitre 9 Le langage SQL avancé select C.NCLI,NOM C left outer join COMMANDE M on (C.NCLI = M.NCLI) where M.NCOM is null; 9.5 LES REQUÊTES RÉCURSIVES with ORGAN (NIVEAU, NPERS, NOM, RESP) as ( -- Initialisation (E0) select 1, NPERS, NOM, RESPONSABLE from PERSONNE where NOM = 'p4' union all -- Incrémentation (Ei) select O.NIVEAU + 1, P.NPERS, P.NOM, P.RESPONSABLE from ORGAN O, PERSONNE P where P.RESPONSABLE = O.NPERS ) -- Elaboration du résultat select NIVEAU, NPERS, NOM, RESP from ORGAN; 9.6 LES EXTENSIONS OBJET DE SQL Types de données complexes (row et array) create table CLIENT( NCLI char(10) not null primary key, NOM char(32) not null, ADRESSE row(rue char(30), LOCALITE char(60)), CAT char(2)); select NCLI, NOM, ADRESSE.RUE where ADRESSE.LOCALITE = 'Poitiers'; create table CLIENT2( NCLI char(10) not null primary key, NOM char(32) not null, PRENOM char(15) array(4), ADRESSES row(rue char(30), LOCALITE char(60)) array(2), CAT char(2)); select NCLI, NOM, PRENOM[1], PRENOM[2], ADRESSES[1].RUE 2 where ADRESSES[1].LOCALITE = 'Poitiers'; 9.6 Les extensions objet de SQL Type défini par l utilisateur (TDU) create type Chaine as varchar(60) default '?'; create type Contact as (RUE Chaine, LOCALITE Chaine); create table CLIENT( NCLI char(10) not null primary key, NOM Chaine not null, ADRESSE Contact, CAT char(2)); Table typée create type TPERSONNE as ( NCLI char(10), NOM Chaine, ADRESSE Contact, LOCALITE char(2)); create table CLIENT of TPERSONNE; create table PROSPECT of TPERSONNE; Hiérarchie de types create type TPERSONNE as ( NCLI char(10), NOM Chaine, ADRESSE Contact, LOCALITE char(2)) REF is system generated; create type TCLIENT under TPERSONNE as ( CAT char(2), COMPTE decimal (9,2)); Hiérarchie de tables typées Dunod La photocopie non autorisée est un délit. create table PERSONNE of TPERSONNE (REF is IdP system generated, NCLI with options not null, NOM with options not null, ADRESSE with options not null, LOCALITE with options not null, primary key (NCLI)); create table CLIENT of TCLIENT under PERSONNE (COMPTE with options not null default 0 check(compte = 0)); select NCLI, NOM, ADRESSE Chapitre 9 Le langage SQL avancé from PERSONNE where LOCALITE = 'Poitiers'; from only(personne) Références entre tables create table COMMANDE ( NCOM char(10) not null primary key, DATECOM date not null, REFCLI REF(TCLIENT) scope CLIENT not null); select NCOM, DATECOM, REFCLI- NCLI, REFCLI- NOM from COMMANDE where REFCLI- LOCALITE = 'Poitiers'; 9.7 LES PRÉDICATS (check) create table CLIENT ( NCLI...,..., CAT char(2), primary key (NCLI), check (CAT is null or CAT in ('B1','B2','C1','C2')); alter table CLIENT add check (CAT is null or CAT in ('B1','B2','C1','C2')); alter table CLIENT add constraint CHK_CAT check (CAT is null or CAT in ('B1','B2','C1','C2')); alter table COMMANDE add check ((DATECOM = (select max(datecom) from COMMANDE) and DATECOM = CURRENT_DATE) is not false); alter table COMMANDE add check (NCLI in (select NCLI )); alter table CLIENT drop constraint CHK_CAT; CAT char(2) check(cat is null or CAT in ('B1','B2','C1','C2')) create domain MONTANT integer check(value = 0); 9.8 Les procédures SQL (stored procedures) 9.8 LES PROCÉDURES SQL (stored procedures) create procedure SUP_DETAIL (in COM char(12), in PRO char(15)) begin delete from DETAIL where NCOM = :COM and NPRO = :PRO; if (select count(*) from DETAIL where NCOM=:COM) = 0 then delete from COMMANDE where NCOM = :COM end if; end; call SUP_DETAIL('30182','PA60'); 9.9 LES DÉCLENCHEURS (Triggers) create trigger SUP_COM after update of CAT on CLIENT for each row when (new.compte 0) begin if (old.cat is not null and new.cat is null) then delete from COMMANDE where NCLI = old.ncli; end if; end; before insert on T for each row when not C begin abort(); end; Dunod La photocopie non autorisée est un délit LE CATALOGUE select CNAME, CTYPE, LEN1, NULLS from SYS_COLUMN where TNAME = 'DETAIL'; select TNAME from SYS_TABLE where TNAME in (select TNAME from SYS_COLUMN where CNAME like 'NCOM%') Chapitre 9 Le langage SQL avancé and TTYPE = 'R'; select distinct CREATOR from SYS_TABLE where TNAME in (select TNAME from SYS_KEY where KEYID in (select KTARG from SYS_KEY where TNAME = 'DETAIL')); 9.11 LES INTERFACES SQL DES PROGRAMMES D APPLICATION select NOM, LOCALITE where NCLI = 'C400'; L interface SQL statique classique select NOM, LOCALITE into :varnom, :varloc where NCLI = :varncli; exec SQL begin declare section; exec SQL end declare section; exec SQL include SQLCA; exec SQL begin declare section; varnom char(32); varloc char(30); varncli char(10); exec SQL end declare section; exec SQL include SQLCA; read varncli; exec SQL select NOM,LOCALITE into :varnom,:varloc where NCLI = :varncli; write varnom, varloc; exec SQL declare CURCLI cursor for select NCLI, NOM where LOCALITE = :varloc; 9.11 Les interfaces SQL des programmes d application varloc := Toulouse ; exec SQL open CURCLI; exec SQL fetch CURCLI into :varncli,:varnom; while SQLCODE = 0 do traiter les valeurs de varncli et varnom exec SQL fetch CURCLI into :varncli,:varnom; endwhile; exec SQL close CURCLI; exec SQL declare CURCLI cursor for select NCLI, NOM where LOCALITE = :varloc; varloc := Toulouse ; exec SQL open CURCLI; exec SQL fetch CURCLI into :varncli,:varnom; while SQLCODE = 0 do if STAT 0 then exec SQL update CLIENT set CAT = 'A1' where current of CURCLI; endif exec SQL fetch CURCLI into :varncli,:varnom; endwhile; exec SQL close CURCLI; SQLJ : une interface statique pour Java String varncli, varnom, varloc; varncli = C400 ; #sql{select NOM, LOCALITE into :varnom, :varloc where NCLI = :varncli }; traiter les valeurs de varnom et varloc Dunod La photocopie non autorisée est un délit. #sql iterator IterateurClient(String NCLI, String NOM); [1] IterateurClient CurCli; [2] String varloc = Poitiers ; #sql CurCli = {select NCLI, NOM where LOCALITE = :varloc}; [3] while (CurCli.next()) { [4] varncli = CurCli.NCLI(); [5] varnom = CurCli.NOM(); [5] traiter les valeurs de varncli et varnom } Chapitre 9 Le langage SQL avancé CurCli.close(); [7] #sql iterator IterateurClient(String, String); [1] IterateurClient CurCli; [2] varloc = Poitiers ; #sql CurCli = {select NCLI, NOM where LOCALITE = :varloc}; [3] #sql {fetch :CurCli into :varncli, :varnom}; [6] while (!CurCli.endFetch()) { traiter les valeurs de varncli et varnom #sql {fetch :CurCli into :varncli, :varnom}; [6] } CurCli.close(); [7] varcat = C2 ; varncli = C400 ; #sql {update CLIENT set CAT = :varcat where NCLI = :varncli}; L interface SQL dynamique classique varncli char(10); exec SQL include SQLCA; exec SQL begin declare section; varnom char(32); varloc char(30); Requete char(250); exec SQL end declare section; varncli := C400 ; [C] Requete := select NOM, LOCALITE + where NCLI = ' + varncli + ' ; [C] exec SQL prepare Q from :Requete; exec SQL execute Q into :varnom, :varloc; [P] [E] exec SQL execute immediate from :Requete into :varnom, varloc; [PE] varncli := C400 ; [C] Requete := select NOM, LOCALITE + where NCLI =? ; [C] exec SQL prepare Q from :Requete; exec SQL execute Q using :varncli into :varnom, :varloc; [P] [E] 9.11 Les interfaces SQL des programmes d application var1 := select ; var2 := NOM, ADRESSE ; var3 := CLIENT ; var4 := LOCALITE =? ; Requete := var1 + var2 + from + var3 + where + var4; Requete := select NCLI, NOM + where LOCALITE =? ; exec SQL declare CURCLI cursor for inst; exec SQL prepare inst from :Requete; varloc := Poitiers ; exec SQL open CURCLI using :varloc; exec SQL fetch CURCLI into :varncli, :varnom; while SQLCODE = 0 do traiter les valeurs de varncli et varnom exec SQL fetch CURCLI into :varncli, :varnom; endwhile; exec SQL close CURCLI; Requete := insert into DETAIL values (?,?,?) ; exec SQL prepare Q from :Requete; exec SQL execute Q using :valncom, :valnpro, :varqcom; Requete = insert into DETAIL values ( + valncom + , + valnpro + , + valqcom + ) ; exec SQL execute immediate from :Requete; Dunod La photocopie non autorisée est un délit. exec SQL execute immediate from insert into DETAIL values ( + valncom + , + valnpro + , + valqcom + ) ; JDBC : une interface CLI-SQL dynamique pour Java String locbd, login, motdepasse; String requete, varncli, varnom, varloc; Connection conn; PreparedStatement inst; ResultSet res; urlbd = adresse de la BD ; login = jlh ; motpasse =..; [1] conn = DriverManager.getConnection(urlBD,login,motPasse); [1] Chapitre 9 Le langage SQL avancé requete = select NOM, LOCALITE + where NCLI =? ; [2] inst = conn.preparestatement(requete); [3] varncli = C400 ; [4] inst.setstring(1, varncli); [4] res = inst.executequery(); [5] if (res.next()){ [6] varnom = res.getstring(1) 2 ; [7] varloc = res.getstring(2); traiter les valeurs de varnom et varloc }; res.close(); [8] Statement inst; inst = conn.createstatement(); [3] varncli = C400 ; [4] requete = select NOM, LOCALITE FROM CLIENT where NCLI = + varncli + ; [4] res = inst.executequery(requete); [5] if (res.next()){ [6] varnom = res.getstring(1); [7] varloc = res.getstring(2); traiter les valeurs de varnom et varloc }; res.close(); [8] requete = select NCLI, NOM + where LOCALITE =? ; [2] inst = conn.preparestatement(requete); [3] varloc = Poitiers ; [4] inst.setstring(1, varloc); [4] res = inst.executequery(); [5] while (res.next()){ [6] varncli = res.getstring(1); [7] varnom = res.getstring(2); traiter les valeurs de varncli et varnom }; res.close(); [8] 2. On peut aussi écrire res.getstring( nom ) 9.12 SQL et l information incomplète requete = update CLIENT set CAT =? where NCLI =? ; inst = con.preparestatement(requete); varcat = C2 ; varncli = C400 ; inst.setint(1, varcat); inst.setint(2, varncli); inst.executeupdate(); Comparaison des modèles d interaction Un problème de sécurité : l injection de code SQL getform(formid, varlogin, varpw); requete = select count(*) from SYS_USERS + where ID_USER = ' + varlogin + ' + and PW = ' + varpw + ' ; exec SQL execute immediate from :requete into :N; if (N = 0) then accepte = False else accepte = True; select count(*) into :N from SYS_USERS where ID_USER = 'Albert-Durant' and PW = 'A7cfg990'; select count(*) into :N from SYS_USERS where ID_USER = 'X' or 'X' = 'X' and PW = ' ' or 'X' = 'X'; 9.12 SQL ET L INFORMATION INCOMPLÈTE Dunod La photocopie non autorisée est un délit Introduction La valeur null de SQL La logique ternaire de SQL (CAT = B1 ) is true (CAT = B1 ) is not true (CAT = B1 ) is false (CAT = B1 ) is not false (CAT = B1 ) is unknown Chapitre 9 Le langage SQL avancé (CAT = B1 ) is not unknown La propagation de null en SQL La propagation de unknown en SQL Les problèmes de l information incomplète en SQL select NCLI where CAT = (select CAT where NCLI = 'K729'); select CAT, count(*) group by CAT; select distinct CAT order by CAT; (CAT 'C1') or (CAT = 'C1') or (CAT 'C1') select TITULAIRE, sum(h_cours) + sum(h_tp) as CHARGE from ACTIVITE group by TITULAIRE; select TITULAIRE, sum(h_cours + H_TP) as CHARGE from ACTIVITE group by TITULAIRE; (select sum(h_cours) from ACTIVITE) (select sum(h_cours) from ACTIVITE where P) + (select sum(h_cours) from ACTIVITE where not P) select NCLI,CAT C1 where not exists (select * C2 where C2.CAT C1.CAT); Deux recommandations create table CLIENT (NCLI.. not null primary key, NOM.. not null, 9.12 SQL et l information incomplète ADRESSE.. not null, LOCALITE.. not null, COMPTE.. not null); create table CLICAT( NCLI.. not null primary key, CAT char(2) not null, foreign key (NCLI) reference CLIENT); create table CLIENT (NCLI.. not null primary key, NOM.. not null, ADRESSE.. not null, LOCALITE.. not null, CAT char(2) default 'A0' not null, COMPTE.. not null); Dunod La photocopie non autorisée est un délit. Chapitre 9 Le langage SQL avancé
Related Search
We Need Your Support
Thank you for visiting our website and your interest in our free products and services. We are nonprofit website to share and download documents. To the running of this website, we need your help to support us.

Thanks to everyone for your continued support.

No, Thanks