Types in Oracle
TYPES
Users can create their own data types by combining previously defined data types
Also called Object types
Object types made up of attributes and members
Creating type Info which holds the attributes –
Create or replace type Info as OBJECT
( Name varchar2(30),
Address varchar2(30),
City varchar2(20),
Phone number(7));
To see the structure of the object -
Desc Info
Creating Relational Table with User-Defined Type
Using this type in table E.
E table becomes a relational table due to the use of Info user defined datatype.
Create table E
(Empno number,
Emp_Det Info,
Sal number);
See the structure of this relational table E
Desc E
To see the attributes in the Desc command for a relational table
To see the attributes and the datatypes of the user defined datatype
set describe depth 2
Desc E
Inserting data in the table E
Insert into E
Values (1, Info(‘abc’,’camp’,’Pune’,7876), 7000);
Validations to the Column of user defined datatype—
Only Not Null can be given.
create table f57
(a number,
b info not null)
Note – Primary key, Unique, Foreign Key and check constraint cannot be given to the column of user defined data type.
Constraint can be given to the attributes of the column for that specific table.
For the name attribute of det column giving constraint –
Syntax -- ColumnName.AttributeName
create table j1
(roll number,
det info,
constraint ccc unique(det.name));
Accessing the values of the attribute columns -
To see the name and city for Empno 1 ---
To view an attribute’s value from user-defined types, dot notation is used
Alias name is a must for the table
Values of the tables can be referred using dot notation prefixed with table alias
Select X.Emp_Det.Name, X.Emp_Det.City, Empno
from E X
where Empno = 1;
To see the records when the condition is having an attribute column -
Select * from E X
where X.Emp_Det.Name = ‘abc’;
Altering types
Dropping the type—
To drop a type first all the tables in which that type is used should be dropped , then only the type can be dropped.
User_Types is the in built table for type information.
select type_name,typecode from user_types;
Altering types –
The attribute’s data type size can be increased at any time.
create or replace type h as object
(x number(3),
y number(4));
/*Can increase the size.*/
alter type h
modify attribute x number(10);
/*Cannot decrease the size. */
alter type h
modify attribute x number(9);
Once a attribute is defined in the type then in future it’s data type cannot be changed. Only its size can be increased.
Can drop an attribute provided the type is not used in a table.
alter type h
drop attribute x;
Adding an attribute after type creation.
create or replace type t1
as object
(a number);
alter type t1
add attribute b number;
Members In type
Procedure inside a type:
create type address as object
(
street varchar2(10),
city varchar2(10),
state varchar2(10),
member procedure changeadd(st_n varchar2, st_c varchar2, st_s varchar2)
);
/
create or replace type body address
is
member procedure changeadd(st_n varchar2, st_c varchar2, st_s varchar2)
is
begin
if (st_n is null) or (st_c is null) or st_s is null or
(upper(st_s) not in ('UK','US','CA') ) then
raise_application_error(-20001,'INVAID DATA');
else
street := st_s;
city := st_c;
state := st_s;
end if;
end changeadd;
end;
/
Implementation:
declare
add1 address;
begin
add1 := address('a','b','c'); -- Initializing (Constructor)
add1.changeadd('x','y','z');
dbms_output.put_line(add1.state);
end;
/
declare
add1 address;
begin
add1 := address('a','b','c'); -- Initializing
dbms_output.put_line(add1.state);
add1.changeadd('x','y','us');
dbms_output.put_line(add1.state);
end;
/
Function inside a type:
create or replace type area as object
(length_c integer,
breadth_c integer,
height_c integer,
member function cube_cal return integer
);
create or replace type body area
as
member function cube_cal
return integer
is
v_area integer;
begin
v_area := length_c * breadth_c * height_c;
return v_area;
end cube_cal;
end;
Implementation:
declare
c area;
a integer;
begin
c := area(3,4,5);
a := c.cube_cal;
dbms_output.put_line('The area is ' || a);
end;
/
Discussion about this article
| Author | Body |
Rahul
8/29/2009 8:06 PM
|
Please provide feedback about this article here.
To participate in this discussion Sign up for free membership of 24x7code.
To Signup click on Login , Use create user link & the follow the instructions.
Thank you
|