Data Manipulation Language
A DML Statement is executed when you:
— Add new rows to a Table.
— Modify existing rows in a Table.
— Remove existing rows from a Table.
A Transaction consists of a collection of DML Statements that form a logical unit of work.
Adding a new row to a Table
Eg.
CREATE TABLE EMP_MASTER AS SELECT * FROM EMP WHERE 1=2
/
— Insert values for all the columns in the Table —
INSERT INTO EMP_MASTER VALUES
(100,’K1′,’CONC’,102,’01-JAN-2012′,12000,0,10)
/
— Insert values for only certain columns in the Table —
INSERT INTO EMP_MASTER(EMPNO,ENAME)VALUES(102,’K2′)
/
— Inserting Rows with NULL values —
- Implicit Method
INSERT INTO EMP_MASTER(EMPNO,ENAME,SAL)VALUES(103,’K3′,15000)
/ - Explicit Method using NULL
INSERT INTO EMP_MASTER(EMPNO,HIREDATE,SAL)VALUES(104,’01-JAN-2012′,NULL)
/ - Explicit Method using ‘ ‘
INSERT INTO EMP_MASTER(EMPNO,HIREDATE,SAL)VALUES(104,”,NULL)
/
— Spaces are not allowed for inserting NULL Values explicitly —
INSERT INTO EMP_MASTER(EMPNO,HIREDATE,SAL)VALUES(104,’ ‘,NULL)
/
— Spaces are not allowed for inserting NULL Values explicitly irrespective of
Datatype —
INSERT INTO EMP_MASTER(EMPNO,HIREDATE,SAL)VALUES(‘ ‘,’01-JAN-2010’,NULL)
/
Inserting Special Values
— Inserting the System Date and Time —
INSERT INTO EMP_MASTER(EMPNO,ENAME,HIREDATE)VALUES(105,’K4′,SYSDATE)
/
— Inserting the USER Name of the Oracle Database —
INSERT INTO EMP_MASTER(EMPNO,ENAME,HIREDATE)VALUES(105,USER,NULL)
/
— Inserting specific Date Values —
INSERT INTO EMP_MASTER(EMPNO,ENAME,HIREDATE)
VALUES(
110,’KL2′,TO_DATE(‘MAY 2012 12′,’MON YYYY DD’))
/
Creating a Script
— Inserting Records into Table using Substitution Variables —
INSERT INTO EMP_MASTER(EMPNO,ENAME,HIREDATE)
VALUES(&1,’&2′,’&3′)
/
Copying Rows from another Table
— Inserting records into Master Table by selecting records from its source Table —
INSERT INTO EMP_MASTER(EMPNO,ENAME,HIREDATE,SAL,COMM)
SELECT EMPNO,ENAME,HIREDATE,SAL,COMM
FROM EMP
WHERE DEPTNO = 10
/
INSERT INTO EMP_MASTER(EMPNO,ENAME,JOB,HIREDATE,SAL,COMM,DEPTNO)
SELECT EMPNO,ENAME,JOB,HIREDATE,SAL,COMM,DEPTNO
FROM EMP
WHERE DEPTNO = 10
/
Changing Data in a Table
Updating Rows in a Table
Ex.
— Update rows omitting the WHERE Clause —
UPDATE EMP_MASTER
SET JOB=’SALES’
/
— Update specific rows using the WHERE Clause —
UPDATE EMP_MASTER
SET JOB=’ADMIN’
WHERE EMPNO = 100
/
— Update two columns with a Subquery —
Update the Employees Master Table of Employee ID 7782’s Job and Salary to match that of Employee ID 7369 from EMP Table
UPDATE EMP_MASTER
SET JOB = (SELECT JOB FROM EMP WHERE EMPNO = 7369),
SAL = (SELECT SAL FROM EMP WHERE EMPNO = 7369)
WHERE EMPNO = 7782
/
Removing a Row from a Table
Delete Statement
Ex.
— Query to Delete specific records from a Table using WHERE Clause —
DELETE EMP_MASTER WHERE DEPTNO = 10
/
— Query to Delete all Records from a Table —
DELETE EMP_MASTER
/
Deleting Rows based on another Table
INSERT INTO EMP_MASTER(EMPNO,ENAME,HIREDATE,SAL,COMM)
SELECT EMPNO,ENAME,HIREDATE,SAL,COMM
FROM EMP
/
DELETE FROM EMP_MASTER
WHERE SAL <= (SELECT SAL FROM EMP WHERE EMPNO = 7654)
/
DELETE FROM EMP_MASTER
WHERE SAL <= 1250
/
Merge in DML Statements
Use the MERGE statement to select rows from one or more sources for update or insertion into a table or view.
We can specify conditions to determine whether to update or insert into the target table or view.
SELECT * FROM EMP
/
CREATE TABLE EMP13 AS SELECT EMPNO,ENAME FROM EMP WHERE 1 = 2
/
INSERT INTO EMP13(EMPNO,ENAME)VALUES(7369,’Rahul’)
/
SELECT EMPNO,ENAME FROM EMP13
/
MERGE INTO EMP13 C
USING EMP E
ON (C.EMPNO = E.EMPNO)
WHEN MATCHED THEN
UPDATE SET
C.ENAME = E.ENAME
WHEN NOT MATCHED THEN
INSERT VALUES(E.EMPNO,E.ENAME)
/
SELECT * FROM EMP13
/
DROP TABLE EMP13
/
— Table having Multiple Columns —
CREATE TABLE EMP13 AS SELECT * FROM EMP WHERE 1 = 2
/
INSERT INTO EMP13(EMPNO,ENAME)VALUES(7369,’Rahul’)
/
SELECT EMPNO,ENAME FROM EMP13
/
MERGE INTO emp13 c
USING emp e
ON (c.empno = e.empno)
WHEN MATCHED THEN
UPDATE SET
c.ename = e.ename
WHEN NOT MATCHED THEN
INSERT(empno,ename)VALUES(E.EMPNO,E.ENAME)
/
SELECT * FROM emp13
/
DROP TABLE emp13
/
TRUNCATE Statement
Removes all rows from a Table leaving the Table Empty and the table structure intact
TRUNCATE is a DDL Statement and ROLLBACK cannot be performed after TRUNCATE.
— Delete all the Records from a Table using TRUNCATE —
TRUNCATE TABLE EMP_MASTER
/
Using a Subquery in an INSERT Statement
In short we can say this : Inserting using a Subquery as a Target.
We can use a Subquery to INSERT Records into a Table.
Instead of explicitly mentioning the Table Name in the INSERT Clause
using Subquery we can mention the Column Names.
This will directly hit Cols selected and the Table referred in the Subquery.
This in turn will also help us to INSERT Records into Mandatory Cols mentioned in the Table Structure.
Ex.
INSERT INTO
(SELECT EMPNO,ENAME,JOB
FROM EMP_MASTER)
VALUES(100,’K1′,’CONC’)
/
SELECT * FROM EMP_MASTER
/
Database Transactions
A Database Transaction consists of one of the following :
a.DML Statements that constitute one consistent change to the Data
b.One DDL Statement
c.One Transaction Control Language Statement
Transactions give you more flexibility and control when changing Data and they ensure data consistency in the event of User Process failure or system failure.
Transaction Types :
DML – Consists of any no.f DML Statements that the Oracle server treats as a single entity.
DDL – Consists of only one DDL Statement.
DCL – Consists of only one DCL Statement.
TCL – Consists of either a COMMIT/ROLLBACK or a SAVEPOINT
Transactions begin when the first DML SQL Statement is executed.
End with one of the following events :
A COMMIT or ROLLBACK statement is issued.
A DDL or DCL statement executes (Automatic Commit)
The User exits SQL*Plus
After one transaction ends the next executable SQL Statement automatically starts the next transaction.
A DDL statement or DCL statement is automatically committed and therefore implicitly ends a transaction.
COMMIT will ensure Data Consistency
ROLLBACK will preview data changes before making changes permanent.
COMMIT
SAVEPOINT
ROLLBACK
ROLLBACK TO SAVEPOINT
— Rolling Back Changes to a Marker —
INSERT INTO EMP_MASTER(EMPNO,ENAME)VALUES(101,’K2′)
/
SAVEPOINT K1
/
SELECT * FROM EMP_MASTER
/
UPDATE EMP_MASTER SET EMPNO = 200
/
SELECT * FROM EMP_MASTER
/
INSERT INTO EMP_MASTER(EMPNO,ENAME)VALUES(400,’K2′)
/
SELECT * FROM EMP_MASTER
/
ROLLBACK TO SAVEPOINT K1
/
SELECT * FROM EMP_MASTER
/
Note : In between these set of transactions if i give a COMMIT
the Data changes are made permanent and all the SAVEPOINTS are erased.
SELECT * FROM EMP_MASTER
/
Here already two savepoints exist in the same name but in different areas.
The Oracle Server will delete the Earlier Savepoints and take the latest savepoint into consideration.