PLSQL Oracle

De Avided.info
Aller à : navigation, rechercher


Le PL-sql est le SQL de oracle.

Il respecte les standards vue que la plupart des standards, viennent d'Oracle.

sqlplus est le client en ligne de commande permettant de ce connecter a oracle.

LDD

Language de définition de donnée

Syntaxe

<Ordre> <Objet> <Nom_Objet>


Ordre CREATE

  • table

Syntaxe :

create table nom_table
  (colon1   type1(longueur1),
   colon2   type2(longueur2),
   ...
   constraint nom_contrainte1
     type_contrainte1,
     ...
   );

Exemple:

-- Table `Mere`
CREATE TABLE service
  (IdService CHAR(3),
    NomService VARCHAR(30),
    CONSTRAINT pk_service
      PRIMARY KEY(IdService)
  );

-- Table ‘Fille’
CREATE TABLE employe
  (IdEmploye NUMBER(5),
   NomEmploye VARCHAR(30),
   Indice NUMBER(3),
   DateEmbauche DATE DEFAULT SYSDATE,
   IdService CHAR(3)
     CONSTRAINT nn_emp_ser NOT NULL,
     CONSTRAINT pk_employe
       PRIMARY KEY(IdEmploye),
     CONSTRAINT fk_emp_ser FOREIGN KEY(IdService)
       REFERENCES service(IdService),
     CONSTRAINT ck_emp_indice CHECK
       (indice BETWEEN 100 AND 900)
   );

Types de données

VARCHAR(size)

Données caractères de longueur variable

CHAR(size)

Données caractères de longueur fixe

NUMBER(p,s)

Numérique de longueur variable

DATE

Valeurs de date et d'heure

LONG

Données caractères de longueur variable (2 Go)

CLOB

Données caractères (4 Go)

RAW

Binaire

BLOB Binaire

jusqu'à 4 giga-octets

BFILE Binaire

stocké dans un fichier externe, (4 Go)



Contraintes

  • PRIMARY KEY
  • FOREIGN KEY
  • CHECK (NOT NULL, UNIQUE)

Syntaxe :

constraint nomcontrainte
{ unique | primary key (col1[,col2]...)
         | foreign key (col1[,col2]...)
references [schema].table (col1[,col2]...)
[ON DELETE CASCADE]
         | check (condition) }

Attention : "ON DELETE CASCADE" Supprime tous les fils

Les contraintes peuvent être activé/désactiver voir "Ordre Alter". Dans le cas de désactivation, elles existent toujours dans le dictionnaire de donnée, mais les traitements liés aux contraintes ne sont plus effectués. Cela peut s’avérer utile lors d’intégration massive de données. Il faudra tous de même être certains que les données devant être intégré respecte les contraintes si nous voulons garder une cohérence de données. ( A VOIR )

Les contraintes peuvent être différées, ce qui n'est pas le comportement par défaut. Si la contrainte est différé, il faudra utilisé l'instruction "COMMIT" pour que la transaction soit controlé. Si elle est KO un roolback sera effectué.

Il est possible de créer une table qui contiendra les lignes d'erreurs

CREATE TABLE tab_ligne_ko
(ligne rowid,
proprietaire varchar(30),
nom_table varchar(30),
contrainte varchar(30));

ALTER TABLE nom_table
ENABLE CONSTRAINT nom_contrainte
EXCEPTIONS INTO tab_ligne_ko;


Contrainte Différées

Une ou toutes les contraintes peuvent-être différées, le contrôle de la contrainte ne ce fait pas lors de l’exécution de la requête, mais l'instruction COMMIT et attendue pour effectué ce contrôle.

  • Différer toutes les contraintes
SET CONSTRAINTS ALL DEFERRED;


Activer / Désactiver une contrainte

ALTER TABLE nom_table
  ENABLE CONSTRAINT nom_contrainte ;

ALTER TABLE nom_table
  ENABLE CONSTRAINT PRIMARY KEY;
ALTER TABLE nom_table
  DISABLE CONSTRAINT nom_contrainte;

