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.
ABSTRACT DATA TYPES
Some times
you may want type which holds all types of data including numbers, chars and
special characters something like this. You can not achieve this using
pre-defined types.
You can
define custom types which holds your desired data.
Ex:
Suppose in a table we have address column
which holds hno and city information.
We will define a custom type which holds
both numeric as well as char data.
CREATING ADT
SQL> create type addr as object(hno
number(3),city varchar(10)); /
CREATING TABLE BASED ON ADT
SQL> create table student(no number(2),name
varchar(2),address addr);
INSERTING DATA INTO ADT TABLES
SQL> insert into student
values(1,'a',addr(111,'hyd'));
SQL> insert into student
values(2,'b',addr(222,'bang'));
SQL> insert into student values(3,'c',addr(333,'delhi'));
SELECTING DATA FROM ADT TABLES
SQL> select * from student;
NO NAME ADDRESS(HNO, CITY)
--- ------- -------------------------
1 a
ADDR(111, 'hyd')
2 b
ADDR(222, 'bang')
3 c
ADDR(333, 'delhi')
SQL> select
no,name,s.address.hno,s.address.city from student s;
NO NAME ADDRESS.HNO ADDRESS.CITY
---- ------- ----------------- ----------------
1 a 111 hyd
2 b 222 bang
3 c 333 delhi
UPDATE WITH ADT
TABLES
SQL> update
student s set s.address.city = 'bombay'
where s.address.hno = 333;
SQL> select
no,name,s.address.hno,s.address.city from student s;
NO NAME ADDRESS.HNO ADDRESS.CITY
---- ------- ----------------- ----------------
1 a 111 hyd
2 b 222 bang
3 c 333 bombay
DELETE WITH ADT
TABLES
SQL> delete
student s where s.address.hno = 111;
SQL> select
no,name,s.address.hno,s.address.city from student s;
NO NAME ADDRESS.HNO ADDRESS.CITY
---- ------- ----------------- ----------------
2 b 222 bang
3 c 333 bombay
DROPPING ADT
SQL> drop type
addr;
OBJECT VIEWS AND METHODS
OBJECT VIEWS
If you want
to implement objects with the existing table, object views come into picture.
You define
the object and create a view which relates this object to the existing table
nothing but object view.
Object views
are used to relate the user defined objects to the existing table.
Ex:
1) Assume that the table student has
already been created with the following columns
SQL> create table
student(no number(2),name varchar(10),hno number(3),city
varchar(10));
2) Create the following types
SQL> create type addr as object(hno
number(2),city varchar(10));/
SQL> create type stud as object(name
varchar(10),address addr);/
3) Relate the objects to the student table
by creating the object view
SQL> create view student_ov(no,stud_info) as
select no,stud(name,addr(hno,city)) from
student;
4) Now you can insert data into student
table in two ways
a) By regular insert
SQL> Insert into student values(1,’Rama’,111,’hyd’);
b) By using object view
SQL> Insert into student_ov values(1,stud(‘Rama’,addr(111,’hyd’)));
METHODS
You can
define methods which are nothing but functions in types and apply in the tables
which holds the types;
Ex:
1) Defining methods in types
SQL> Create type
stud as object(name varchar(10),marks number(3),
Member function makrs_f(marks
in number) return number,
Pragma
restrict_references(marks_f,wnds,rnds,wnps,fnps));/
2) Defining type body
SQL> Create type body stud as
Member function marks_f(marks in
number) return number is
Begin
Return (marks+100);
End marks_f;
End;/
3) Create a table using stud type
SQL> Create table student(no number(2),info
stud);
4) Insert some data into student table
SQL> Insert into student values(1,stud(‘Rama’,100));
5) Using method in select
SQL> Select
s.info.marks_f(s.info.marks) from student s;
-- Here we are using the pragma
restrict_references to avoid the writes to the database.
No comments:
Post a Comment