SQL - constraint
Suppression de vue :
LDD
drop view v_sgbd;
TABLE
Création de table : SÉQUENCE
Générer une valeur de clé primaire
create table SGBD ( , , , ) ;
create table ldd as select * from SGBD where Création de séquence :
(condition);
create sequence seq_sgbd
LES CONTRAINTES start with val
increment by pas
Il existe 5 types de contraintes :
maxvalue val_max
- Not null
minvalue val_min
- Unique
cycle
- Check
cache val ;
- Primary key
- Foreign key Modification de séquence :
Il existe 2 niveaux de définitions :
- Contrainte au niveau colonne alter sequence seq_sgbd
- Contrainte au niveau table start with val
Primary key: constraint pk_const primary key increment by pas
Foreign key: constraint fk_const foreign key maxvalue val_max
(colonne) references table (colonne) minvalue val_min
cycle
Modification de table : cache val ;
alter table SGBD Utilisation de séquence :
- Add : ajouter une colonne
- Modify : modifier une colonne Se fait par des pseudo-colonnes :
- Drop : supprimer une colonne - Nextval : incrémente la séquence et retourne
- Add constraint la nouvelle valeur
- Drop constraint - Currval : retourne la valeur courante de la
- Enable : activer une contrainte séquence
- Disable : désactiver une contrainte insert into table values (seq_sgbd.nextval, ‘y’) ;
Suppression de table : Suppression de séquence :
drop table SGBD ; drop sequence seq_sgbd ;
truncate table SGBD ;
Renommer table :
rename ancien to nouveau ; INDEX
Est un objet qui permet d’accélérer la recherche
VUE des lignes
Il contient 2 champs :
Est une table virtuelle qui ne contient pas des
- Clé d’index
donnés
- @ du bloc contenant la clé
Création et modification de vue : Créez index si :
Colonne contient un grand nbr de valeurs NULL
create or replace view v_sgbd (alias) as select Condition de jointure
col1,col2 from table where (condition) with Utilisée souvent dans la Clause WHERE
- with check option Table de grande taille
- with read only
Création d’index : select last_name nom, first_name as prénom,
salary*12 “revenue annuel” from employes;
create unique index idx_sgbd on SGBD
(col1,col2) ; CONCATÉNATION
alter index ancient rename to nouveau;
representee par le symbole ||
Suppression d’index : select dep_id||’**’||dep_name as “dep” from
departments;
drop index idx_sgbd ;
OPÉRATEURS DE COMPARAISON
SYNONYME BETWEEN
Est un alias, objet qui peut être une table, une vue, SELECT COL FROM TABLE WHERE COL BETWEEN ..
une séquence, une procédure, une fonction, un AND ..;
package, etc
Masquer le vrai nom des objets IN
Simplifier les noms des objets
SELECT COL FROM TABLE WHERE COL IN(,);
Création et suppression de synonyme :
LIKE
create public synonym sy_sgbd for nom_objet;
drop synonym sy_sgbd; SELECT COL FROM TABLE WHERE COL LIKE ‘Y_R%’;
IS NULL
LMD
Insertion des lignes : SELECT COL FROM TABLE WHERE COL IS NULL;
Insertion implicite: insert into table(col1,col2) OPERATEURS LOGIQUES
values(val1,val2); AND
Insertion explicite: insert into table
values(val1,val2); SELECT COL1,COL2 FROM TABLE WHERE
Insertion à partir d’autres tables: insert into COL2>=VAL AND COL1 LIKE ‘%SGBD%’;
table(col1,col2) select att1,att2 from table where
OR
condition;
SELECT COL1,COL2 FROM TABLE WHERE
Mise à jour de lignes :
COL2>=VAL OR COL1 LIKE ‘%SGBD%’;
update table set att1=val where condition;
NOT
update table set (col) = (select att from table
where condition) where condition; SELECT COL FROM TABLE WHERE COL NOT IN(‘ ‘,’
update table t set alias = (select att from table1 t1 ’,’ ’);
where [Link]=[Link]);
LES PARENTHESES > OPÉRATEURS DE COMPARAISON >
Suppression des lignes : NOT > AND > OR
delete from table ; CLAUSE ORDER BY
delete from table where condition;
delete from table where att= (select att from tab Tri des lignes ASC ou DESC
where condition); La dernière clause dans SELECT
select col from table order by col DESC;
LID select col1,col2 from table order by col1, col2;
FONCTIONS DE CARACTÈRES
INSTRUCTION SELECT
select * from table; Lower(ch) Majmin
select col1, col2 from table; Upper(ch) Minmaj
select distinct col from table;
Initcap(ch) 1er caractere en maj
ALIAS DE COLONNE Concat(ch1,ch2) Concaténation
SELECT COL1,COL2, ROW_NUMBER() OVER
Substr(ch,pos,long) Extraction
(ORDER BY EXPRESSION [ASC|DESC]) “NUMERO”
Length(ch) Taille FROM TABLE;
Instr(ch1,ch2) Pos de ch2 ds ch1 RANK
Lpad(ch,long,car) Complète ch par car à
gauche SELECT NOM, SCORE, RANK() OVER (PARTITION BY
EXPRESSION ORDER BY EXPRESSION [ASC|DESC])
Rpad(ch,long,car) Complète ch par car à AS CLASSEMENT FROM SCORES;
droite
DENSE_RANK
Ltrim(ch,car) Supprime car à gauche
Rtrim(ch,car) Supprime car à droite SELECT NOM, SCORE, DENSE_RANK() OVER
(PARTITION BY EXPRESSION ORDER BY
Replace(ch,ch1,ch2) Remplace ch1 par ch2
EXPRESSION [ASC|DESC]) AS CLASSEMENT FROM
dans ch
SCORES;
Ascii(ch) Retourne le code ascii
FIRST_VALUE
Chr(n) Retourne le car ayant
le code ascii n SELECT PRODUIT, DATE, MONTANT,
FIRST_VALUE(MONTANT) OVER (PARTITION BY
FONCTIONS NUMÉRIQUES
PRODUIT ORDER BY DATE ASC) AS
ABS(n) Valeur absolue PREMIERMONTANT FROM VENTES;
MOD(n1,n2) Reste LAST_VALUE
POWER(n,e) Puissance
SELECT PRODUIT, DATE, MONTANT,
SQRT(n) Racine carrée LAST_VALUE(MONTANT) OVER (PARTITION BY
PRODUIT ORDER BY DATE ASC) AS
ROUND(n,p) Arrondissement
DERNIERMONTANT FROM VENTES;
TRUNC(n,p) Tronque n à la pos p
FONCTIONS MULTI-LIGNES
FLOOR(n) Partie entière
inferieure AVG
CEIL(n) Partie entière SELECT AVG(MONTANT) AS MOYENNEVENTES
supérieure
FROM VENTES;
GREATEST(n,n1,n2) Max
COUNT
LEAST(n,n1,n2) Min
SELECT COUNT(*) FROM TABLE WHERE
FONCTIONS DE CONVERSION
CONDITION;
TO_CHAR SELECT COUNT(NOM) AS
NOMBRECLIENTSAVECNOM FROM CLIENTS;
SELECT TO_CHAR(COL,’FM DD MONTH YYYY’) AS
DATE FROM TABLE ; MAX
TO_DATE SELECT MAX(PRIX) AS PRIXMAXIMUM FROM
PRODUITS;
SELECT HIRE_DATE FROM TAB WHERE
HIRE_DATE>TO_DATE(‘01/01/1982’,’DD-MM- MIN
YYYY’);
SELECT MIN(PRIX) AS PRIXMINIMUM FROM
TO_NUMBER PRODUITS;
SELECT COL FROM TABLE WHERE COL>= STDDEV
TO_NUMBER(‘7777’);
SELECT STDDEV(MONTANT) AS ECARTTYPEVENTES
FONCTIONS ANALYTIQUES FROM VENTES;
ROW_NUMBER SUM
SELECT SUM(MONTANT) AS TOTALVENTES FROM BLOCS ANONYMES
VENTES;
VARIANCE Declare – optionnelle
/* déclaration var, cte, types, curseurs
SELECT VARIANCE(MONTANT) AS
Integer/number := val ;
VARIANCEVENTES FROM VENTES;
Varchar2() := ‘ ’ ;
LES JOINTURES Date;
JOINTURE INTERNE Dec%type ;
Dec%rowtype ; */
SELECT TAB1.A, TAB1.B, TAB2.A,TAB2.B FROM
TAB1 INNER JOIN TAB2 ON TAB1.A=TAB2.A AND Begin
TAB1.B=TAB2.B; /*traitements*/
Exception – optionnelle
JOINTURE EXTERNE
End ;
SELECT TAB1.A, TAB1.B, TAB2.A,TAB2.B FROM /
TAB1 LEFT OUTER JOIN TAB2 ON TAB1.A=TAB2.A
Déclaration et initialisation des variables :
AND TAB1.B=TAB2.B;
SELECT TAB1.A, TAB1.B, TAB2.A,TAB2.B FROM v_date date default ’01-01-2009’ ;
TAB1 RIGHT OUTER JOIN TAB2 ON TAB1.A=TAB2.A v_cte constant := 7 ;
AND TAB1.B=TAB2.B; v_char char(5) notnull := ‘SGBD’ ;
SELECT TAB1.A, TAB1.B, TAB2.A,TAB2.B FROM dbms_output.put_line(‘ ‘ || val);
TAB1 FULL OUTER JOIN TAB2 ON TAB1.A=TAB2.A
AND TAB1.B=TAB2.B; Structures de contrôle :
EQUIJOINTURE If condition then Case v1 while condition loop
When cond then End loop;
SELECT TAB1.A, TAB1.B, TAB2.A,TAB2.B Elsif condition then
FROM TAB1 LEFT OUTER JOIN TAB2 ON When cond then
TAB1.A=TAB2.A; Else When cond then Loop
End if ; End case; Exit when condition
NON-EQUIJOINTURE
End loop;
SELECT TAB1.A, TAB2.A, TAB2.C FROM TAB1 INNER
JOIN TAB2 ON TAB1.A>=TAB2.A AND
For I in inf..sup Loop Select * intro v_sgbd from SGBD where
TAB1.A<=TAB2.C;
condition;
End loop;
AUTO-JOINTURE
SELECT T1.A, T2.B, T2.C FROM TAB1 T1 INNER JOIN LES CURSEURS
TAB1 T2 ON T2.C=T1.A
Variable qui pointe vers le résultat d’une requête
JOINTURE NATURELLE
Manipulation des curseurs :
SELECT DEPARTMENT ID, DEPARTMENT NAME,
DÉCLARATION
LOCATION ID, CITY FROM DEPARTMENTS
NATURAL JOIN LOCATIONS WHERE LOCATION ID CURSOR CUR_SGBD IS SELECT * FROM SGBD
<>1700; WHERE CONDITION ORDER BY COL;
OUVERTURE
OPEN CUR_SGBD :
EXÉCUTION
PLSQL FETCH CUR_SGBD INTO VARS;
WHILE/EXIT WHEN CUR_SGBD%ATTS;
FERMETURE return type IS
declaration;
CLOSE CUR_SGBD ; begin
return ;
Attributs des curseurs :
end;
- %ISOPEN : curseur ouvert
NON STOCKÉES
- %FOUND : fetch réussi
- %NOTFOUND : fetch échoué Procédures :
- %ROWCOUNT : renvoie nbr de lignes
contenues DECLARE …
PROCEDURE myproc(a in type, b in type, prod out
Utilisation des curseurs : type) IS BEGIN prod:=a*b;
END myproc;
BEGIN myproc(a,b,p);
Declare dbms [Link] line(Le produit: ’||s); END;
Cursor cur_sgbd is select … ;
Fonctions :
Rec_sgbd cur_sgbd%rowtype;
Begin DECLARE
Open cur_sgbd; a type := val;
b type := val;
Loop
c type;
Fetch cur_sgbd into rec_sgbd; FUNCTION myfunc(a type, b type) RETURN type IS
… prod type;
End; BEGIN
prod:=a*b;
return prod;
Declare END;
Cursor cur_sgbd is select … ; BEGIN c:=myfunc(a,b);
Begin dbms [Link] line(’Le produit: ’||c); END;
For rec_sgbd in cur_sgbd
LES TRIGGERS
Loop
Se déclenche automatiquement lorsqu’un
…
événement se produit (insert,update,delete)
End;
DÉCLENCHEUR DE TABLE
Curseurs paramétrés :
Declare Create trigger trig_sgbd
After / before insert or update or delete on table
Cursor cur_sgbd(param type,param1 type) is select …; Begin
If inserting then
…
Elsif updating then
PROCÉDURES ET FONCTIONS …
Else
…
STOCKÉES End if;
Procédures : End;
create or replace procedure nom (arg1 IN type,
arg2 OUT type) IS
begin … end;
Fonctions :
create or replace function fn_nom(arg1 type,arg2 Create or replace trigger trig_sgbd
type) After / before insert or update of att1,att2
or delete on table
Begin
Case
ZERO_DIVIDE
DUP_VAL_ON_INDEX
INVALID_CURSOR
CURSOR_ALREADY_OPEN
INVALID_NUMBER
ROWTYPE_MISMATCH
Redéclaration des exceptions prédéfinies :
DECLARE a type;
b type;
DÉCLENCHEUR DE LIGNE nom EXCEPTION;
PRAGMA EXCEPTION_INIT(nom,code); BEGIN
CREATE OR REPLACE TRIGGER trig dep …
BEFORE INSERT OR UPDATE OR DELETE ON table
EXCEPTION WHEN nom THEN
FOR EACH ROW
BEGIN …
IF INSERTING THEN END;
dbms [Link] line(’Insertion’);
END IF; LES EXCEPTIONS DÉFINIES
IF UPDATING THEN
dbms [Link] line(’Modification’); Avec le mot clé RAISE :
END IF;
IF DELETING THEN
dbms [Link] line(’Suppression’);
END IF;
END;
CREATE OR REPLACE TRIGGER trig_insert
BEFORE INSERT ON table
FOR EACH ROW
WHEN ([Link] < val)
BEGIN
:[Link] := :[Link]+10;
END;
LES EXCEPTIONS Avec la procédure RAISE_APPLICATION_ERROR :
LES EXCEPTIONS PREDEFINIES
CREATE OR REPLACE PROCEDURE nom (arg type) I
S
var type ;
BEGIN
SELECT arg INTO var FROM table
WHERE condition;
…
EXCEPTION WHEN TOO MANY ROWS –THEN
DBMS [Link] LINE (’La requˆete revoie plusi
eurs lignes, utilisez un curseur’);
WHEN OTHERS THEN
ROLLBACK;
DBMS [Link] LINE(’code de l”erreur : ’||SQ
LCODE);
DBMS [Link] LINE(’message de l”erreur : ’|
|SQLERRM); END;
NO_DATA_FOUND
