Types in Oracle PL/SQL

Types in Oracle


   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
member procedure changeadd(st_n varchar2, st_c varchar2, st_s varchar2)
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’);
street := st_s;
city := st_c;
state := st_s;
end if;
end changeadd;

add1 address;
add1 := address(‘a’,’b’,’c’); — Initializing (Constructor)

add1 address;
add1 := address(‘a’,’b’,’c’); — Initializing

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
member function cube_cal
return integer
v_area integer;
v_area := length_c * breadth_c * height_c;
return v_area;
end cube_cal;


c area;
a integer;
c := area(3,4,5);
a := c.cube_cal;
dbms_output.put_line(‘The area is ‘ || a);

Posted in UncategorizedTagged