Overblog Suivre ce blog
Administration Créer mon blog
16 juin 2012 6 16 /06 /juin /2012 15:25

 

DELETE FROM.

Comment supprimer des lignes dans une table avec la commande SQL DELETE.
Comment utiliser la commande SQL DELETE sur une table Oracle.
Comment supprimer des doublons dans une table avec DELETE.

L'instruction sql delete from est utilisée pour supprimer des lignes existantes dans une table.
DELETE supprime une ou plusieurs lignes dans une table ou vue Oracle.

Vous pouvez supprimer des lignes dans une table, si cette table est dans votre schéma ou si vous avez reçu le privilège objet DELETE sur la table.
Si vous avez le privilège system DELETE ANY TABLE, vous pouvez alors supprimer des lignes de tables dans toutes les tables des schéma.

Respectez les contraintes définies sur la table sous peine d'échec avec l'instruction DELETE.

Syntaxe SQL DELETE FROM TABLE.

 
 DELETE [FROM] [schéma.]Nom_Table [@DBLink] [WHERE (conditions)] ; 

sql-delete-from

Table de test pour les suppressions avec l'instruction DELETE.

 SQL> SELECT * FROM emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------------- ---- ---------- ---------- ---------- ---------- 1 SMITH CLERK 800,5 10 2 ALLEN SALESMAN 4 20/02/1985 1600 300 30 3 WARD SALESMAN 4 22/02/1983 1250 500,56 30 4 JONES MANAGER 02/04/1981 2975 30 5 MARTIN SALESMAN 4 28/09/1987 1250 1400 40 SQL> 

 

Supprimer toutes les lignes d'une table avec DELETE FROM.

Nous ne mettons pas de clause WHERE avec des conditions pour supprimer toutes les lignes d'une table Oracle.
Toules les lignes de la table emp seront supprimées.
Vous pouvez aussi utiliser la commande sql TRUNCATE TABLE pour vider une table de tous ses enregistrements.

 
 SQL> DELETE emp; 5 ligne(s) supprimée(s). ou SQL> DELETE FROM emp; 5 ligne(s) supprimée(s). SQL> SELECT * FROM emp; aucune ligne sélectionnée SQL> 

 


 

DELETE FROM avec la clause WHERE de conditions.

 
 SQL> DELETE FROM emp WHERE deptno=30; 3 ligne(s) supprimée(s). SQL> SELECT * FROM emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------------- ---- ---------- ---------- ---------- ---------- 1 SMITH CLERK 800,5 10 5 MARTIN SALESMAN 4 28/09/1987 1250 1400 40 SQL> 

 


 

DELETE FROM avec une sous-requête dans clause WHERE.

Utilisation de sous-requête pour établir la condition de suppression dans la clause WHERE.
Ici nous supprimons les lignes de la table dont le salaire est supérieure à la moyenne de tous les salaires de la table emp.

 
 SQL> DELETE FROM emp WHERE sal > (SELECT AVG(sal) FROM emp); 2 ligne(s) supprimée(s). SQL> SELECT * FROM emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------------- ---- ---------- ---------- ---------- ---------- 1 SMITH CLERK 800,5 10 3 WARD SALESMAN 4 22/02/1983 1250 500,56 30 5 MARTIN SALESMAN 4 28/09/1987 1250 1400 40 SQL> 

 


 

DELETE FROM avec une liste dans la clause WHERE.

Utilisation d'une liste pour effectuer la suppression de lignes dans une table.
Ici nous supprimons toutes les lignes de la table dont le numéro DEPTNO est 10 ou 20 ou 30.

 
 SQL> DELETE FROM emp WHERE deptno IN(10, 20, 30); 4 ligne(s) supprimée(s). SQL> SELECT * FROM emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------------- ---- ---------- ---------- ---------- ---------- 5 MARTIN SALESMAN 4 28/09/1987 1250 1400 40 SQL> 

 


 

SUPPRESSION DE DOUBLONS AVEC DELETE FROM

Utilisation d'une sous-requête pour supprimer les lignes en doublons sur le numéro DEPTNO.
Ici nous avions 3 lignes dont le DEPTNO etait égale à 30, aprés exécution de l'instruction DELETE suivante, nous avons maintenant une ligne unique dont le numéro DEPTNO est égale à 30.

 
 SQL> DELETE emp WHERE rowid NOT IN (SELECT MAX(rowid) FROM emp GROUP BY deptno); 2 ligne(s) supprimée(s). SQL> SELECT * FROM emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------------- ---- ---------- ---------- ---------- ---------- 1 SMITH CLERK 800,5 10 4 JONES MANAGER 02/04/1981 2975 30 5 MARTIN SALESMAN 4 28/09/1987 1250 1400 40 SQL> 

 

Haut de Page www.dba-ora.fr

 

Liens en rapport avec l'article qui sont susceptibles de vous intéresser
• TRUNCATE TABLE ORACLE
Repost 0
Published by Oracle SQL - dans COMMANDES SQL DML-LMD
commenter cet article
13 mai 2012 7 13 /05 /mai /2012 14:40

 

SQL UPDATE.

Comment utiliser la commande sql UPDATE.
Comment changer des valeurs dans des tables Oracle.
Comment modifier et mettre à jour des données dans Oracle.

L'instruction SQL UPDATE est utilisée pour modifier les lignes existantes dans une table.
UPDATE modifie les valeurs des colonnes spécifiées dans une ou plusieurs lignes d'une table ou vue Oracle.

Vous pouvez modifier des colonnes dans une table, si elle est dans votre schéma ou si vous avez reçu le privilège UPDATE sur la table.
Si vous avez le privilège UPDATE ANY TABLE, vous pouvez alors modifier les colonnes des lignes de toutes les tables des schéma.

