Class 2.1 Statements in Oracle DML

Statements in Oracle DML

In this session that we are going to see about statements in oracle DML (Data Manipulation Language). So it's better to read the post of Class 2 Statements in Oracle DDL, after read this article.

DML => Data Manipulation Language.

Difference Between statements in Oracle DDL and DML

Data Define Language	Data Manipulation Language
It is used to build the structure for the table	It is used to change or add the data in the form of records
It is an Auto Commit Statement, so wherever we use DDL it will get committed and save the transaction.	It is a Manual Commit Statement, so manually we should commit the statements else it will rollback to the implicit save point.
As per the Performance wise it has more speed than DML.	Performance wise DML is slow because it makes the transaction to store in undo table space until we giving commit for that particular transaction
If any changes happen it will affect the structure of the table.	If any changes happen it means it will affect only records in the table, won't affect the structure of the table.

Statements in ORACLE Data Manipulation Language

In Data manipulation language is used to manipulated the data by using the following below statements such as:

INSERT UPDATE DELETE MERGE

Shortcuts to Remember Data manipulation Language Statements in Oracle

INSERT COMMAND

• It is used to Insert a records in a table

• Syntax: INSERT INTO table_name column_name VALUES (data in comma separated for each column)

FOR EXAMPLE:

As we already know DDL (Data Define Language) in previous Class. By the continuation recalling the concepts once again in this example

—-- DDL statement

CREATE TABLE orders (id NUMBER

  , customer_name VARCHAR2(50)

  , total_qty NUMBER);

—--- DML statement

As we know the table name and column name

INSERT INTO orders (id, customer_name, total_qty) VALUES (1, ‘Krishna’, 45);

1 row created.

It’s not mandatory to mention the column name during inserting the value, it only mandatory when you are loading data in PL SQL.

INSERT INTO orders VALUES (2, ‘Bala’, 23);

1 row created

Output:

Another method to Insert multiple data: using PLSQL block

BEGIN

    INSERT INTO orders VALUES (1, 'Krishna');

    INSERT INTO orders VALUES (2, 'Bala');

COMMIT;        ------- Mandatory

END;

/                    ------ Mandatory

To know more about this structure, already there is a content for PLSQL basics

UPDATE COMMAND

• It is used to update a records in a table

• Syntax: UPDATE table_name

SET column_name = newvalue

WHERE column_name = refvalue    ← (condition)

FOR EXAMPLE

To update orders table by using this command, update the total quantity as 100 for ID = 1 

UPDATE orders

SET total_qty = 100

WHERE id = 1;

DELETE COMMAND

• It is used to delete a particular records in a table using where condition or else we can delete all the records in a table

• Syntax: DELETE FROM table_name

                           WHERE conditions

FOR EXAMPLE

To delete particular records in a orders table.

DELETE FROM orders

WHERE id = 2;

NOTE: "FROM " keyword is optional to mention in the delete syntax of DML statements in oracle.

MERGE COMMAND

• It is used to merge records in two or more tables using the INSERT and UPDATE command.

• Syntax: MERGE INTO newtable T2

USING basetable T1

ON (join condition)

WHEN NOT MATCHED THEN

INSERT VALUES (T1.columns, T1.columns)

WHEN MATCHED THEN

UPDATE 

SET column_name = T1.columns;

FOR EXAMPLE

Practical:

CREATE TABLE m1 (id NUMBER, Name VARCHAR2(9));

Table created.

CREATE TABLE m2 (id NUMBER, Name VARCHAR2(9));

Table created.

BEGIN

  INSERT INTO m1 VALUES (1, 'eduflee');

  INSERT INTO m1 VALUES (2, 'Oracle');

  INSERT INTO m1 VALUES (3, 'java');

  INSERT INTO m2 VALUES (1, 'Microsoft');

  INSERT INTO m2 VALUES (4, 'Dell');

  COMMIT;

END;

  /

After creating and Inserting m1 and m2 Table

 SELECT * FROM m1;

    ID NAME

------ ---------

     1 eduflee

     2 Oracle

     3 java

 SELECT * FROM m2;

    ID NAME

------ ---------

     1 Microsoft

     4 Dell

Merging this two tables

MERGE INTO newtable alias name

USING basetable alias name

ON (join condition)

WHEN NOT MATCHED THEN

INSERT VALUES (T1.columns, T1.columns)

WHEN MATCHED THEN

UPDATE 

SET column_name = T1.columns;

MERGE INTO m2 new

USING m1 old

ON (new.id = old.id)

WHEN NOT MATCHED THEN

    INSERT VALUES (old.id, old.name)

WHEN MATCHED THEN

    UPDATE

        SET name = new.name ;

3 rows merged.

SELECT * FROM m2;

        ID NAME

---------- ---------

         1 Microsoft

         4 Dell

         3 java

         2 Oracle

Conclusion

I hope you got clear idea in understanding this Data Manipulation Concepts, so if you having any doubts regarding the post means kindly reach out us.

Frequently Asked Interview Questions

1, Difference between DDL and DML ?

2. Merge Statement Syntax

3. Truncate vs Delete

4. What happens if we fail to give commit inside the PLSQL block ?

5. Why Truncate is fast compared to Delete ?

6. Is it possible to clear all the records using delete command without giving any conditions ?

7. Update command Syntax

8. Difference between DELETE vs TRUNCATE vs DROP

Comment your answers or mail us to our official mail id.