Short Note for Lecture 1
1.Create a type and table
Type is same as class in oop. Also, we create table to store objects.use type to
declare blue print
Type >> table >> objects
CREATE TYPE student_t AS OBJECT
(
Sno CHAR(50),
Sname VARCHAR2(50),
Addr VARCHAR2(50)
)
/
CREATE TABLE students FROM student_t;
SELECT * FROM tab; - View all tables
SELECT type_name FROM user_types; - view all types
DESC student_t;- get type description/details
DESC students: get table details
“/” this forward slash says to execute the previous query
2.Create Objects in the table
Insert into command use to create objects.not save data to table only objects
INSERT INTO students VALUES (student_t(‘s123’,’Ishara’,’Galle’))
/
Show details in the table
Select * from students
Or
Select value(s) from students s
Get specific columns.
Select s.sno, s.addr FROM students s
Object ID is like a pointer. Use to create relationships between objects
.
Create a object reference to one type object to another
dept
Create type dept_t as object
(
Dno char(5),
Dname varchar2(50)
)
CREATE TABLE dept of dept_t
(
PRIMARY KEY (dno) //if we want we can add a primary key while we are
creating tables
)
emp
Create type emp_t as object
(
Eno char(5),
Ename varchar2(50),
Department REF dept_t //store dept_t type object ids
)
CREATE TABLE emp of emp_t
(
PRIMARY KEY (eno) //if we want we can add a primary key while we are
creating tables
)
INSERT INTO dept VALUES (dept_t(‘D1’,’IT’))
INSERT INTO dept VALUES (dept_t(‘D2’,’SE’))
INSERT INTO emp VALUES (emp_t(‘E100’,’Ishara’,’Galle’,(
SELECT Ref(d)
FROM dept d
WHERE d.dno = ‘D1’
)))
This sub query give the D1’s object refference id in department type
Ex -
0000280209A73C3FD13A31498DAF404930FCF10B07A73EE226192F468A872B
B667A061385E0041B9890000
3.Retreive data
SELECT e.eno, e.ename, e.workdept.dname
FROM emp e
WHERE e.ename = ‘Ishara’
Or
SELECT e.eno, e.ename
FROM emp e
WHERE e.workdept.dname = ‘IT’
Incomplete Types
