1/28/16

CASE AND DEFAULT

CASE


Case is similar to decode but easier to understand while going through coding


Ex:

SQL> Select sal,

Case sal

When 500 then ‘low’

When 5000 then ‘high’

Else ‘medium’

End case

From emp;


SAL
CASE
-----
--------
500
low
2500
medium
2000
medium
3500
medium
3000
medium
5000
high
4000
medium
5000
high
1800
medium
1200
medium
2000
medium
2700
medium
2200
medium
3200
medium





DEFAULT


Default can be considered as a substitute behavior of not null constraint when applied to new rows being entered into the table.
When you define a column with the default keyword followed by a value, you are actually telling the database that, on insert if a row was not assigned a value for this column, use the default value that you have specified.

Default is applied only during insertion of new rows.


Ex:

SQL> create table student(no number(2) default 11,name varchar(2)); SQL> insert into student values(1,'a');
SQL> insert into student(name) values('b');


SQL> select * from student;


NO  NAME

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

1
a
11
b


SQL> insert into student values(null, ‘c’);


SQL> select * from student;


NO  NAME

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

1           a

11              b C

--  Default can not override nulls.



0 comments:

Post a Comment