Utilisation de la manipulation des données

Oracle fournit des commandes de langage de manipulation de données pour effectuer des opérations sur les données dans la base de données.Les opérations sur les données peuvent être le remplissage des tables de la base de données avec l'application ou les données métier, la modification des données et la suppression des données de la base de données, si nécessaire. Outre les opérations sur les données, il existe un ensemble de commandes qui sont utilisées pour contrôler ces opérations. Ces commandes sont regroupées sous le nom de langage de contrôle des transactions.

Il existe trois types d'instructions DML impliquées dans une transaction SQL logique, à savoir, insérer, mettre à jour, supprimer et fusionner. Une transaction est la collection logique d'actions DML dans une session de base de données.

Instruction INSERT

La commande INSERT est utilisée pour stocker des données dans des tables. La commande INSERT est souvent utilisée dans les langages de programmation de niveau supérieur tels que Visual Basic.NET ou C ++ en tant que commande Embedded SQL; cependant, cette commande peut également être exécutée à l'invite SQL * PLUS en mode commande. Il existe deux formes différentes de la commande INSERT. Le premier formulaire est utilisé si une nouvelle ligne aura une valeur insérée dans chaque colonne de la ligne. La deuxième forme de la commande INSERT est utilisée pour insérer des lignes dont certaines des données de colonne sont inconnues ou par défaut d'une autre logique métier. Cette forme de la commande INSERT nécessite que vous spécifiiez les noms de colonne pour lesquels les données sont stockées.

Syntaxe:

La syntaxe ci-dessous peut être suivie si les valeurs de toutes les colonnes du tableau sont définies et connues.

INSERT INTO table
VALUES (column1 value, column2 value, 
...);

La syntaxe ci-dessous peut être utilisée si seules quelques colonnes de la table doivent être remplies avec une valeur. Le reste des colonnes peut déduire leurs valeurs sous la forme NULL ou à partir d'une logique métier différente.

INSERT INTO table (column1 name, column2 name, . . .)
VALUES (column1 value, column2 value, . . .);

L'instruction INSERT ci-dessous crée un nouvel enregistrement d'employé dans la table EMPLOYEES. Notez qu'il insère les valeurs des colonnes principales EMPLOYEE_ID, FIRST_NAME, SALARY et DEPARTMENT_ID.

INSERT INTO employees (EMPLOYEE_ID, FIRST_NAME, SALARY, DEPARTMENT_ID)
VALUES (130, 'KEMP', 3800, 10);

Sinon, les données complètes des employés peuvent être insérées dans la table EMPLOYEES sans spécifier la liste des colonnes à l'aide de l'instruction INSERT ci-dessous - à condition que les valeurs soient connues à l'avance et doivent être conformes au type de données et à la position des colonnes dans la table.

INSERT INTO employees
VALUES (130, 'KEMP','GARNER', '[email protected]', '48309290',TO_DATE ('01-JAN-2012'), 'SALES', 3800, 0, 110, 10);

Les valeurs à insérer doivent être compatibles avec le type de données de la colonne. Des littéraux, des valeurs fixes et des valeurs spéciales telles que des fonctions, SYSDATE, CURRENT_DATE, SEQ.CURRVAL (NEXTVAL) ou USER peuvent être utilisés comme valeurs de colonne. Les valeurs spécifiées doivent suivre les règles génériques. Les littéraux de chaîne et les valeurs de date doivent être placés entre guillemets. La valeur de date peut être fournie au format JJ-MON-RR ou D-MON-AAAA, mais AAAA est préférable car elle spécifie clairement le siècle et ne dépend pas de la logique de calcul interne du siècle RR.

Instruction INSERT-AS-SELECT (IAS)

Les données peuvent être remplies dans la table cible à partir de la table source à l'aide de l'opération INSERT..AS..SELECT (IAS). C'est une opération de lecture de chemin direct. C'est un moyen simple de créer une copie des données d'une table à une autre ou de créer une copie de sauvegarde de la table dans laquelle les opérations de la table source sont en ligne.

Par exemple, les données peuvent être copiées de la table EMPLOYEES vers la table EMP_HISTORY.

INSERT INTO EMP_HISTORY
SELECT EMPLOYEE_ID, EMPLOYEE_NAME, SALARY, DEPARTMENT_ID
FROM employees;

Instruction UPDATE

