DML statements are used for managing data within schema objects like retrieving, inserting, update and delete etc.
INSERT : Insert statement is used to insert the data into a table.
Syntax :
- insert into table_name(column1,..,columnn) values(value1,..,value2);
- insert into table_name values(value1,value2,…,value4);
We can use #1 syntax to insert the data into particular fields or columns and #2 syntax used insert data for all fields or columns.
For #1, we need to provide the values for mentioned fields or columns in “table ()”.
For #2, we need to provide values for all fields or columns.
SQL> CREATE TABLE STUDENT(SI_NO NUMBER(6),FULL_NAME VARCHAR2(30),BRANCH CHAR(4), SECTION CHAR(1),PERCENTAGE NUMBER(4,2),GRADE CHAR(1)); Table created. SQL> SQL> SQL> SQL> INSERT INTO STUDENT(SI_NO,FULL_NAME,BRANCH,SECTION) VALUES(101,'AAA','CSE', 'A'); 1 row created. SQL> SQL> SELECT SI_NO,FULL_NAME,BRANCH,SECTION FROM STUDENT; SI_NO FULL_NAME BRAN S ---------- ------------------------------ ---- - 101 AAA CSE A SQL> SQL> INSERT INTO STUDENT VALUES(102,'AAB','CSE','A',74.90,'B'); 1 row created. SQL> SELECT * FROM STUDENT; SI_NO FULL_NAME BRAN S PERCENTAGE G ---------- ------------------------------ ---- - ---------- - 101 AAA CSE A 102 AAB CSE A 74.9 B SQL> SQL> SQL> SQL> INSERT INTO STUDENT VALUES(&SI_NO,&FULL_NAME,&BRANCH,&SECTION,&PERCENTAGE, &GRADE); Enter value for si_no: 103 Enter value for full_name: 'AAC' Enter value for branch: 'CSE' Enter value for section: 'A' Enter value for percentage: 75.20 Enter value for grade: 'A' old 1: INSERT INTO STUDENT VALUES(&SI_NO,&FULL_NAME,&BRANCH,&SECTION,&PERCENTAGE,&GRADE) new 1: INSERT INTO STUDENT VALUES(103,'AAC','CSE','A',75.20,'A') 1 row created. SQL> / Enter value for si_no: 104 Enter value for full_name: 'AAD' Enter value for branch: 'CSE' Enter value for section: 'A' Enter value for percentage: 65.90 Enter value for grade: 'C' old 1: INSERT INTO STUDENT VALUES(&SI_NO,&FULL_NAME,&BRANCH,&SECTION,&PERCENTAGE, &GRADE) new 1: INSERT INTO STUDENT VALUES(104,'AAD','CSE','A',65.90,'C') 1 row created. SQL> INSERT INTO STUDENT VALUES(&SI_NO,'&FULL_NAME','&BRANCH','&SECTION', &PERCENTAGE,'&GRADE'); Enter value for si_no: 105 Enter value for full_name: AAE Enter value for branch: CSE Enter value for section: A Enter value for percentage: 76.00 Enter value for grade: A old 1: INSERT INTO STUDENT VALUES(&SI_NO,'&FULL_NAME','&BRANCH','&SECTION',&PERCENTAGE,'&GRADE') new 1: INSERT INTO STUDENT VALUES(105,'AAE','CSE','A',76.00,'A') 1 row created. SQL> SQL> SELECT * FROM STUDENT; SI_NO FULL_NAME BRAN S PERCENTAGE G ---------- ------------------------------ ---- - ---------- - 101 AAA CSE A 102 AAB CSE A 74.9 B 103 AAC CSE A 75.2 A 104 AAD CSE A 65.9 C 105 AAE CSE A 76 A SQL>
We can use “&” char in values statement to provide values manually out side of the query. If we want into insert multiple records this will very help full.
We can repeat the same query by ” / ” char and then enter. It will execute previous query.
Note : For string and date values, we must enclose the values by single quote or double quote.
Note : In above query using “&”, we can enclose fields in values statement(‘&FULL_NAME) or while entering the values (‘AAA’).
UPDATE : It is used to update existing records in a table.
Syntax : update table_name set field=value where conditions;
SQL> SELECT * FROM STUDENT; SI_NO FULL_NAME BRAN S PERCENTAGE G ---------- ------------------------------ ---- - ---------- - 101 AAA CSE A 102 AAB CSE A 74.9 B 103 AAC CSE A 75.2 A 104 AAD CSE A 65.9 C 105 AAE CSE A 76 A SQL> SQL> SQL> SQL> UPDATE STUDENT SET PERCENTAGE=85.09 WHERE SI_NO=101; 1 row updated. SQL> SELECT * FROM STUDENT; SI_NO FULL_NAME BRAN S PERCENTAGE G ---------- ------------------------------ ---- - ---------- - 101 AAA CSE A 85.09 102 AAB CSE A 74.9 B 103 AAC CSE A 75.2 A 104 AAD CSE A 65.9 C 105 AAE CSE A 76 A SQL> SQL> SQL> UPDATE STUDENT SET GRADE='A' WHERE PERCENTAGE>75; 3 rows updated. SQL> SELECT * FROM STUDENT; SI_NO FULL_NAME BRAN S PERCENTAGE G ---------- ------------------------------ ---- - ---------- - 101 AAA CSE A 85.09 A 102 AAB CSE A 74.9 B 103 AAC CSE A 75.2 A 104 AAD CSE A 65.9 C 105 AAE CSE A 76 A SQL>
Update multiple fields.
SQL> SELECT * FROM STUDENT; SI_NO FULL_NAME BRAN S PERCENTAGE G ---------- ------------------------------ ---- - ---------- - 101 AAA CSE A 85.09 A 102 AAB CSE A 74.9 B 103 AAC CSE A 75.2 A 104 AAD CSE A 65.9 C 105 AAE CSE A 76 A SQL> SQL> SQL> SQL> SQL> SQL> UPDATE STUDENT SET SI_NO=165,BRANCH='IT',SECTION='B' WHERE FULL_NAME='AAE'; 1 row updated. SQL> SELECT * FROM STUDENT; SI_NO FULL_NAME BRAN S PERCENTAGE G ---------- ------------------------------ ---- - ---------- - 101 AAA CSE A 85.09 A 102 AAB CSE A 74.9 B 103 AAC CSE A 75.2 A 104 AAD CSE A 65.9 C 165 AAE IT B 76 A SQL>
Note : We can use update command with out where condition. But it is very risky, it updates all records in the table. If auto commit enabled, we can’t rollback it.
DELETE: It is used to delete records in a table. It never delete allocated space.
Syntax : delete from table_name [where conditions],
SQL> SELECT * FROM STUDENT; SI_NO FULL_NAME BRAN S PERCENTAGE G ---------- ------------------------------ ---- - ---------- - 101 AAA CSE A 85.09 A 102 AAB CSE A 74.9 B 103 AAC CSE A 75.2 A 104 AAD CSE A 65.9 C 165 AAE IT B 76 A SQL> SQL> DELETE FROM STUDENT WHERE SI_NO=103; 1 row deleted. SQL> SQL> SELECT * FROM STUDENT; SI_NO FULL_NAME BRAN S PERCENTAGE G ---------- ------------------------------ ---- - ---------- - 101 AAA CSE A 85.09 A 102 AAB CSE A 74.9 B 104 AAD CSE A 65.9 C 165 AAE IT B 76 A SQL>
Delete all records from table.
SQL> DELETE FROM STUDENT; 4 rows deleted. SQL> SELECT *FROM STUDENT; no rows selected SQL>
MERGE : Use this statement to select rows from one or more source tables for update or insertion into a another table (target table).
SQL> CREATE TABLE EMP_BONUS (EMP_ID NUMBER,BONUS NUMBER DEFAULT 100); Table created. SQL> SQL> INSERT INTO EMP_BONUS(EMP_ID) (SELECT EMPNO FROM EMPLOYEES); 14 rows created. SQL> SQL> SELECT * FROM EMP_BONUS; EMP_ID BONUS ---------- ---------- 7369 100 7499 100 7521 100 7566 100 7654 100 7698 100 7782 100 7788 100 7839 100 7844 100 7876 100 EMP_ID BONUS ---------- ---------- 7900 100 7902 100 7934 100 14 rows selected. SQL> SQL> MERGE INTO EMP_BONUS B USING (SELECT * FROM EMPLOYEES) S ON (B.EMP_ID = S.EMPNO) WHEN MATCHED THEN UPDATE SET B.BONUS = B.BONUS + S.SAL*.01 DELETE WHERE (S.SAL > 2000) WHEN NOT MATCHED THEN INSERT (B.EMP_ID, B.BONUS) VALUES (S.EMPNO, S.EMPNO*0.1) WHERE (S.SAL <= 2000); 2 3 4 5 6 7 8 14 rows merged. SQL> select * from EMP_BONUS; EMP_ID BONUS ---------- ---------- 7369 108 7499 116 7521 112.5 7654 112.5 7844 115 7876 111 7900 109.5 7934 113 8 rows selected. SQL> SQL> select d.EMP_ID,e.sal,d.BONUS from EMP_BONUS d,EMPLOYEES e where d.EMP_ID=e.EMPNO; EMP_ID SAL BONUS ---------- ---------- ---------- 7369 800 108 7499 1600 116 7521 1250 112.5 7654 1250 112.5 7844 1500 115 7876 1100 111 7900 950 109.5 7934 1300 113 8 rows selected. SQL>
Here, We have increased the bonus to 1% for employees whose salary less than 2000 and deleted entries of whose salary greater than 2000.
LOCK TABLE : Lock one or more tables in a specified mode. Table access denied to a other users until your table operation completed.
Syntax : LOCK TABLE table_name IN [ SHARE | EXCLUSIVE ] MODE
SQL> LOCK TABLE EMPLOYEES IN SHARE MODE NOWAIT; Table(s) Locked. SQL>
Click here for more on LOCK TABLE
CALL :It is used to execute a stored procedure or a function within SQL.
SQL> CALL my_procedure()
We will learn more about Stored procedures in next chapters.
EXPLAIN PLAN : The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements.
SQL> explain plan for select * from EMPLOYEES; Explained. SQL> SQL> SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY()); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------- Plan hash value: 1445457117 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 14 | 532 | 3 (0)| 00:00:01 | | 1 | TABLE ACCESS FULL| EMPLOYEES | 14 | 532 | 3 (0)| 00:00:01 | ------------------------------------------------------------------------------- 8 rows selected. SQL>