Respectez les CONTRAINTES (Unicité, Not NULL, Check,...) définies sur la table sous peine d'échec avec l'instruction UPDATE.

Syntaxe UPDATE table SET.

 
 UPDATE [schéma.] Nom_Table [@DBLink] SET column = expression | (subquery) (column1, column2,...) = (subquery) [WHERE (conditions)]; 

sql-update-table

La table de test pour mise à jour avec la commande UPDATE.

 SQL> SELECT * FROM scott.emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ ---------- --------------- ----- ---------- ---------- ---------- ---------- 1 SMITH CLERK 800,5 10 2 ALLEN SALESMAN 4 20/02/1985 1600 300 30 3 WARD SALESMAN 4 22/02/1983 1250 500,56 30 4 JONES MANAGER 02/04/1981 2975 30 5 MARTIN SALESMAN 4 28/09/1987 1250 1400 40 SQL> 

 

 

SQL UPDATE sur une colonne.

Update d'une colonne avec une valeur numérique.
On oubliera pas la condition de mise à jour dans la clause WHERE, sinon toutes les lignes de la table EMP seront mises à jour avec la nouvelle valeur.

 
 SQL> UPDATE emp SET sal = 800 WHERE empno = 1; 1 ligne mise à jour. SQL> COMMIT; Validation effectuée. SQL> SELECT * FROM emp WHERE empno = 1; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ ---------- --------------- ----- ---------- ---------- ---------- ---------- 1 SMITH CLERK 800 10 SQL> 

 


 

SQL UPDATE sur plusieurs colonnes.

Update sur plusieurs colonnes de table, elles sont séparées par des virgules dans la clause SET.
Dans la commande SQL UPDATE, il est possible d’affecter explicitement à une colonne la valeur par défaut définie sur cette colonne.

 
 SQL> UPDATE emp SET sal = 800, comm = DEFAULT WHERE empno = 1; 1 ligne mise à jour. SQL> COMMIT; Validation effectuée. SQL> SELECT * FROM emp WHERE empno = 1; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ ---------- --------------- ----- ---------- ---------- ---------- ---------- 1 SMITH CLERK 800 0 10 SQL> 

 


 

SQL UPDATE avec des fonctions et opérateurs.

Utilisation de fonctions et opérateurs dans la clause SET de la commande sql UPDATE.

 
 SQL> UPDATE emp SET ename = INITCAP(ename), job = LOWER(job), comm = (sal/2)*1.1 WHERE empno = 1; 1 ligne mise à jour. SQL> COMMIT; Validation effectuée. SQL> SELECT * FROM emp WHERE empno = 1; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ ---------- --------------- ----- ---------- ---------- ---------- ---------- 1 Smith clerk 800,5 440,28 10 SQL> 

 


 

SQL UPDATE avec des valeurs provenant d'une autre table.