ALTER TABLE nom_table
  DISABLE CONSTRAINT PRIMARY KEY;

Ordre DROP

Suppression

Syntaxe:

Suppression complète de la table : définition et données

DROP TABLE nom_table;

Suppression aussi des contraintes de référence filles

DROP TABLE nom_table CASCADE CONSTRAINTS;

Ordre ALTER

Modification

Syntaxe :

Ajout de colonnes

ALTER TABLE nom_table
  ADD (colonne1 type1, colonne2 type2);

Modification de colonnes

ALTER TABLE nom_table
  MODIFY (colonne1 type1, colonne2 type2);

Suppression de colonnes

ALTER TABLE nom_table DROP COLUMN (colonne1, colonne2);

Objet TABLE

Voir les tables de l'utilisateur courant dans la base courante Syntaxe :

 select * from tab; 
 -- ou
 select table_name from user_tables;

Liste toutes les tables de la base de données

  select table_name from all_tables

Objet INDEX

Les indexes améliore les accès sur les colonnes recherchées (sélection) Accéléré les jointures ( equi-jointure entre une clé primaire et sa clé étrangère

Création d'un index Unique

Syntaxe :

CREATE UNIQUE INDEX nom_index
  ON nom_table(colonne[,colonne2 …]);

Création d’un index non Unique

Syntaxe :

CREATE INDEX nom_index
  ON nom_table(colonne[,colonne2 …]);

Suppression d’un index

Syntaxe :

DROP INDEX nom_index;

Objet CLUSTER

Les cluster sont comme des jointure physique. Elle ranger et presente les information par bloc. La taille des blocks doit etyre definit

Création du cluster

Syntaxe :

CREATE CLUSTER personnel
 (no_dept NUMBER(3))
 SIZE 200 TABLESPACE ts1
 STORAGE(INITIAL 5M NEXT 5M PCTINCREASE 0);

Creation de l'index du cluster

CREATE INDEX idx_personnel
  ON CLUSTER personnel
  TABLESPACE tsx1
  STORAGE(INITIAL 1M NEXT 1M PCTINCREASE 0);

Création de tables dans le cluster

CREATE TABLE dept
 (no_dept NUMBER(3)
 CONSTRAINT pk_dept PRIMARY KEY,
 nom_dept VARCHAR(30),resp_dept VARCHAR(30))
 CLUSTER personnel(no_dept);

CREATE TABLE emp
 (no_emp NUMBER(3) CONSTRAINT pk_emp PRIMARY KEY,
 nom_emp VARCHAR(30),
 no_dept NUMBER(3) REFERENCES dept(no_dept))
 CLUSTER personnel(no_dept);

Suppression d'un cluster

DROP CLUSTER personnel
INCLUDING TABLES;

ou

DROP TABLE emp;
DROP TABLE dept;
DROP CLUSTER personnel;

Objet SEQUENCE

  • Il permet d’obtenir des valeurs incrémentales
  • Il n’est pas associée à une colonne particulière
  • Verrouillage automatique en cas de concurrence
  • d’accès
  • Valeur suivante : <nom_séquence>.NEXTVAL
  • Valeur courante : <nom_séquence>.CURRVAL

Syntaxe :

  • Creation de la sequence
CREATE SEQUENCE nom_séquence
START WITH valeur_départ
INCREMENT BY incrément;
  • Utilisation de la sequance
INSERT INTO t1 VALUES
(nom_séquence.NEXTVAL, …..);
INSERT INTO t2 VALUES
(……….., nom_séquence.CURRVAL);


  • Suppression de la séquence
DROP SEQUENCE nom_séquence;


LMD

Langage de manipulation de données

INSERT

– Insertion (ajout) de ligne(s) dans une table – Utiliser SQL*LOAD pour des chargements externes

Syntaxe:

INSERT INTO nom_table [(liste des colonnes)]
  VALUES (liste des valeurs);

UPDATE

– Mise à jour (modification) de une ou plusieurs colonnes de une ou plusieurs lignes

UPDATE nom_table
  SET colonne1 = valeur1
  [,colonne2 = valeur2 ….]
  [ WHERE prédicat];

DELETE

– Suppression de une ou plusieurs lignes

DELETE FROM nom_table
[WHERE prédicat];

COMMIT / ROLLBACK

– Fin d’une transaction


  • Notion de transaction : ensemble fini d’actions (update, delete et insert)
  • Commit : point de confirmation dans la base
  • Rollback (‘retour arrière’) : les actions sont ‘défaites’ jusqu’au dernier point de confirmation
  • Le Commit peut être automatique (pas conseillé)

LID

Langage d'Interrogation de Données

Selection et affichage

Mono Table

Ordre SELECT

SELECT {DISTINCT|*|col1[,col2,….]
 [AS nom_col]}
 FROM nom_de_table
 WHERE <prédicat sur les lignes>
 GROUP BY col1 [,col2,….]
 HAVING <prédicat sur les groupages>
 ORDER BY {col1 {ASC|DESC}
 [,col2 …]| n°col }

ORDER BY

La clause ORDER BY permet d'effectuer des tries sur les résultats

DISTINCT

Distinct, permet de supprimer tous les lignes de résultats en doublons

GROUP BY

Permet de creer des groupe dans l'affichage du reslutat

HAVING

Filtre les resultats renvoyé par GROUP BY

FONCTIONS DE GROUPAGES

Les fonction pouvant etre utilisé par GROUP BY sont

AVG Moyenne
COUNT Nombre de lignes
GROUPING Composer des regroupements (datawarehouse)
MAX Maximum
MIN Minimum
STDDEV Ecart type
SUM Somme
VARIANCE Variance
FONCTIONS NUMERIQUES
ABS Valeur absolue
ACOS Arc cosinus (de -1 à 1)
ADD_MONTHS Ajoute des mois à une date
ATAN Arc tangente
CEIL Plus grand entier <= n, CEIL(15.7) donne 16
COS Cosinus
COSH Cosinus hyperbolique
EXP Retourne "e" à la puissance "n" ("e" = 2.71828183)
FLOOR tronque la partie fractionnaire
CEIL Arrondi à l'entier le plus proche
MOD (m,n) Division entière de m par n
POWER (m,n) m puissance n
ROUND (m,n) Arrondi à une ou plusieurs décimales
SIGN(n) Retourne le signe d'un nombre
SQRT Racine carré
SIN Sinus
Fonctions chaînes de caractères
CHR Retourne le caractère ASCII équivalent
CONCAT Concatène
INITCAP Première lettre de chaque mot en majuscule
LOWER Tout en minuscules
LPAD(c1,n,c2) Cadrage à gauche de c2 dans c1 et affichage de n caractères
LTRIM(c1,c2) Enlève c2 à c1 gauche
RTRIM(c1,c2) Enlève c2 à c1 par la droite
TRIM(c1,c2) Enlève c2 à c1 des deux côtés
REMPLACE(c1,c2,c3) Remplace c2 par c3 dans c1
LPAD(c1,n,c2) Cadrage à gauche de c2 dans c1
SOUNDEX Compare des chaines phonétiquement (seulement en englais)
SUBSTR(c,d,l) Extraction d'une sous-chaîne dasn c à partir de d et d'une longueur de l
UPPER Tous en majuscules
TO_CHAR(entier) Transforme en chaîne
TO_NUMBER(chaîne) Transforme en entier
Fonctions pour les dates
ADD_MONTHS(d,n) Ajoute n mois a la date d
LAST_DAY(d) Retourne le dernier jour du mois
MONTHS_BETWEEN(d1,d2) retourne le nombre de mois entre 2 dates
NEXT_DAY(d,chaine) Retourne le prochain jour ouvrable
ROUND(FonctionDate) Arrondie une date
SYSDATE Date courante
TRUC(FonctionDate) Tronque une date
TO_DATE(chaine,format) Exemple:'DD-MM-YYYY'

Multi-Table

Jointures