Remerciez-le!

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

Fondements des bases de données Programmation en PL/SQL Oracle – les transactio

Fondements des bases de données Programmation en PL/SQL Oracle – les transactions Équipe pédagogique BD http://liris.cnrs.fr/~mplantev/doku/doku.php?id=lif10_2016a Version du 13 octobre 2016 Transactions Problèmes de cohérence et transaction Un SGBD doit pouvoir supporter : ▶plusieurs utilisateurs l’utilisant en parallèle ▶eﬀectuant des opérations d’écriture et de lecture ▶tout en garantissant la cohérence des données Une transaction est un ensemble d’ordres (SQL) indivisibles, faisant passer la base de données d’un état cohérent à un autre en une seule étape. Propriétés ACID Atomicité Une transaction réussi ssi toutes ses opérations réussisent. Cohérence Une transaction terminée laisse la base dans un état cohérent où les données sont intègres. Isolation Les transactions doivent être rendues indépendantes les unes des autres. Durabilité Les eﬀets d’uns transaction terminée sont persistant. Opérations COMMIT et ROLLBACK ROLLBACK annule entièrement une transaction : toutes les modiﬁcations depuis le début de la transaction sont alors défaites. COMMIT valide entièrement une transaction : les modiﬁcations deviennent déﬁnitives et visibles à tous les utilisateurs. SAVEPOINT point de contrôle, état de la base où l’on pourra revenir plus tard. A noter ▶En cours de transaction, seul l’utilisateur ayant eﬀectué les modiﬁcations les voit. ▶En cas de ﬁn anormale d’une tâche utilisateur il y a automatiquement ROLLBACK des transactions non terminées. ▶Une transaction commence (implicitement) à la première opération SQL rencontrée et dès qu’une transaction est terminée. ▶Les commandes de déﬁnition de données sont automatiquement commitées (auto-commit) et valident donc les ordres précédent. ▶Un mécanisme de vérouillage permet de gérer les conﬂits d’accès parallèle. Exemple INSERT INTO t r a n s a c t i o n s VALUES (1 , ’ Pas␣de␣s a v e p o i n t ’ ) ; −−1 l i g n e cr e e e . INSERT INTO t r a n s a c t i o n s VALUES (2 , ’ Savepoint ␣A ’ ) ; −−1 l i g n e cr e e e . SAVEPOINT A; −−Savepoint cree . INSERT INTO t r a n s a c t i o n s VALUES (3 , ’ Savepoint ␣B ’ ) ; −−1 l i g n e cr e e e . SAVEPOINT B; −−Savepoint cree . INSERT INTO t r a n s a c t i o n s VALUES (4 , ’ Pas␣de␣s a v e p o i n t ’ ) ; −−1 l i g n e cr e e e . Exemple ROLLBACK TO SAVEPOINT B; −−Annulation ( r o l l b a c k ) e f f e c t u e e . SELECT ∗FROM t r a n s a c t i o n s ; /∗ ID DESCRIPTION − − − − − − − − − −− − − − − − − − − − − − − − − − − − − − 1 Pas de s a v e p o i n t 2 Savepoint A 3 Savepoint B ∗/ ROLLBACK TO SAVEPOINT A; −−Annulation ( r o l l b a c k ) e f f e c t u e e . SELECT ∗FROM t r a n s a c t i o n s ; /∗ ID DESCRIPTION − − − − − − − − − −− − − − − − − − − − − − − − − − − − − − 1 Pas de s a v e p o i n t 2 Savepoint A ∗/ Exemple COMMIT; −−V a l i d a t i o n e f f e c t u e e . SELECT ∗FROM t r a n s a c t i o n s ; ID DESCRIPTION −− − − − − − − − −− − − − − − − − − − − − − − − − − − − − /∗ 1 Pas de s a v e p o i n t 2 Savepoint A ∗/ Triggers et transactions Le trigger est une extension de l’opération de modiﬁcation de donnée sur laquelle il se déclenche, or ces opérations sont atomiques et ne peuvent pas être divisées : COMMIT et ROLLBACK sont donc interdits dans le corps des triggers. Workaround : PRAGMA AUTONOMOUS_TRANSACTION Permet de déclarer une transaction autonome. Attention aux eﬀets : ici, si l’insert est annulé, le tuple de la relation log sera bien ajouté ! CREATE OR REPLACE TRIGGER tab1_trig AFTER i n s e r t ON tab1 DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO log VALUES (SYSDATE, ’ I n s e r t ␣on␣TAB1 ’ ) ; COMMIT; −−only allowed i n autonomous t r i g g e r s END; / Niveaux d’isolation Du plus souple (garantie minimum) au plus restrictif : READ UNCOMMITED Permet à une autre transaction de lire des données qui ont été changées, mais pas encore validées. READ COMMITED C’est le paramètre par défaut pour Oracle. Il assure que chaque requête dans une transaction lit seulement les données validées. REPEATABLE READ Ce niveau permet à une transaction de lire les mêmes données plusieurs fois avec la garantie qu’elle recevra les mêmes résultats à chaque fois. SERIALIZABLE Une transaction ne prend en compte que les données validées avant le démarrage de la transaction. Niveaux Oracle SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ; SET TRANSACTION READ ONLY; −−semblable a REPEATABLE READ mais −−sans a u t o r i s e r l e s m o d i f i c a t i o n s . Fin du cours. Introduction au langage PL/SQLSAYADI Fouad Consultant Formateur Développement d’applications Caractéristiques du PL/SQL Un langage procédural qui étend SQL. Il est propre à Oracle Modulaire, grâce aux blocs, fonctions & procédures, ainsi que les packages Déclaratif : déclaration de variables, constantes, exceptions, de curseur. Fournit les structures itératives et conditionnelles Traitement des erreurs (routines ou définies par l’utilisateur) Portabilité vers n’importe quel environnement supportant Oracle Intégration : pas de problèmes d’intégration entre Oracle et les environnements de développements Conventions de programmation Conventions du code En majuscule Les commandes SQL (SELECT, FROM, WHERE etc.) Les mots clé PL/SQL (DECLARE, BEGIN, NUMBER etc.) En minuscule Les noms de variables PL/SQL Les noms de colonnes, tables, vues etc. Conventions de programmation Conventions de nommage des variables Structure d’un bloc PL/SQL Un bloc PL/SQL contient trois sections : Une section déclarative: optionnelle, contient les déclarations des variables, constantes, curseurs, exceptions, etc. Commence par le mot clé DECLARE Une section exécutable: obligatoire, contient les requêtes SQL et les instructions PL/SQL. Commence par le mot BEGIN Une section de traitement d’erreurs : optionnelle, contient les instructions à exécuter au cas où des erreurs se produisent. Commence par le mot EXCEPTION À noter que le bloc se termine par le mot clé END Structure d’un bloc PL/SQL Exemple de bloc PL/SQL DECLARE v_sal NUMBER; BEGIN SELECT sal INTO v_sal FROM emp WHERE empno=9854; EXCEPTION WHEN NO_DATA_FOUND THEN … END Les structures de programme PL/SQL Les blocs anonymes Les procédures et fonctions stockées Les procédures et fonctions d’application Les packages Les triggers (déclencheurs) de base de données Les triggers (déclencheurs) d’application Les règles syntaxiques Un identifiant : Max de 30 caractères, doit débuter par une lettre. Ne doit pas être un mot réservé. Son nom doit être différent des noms de colonnes. Les chaînes de caractères et les dates constantes entre simples quotes. Les commentaires sur plusieurs lignes doivent être inclus entre les caractères " /* " et " */ " Les caractères "- -" sont utilisés pour mettre une seule ligne en commentaire. La base de données exemple Les types de variables Il existe deux types de variables qui fonctionnent sous PL/SQL Les variables PL/SQL Variable scalaire : contenant une valeur unique Variable composée : contenant plusieurs valeurs comme le RECORD, le TABLE, le NESTED TABLE, le VARRAY Variable référence : variable qui pointe vers un type de données LOB : variable localisateur d’objets volumineux tel que les images et les vidéos Les variables non PL/SQL Les variables de substitution Les variables hôtes Les types de données scalaires Les principaux types de données scalaires Les types numériques INTEGER, POSITIVE PLS_INTEGER, BINARY_INTEGER NUMBER(n,d), DECIMAL, REAL Les types chaine de caractère LONG, VARCHAR2(n) CHAR(n) Le type booléen BOOLEAN (true, false ou null) Le type date DATE Les variables scalaires Déclaration et initialisation variable_name [CONSTANT] datatype [NOT NULL] [:=|DEFAULT expr]; datatype : le type de données de la variable, qui est soit scalaire, composé, référence ou LOB. CONSTANT : contraint la variable à être une constante NOT NULL : ce mot clé contraint la variable à contenir une valeur. expr : valeur initiale d’une variable, peut être une valeur littérale, une autre variable ou une expression impliquant des opérateurs et des fonctions. Les variables scalaires La déclaration de plusieurs variables sur la même ligne est interdite Affectation variable := expression; DEFAULT : contraint la variable à être une constante uploads/Finance/ ilovepdf-merged-1.pdf