Utilisation d'une sous-interrogation pour mettre à jour les départements à partir de la table DEPT, la sous-requête devra impérativement ramener une ligne unique sinon échec de l'instruction UPDATE avec l'erreur ORA-01427.

 
 SQL> UPDATE emp SET deptno = (SELECT deptno FROM dept WHERE dname = 'SALES') WHERE job = 'SALESMAN'; 3 ligne(s) mise(s) à jour. SQL> COMMIT; Validation effectuée. SQL> SELECT * FROM emp WHERE job = 'SALESMAN'; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ ---------- --------------- ----- ---------- ---------- ---------- ---------- 2 ALLEN SALESMAN 4 20/02/1985 1600 300 30 3 WARD SALESMAN 4 22/02/1983 1250 500,56 30 5 MARTIN SALESMAN 4 28/09/1987 1250 1400 30 SQL> SQL> UPDATE emp SET deptno = (SELECT deptno FROM dept) WHERE job = 'SALESMAN'; SET deptno = (SELECT deptno * ERREUR à la ligne 2 : ORA-01427: sous-interrogation ramenant un enregistrement de plus d'une ligne SQL> 

 


 

SQL UPDATE avec des sous-requêtes.

Les sous-requêtes ou sous-interrogation peuvent être utilisées avec la commande UPDATE pour modifier les valeurs d'une ou plusieurs colonnes de tables Oracle.

 
 SQL> UPDATE emp e1 SET comm = (SELECT AVG(NVL(comm,0)) FROM emp e2 WHERE e1.deptno = e2.deptno); 5 ligne(s) mise(s) à jour. SQL> SELECT * FROM emp ; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ ---------- --------------- ----- ---------- ---------- ---------- ---------- 1 SMITH CLERK 800,5 0 10 2 ALLEN SALESMAN 4 20/02/1985 1600 266,85 30 3 WARD SALESMAN 4 22/02/1983 1250 266,85 30 4 JONES MANAGER 02/04/1981 2975 266,85 30 5 MARTIN SALESMAN 4 28/09/1987 1250 1400 40 SQL> SQL> UPDATE emp e1 SET (comm,sal) = (SELECT AVG(NVL(comm,0)), MAX(sal) FROM emp e2 WHERE e1.deptno = e2.deptno); 5 ligne(s) mise(s) à jour. SQL> SELECT * FROM emp ; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ ---------- --------------- ----- ---------- ---------- ---------- ---------- 1 SMITH CLERK 800,5 0 10 2 ALLEN SALESMAN 4 20/02/1985 2975 266,85 30 3 WARD SALESMAN 4 22/02/1983 2975 266,85 30 4 JONES MANAGER 02/04/1981 2975 266,85 30 5 MARTIN SALESMAN 4 28/09/1987 1250 1400 40 SQL> 

 

Haut de Page www.dba-ora.fr

 

Liens en rapport avec l'article et qui sont susceptibles de vous intéresser
• Update Table avec MERGE INTO
Repost 0
Published by Oracle SQL - dans COMMANDES SQL DML-LMD
commenter cet article
11 mai 2012 5 11 /05 /mai /2012 17:02

 

LANGAGE SQL ORACLE.

A l'origine, c'est IBM Corporation qui a développé le langage SEQUEL (Structured English Query Language) qui est devenu ensuite SQL (Structured Query Language).
SQL est aujourd'hui le langage standard des RDBMS (Relational Database Management System).

Les commandes SQL sont simples d'utilisation mais ont une syntaxe spécifique.
Le langage SQL est non procédural, il est simple et puissant et permet de :
- Interroger des données.
- Insertion, mise à jour, et suppression de lignes dans une table.
- Création, remplacement, modification et suppression des objets.
- Contrôler l'accès à la base de données et ses objets.
- Garantir la cohérence et l'intégrité de la base de données.

TYPES DE COMMANDES SQL.

Les commandes SQL peuvent être classées par catégories comme indiqué ci-dessous.

 

  • • DDL - DATA DEFINITION LANGUAGE.

  • Les commandes SQL du type DDL ont pour rôle d'effectuer différentes tâches dans une base de données Oracle.
    - Créer des objets de schéma.
    - Modifier des objets de schéma.
    - Supprimer des objets de schéma.
    - Accorder des privilèges et des rôles.
    - Révoquer des privilèges et des rôles.
    - Analyse de table, index, cluster.
    - Mise en place d'Audit.

    Oracle COMMIT implicitement les transactions courantes avant et après chaque exécution d'une commande SQL DDL.
    On utilisera le package DBMS_SQL dans PL/SQL pour utiliser les commandes DDL.

    • LANGAGE SQL DDL - DATA DEFINITION LANGUAGE.

    - Toutes les commandes Alter (Objects).
    - Toutes les commandes Create.
    - Toutes les commandes Drop.
    - Toutes les commandes Flashback.
    - Purge.
    - Truncate.
    - Grant.
    - Revoke.
    - Audit, NoAudit.
    - Comment.

 


 

  • • DML - DATA MANIPULATION LANGUAGE.

  • Les commandes SQL du type DML ont pour rôle la manipulation des données dans des objets de schéma existants d'une base de données Oracle.
    - Sélection de données.
    - Insertion de données.
    - Modification de données.
    - Suppression de données.
    - Manipulation table.

    • LANGAGE SQL DML - DATA MANIPULATION LANGUAGE.

    - Select.
    - Insert.
    - Update.
    - Delete.
    - Merge.
    - Call.
    - Explain Plan.
    - Lock Table.

    La commande SQL Select peut être considérée comme une commande DML, mais elle ne permet pas de manipuler des données dans une base Oracle.



 

  • • TRANSACTION CONTROL LANGUAGES.

  • Les commandes SQL du type TRANSACTION CONTROL LANGUAGES ont pour rôle de contrôler, valider et annuler les transactions effectuées par les commandes DML d'une base de données Oracle.
    - Validation de transaction.
    - Annulation de transaction.
    - Point d'annulation de transaction.

    • LANGAGE SQL TRANSACTION CONTROL LANGUAGES.

    - Commit.
    - Rollback.
    - Savepoint.
    - Set Transaction.

 


 

  • • SESSION CONTROL LANGUAGES.

  • Les commandes SQL du type SESSION CONTROL LANGUAGES ont pour rôle de modifier les propriétés d'une session dans une base de données Oracle.
    - Propriétés de Session.
    - Activation et désactivation de Rôle.

    • LANGAGE SQL SESSION CONTROL LANGUAGES.

    - Alter Session.
    - Set Role.


 

  • • SYSTEM CONTROL LANGUAGES.

  • Une seule instruction dans cette catégorie, la commande SQL du type SYSTEM CONTROL LANGUAGES est utilisée pour gérer les paramètres de l'instance Oracle.
    - propriétés de la base de données.

    • LANGAGE SQL SYSTEM CONTROL LANGUAGES.

    - Alter System.

 

Haut de Page www.dba-ora.fr

Liens en rapport avec l'article et qui sont susceptibles de vous intéresser
Commandes SQL Oracle.
Repost 0
Published by Oracle SQL - dans COMMANDES SQL DML-LMD
commenter cet article
15 avril 2012 7 15 /04 /avril /2012 16:06

 

commande sql oracleCOMMANDES SQL ORACLE.

Les commandes sql oracle listées ci-dessous sont toutes accompagnées d'exemple d'utilisation testés sous Oracle 10G.

 

COMMANDES SQL DDL - DATA DEFINITION LANGUAGE.

  1. CREATE DATABASE.
  2. CREATE TABLESPACE.
  3. CREATE USER.
  4. CREATE SCHEMA.
  5. CREATE TABLE.
  6. CREATE EXTERNAL TABLE.
  7. CREATE SYNONYM.
  8. CREATE VIEW.
  9. CREATE ROLE.
  10. CREATE SEQUENCE.
  11. CREATE DIRECTORY.
  12. ALTER TABLESPACE.
  13. ALTER USER.
  14. ALTER TABLE MODIFY.
  15. ALTER TABLE ADD COLUMN.
  16. ALTER TABLE RENAME COLUMN.
  17. ALTER TABLE RENAME TO.
  18. ALTER TABLE DROP COLUMN.
  19. ALTER SEQUENCE.
  20. ALTER VIEW.
  21. DROP DATABASE.
  22. DROP TABLESPACE.
  23. DROP TABLE.
  24. DROP SEQUENCE.
  25. DROP SYNONYM.
  26. PURGE TABLE.
  27. TRUNCATE TABLE.
  28. GRANT.
  29. REVOKE.
  30. AUDIT.

COMMANDES SQL DML - DATA MANIPULATION LANGUAGE..

  1. SELECT FROM.
  2. INSERT INTO.
  3. INSERT ALL.
  4. MERGE INTO.
  5. UPDATE.
  6. DELETE.
  7. EXPLAIN PLAN.

COMMANDES SQL SYSTEM CONTROL LANGUAGES.

  1. ALTER SYSTEM KILL / DISCONNECT SESSION.
  2. ALTER SYSTEM SWITCH LOGFILE.
  3. ALTER SYSTEM ENABLE RESTRICTED SESSION.
  4. ALTER SYSTEM SET JOB_QUEUE_PROCESSES.
  5. ALTER SYSTEM SET ARCHIVE_LAG_TARGET.
  6. ALTER SYSTEM SET DB_FILE_MULTIBLOCK_READ_COUNT.
  7. ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE.

COMMANDES SQL SESSION CONTROL LANGUAGES.

  1. ALTER SESSION SET RECYCLEBIN.

COMMANDES SQL TRANSACTION CONTROL LANGUAGES.

  1. COMMIT - ROLLBACK - SAVEPOINT.

















































Repost 0
Published by Daniel Roesch - dans COMMANDES SQL DML-LMD
commenter cet article
3 août 2011 3 03 /08 /août /2011 13:50

 

Insérer des lignes dans plusieurs tables avec la commande SQL INSERT ALL.

Comment insérer des enregistrements dans plusieurs tables Oracle. ?

Inserting into Multiple Tables.

L'instruction DML INSERT ALL est utilisée pour ajouter des lignes dans plusieurs tables.
Les lignes peuvent être ajoutées avec des valeurs de données spécifiques, ou alors elles peuvent être créés à partir d'une sous-requête.

Syntaxe INSERT ALL multiple TABLES.

 
 INSERT [ALL | FIRST] WHEN condition THEN INTO [ schéma ]. table [ (column1 [, column2 ]...) ] VALUES ({ expr1 | DEFAULT } [, { expr2 | DEFAULT } ]...) WHEN condition THEN INTO [ schéma ]. table [ (column1 [, column2 ]...) ] VALUES ({ expr1 | DEFAULT } [, { expr2 | DEFAULT } ]...) ... SELECT column1 [, column2 ]... FROM table; 

sql insert all multiple tables

CREATION ENVIRONNEMENT DE TEST (tables Oracle).

 
 C:\>SET ORACLE_SID=DBTEST C:\>SQLPLUS scott/tiger SQL*Plus: Release 10.2.0.1.0 - Production on Mer. Ao¹t 3 14:29:49 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connecté à : Oracle Database 10g Release 10.2.0.1.0 - Production SQL> CREATE TABLE SCOTT.VENTES ( ID_VENTES NUMBER(10), ID_MARQUES VARCHAR2(15), ID_VENDEUR NUMBER(5), PRIX NUMBER(10,2) ); Table créée. SQL> Insert into SCOTT.VENTES Values (1, 'RENAULT', 10, 18000.99); Insert into SCOTT.VENTES Values (2, 'RENAULT', 11, 16500); Insert into SCOTT.VENTES Values (3, 'VOLVO', 12, 24000); Insert into SCOTT.VENTES Values (4, 'AUDI', 10, 34000); Insert into SCOTT.VENTES Values (5, 'VOLVO', 13, 19000); SQL> COMMIT; Validation effectuée. SQL> CREATE TABLE SCOTT.VOLVO ( ID_VENDEUR NUMBER(5), PRIX NUMBER(10,2) ); Table créée. SQL> CREATE TABLE SCOTT.RENAULT ( ID_VENDEUR NUMBER(5), PRIX NUMBER(10,2) ); Table créée. SQL> CREATE TABLE SCOTT.AUDI ( ID_VENDEUR NUMBER(5), PRIX NUMBER(10,2) ); Table créée. SQL> SELECT * FROM SCOTT.VENTES; ID_VENTES ID_MARQUES ID_VENDEUR PRIX ---------- --------------- ---------- ---------- 1 RENAULT 10 18000,99 2 RENAULT 11 16500 3 VOLVO 12 24000 4 AUDI 10 34000 5 VOLVO 13 19000 

 

Insertion d'enregistrement dans plusieurs table avec INSERT ALL.

Important :
- le nombre de champs déclaré dans l'instruction INSERT INTO doit être égale au nombre de champs déclaré dans VALUES.
- si tous les champs de la table sont déclarés dans VALUES alors il n'est pas obligatoire de déclarer les colonnes.
- Respectez les CONTRAINTES (Unicité, Not NULL, Check,...) définies sur la table sous peine d'échec avec l'instruction INSERT INTO.

Ici nous allons à partir de la table VENTES insérer des enregistrements dans les tables RENAULT, VOLVO, AUDI.

 
 SQL> INSERT ALL 2 WHEN id_marques='RENAULT' THEN 3 INTO RENAULT(id_vendeur, prix) 4 VALUES(id_vendeur, prix) 5 WHEN id_marques='VOLVO' THEN 6 INTO VOLVO(id_vendeur, prix) 7 VALUES(id_vendeur, prix) 8 WHEN id_marques='AUDI' THEN 9 INTO AUDI(id_vendeur, prix) 10 VALUES(id_vendeur, prix) 11 SELECT id_marques ,id_vendeur, prix 12 FROM ventes; 5 ligne(s) créée(s). SQL> SELECT * FROM SCOTT.RENAULT; ID_VENDEUR PRIX ---------- ---------- 10 18000,99 11 16500 SQL> SELECT * FROM SCOTT.AUDI; ID_VENDEUR PRIX ---------- ---------- 10 34000 SQL> SELECT * FROM SCOTT.VOLVO; ID_VENDEUR PRIX ---------- ---------- 12 24000 13 19000 SQL> 

Si vous spécifiez ALL la valeur par défaut, Oracle évalue chaque clause WHEN indépendamment des résultats de l'évaluation de toute autre clause WHEN. Pour chaque clause WHEN dont la condition est évaluée à vrai, la base de données exécute l'INSERT dans la liste de la clause.
Si vous spécifiez FIRST Oracle évalue chaque clause WHEN dans l'ordre dans lequel il apparaît dans la déclaration. A la première clause WHEN qui renvoie true, Oracle exécute la clause INTO correspondante et saute ultérieurement les autres clauses pour la ligne donnée.

 

SUPPRESSION ENVIRONNEMENT DE TEST (tables Oracle).

 
 SQL> DROP TABLE SCOTT.VENTES CASCADE CONSTRAINTS PURGE; Table supprimée. SQL> DROP TABLE SCOTT.VOLVO CASCADE CONSTRAINTS PURGE; Table supprimée. SQL> DROP TABLE SCOTT.RENAULT CASCADE CONSTRAINTS PURGE; Table supprimée. SQL> DROP TABLE SCOTT.AUDI CASCADE CONSTRAINTS PURGE; Table supprimée. SQL> 

 

Haut de Page www.dba-ora.fr

 

Liens en rapport avec l'article et qui sont susceptibles de vous intéresser
• SQL INSERT INTO TABLE ORACLE
• CREATE TABLE ORACLE
• Update Table avec MERGE INTO
• Instruction SQL SELECT FROM WHERE
• Oracle SQL SOUS-REQUETES SELECT IN, ANY, ALL
• Instruction SQL GROUP BY
Repost 0
Published by Daniel Roesch - dans COMMANDES SQL DML-LMD
commenter cet article
20 juillet 2011 3 20 /07 /juillet /2011 21:38

 

Insérer des lignes dans table avec commande SQL INSERT INTO.

Comment insérer des enregistrements dans une table Oracle.
Comment ajouter des lignes dans une table Oracle.

L'instruction DML INSERT INTO TABLE est utilisée pour ajouter des lignes à une ou plusieurs tables.
Les lignes peuvent être ajoutées avec des valeurs de données spécifiques, ou alors elles peuvent être créés à partir d'une sous-requête.

Pour pouvoir insérer des enregistrements dans une table, il faut que cette dernière soit dans votre schéma sauf si vous avez reçu le privilège INSERT sur la table.
Si vous avez le privilège INSERT ANY TABLE, vous pouvez ajouter des données dans toutes les tables de tout schéma.

Syntaxe INSERT INTO TABLE mono-ligne.

 
 INSERT INTO [ schéma ]. table [ (column1 [, column2 ]...) ] VALUES ({ expr1 | DEFAULT } [, { expr2 | DEFAULT } ]...) 

sql-insert-into-table

Création environnement test insert into (tables et séquence Oracle)

 
 C:\>SET ORACLE_SID=DBTEST C:\>SQLPLUS scott/tiger SQL*Plus: Release 10.2.0.1.0 - Production on Jeu. Juil. 21 14:09:34 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. Connecté à : Oracle Database 10g Release 10.2.0.1.0 - Production SQL> CREATE TABLE SCOTT.JOUEUR ( ID_JOUEUR NUMBER(5) NOT NULL, J_NOM VARCHAR2(10), J_DT_NAIS DATE, J_NIVEAU NUMBER(1) DEFAULT 0 ); Table créée. SQL> CREATE SEQUENCE SCOTT.SEQ_ID_JOUEUR START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 20000 NOCACHE NOCYCLE NOORDER; Séquence créée. SQL> CREATE TABLE SCOTT.NIVEAU ( NB_JOUEUR NUMBER(5) NOT NULL, N_NIVEAU NUMBER(1) DEFAULT 0 ); Table créée. 

 

 

INSERT INTO TABLE avec numéro de SEQUENCE Oracle.

Important :
- le nombre de champs déclaré dans l'instruction INSERT INTO doit être égale au nombre de champs déclaré dans VALUES.
- si tous les champs de la table sont déclarés dans VALUES alors il n'est pas obligatoire de déclarer les colonnes.
- Respectez les CONTRAINTES (Unicité, Not NULL, Check,...) définies sur la table sous peine d'échec avec l'instruction INSERT INTO.

 
 SQL> INSERT INTO scott.joueur (id_joueur, j_nom, j_dt_nais, j_niveau) VALUES (scott.seq_id_joueur.nextval,'Pierre','01/05/1972',4); 1 ligne créée. SQL> SELECT * FROM scott.joueur; ID_JOUEUR J_NOM J_DT_NAIS J_NIVEAU ---------- ---------- ---------- ---------- 1 Pierre 01/05/1972 4 

Pour insérer l'ID du joueur, nous avons créé précédemment une séquence oracle SEQ_ID_JOUEUR (équivalent à une auto-incrémentation. Voir lien en fin d'article).
L'obtention d'un ID se fait avec la commande NEXTVAL, ce qui nous donne SEQ_ID_JOUEUR.NEXTVAL pour la colonne id_joueur.

 