La commande UPDATE modifie les données stockées dans une colonne. Elle peut mettre à jour une ou plusieurs lignes à la fois en fonction du jeu de résultats filtré par les conditions spécifiées dans la clause WHERE. Notez que la mise à jour des colonnes est différente de la modification des colonnes. Plus tôt dans ce chapitre, vous avez étudié la commande ALTER. La commande ALTER modifie la structure de la table, mais ne modifie pas les données de la table. La commande UPDATE change les données de la table, pas la structure de la table.

Syntaxe:

UPDATE table
SET column = value [, column = value ...]
[WHERE condition]

De la syntaxe,

La colonne SET = expression peut être n'importe quelle combinaison de caractères, de formules ou de fonctions qui mettront à jour les données dans le nom de colonne spécifié.La clause WHERE est facultative, mais si elle est incluse, elle spécifie quelles lignes seront mises à jour.Une seule table peut être mis à jour à la fois avec une commande UPDATE.

L'instruction UPDATE ci-dessous met à jour le salaire de l'employé JOHN à 5000.

UPDATE employees
SET salary = 5000
WHERE UPPER (first_name) = 'JOHN';

Bien que les prédicats WHERE soient facultatifs, ils doivent être logiquement ajoutés afin de ne modifier que la ligne requise dans la table. L'instruction UPDATE ci-dessous met à jour les salaires de tous les employés du tableau.

UPDATE employees
SET salary = 5000;

Plusieurs colonnes peuvent également être mises à jour en spécifiant plusieurs colonnes dans la clause SET séparées par une virgule. Par exemple, si le salaire et le rôle du poste doivent être modifiés respectivement à 5000 et SALES pour JOHN, l'instruction UPDATE ressemble à ceci:

UPDATE employees
SET	SALARY = 5000,
	JOB_ID = 'SALES'
WHERE UPPER (first_name) = 'JOHN';

1 row updated.

Une autre façon de mettre à jour plusieurs colonnes de la même ligne montre l'utilisation de la sous-requête.

UPDATE employees
SET (SALARY, JOB_ID) = (SELECT 5000, 'SALES' FROM DUAL)
WHERE UPPER (ENAME) = 'JOHN'

Instruction DELETE

La commande DELETE est l'une des instructions SQL les plus simples. Il supprime une ou plusieurs lignes d'une table. Les opérations de suppression de plusieurs tables ne sont pas autorisées dans SQL. La syntaxe de la commande DELETE est la suivante.

DELETE FROM table_name
    [WHERE condition];

La commande DELETE supprime toutes les lignes de la table qui satisfont à la condition de la clause WHERE facultative. La clause WHERE étant facultative, on peut facilement supprimer toutes les lignes d'une table en omettant une clause WHERE car la clause WHERE limite la portée de l'opération DELETE.

L'instruction DELETE ci-dessous supprimerait les détails d'EDWIN de la table EMP.

DELETE employees
WHERE UPPER (ENAME) = 'EDWIN'

1 row deleted.

Remarque: DELETE [TABLE NAME] et DELETE FROM [TABLE NAME] ont la même signification.

La condition WHERE dans les instructions de suppression conditionnelle peut utiliser la sous-requête comme indiqué ci-dessous.

DELETE FROM employees
WHERE DEPARTMENT_ID IN (SELECT DEPARTMENT_ID
				    FROM LOCATIONS
				    WHERE LOCATION_CODE = 'SFO')

TRONQUER

Truncate est une commande DDL qui est utilisée pour vider tous les enregistrements d'une table mais en conservant la structure de la table. Il ne prend pas en charge la condition WHERE pour supprimer les enregistrements sélectionnés.

Syntaxe:

TRUNCATE [table name]

C'est Auto Commit, c'est-à-dire qu'il valide la transaction active en cours dans la session. La troncature de la table ne supprime pas les index dépendants, les déclencheurs ou les contraintes de table. Si une table A est le parent d'une contrainte de référence d'une table B dans la base de données, la table A n'a pas pu être tronquée.

Transaction

Une transaction est une unité logique de travail effectuée dans la base de données. Il peut contenir soit -

  • Plusieurs commandes DML se terminant par une commande TCL, c'est-à-dire COMMIT ou ROLLBACK

  • Une commande DDL

  • Une commande DCL

Le début d'une transaction est marqué par la première commande DML. Il se termine par une commande TCL, DDL ou DCL. Une commande TCL, c'est-à-dire COMMIT ou ROLLBACK, est émise explicitement pour mettre fin à une transaction active. En vertu de leur comportement de base, si l'une des commandes DDL ou DCL est exécutée dans une session de base de données, validez la transaction active en cours dans la session. Si l'instance de base de données se bloque anormalement, la transaction est arrêtée.

