Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

Créer schéma/Utilisateur l CREATE USER company l IDENTIFIED BY <<password>> l D

Créer schéma/Utilisateur l CREATE USER company l IDENTIFIED BY <<password>> l DEFAULT TABLESPACE <<tablespace to use for objects by default>> l TEMPORARY TABLESPACE <<temporary tablespace to use>> l GRANT CREATE SESSION TO company; l GRANT CREATE TABLE TO company; l GRANT CREATE VIEW TO company; l ... Créer Tablespace l CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M l EXTENT MANAGEMENT LOCAL AUTOALLOCATE; l CREATE TABLESPACE lmtbsb DATAFILE '/u02/oracle/data/lmtbsb01.dbf' SIZE 50M l EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K; Définitions des contraintes d’intégrité • Clé primaire : CONSTRAINT nom_contrainte PRIMARY KEY (attribut_clé [, attribut_clé2, …]) • Clé étrangère : CONSTRAINT nom_contrainte FOREIGN KEY (attribut_clé_ét) REFERENCES table(attribut) • Contrainte de domaine : CONSTRAINT nom_contrainte CHECK (condition) Définitions des contraintes d’intégrité Création d’index (accélération des accès) CREATE [UNIQUE] INDEX nom_index ON nom_table (attribut [ASC|DESC], …); UNIQUE  pas de double ASC/DESC  ordre croissant ou décroissant ex. CREATE UNIQUE INDEX Icli ON Client (NumCli); • Destructions : DROP TABLE nom_table; DROP INDEX nom_index; Opérations sur les attributs Ajout d’attributs ALTER TABLE nom_table ADD (attribut TYPE, …); ex. ALTER TABLE Client ADD (tel NUMBER(8)); • Modifications d’attributs ALTER TABLE nom_table MODIFY (attribut TYPE, …); ex. ALTER TABLE Client MODIFY (tel NUMBER(10)); • Suppression de contraintes ALTER TABLE nom_table DROP CONSTRAINT nom_contrainte; l Fonctions SQL*Plus – ABS(n) : Valeur absolue de n – CEIL(n) : Plus petit entier  n – FLOOR(n) : Plus grand entier  n – MOD(m, n) : Reste de m/n – POWER(m, n) : mn – SIGN(n) : Signe de n – SQRT(n) : Racine carrée de n – ROUND(n, m) : Arrondi à 10-m – TRUNC(n, m) : Troncature à 10-m l Fonctions SQL*Plus – CHR(n) : Caractère ASCII n° n – INITCAP(ch) : 1ère lettre en maj. – LOWER(ch) : c en minuscules – UPPER(ch) : c en majuscules – LTRIM(ch, n) : Troncature à gauche – RTRIM(ch, n) : Troncature à droite – REPLACE(ch, car) : Remplacement de caractère – SUBSTR(ch, pos, lg) : Extraction de chaîne – SOUNDEX(ch) : Cp. Phonétique – LPAD(ch, lg, car) : Compléter à gauche – RPAD(ch, lg, car) : Compléter à droite l Fonctions SQL*Plus – ASCII(ch) : Valeur ASCII de ch – INSTR(ch, ssch) : Recherche de ssch dans ch – LENGTH(ch) : Longueur de ch – ADD_MONTHS(dte, n) : Ajout de n mois à dte – LAST_DAY(dte) : Dernier jour du mois – MONTHS_BETWEEN(dt1, dt2) : Nombre de mois entre dt1 et dt2 – NEXT_DAY(dte) : Date du lendemain – SYSDATE : Date/heure système l Fonctions SQL*Plus – TO_NUMBER(ch) : Conversion de ch en nombre – TO_CHAR(x) : Conversion de x en chaîne – TO_DATE(ch) : Conversion de ch en date – NVL(x, val) : Remplace par val si x a la valeur NULL – GREATEST(n1, n2…) : + grand – LEAST (n1, n2…) : + petit – UID : Identifiant numérique de l’utilisateur – USER : Nom de l’utilisateur Gestion des transactions l Transaction : ensemble de modifications de la base l Début de transaction : début de la session de travail ou fin de la transaction précédente. l Validation (et fin) d’une transaction : COMMIT; l Annulation (et fin) d’une transaction : ROLLBACK; l Fin de session de travail  validation automatique Les vues Vue : table virtuelle calculée à partir d’autres tables grâce à une requête. Définition d’une vue CREATE VIEW nom_vue AS requête; ex. CREATE VIEW Noms AS SELECT Nom, Prenom FROM Client; Les vues - Intérêt Intérêt des vues Simplification de l’accès aux données en masquant les opérations de jointure ex. CREATE VIEW Prod_com AS SELECT P.NumProd, Dési, PrixUni, Date, Quantite FROM Produit P, Commande C WHERE P.NumProd=C.NumProd; SELECT NumProd, Dési FROM Prod_com WHERE Quantite>10; Les vues - Intérêt Intérêt des vues l Sauvegarde indirecte de requêtes complexes l Présentation de mêmes données sous différentes formes adaptées aux différents usagers particuliers l Support de l’indépendance logique ex. Si la table Produit est remaniée, la vue Prod_com doit être refaite, mais les requêtes qui utilisent cette vue n’ont pas à être remaniées. l Renforcement de la sécurité des données par masquage des lignes et des colonnes sensibles aux usagers non habilités Les vues - Restrictions Problèmes de mise à jour, restrictions l La mise à jour de données via une vue pose des problèmes et la plupart des systèmes impose d’importantes restrictions. l Le mot clé DISTINCT doit être absent. l La clause FROM doit faire référence à une seule table. l La clause SELECT doit faire référence directement aux attributs de la table concernée (pas d’attribut dérivé). l Les clauses GROUP BY et HAVING sont interdites. Sécurité et autorisation l Transmission de privilèges GRANT privilège ON table|vue TO user|PUBLIC [WITH GRANT OPTION]; Privilèges : SELECT : lecture INSERT : insertion UPDATE : mise à jour DELETE : suppression ALL : tous les privilèges ALTER : destruction INDEX : construction d’index l Suppression de privilèges REVOKE privilège ON table|vue FROM user|PUBLIC; Catalogue du système Contient sous forme relationnelle la définition de tous les objets créés par le système et les usagers. Ces tables sont accessibles avec SQL (en mode consultation uniquement). Quelques tables utiles gérées par Oracle • USER_CATALOG (TABLE_NAME, TABLE_TYPE) • USER_TAB_COLUMNS (TABLE_NAME, COLUMN_NAME, …) • USER_IND_COLUMNS (INDEX_NAME, TABLE_NAME, COLUMN_NAME, …) • ALL_TABLES (TABLE_NAME, OWNER, …) Catalogue du système Exemples • Tables qui contiennent un attribut Intitule SELECT TABLE_NAME FROM USER_TAB_COLUMNS WHERE COLUMN_NAME=‘Intitule’; • Attributs de la table Client SELECT COLUMN_NAME FROM USER_TAB_COLUMNS WHERE TABLE_NAME=‘Client’; • Tables de l’utilisateur darmont SELECT TABLE_NAME FROM ALL_TABLES WHERE OWNER=‘darmont’; PL/SQL PL/SQL l PL/SQL : Langage procédural Extension de SQL Déclaration de variables et de constantes Définition de sous-programmes Gestion des erreurs à l’exécution (exceptions) Manipulation de données avec SQL Bloc PL/SQL DECLARE --Déclaration constantes/variables BEGIN --Commandes/instructions EXCEPTION --Traitement des erreurs à l’exé. END; Déclarations l Partie déclarative d’un bloc PL/SQL ou d’un sous-programme l Types usuels : INTEGER, REAL, STRING, DATE, BOOLEAN + types SQL l Variables ex. date_naissance DATE; compteur INTEGER:=0; -- Valeur par défaut compteur INTEGER DEFAULT 0; -- idem id CHAR(5) NOT NULL:=‘AP001’; Déclarations l Constantes ex. euro CONSTANT REAL:=6.55957; l Type d’une autre variable ex. credit REAL; debit credit%TYPE; l Type d’un tuple d’une table ex. un_client client%ROWTYPE; l Tableaux : 1)Déclaration d’un type tableau 2)Déclaration d’une variable de ce type Déclarations l Tableaux ex. TYPE Tab_entiers TABLE OF INTEGER INDEX BY BINARY_INTEGER; TYPE Tab_cli TABLE OF client.nom%TYPE INDEX BY BINARY_INTEGER; un_entier Tab_entiers; un_client Tab_cli; Déclarations l Enregistrements personnalisés 1) Définition d’un type enregistrement ex. TYPE Enr_four IS RECORD ( numero fournisseur.numfour%TYPE, raison_sociale CHAR(20)); 2) Définition d’une variable de ce type ex. un_fournisseur Enr_four; Instructions de base l Affectation ex. numero:=0; numero:=numero+1; SELECT numcli INTO numero FROM client WHERE numcli=numero+1; l Utilisation des tableaux ex. i:=1; un_entier(i):=i*2; NB : i doit être de type BINARY_INTEGER. Instructions de base l Utilisation des enregistrements personnalisés ex. un_fournisseur.numero:=4589; un_fournisseur.raison_sociale:=‘COGIP’; SELECT numfour, raisonsoc INTO un_fournisseur FROM fournisseur WHERE numfour=4589; Structures de contrôle l Instruction sélective IF-THEN, IF-THEN-ELSE ou IF-THEN-ELSIF IF condition1 THEN -- Instructions ELSEIF condition2 THEN -- Instructions ELSE -- Instructions END IF; Structures de contrôle l Instructions itératives FOR compteur IN [REVERSE] min..max LOOP -- Instructions END LOOP; WHILE condition LOOP -- Instructions END LOOP; LOOP -- Instructions END LOOP; Structures de contrôle l Branchements -- Saut inconditionnel GOTO étiquette; -- Sortie de boucle EXIT WHEN condition; NB : À utiliser avec énormément de modération ! Curseurs l Curseur : structure de données permettant de stocker le résultat d’une requêtes qui retourne plusieurs tuples l Déclaration : CURSOR nom_curs IS requête; ex. CURSOR calcul IS SELECT numprod, prixuni*1.206 prixttc FROM produit; NB : Un tuple du curseur sera de type calcul%ROWTYPE. Curseurs l Ouverture d’un curseur : OPEN nom_curs; l Gestion automatique d’un curseur ex. FOR tuple IN calcul LOOP var1:=tuple.numprod; var2:=tuple.prixttc; END LOOP; l Gestion « manuelle » ex. LOOP FETCH calcul INTO tuple; EXIT WHEN calcul%NOTFOUND; … END LOOP Curseurs l Attributs des curseurs "%NOTFOUND : FALSE si FETCH retourne un résultat "%FOUND : opposé logique de %NOTFOUND "%ROWCOUNT : Nombre de lignes lues "%ISOPEN : TRUE si le curseur est ouvert l Fermeture d’un curseur : CLOSE nom_curs; Exceptions l À chaque erreur à l’exécution, une exception est levée. Ces exceptions sont gérées par des routines séparées. l Fonctions PL/SQL pour la gestion d’erreurs "SQLCODE : Code de la dernière exception levée "SQLERRM : Message d’erreur associé Exceptions l Exceptions prédéfinies Nom SQLCODE Code erreur --------------------------------------------------------------------------------------------- CURSOR_ALREADY_OPEN ORA-06511 -6511 DUP_VAL_ON_INDEX ORA-00001 -1 INVALID_CURSOR ORA-01001 -1001 INVALID_NUMBER ORA-01722 -1722 LOGIN_DENIED ORA-01017 -1017 NO_DATA_FOUND ORA-01403 -1403 NOT_LOGGED_ON ORA-01012 -1012 PROGRAM_ERROR ORA-06501 -6501 STORAGE_ERROR ORA-06500 -6500 TIMEOUT_ON_RESOURCE ORA-00051 -51 TOO_MANY_ROWS ORA-01422 -1422 VALUE_ERROR ORA-06502 -6502 ZERO_DIVIDE ORA-01476 -1476 Exceptions l Exceptions personnalisées Déclaration : nom_exc EXCEPTION; Lever l’exception : IF condition THEN RAISE nom_exc; END IF; l Traitement des exceptions WHEN nom_exc THEN -- Instruction ex. WHEN probleme THEN RAISE_APPLICATION_ERROR(-20501,’Erreur !’); NB : -20999  no d’erreur  -20001 Sous-programmes l Fonctions FUNCTION nomf (param1, param2…) RETURN type_valeur_de_retour IS -- Déclarations locales BEGIN -- Instructions RETURN valeur_de_retour; EXCEPTION -- Traitement des exceptions END; Sous-programmes l Procédures uploads/Voyage/ plsql-trigger.pdf