SQL INSERT INTO en déclarant quelques colonnes seulement.

 
 SQL> INSERT INTO scott.joueur (id_joueur, j_nom, j_dt_nais) VALUES (scott.seq_id_joueur.nextval,'Jean','20/08/1975'); 1 ligne créée. SQL> SELECT * FROM scott.joueur; ID_JOUEUR J_NOM J_DT_NAIS J_NIVEAU ---------- ---------- ---------- ---------- 2 Jean 20/08/1975 0 

Ici nous avons omis volontairement la colonne J_NIVEAU dans l'instruction INSERT INTO, car celle ci a une valeur par défaut. La valeur par défaut du champ est automatiquement insérée si celui ci n’apparaît pas dans l'instruction INSERT INTO.
Si des champs acceptant la valeur NULL sont omis alors une valeur NULL leur sont affectée.
Si des champs n'acceptant pas de valeur NULL sont omis et n'ont pas de valeur par défaut attribuée alors l'instruction sql INSERT INTO échoue.

 

SQL INSERT INTO avec des fonctions Oracle et Opérateurs.

 
 SQL> INSERT INTO scott.joueur VALUES (scott.seq_id_joueur.nextval,INITCAP('stephane'),to_date('15/03/1972','dd/mm/yyyy'),2+1); 1 ligne créée. SQL> SELECT * FROM scott.joueur; ID_JOUEUR J_NOM J_DT_NAIS J_NIVEAU ---------- ---------- ---------- ---------- 3 Stephane 15/03/1972 3 

