DATA MANIPULATION LANGUAGE IN ORACLE

DATA MANIPULATION LANGUAGE (DML)

Theses commands are used to manipulate data in a table. Oracle having following DML commands.

1. INSERT - This command is used to insert data in a table.

Method 1

Syntax - insert into tablename values(value1,value2....)

Example -

SQL> create table c1(sno number(10), varchar2(10));

SQL> select * from c1;

no rows selected

SQL> insert into c1 values(1,'abc');

SQL> insert into c1 values(2,'sachin');

Method 2(using substitutional operator(&))

Syntax - insert into tablename values(&col1,&col2....)

Example -

SQL> insertinto c1 values(&sno,'&name');

enter value for sno : 3

enter value for name : xyz

SQL> /                                 ----- / is used to run the previous command

enter value : 4

enter value for name : aaa;

SQL> insert into c1 values(&1,'&2');

enter value for 1 : 5

enter value for 2 : bbb

Hence we can say that after & we don't have to give column name as we can type anything for message purpose.

Method 3(skipping columns)

Syntax - insert into tablename(col1,col2...) values (val1,val2...)

Eaxmple -

SQL> insert into c1(name) values ('zzz');

2. UPDATE - It is used to change data within a table.

Syntax - update tablename set colname=newvalue where colname=someexistingname;

Example -

SQL> update c1 set name='java' where name='xyz';

Note - using update command we can also insert particular cell value where value is not present.

3. DELETE - It is used to delete all rows or particular rows from a table.

Syntax - delete from tablename;

Syntax - delete from tablename where col='condition';

Example -

SQL> delete from c1;

rows are deleted

Get it back

SQL> rollback;

SQL> select * from c1;