SQL LEARNING CLASS6

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,’sudha’,111,’hyd’);

b) By using object view

SQL> Insert into student_ov values(1,stud(‘sudha’,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(‘sudha’,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.

VARRAYS AND NESTED TABLES:

VARRAYS

A varying array allows you to store repeating attributes of a record in a single row but with limit.

Ex:

1) We can create varrays using oracle types as well as user defined types.

a) Varray using pre-defined types

SQL> Create type va as varray(5) of varchar(10);/

b) Varrays using user defined types

SQL> Create type addr as object(hno number(3),city varchar(10));/

SQL> Create type va as varray(5) of addr;/

2) Using varray in table

SQL> Create table student(no number(2),name varchar(10),address va);

3) Inserting values into varray table

SQL> Insert into student values(1,’sudha’,va(addr(111,’hyd’)));

SQL> Insert into student values(2,’jagan’,va(addr(111,’hyd’),addr(222,’bang’)));

4) Selecting data from varray table

SQL> Select * from student;

-- This will display varray column data along with varray and adt;

SQL> Select no,name, s.* from student s1, table(s1.address) s;

-- This will display in general format

5) Instead of s.* you can specify the columns in varray

SQL> Select no,name, s.hno,s.city from student s1,table(s1.address) s;

-- Update and delete not possible in varrays.

-- Here we used table function which will take the varray column as input for producing

output excluding varray and types.

NESTED TABLES

A nested table is, as its name implies, a table within a table. In this case it is a table that is represented as a column within another table.

Nested table has the same effect of varrays but has no limit.

Ex:

1) We can create nested tables using oracle types and user defined types which has no limit

a) Nested tables using pre-defined types

SQL> Create type nt as table of varchar(10);/

b) Nested tables using user defined types

SQL> Create type addr as object(hno number(3),city varchar(10));/

SQL> Create type nt as table of addr;/

2) Using nested table in table

SQL> Create table student(no number(2),name varchar(10),address nt) nested table

address store as student_temp;

3) Inserting values into table which has nested table

SQL> Insert into student values (1,’sudha’,nt(addr(111,’hyd’)));

SQL> Insert into student values (2,’jagan’,nt(addr(111,’hyd’),addr(222,’bang’)));

4) Selecting data from table which has nested table

SQL> Select * from student;

-- This will display nested table column data along with nested table and adt;

SQL> Select no,name, s.* from student s1, table(s1.address) s;

-- This will display in general format

5) Instead of s.* you can specify the columns in nested table

SQL> Select no,name, s.hno,s.city from student s1,table(s1.address) s;

6) Inserting nested table data to the existing row

SQL> Insert into table(select address from student where no=1)

values(addr(555,’chennai’));

7) Update in nested tables

SQL> Update table(select address from student where no=2) s set s.city=’bombay’ where

s.hno = 222;

8) Delete in nested table

SQL> Delete table(select address from student where no=3) s where s.hno=333;

DATA MODEL

• ALL_COLL_TYPES
• ALL_TYPES
• DBA_COLL_TYPES
• DBA_TYPES
• USER_COLL_TYPES
• USER_TYPES

FLASHBACK QUERY:

Used to retrieve the data which has been already committed with out going for recovery.

Flashbacks are of two types

• Time base flashback
• SCN based flashback (SCN stands for System Change Number)

Ex:

1) Using time based flashback

a) SQL> Select *from student;

-- This will display all the rows

b) SQL> Delete student;

c) SQL> Commit; -- this will commit the work.

d) SQL> Select *from student;

-- Here it will display nothing

e) Then execute the following procedures

SQL> Exec dbms_flashback.enable_at_time(sysdate-2/1440)

f) SQL> Select *from student;

-- Here it will display the lost data

-- The lost data will come but the current system time was used

g) SQL> Exec dbms_flashback.disable

-- Here we have to disable the flashback to enable it again

2) Using SCN based flashback

a) Declare a variable to store SCN

SQL> Variable s number

b) Get the SCN

SQL> Exec :s := exec dbms_flashback.get_system_change_number

c) To see the SCN

SQL> Print s

d) Then execute the following procedures

SQL> Exec dbms_flashback.enable_at_system_change_number(:s)

SQL> Exec dbms_flashback.disable

EXTERNAL TABLES:

You can user external table feature to access external files as if they are tables inside the database.

When you create an external table, you define its structure and location with in oracle.

When you query the table, oracle reads the external table and returns the results just as if the data had been stored with in the database.

ACCESSING EXTERNAL TABLE DATA

To access external files from within oracle, you must first use the create directory command to define a directory object pointing to the external file location

Users who will access the external files must have the read and write privilege on the directory.

Ex:

CREATING DIRECTORY AND OS LEVEL FILE

SQL> Sqlplus system/manager

SQL> Create directory saketh_dir as ‘/Visdb/visdb/9.2.0/external’;

SQL> Grant all on directory saketh_dir to saketh;

SQL> Conn saketh/saketh

SQL> Spool dept.lst

SQL> Select deptno || ‘,’ || dname || ‘,’ || loc from dept;