Nous pouvons intégrer des fonctions et opérateurs dans les instructions sql INSERT INTO.
Ici la fonction INITCAP qui permet de convertir le premier caractère de chaque mot en une Majuscule.
La fonction TO_DATE qui permet de formater vos dates par rapport à vos NLS_LANG.
Une opération avec l'opérateur +.
Notez qu'ici nous n'avons déclaré aucun champ de destination car nous mettons à jour toutes les colonnes dans VALUES.

 

SQL INSERT INTO avec le mot clef DEFAULT.

 
 SQL> INSERT INTO scott.joueur VALUES (scott.seq_id_joueur.nextval,'Daniel','10/05/1972',DEFAULT); 1 ligne créée. SQL> SELECT * FROM scott.joueur; ID_JOUEUR J_NOM J_DT_NAIS J_NIVEAU ---------- ---------- ---------- ---------- 4 Daniel 10/05/1972 0 

Le mot clef DEFAULT permet de mettre la valeur par défaut pour ce champ, ici DEFAULT = 0.

 

SQL INSERT INTO avec des valeurs NULL.

 
 SQL> INSERT INTO scott.joueur VALUES (scott.seq_id_joueur.nextval,'Sandrine',NULL,2); OU SQL> INSERT INTO scott.joueur VALUES (scott.seq_id_joueur.nextval,'Sandrine','',2); 1 ligne créée. SQL> SELECT * FROM scott.joueur; ID_JOUEUR J_NOM J_DT_NAIS J_NIVEAU ---------- ---------- ---------- ---------- 5 Sandrine   2 