COMMIT, ROLLBACK et SAVEPOINT sont le langage de contrôle des transactions. COMMIT applique les modifications de données de manière permanente dans la base de données tandis que ROLLBACK effectue une opération anti-commit. SAVEPOINT contrôle la série d'une transaction en définissant des marqueurs à différentes étapes de la transaction. L'utilisateur peut restaurer la transaction en cours jusqu'au point de sauvegarde souhaité, qui a été défini précédemment.

COMMIT- Commit met fin à la transaction active actuelle en appliquant les modifications de données de manière permanente dans les tables de la base de données. COMMIT est une commande TCL qui termine explicitement la transaction. Cependant, la commande DDL et DCL valide implicitement la transaction.

SAVEPOINT- Savepoint est utilisé pour marquer un point spécifique dans la transaction en cours dans la session. Puisqu'il s'agit d'un marqueur logique dans la transaction, les points de sauvegarde ne peuvent pas être interrogés dans les dictionnaires de données.

ROLLBACK- La commande ROLLBACK est utilisée pour terminer l'ensemble de la transaction en annulant les modifications de données. Si la transaction contient des points de sauvegarde marqués, ROLLBACK TO SAVEPOINT [nom] peut être utilisé pour restaurer la transaction jusqu'au point de sauvegarde spécifié uniquement. Par conséquent, toutes les modifications de données jusqu'au point de sauvegarde spécifié seront supprimées.

Manifestation

Considérez le tableau EMPLOYEES qui est rempli avec les détails des employés nouvellement embauchés au cours du premier trimestre de chaque année. Le personnel de bureau ajoute chaque détail d'employé avec un point de sauvegarde, de manière à restaurer les données défectueuses à tout moment pendant l'activité d'alimentation des données. Notez qu'il conserve les noms des points de sauvegarde identiques aux noms des employés.

INSERT INTO employees (employee_id, first_name, hire_date, job_id, salary, department_id)
VALUES (105, 'Allen',TO_DATE ('15-JAN-2013','SALES',10000,10);

SAVEPOINT Allen;

INSERT INTO employees (employee_id, first_name, hire_date, job_id, salary, department_id)
VALUES (106, 'Kate',TO_DATE ('15-JAN-2013','PROD',10000,20);

SAVEPOINT Kate;

INSERT INTO employees (employee_id, first_name, hire_date, job_id, salary, department_id)
VALUES (107, 'McMan',TO_DATE ('15-JAN-2013','ADMIN',12000,30);

SAVEPOINT McMan;

Supposons que l'opérateur d'alimentation de données se rende compte qu'il a mal saisi le salaire de «Kate» et de «McMan». Il annule la transaction active au point de sauvegarde Kate et entre à nouveau les détails de l'employé pour Kate et McMan.

ROLLBACK TO SAVEPOINT Kate;

INSERT INTO employees (employee_id, first_name, hire_date, job_id, salary, department_id)
VALUES (106, 'Kate',TO_DATE ('15-JAN-2013','PROD',12500,20);

SAVEPOINT Kate;

INSERT INTO employees (employee_id, first_name, hire_date, job_id, salary, department_id)
VALUES (107, 'McMan',TO_DATE ('15-JAN-2013','ADMIN',13200,30);

SAVEPOINT McMan;

Une fois qu'il a terminé la saisie des données, il peut valider l'intégralité de la transaction en émettant COMMIT dans la session en cours.

Lire la cohérence

Oracle maintient la cohérence entre les utilisateurs de chaque session en termes d'accès aux données et d'actions de lecture / écriture.

Lorsqu'un DML se produit sur une table, les valeurs de données d'origine modifiées par l'action sont enregistrées dans les enregistrements d'annulation de la base de données. Tant que la transaction n'est pas validée dans la base de données, tout utilisateur d'une autre session qui interroge ultérieurement les données modifiées affiche les valeurs de données d'origine. Oracle utilise les informations actuelles de la zone globale du système et les informations des enregistrements d'annulation pour créer une vue cohérente en lecture des données d'une table pour une requête. Ce n'est que lorsqu'une transaction est validée que les modifications de la transaction sont rendues permanentes. La transaction est la clé de la stratégie d'Oracle pour assurer la cohérence de lecture.

Le point de départ des vues cohérentes en lecture est généré au nom des lecteurs

Contrôle le moment où les données modifiées peuvent être vues par d'autres transactions de la base de données pour lecture ou mise à jour