SQL> Spool off

CREATING EXTERNAL TABLE

SQL> Create table dept_ext

(deptno number(2),

Dname varchar(14),

Loc varchar(13))

Organization external ( type oracle_loader

Default directory saketh_dir

Access parameters

( records delimited by newline

Fields terminated by “,”

( deptno number(2),

Dname varchar(14),

Loc varchar(13)))

Location (‘/Visdb/visdb/9.2.0/dept.lst’));

SELECTING DATA FROM EXTERNAL TABLE

SQL> select * from dept_ext;

This will read from dept.lst which is a operating system level file.

LIMITATIONS ON EXTERNAL TABLES

1. You can not perform insert, update, and delete operations

1. Indexing not possible
2. Constraints not possible

BENEFITS OF EXTERNAL TABLES

1. Queries of external tables complete very quickly even though a full table scan id required with each access
2. You can join external tables to each other or to standard tables

REF DEREF VALUE:

REF

• The ref function allows referencing of existing row objects.
• Each of the row objects has an object id value assigned to it.
• The object id assigned can be seen by using ref function.

DEREF

• The deref function performs opposite action.
• It takes a reference value of object id and returns the value of the row objects.

VALUE

• Even though the primary table is object table, still it displays the rows in general format.
• To display the entire structure of the object, this will be used.

Ex:

1) create vendot_adt type

SQL> Create type vendor_adt as object (vendor_code number(2), vendor_name

varchar(2), vendor_address varchar(10));/

2) create object tables vendors and vendors1

SQL> Create table vendors of vendor_adt;

SQL> Create table vendors1 of vendor_adt;

3) insert the data into object tables

SQL> insert into vendors values(1, ‘a’, ‘hyd’);

SQL> insert into vendors values(2, ‘b’, ‘bang’);

SQL> insert into vendors1 values(3, ‘c’, ‘delhi’);

SQL> insert into vendors1 values(4, ‘d’, ‘chennai’);

4) create another table orders which holds the vendor_adt type also.

SQL> Create table orders (order_no number(2), vendor_info ref vendor_adt);

Or

SQL> Create table orders (order_no number(2), vendor_info ref vendor_adt with rowid);

5) insert the data into orders table

The vendor_info column in the following syntaxes will store object id of any table which

is referenced by vendor_adt object ( both vendors and vendors1).

SQL> insert into orders values(11,(select ref(v) from vendors v where vendor_code = 1));

SQL> insert into orders values(12,(select ref(v) from vendors v where vendor_code = 2));

SQL> insert into orders values(13,(select ref(v1) from vendors1 v1 where vendor_code =

1));

SQL> insert into orders values(14,(select ref(v1) from vendors1 v1 where vendor_code =

1));

6) To see the object ids of vendor table

SQL> Select ref(V) from vendors v;

7) If you see the vendor_info of orders it will show only the object ids not the values, to see

the values

SQL> Select deref(o.vendor_info) from orders o;

8) Even though the vendors table is object table it will not show the adt along with data, to

see the data along with the adt

SQL>Select * from vendors;

This will give the data without adt.

SQL>Select value(v) from vendors v;

This will give the columns data along wih the type.

REF CONSTRAINTS

Ref can also acts as constraint.

Even though vendors1 also holding vendor_adt, the orders table will store the object ids of vendors only because it is constrained to that table only.

The vendor_info column in the following syntaxes will store object ids of vendors only.

SQL> Create table orders (order_no number(2), vendor_info ref vendor_adt scope is

vendors);

Or

SQL> Create table orders (order_no number(2), vendor_info ref vendor_adt constraint fk

references vendors);

OBJECT VIEWS WITH REFERENCES:

To implement the objects and the ref constraints to the existing tables, what we can do? Simply drop the both tables and recreate with objects and ref constrains.

But you can achieve this with out dropping the tables and without losing the data by creating object views with references.

Ex:

a) Create the following tables

SQL> Create table student1(no number(2) primary key,name varchar(2),marks

number(3));

SQL> Create table student2(no number(2) primary key,hno number(3),city varchar(10),id

number(2),foreign Key(id) references student1(no));

b) Insert the records into both tables

SQL> insert into student1(1,’a’,100);

SQL> insert into student1(2,’b’,200);

SQL> insert into student2(11,111,’hyd’,1);

SQL> insert into student2(12,222,’bang’,2);

SQL> insert into student2(13,333,’bombay’,1);

c) Create the type

SQL> create or replace type stud as object(no number(2),name varchar(2),marks

number(3));/

d) Generating OIDs

SQL> Create or replace view student1_ov of stud with object identifier(or id) (no) as

Select * from Student1;

e) Generating references

SQL> Create or replace view student2_ov as select no,hno,city,make_ref(student1_ov,id)

id from Student2;

d) Query the following

SQL> select *from student1_ov;

SQL> select ref(s) from student1_ov s;

SQL> select values(s) from student1_ov;

SQ> select *from student2_ov;

SQL> select deref(s.id) from student2_ov s;