Le mot clef NULL ou '' permet d’insérer une valeur null dans le champ si celui ci le permet biensur.

 

SQL INSERT INTO avec SUBQUERY (sous-requêtes) provenant d'une autre table.

 
 SQL> INSERT INTO scott.niveau(nb_joueur, n_niveau) 2 SELECT COUNT(*), 3 j_niveau 4 FROM scott.joueur 5 GROUP BY j_niveau ; 4 ligne(s) créée(s). SQL> SELECT * FROM scott.niveau; NB_JOUEUR N_NIVEAU ---------- ---------- 1 2 1 4 1 3 2 0 SQL> SELECT * FROM scott.joueur; ID_JOUEUR J_NOM J_DT_NAIS J_NIVEAU ---------- ---------- ---------- ---------- 1 Pierre 01/05/1972 4 2 Jean 20/08/1975 0 3 Stephane 15/03/1972 3 4 Daniel 10/05/1972 0 5 Sandrine 2 SQL> 

Ici nous avons inseré dans la table NIVEAU avec INSERT INTO le nombre de joueur par niveau provenant de la table JOUEUR.

 

SUPPRESSION ENVIRONNEMENT DE TEST (tables et séquence Oracle).

 
 SQL> DROP TABLE SCOTT.NIVEAU CASCADE CONSTRAINTS PURGE; Table supprimée. SQL> DROP TABLE SCOTT.JOUEUR CASCADE CONSTRAINTS PURGE; Table supprimée. SQL> DROP SEQUENCE SCOTT.SEQ_ID_JOUEUR; Séquence supprimée. SQL> 

 

Haut de Page www.dba-ora.fr

 

Liens en rapport avec l'article et qui sont susceptibles de vous intéresser
• CREATE TABLE ORACLE
• Update Table avec MERGE INTO
• Instruction SQL SELECT FROM WHERE
• Oracle SQL SOUS-REQUETES SELECT IN, ANY, ALL
• Instruction SQL GROUP BY
Repost 0
Published by Daniel Roesch - dans COMMANDES SQL DML-LMD
commenter cet article
14 juillet 2011 4 14 /07 /juillet /2011 13:33

 

Voir, Lister les Objects et Users d'une TRANSACTION active Oracle.

Comment voir les transactions actives dans ma base Oracle ?.
Comment lister les Objets impliqués dans mes transactions actives dans ma base Oracle ?.
Comment lister les Users impliqués dans mes transactions actives dans ma base Oracle ?.

Toutes les transactions actives, c'est à dire en attente de validation COMMIT ou ROLLBACK sont listées dans la vue dynamique V$TRANSACTION.

Une transaction est une unité logique de travail qui contient une ou plusieurs instructions SQL.
Une transaction est une unité atomique et se termine quand elle est validée ou annulée, soit explicitement par une instruction COMMIT ou ROLLBACK ou alors implicitement quand une instruction DDL est exécutée.

 

Voir et Lister les transactions actives.

 
 C:\>set oracle_sid=dbtest C:\>sqlplus /nolog SQL*Plus: Release 10.2.0.1.0 - Production on Thu Jul 14 14:04:23 2011 Copyright (c) 1982, 2005, Oracle. All rights reserved. SQL> connect / as sysdba Connected. SQL> alter session set NLS_DATE_FORMAT = 'mm-dd-yyyy HH24:mi:ss'; Session altered. SQL> SQL> SELECT t.addr, 2 t.status, 3 t.start_scn, 4 t.start_date "DEBUT TRANSACTION" 5 FROM V$TRANSACTION t; ADDR STATUS START_SCN DEBUT TRANSACTION -------- ---------------- ---------- ------------------- 216077E0 ACTIVE 7624010 07-14-2011 13:21:26 21623E14 ACTIVE 7621984 07-14-2011 12:16:35 SQL> 

 

Voir et Lister les USERS de mes transactions actives.

 
 SQL> SET LINESIZE 100 SQL> alter session set NLS_DATE_FORMAT = 'mm-dd-yyyy HH24:mi:ss'; Session altered. SQL> SELECT s.sid, 2 s.username, 3 s.logon_time, 4 t.start_date "DEBUT TRANSACTION" 5 FROM V$TRANSACTION t INNER JOIN V$SESSION s ON (t.addr = s.taddr); SID USERNAME LOGON_TIME DEBUT TRANSACTION ---------- ------------------------------ ------------------- ------------------- 146 SYSADM 07-14-2011 13:18:40 07-14-2011 13:21:26 155 SCOTT 07-14-2011 11:11:10 07-14-2011 12:16:35 SQL> 

 

Voir et Lister les TABLES impliquées dans mes transactions actives.

 
 SQL> alter session set NLS_DATE_FORMAT = 'mm-dd-yyyy HH24:mi:ss'; Session altered. SQL> SET LINESIZE 130 SQL> COL USERNAME FORMAT A10 SQL> SELECT s.sid, 2 s.username, 3 s.logon_time, 4 t.start_date "DEBUT TRANSACTION", 5 o.name "OBJECT NAME" 6 FROM V$TRANSACTION t INNER JOIN V$SESSION s ON (t.addr = s.taddr) 7 INNER JOIN V$LOCK lo ON (s.sid = lo.sid) 8 INNER JOIN sys.OBJ$ o ON (lo.id1 = o.obj#); SID USERNAME LOGON_TIME DEBUT TRANSACTION OBJECT NAME ---------- ---------- ------------------- ------------------- ----------------------- 155 SCOTT 07-14-2011 11:11:10 07-14-2011 12:16:35 SOCIETE 155 SCOTT 07-14-2011 11:11:10 07-14-2011 12:16:35 T_CLIENT 155 SCOTT 07-14-2011 11:11:10 07-14-2011 12:16:35 T_CONTRAT 146 SYSADM 07-14-2011 13:18:40 07-14-2011 13:21:26 PAYS 4 rows selected. SQL> 

 

Liens en rapport avec l'article et qui sont susceptibles de vous intéresser
• Vues V$ du Dictionnaire de données Oracle
• SCN System Change Number Oracle
Repost 0
Published by Daniel Roesch - dans COMMANDES SQL DML-LMD
commenter cet article
18 juin 2011 6 18 /06 /juin /2011 11:27

 

Commande Oracle SQL SELECT / FROM / WHERE.

Comment interroger, extraire des enregistrements d’une table avec l'ordre SELECT ?.

Nous allons voir très rapidement des instructions SELECT basiques.

SELECT permet d’extraire une ou plusieurs colonnes.
FROM permet de préciser le ou les tables ou sont stockées les valeurs.
WHERE permet de restreindre la recherche avec des conditions et restrictions.

 

SYNTAXE ORDRE SELECT BASIQUE

 
 SELECT [ { { DISTINCT | UNIQUE } | ALL } ] [ schema. ] { table | view | materialized view } .* | column | expr [ [ AS ] c_alias ] FROM { table1 [, table2 ]...} [ WHERE conditions ] 

 

SELECT avec toutes les colonnes et lignes de la table EMP.

 
 SQL> COLUMN empno FORMAT 9999 SQL> COLUMN ename FORMAT A7 SQL> COLUMN mgr FORMAT 9999 SQL> COLUMN sal FORMAT 9999 SQL> COLUMN comm FORMAT 9999 SQL> SELECT * FROM emp; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ------- --------- ----- ---------- ----- ----- ---------- 7369 SMITH CLERK 7902 17/12/1980 800 20 7499 ALLEN SALESMAN 7698 20/02/1981 1600 300 30 7521 WARD SALESMAN 7698 22/02/1981 1250 500 30 7566 JONES MANAGER 7839 02/04/1981 2975 20 7654 MARTIN SALESMAN 7698 28/09/1981 1250 1400 30 7698 BLAKE MANAGER 7839 01/05/1981 2850 30 7782 CLARK MANAGER 7839 09/06/1981 2450 10 7788 SCOTT ANALYST 7566 19/04/1987 3000 20 7839 KING PRESIDENT 17/11/1981 5000 10 7844 TURNER SALESMAN 7698 08/09/1981 1500 0 30 7876 ADAMS CLERK 7788 23/05/1987 1100 20 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ------- --------- ----- ---------- ----- ----- ---------- 7900 JAMES CLERK 7698 03/12/1981 950 30 7902 FORD ANALYST 7566 03/12/1981 3000 20 7934 MILLER CLERK 7782 23/01/1982 1300 10 14 rows selected. SQL> 

 

SELECT de toutes les colonnes avec une condition (restriction) dans clause WHERE.

 
 SQL> SELECT * FROM emp WHERE deptno=20; EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ----- ------- --------- ----- ---------- ----- ----- ---------- 7369 SMITH CLERK 7902 17/12/1980 800 20 7566 JONES MANAGER 7839 02/04/1981 2975 20 7788 SCOTT ANALYST 7566 19/04/1987 3000 20 7876 ADAMS CLERK 7788 23/05/1987 1100 20 7902 FORD ANALYST 7566 03/12/1981 3000 20 SQL> 

 

SELECT colonnes Nom, Métier, et salaire avec une condition (restriction) dans clause WHERE.

 
 SQL> SELECT ename, job, sal FROM emp WHERE sal >= 2975; ENAME JOB SAL ------- --------- ----- JONES MANAGER 2975 SCOTT ANALYST 3000 KING PRESIDENT 5000 FORD ANALYST 3000 SQL> 

 

SELECT avec Concaténation de colonnes et utilisation de AS alias pour nommer une colonne .

 
 SQL> COLUMN "Concat Colonnes" FORMAT A55 SQL> SELECT ename 2 || ' fait parti du dept N ' 3 || deptno 4 || ', son salaire est de ' 5 || sal AS "Concat Colonnes", 6 job AS "Emploi" 7 FROM emp 8 WHERE sal >= 2975; Concat Colonnes Emploi ------------------------------------------------------- --------- JONES fait parti du dept N 20, son salaire est de 2975 MANAGER SCOTT fait parti du dept N 20, son salaire est de 3000 ANALYST KING fait parti du dept N 10, son salaire est de 5000 PRESIDENT FORD fait parti du dept N 20, son salaire est de 3000 ANALYST SQL> 

 

SELECT FROM DUAL ou Interrogation de la Pseudo-table DUAL.

La table DUAL est une table utilisable par tous (en lecture seulement) et qui appartient à l’utilisateur SYS.
L’interrogation de DUAL est utile pour évaluer une expression.

 
 SQL> SELECT (2000*(10-2.5)) AS "Resultat" FROM DUAL; Resultat ---------- 15000 SQL> SELECT sysdate AS "Ma date" FROM DUAL; Ma date ---------- 18/06/2011 SQL> 

 

 

Liens en rapport avec l'article et qui sont susceptibles de vous intéresser
• Update conditionnées de Tables avec MERGE INTO
• Oracle SQL GROUP BY ROLLUP
• Oracle SQL GROUP BY CUBE
Repost 0
Published by Daniel Roesch - dans COMMANDES SQL DML-LMD
commenter cet article
7 novembre 2010 7 07 /11 /novembre /2010 16:23

 

UPDATE conditionnées de tables avec l'instruction Sql MERGE INTO.

Comment mettre à jour (UPDATE) une table cible et insérer (INSERT) des lignes à partir de données situées dans une table source ?
Avec l'instruction SQL MERGE INTO qui extrait des enregistrements d’une table source afin de mettre à jour (UPDATE) ou d’insérer (INSERT) des données dans une table cible.
La mise à jour et l’insertion dans la table cible est conditionné par la clause ON. Pour chaque enregistrement de la table cible qui vérifie la condition, l’enregistrement correspondant de la table source est modifié (UPDATE). Les données de la table cible qui ne vérifient pas la condition, déclenchent une insertion dans la table cible, basée sur des valeurs d’enregistrements de la table source.

 

Syntax SQL MERGE INTO.

 
 MERGE [ hint ] INTO [ schema. ] { table | view } [ t_alias ] USING [ schema. ] { table | view | subquery }[ t_alias ] ON ( condition ) [ WHEN MATCHED THEN UPDATE SET column = { expr | DEFAULT } [, column = { expr | DEFAULT } ]... [ WHERE condition ] [ DELETE WHERE condition ]] [ WHEN NOT MATCHED THEN INSERT [ (column [, column ]...) ] VALUES ({ expr [, expr ]... | DEFAULT }) [ WHERE condition ]]; 

 

Création de la table SALAIRE + INSERT Statement.

 
 CREATE TABLE SALAIRE ( ID_SAL INTEGER NOT NULL, SAL_MONTANT NUMBER(6,2) NOT NULL, SAL_PRIME NUMBER(6,2) ) NOLOGGING NOCOMPRESS NOCACHE NOPARALLEL NOMONITORING; INSERT INTO SALAIRE Values(1,1300,0); INSERT INTO SALAIRE Values(2,1500,0); INSERT INTO SALAIRE Values(3,2300,0); INSERT INTO SALAIRE Values(4,2870,0); INSERT INTO SALAIRE Values(5,3455,0); INSERT INTO SALAIRE Values(6,1499,0); INSERT INTO SALAIRE Values(7,2100,0); 

 

Création de la table PRIME + INSERT Statement.

 
 CREATE TABLE PRIME ( ID_PRIM INTEGER NOT NULL, ID_SAL INTEGER NOT NULL, SAL_PRIME NUMBER(6,2) ) NOLOGGING NOCOMPRESS NOCACHE NOPARALLEL NOMONITORING; INSERT INTO PRIME Values(10,1,35); INSERT INTO PRIME Values(11,2,40); INSERT INTO PRIME Values(12,3,100); INSERT INTO PRIME Values(13,4,150); INSERT INTO PRIME Values(14,5,250); 

 

UPDATE avec MERGE INTO des primes dans la table salaire..

 

SQL> MERGE INTO SALAIRE A
USING (SELECT ID_SAL,SAL_PRIME FROM PRIME) B
ON (A.ID_SAL = B.ID_SAL)
WHEN MATCHED THEN UPDATE SET A.SAL_PRIME = B.SAL_PRIME ;

ID_SAL SAL_MONTANT SAL_PRIME
1 1300 35
2 1500 40
3 2300 100
4 2870 150
5 3455 250
6 1499 0
7 2100 0

 



Repost 0
Published by Daniel Roesch - dans COMMANDES SQL DML-LMD
commenter cet article