Oracle8i ORDBMS The Database for Internet Computing Oracle8i ORDBMS : Page 1
2 Oracle8i ORDBMS : Page 2
Introduction ~ What is ORDBMS? ~ 3 Oracle8i ORDBMS : Page 3
4 Oracle8i ORDBMS : Page 4
status : activate / deactivate method : member methods objects identifier attributes 5 Oracle8i ORDBMS : Page 5
( ) ( ) 6 Oracle8i ORDBMS : Page 6
7 Oracle8i ORDBMS : Page 7
Application Data 8 Oracle8i ORDBMS : Page 8
Oracle TOOL New! JPublisher OTT Developer R6.0 JDeveloper R2.0 Designer R6.0 Object Database Designer JDBC New! New! Jserver SQLJ Pro*C/C++ OCI Technology PL/SQL SQL Object Cache Server Side Client Side 9 Oracle8i ORDBMS : Page 9
Oracle8i Oracle8i UniSQL ObjectStore RDBMS Infomix DataBlade OODBMS DB2 10 Oracle8i ORDBMS : Page 10
PK/FK ( ) 11 Oracle8i ORDBMS : Page 11
Oracle Database Designer R6.0 Object Database Designer UML (Unified Modeling Language) UML DDL 12 Oracle8i ORDBMS : Page 12
UML? UML OMT BOOCH OOSE Objectory Fusion UML Unified Modeling Language 13 Oracle8i ORDBMS : Page 13
UML 14 Oracle8i ORDBMS : Page 14
Introduction ~ Oracle Objects Features ~ 15 Oracle8i ORDBMS : Page 15
( ) ( ) ( ) ( ) ID ( ) 16 Oracle8i ORDBMS : Page 16
( ) 17 Oracle8i ORDBMS : Page 17
Object Type ~ One of the key feature of Oracle Objects ~ 18 Oracle8i ORDBMS : Page 18
LOB REF VARCHAR2(n), CHAR,... RAW(n) NUMBER,... DATE LONG LONG RAW VARRAY NESTED TABLE CLOB, NCLOB BLOB BFILE REF SCOPED REF REF w/ ROWID 19 Oracle8i ORDBMS : Page 19
: RDBMS NUMBER 20 Oracle8i ORDBMS : Page 20
: (NUMBER,VARCHAR2,DATE,etc...) (REF ) (NESTED TABLE, VARRAY) LOB (CLOB, BLOB, BFILE) 21 Oracle8i ORDBMS : Page 21
: (REF ) 22 Oracle8i ORDBMS : Page 22
: CREATE OR REPLACE TYPE AS OBJECT <schema_name>. <type_name>, ( <attribute_name> <data_type> ) MEMBER <method_spec> NOTE : Oracle8i SQL 23 Oracle8i ORDBMS : Page 23
: Oracle8 Oracle8i 24 Oracle8i ORDBMS : Page 24
: CREATE TYPE person_type AS OBJECT ( first_name VARCHAR2(30), last_name VARCHAR2(30), birthday DATE, ssn NUMBER(10) ); / first_name last_name birthday ssn person_type 25 Oracle8i ORDBMS : Page 25
: CREATE TYPE employee_type AS OBJECT ( emp_id NUMBER(5), personal_info person_type, salary NUMBER(7,2), dept_no NUMBER(4), mgr_no NUMBER(5) ); / emp_id personal_info salary dept_no mgr_no employee_type first_name last_name birthday ssn dependency 26 person_type Oracle8i ORDBMS : Page 26
: CREATE TYPE department_type; -- / CREATE OR REPLACE TYPE employee_type AS OBJECT ( emp_id NUMBER(5), personal_info person_type, department_type salary NUMBER(7,2), dept REF department_type, mgr_no NUMBER(5)); / CREATE TYPE department_type AS OBJECT ( dept_no NUMBER(4), dept_name VARCHAR2(30), mgr REF employee_type); / employee_types 27 Oracle8i ORDBMS : Page 27
: CREATE TYPE employee_type AS OBJECT ( emp_id NUMBER(5), personal_info person_type, salary NUMBER(7,2), dept REF department_type, mgr REF employee_type ); / emp_id personal_info salary dept mgr employee_type emp_id personal_info salary dept mgr employee_type 28 Oracle8i ORDBMS : Page 28
: CREATE TYPE address_type AS OBJECT ( street_num VARCHAR2(40),..., zipcode CHAR(8)); / CREATE TABLE customers_rel ( cust_id NUMBER(5),..., address address_type,...); customers_rel address_type street_num street state zipcode... 29 Oracle8i ORDBMS : Page 29
: CREATE TYPE address_type AS OBJECT ( street_num VARCHAR2(30), street VARCHAR2(30), city VARCHAR2(30), state VARCHAR2(30), zipcode CHAR(8) ); / CREATE TABLE customers_rel ( cust_id NUMBER(5), name VARCHAR2(30), address address_type, sex CHAR(1) ); SELECT c.cust_id, c.name FROM customers_rel c WHERE c.address.zipcode = 102-0094 ; 30 Oracle8i ORDBMS : Page 30
: CREATE TYPE customer_type AS OBJECT ( cust_id NUMBER(5), name VARCHAR2(30), address address_type, sex CHAR(1) ); / CREATE TABLE customers_obj OF customer_type; customers_obj customer_type OID cust_id name address sex 31 Oracle8i ORDBMS : Page 31
: CREATE TABLE customers_obj OF customers_type (CONSTRAINT pk_customers_obj PRIMARY KEY (cust_id) USING INDEX TABLESPACE users STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0 MAXEXTENTS UNLIMITED) ) OBJECT ID SYSTEM GENERATED OIDINDEX oid_customers_obj TABLESPACE users; CREATE TABLE customers_obj OF customers_type (CONSTRAINT pk_customers_obj PRIMARY KEY (cust_id) USING INDEX TABLESPACE users STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0 MAXEXTENTS UNLIMITED) ) OBJECT ID PRIMARY KEY TABLESPACE users; 32 Oracle8i ORDBMS : Page 32
(OID) (OID) 33 Oracle8i ORDBMS : Page 33
Object Identifiers ~ One of the key feature of Oracle Objects ~ 34 Oracle8i ORDBMS : Page 34
(OID) : 2 35 Oracle8i ORDBMS : Page 35
(OID) : 16 36 Oracle8i ORDBMS : Page 36
(OID) : 16 OIDINDEX 37 Oracle8i ORDBMS : Page 37
Object Reference ~ One of the key feature of Oracle Objects ~ 38 Oracle8i ORDBMS : Page 38
Employees REF Pointer to an Object Departments OID 1 1 REF Navigational access 39 Oracle8i ORDBMS : Page 39
(REF) : 2 REF REF REF 40 Oracle8i ORDBMS : Page 40
( REF) : REF = REF REF 3 REF : 36bytes ROWID REF : 50bytes SCOPED REF : 16bytes Tab1 Tab2 Tab3 41 Oracle8i ORDBMS : Page 41
( REF) : REF = REF SCOPED REF Tab1 SCOPED REF Tab2 42 Tab3 Oracle8i ORDBMS : Page 42
(REF) : = = 43 Oracle8i ORDBMS : Page 43
(REF) : CREATE TYPE department_type; -- / CREATE OR REPLACE TYPE employee_type AS OBJECT ( emp_id NUMBER(5), personal_info person_type, department_type salary NUMBER(7,2), dept REF department_type, mgr_no NUMBER(5)); / CREATE TYPE department_type AS OBJECT ( dept_no NUMBER(4), dept_name VARCHAR2(30), mgr REF employee_type); / employee_types 44 Oracle8i ORDBMS : Page 44
: REF CREATE TABLE departments_soid OF department_type; CREATE TABLE employees_soid OF employee_type; department_type employee_types 45 Oracle8i ORDBMS : Page 45
: REF CREATE TABLE departments_uoid OF department_type (CONSTRAINT pk_departments_uoid PRIMARY KEY (dept_no)) OBJECT ID PRIMARY KEY; CREATE TABLE employees_uoid OF employee_type (CONSTRAINT pk_employees_uoid PRIMARY KEY (emp_id),scope FOR (dept) IS departments_uoid) OBJECT ID PRIMARY KEY; department_type ALTER TABLE departments_uoid ADD (SCOPE FOR (mgr) IS employees_uoid); employee_types 46 Oracle8i ORDBMS : Page 46
: REF REF REF INSERT INTO employees_soid VALUES (1098, person_type( Kei, Aoki, 14-OCT-1973, 7481296788), 4600.00, (SELECT REF(d) FROM departments_soid d WHERE d.dept_no = 1), 465); INSERT INTO employees_soid SELECT 1098, person_type( Kei, Aoki, 14-OCT-1973, 7481296788), 4600.00, REF(d), 465 FROM departments_soid d WHERE d.dept_no = 1; 47 Oracle8i ORDBMS : Page 47
: REF DEREF SELECT DEREF(e.dept) FROM employees_soid e WHERE e.emp_id = 1097; REF SELECT e.dept.dept_no, e.dept.dept_name FROM employees_soid e WHERE e.emp_id = 1098; 48 Oracle8i ORDBMS : Page 48
: DANGLING REF REF ( ) DANGLING REF ( REF) Dangling REF 49 Oracle8i ORDBMS : Page 49
: DANGLING REF SQL DANGLING REF SELECT e.emp_id FROM employees_soid e WHERE e.dept IS DANGLING; SELECT e.emp_id FROM employees_soid e WHERE e.dept IS NOT DANGLING; DANGLING REF IS NULL 50 Oracle8i ORDBMS : Page 50
: DANGLING REF ANALYZE DANGLING REF NULL ANALYZE TABLE employees_soid VALIDATE REF UPDATE SET DANGLING TO NULL; Dangling REF 51 Oracle8i ORDBMS : Page 51
(REF) : REF REF UPDATE employees_soid e SET e.dept.mgr = 1000 WHERE e.emp_id = 1097; 52 Oracle8i ORDBMS : Page 52
Methods ~ One of the key feature of Oracle Objects ~ 53 Oracle8i ORDBMS : Page 53
( ) 54 Oracle8i ORDBMS : Page 54
: PL/SQL, Java, C, C++ l MAP l ORDER 55 Oracle8i ORDBMS : Page 55
: CREATE TYPE person_type AS OBJECT (...); Constructor Method : person_type( ) 56 Oracle8i ORDBMS : Page 56
: NULL INSERT INTO employees_soid VALUES (1097, person_type( Tsuyoshi, Kitagawa, 08-AUG-1974, 7481296756), 5300.00, (SELECT REF(d) FROM departments_soid d WHERE d.dept_no = 2), 675); INSERT INTO employees_soid VALUES (employee_type(1098, person_type( Kei, Aoki, 14-OCT-1973, NULL), 4600.00, (SELECT REF(d) FROM departments_soid d WHERE d.dept_no = 1), 465)); 57 Oracle8i ORDBMS : Page 57
: ( ) : : CREATE TYPE 58 Oracle8i ORDBMS : Page 58
/ : CREATE OR REPLACE TYPE employee_type AS OBJECT ( empno NUMBER(5), personal_info personal_type, sal NUMBER(7,2), comm NUMBER(7,2), dept REF department_type, mgr_no NUMBER(5), MEMBER FUNCTION total_income RETURN NUMBER, PRAGMA RESTRICT_REFEREMCES(total_income, WNDS, WNPS) ); CREATE OR REPLACE TYPE BODY employee_type AS MEMBER FUNCTION total_income RETURN NUMBER IS BEGIN RETURN SELF.sal + SELF.comm; END; END; 59 Oracle8i ORDBMS : Page 59
: CREATE TABLE employees_soid OF employee_type; SELECT e.emp_id, e.personal_info.first_name, e.personal_info.last_name, e.total_income() FROM employees_soid e; : SELECT e.emp_id, e.personal_info.first_name, e.personal_info.last_name, e.sal+e.comm FROM employees e; 60 Oracle8i ORDBMS : Page 60
: CREATE TABLE employees_soid OF employee_type; SELECT e.emp_id, e.personal_info.last_name, e.personal_info.last_name FROM employees e WHERE e.total_income() < 2500; : SELECT e.emp_id, e.personal_info.first_name, e.personal_info.last_name FROM employees e WHERE e.sal+e.comm < 2500; 61 Oracle8i ORDBMS : Page 61
: PRAGMA WNDS WNPS RNDS RNPS 62 Oracle8i ORDBMS : Page 62
: MAP ORDER 1 63 Oracle8i ORDBMS : Page 63
MAP : : DATE, NUMBER, VARCHAR2 ANSI SQL ORDER BY 64 Oracle8i ORDBMS : Page 64
ORDER : 2 NUMBER 2 65 Oracle8i ORDBMS : Page 65
MAP : CREATE TYPE ball_t AS OBJECT ( id NUMBER(4), name VARCHAR2(20), color VARCHAR2(20), MAP MEMBER FUNCTION ball_order RETURN INTEGER, PRAGMA RESTRICT_REFERENCES(BALL_ORDER, WNDS)); / CREATE OR REPLACE TYPE BODY ball_t AS MAP MEMBER FUNCTION ball_order RETURN INTEGER IS BEGIN RETURN ASCII(SUBSTR(SELF.color, 1, 1)); END; END; / 66 Oracle8i ORDBMS : Page 66
MAP : SQL> SELECT * FROM ball; ID NAME COLOR ---------- -------------------- -------------------- 10 golf ball white 22 basket ball brown 43 tennis ball yellow 24 soccer ball black and white SQL> SELECT * FROM ball b ORDER BY VALUE(b); ID NAME COLOR ---------- -------------------- -------------------- 22 basket ball brown 24 soccer ball black and white 10 golf ball white 43 tennis ball yellow SQL> 67 Oracle8i ORDBMS : Page 67
ORDER : CREATE TYPE ball_t AS OBJECT ( id NUMBER(4), name VARCHAR2(20), color VARCHAR2(20), ORDER MEMBER FUNCTION ball_order(in_ball ball_t) RETURN INTEGER, PRAGMA RESTRICT_REFERENCES(BALL_ORDER, WNDS, WNPS)); / CREATE OR REPLACE TYPE BODY ball_t AS ORDER MEMBER FUNCTION ball_order(in_ball ball_t) RETURN INTEGER IS BEGIN IF SELF.id < in_ball.id THEN RETURN -1; ELSIF SELF.id > in_ball.id THEN RETURN 1; ELSE RETURN 0; END IF; END; END; / 68 Oracle8i ORDBMS : Page 68
ORDER : SQL> SELECT * FROM ball b 2 WHERE VALUE(b) < ball_t(24,'basket ball','brown'); ID NAME COLOR ---------- -------------------- -------------------- 10 golf ball white 22 basket ball brown SQL> 69 Oracle8i ORDBMS : Page 69
Collection ~ One of the key feature of Oracle Objects ~ 70 Oracle8i ORDBMS : Page 70
: 1 VARRAY (Variable Array) (NESTED TABLE) 71 Oracle8i ORDBMS : Page 71
VARRAY : 0 = 72 Oracle8i ORDBMS : Page 72
VARRAY : 1 2 1 1 2 2 3 3 4 5 73 Oracle8i ORDBMS : Page 73
VARRAY : VARRAY 74 Oracle8i ORDBMS : Page 74
VARRAY : CREATE TYPE <type_name> AS <schema_name>. ( limit ) OF VARRAY VARYING ARRAY <element_type> NOTE : Oracle8i SQL 75 Oracle8i ORDBMS : Page 75
VARRAY : VARRAY VARRAY SQL 76 Oracle8i ORDBMS : Page 76
VARRAY : CREATE OR REPLACE TYPE employee_type AS OBJECT ( empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), hiredate DATE, mgr NUMBER(4), sal NUMBER(7,2), comm NUMBER(7,2) ); / CREATE OR REPLACE TYPE employee_va AS VARRAY(50) OF employee_type; / CREATE OR REPLACE TYPE department_type AS OBJECT ( deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13), employees employees_va ); / 77 Oracle8i ORDBMS : Page 77
VARRAY : VARRAY CREATE TABLE departments OF department_type (CONSTRAINT pk_departments PRIMARY KEY (deptno)) OBJECT ID PRIMARY KEY TABLESPACE users VARRAY employees STORE AS LOB department_employees_lob ( DISABLE STORAGE IN ROW STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0)); 78 Oracle8i ORDBMS : Page 78
VARRAY : VARRAY 4K DISABLE STORAGE IN ROW LOB TABLESPACE : users TABLESPACE : users NOTE : NOTE : DISABLE STORAGE IN ROW VARRAY 4K VARRAY 79 Oracle8i ORDBMS : Page 79
VARRAY : INSERT INTO departments VALUES (1, Marketing, Tokyo, employee_va( ) ); employee_type(1097, T.Kitagawa, Engineer, 01- APL-1997, 657, 5300.00, 0), employee_type(657, T.Nikaido, Director, 01-OCT- 1995, 107, 8900.00, 0) 80 Oracle8i ORDBMS : Page 80
NESTED TABLE : NESTED TABLE 81 Oracle8i ORDBMS : Page 81
NESTED TABLE : ( / ) 82 Oracle8i ORDBMS : Page 82
NESTED TABLE : NESTED TABLE 83 Oracle8i ORDBMS : Page 83
NESTED TABLE : CREATE TYPE AS TABLE OF <schema_name>. <type_name> <element_type> NOTE : Oracle8i SQL 84 Oracle8i ORDBMS : Page 84
NESTED TABLE : / NESTED TABLE / 36 85 Oracle8i ORDBMS : Page 85
NESTED TABLE : CREATE OR REPLACE TYPE employee_type AS OBJECT ( empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), hiredate DATE, mgr NUMBER(4), sal NUMBER(7,2), comm NUMBER(7,2) ); / CREATE OR REPLACE TYPE employee_nt AS TABLE OF employee_type; / CREATE OR REPLACE TYPE department_type AS OBJECT ( deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13), employees employees_nt ); / 86 Oracle8i ORDBMS : Page 86
NESTED TABLE : NESTED TABLE CREATE TABLE departments OF department_type (CONSTRAINT pk_departments PRIMARY KEY (deptno)) OBJECT ID PRIMARY KEY TABLESPACE users NESTED TABLE employees STORE AS department_employees_store ((CONSTRAINT pk_department_employees_store PRIMARY KEY (NESTED_TABLE_ID, empno) USING INDEX TABLESPACE users STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0) STORAGE (INITIAL 100KNEXT 100K PCTINCREASE 0)); 87 Oracle8i ORDBMS : Page 87
NESTED TABLE : NESTED TABLE TABLESPACE : users NOTE : 88 Oracle8i ORDBMS : Page 88
NESTED TABLE : INSERT INTO departments VALUES (1, Marketing, Tokyo, employee_nt( ) ); employee_type(1097, T.Kitagawa, Engineer, 01- APL-1997, 657, 5300.00, 0), employee_type(657, T.Nikaido, Director, 01-OCT- 1995, 107, 8900.00, 0) 89 Oracle8i ORDBMS : Page 89
NESTED TABLE : INSERT INTO THE (SELECT employees FROM departments WHERE deptno = 1) VALUES ( ); 1098, K.Aoki, Engineer, 26-NOV-1999, 657, 4900.00, 0 90 Oracle8i ORDBMS : Page 90
NESTED TABLE : DELETE FROM THE (SELECT employees FROM departments WHERE deptno = 1) e WHERE e.empno = 1098; 91 Oracle8i ORDBMS : Page 91
NESTED TABLE : UPDATE THE (SELECT employees FROM departments WHERE deptno = 1) e SET e.sal = 5400.00 WHERE e.empno = 1097; 92 Oracle8i ORDBMS : Page 92
VARRAY NESTED TABLE VARRAY NESTED TABLE (4K ) (SQL ) SQL DML 93 Oracle8i ORDBMS : Page 93
Object View ~ One of the key feature of Oracle Objects ~ 94 Oracle8i ORDBMS : Page 94
File Edit File Edit 95 Oracle8i ORDBMS : Page 95
File Edit 96 Oracle8i ORDBMS : Page 96
OID 97 Oracle8i ORDBMS : Page 97
: = pkoid 98 Oracle8i ORDBMS : Page 98
: CREATE TABLE dept ( deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13) ) (CONSTRAINT pk_dept PRIMARY KEY(deptno)); CREATE TABLE emp ( empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), hiredate DATE, mgr NUMBER(4), sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2)) (CONSTRAINT pk_emp PRIMARY KEY (empno), CONSTRAINT fk_emp_deptno FOREIGN KEY (deptno) REFERENCES dept); 99 Oracle8i ORDBMS : Page 99
: CREATE TYPE dept_t AS OBJECT (deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13)); / CREATE TYPE emp_t AS OBJECT (empno NUMBER(4), ename VARCHAR2(10), job VARCHAR(9), hiredate DATE, mgr NUMBER(4), sal NUMBER(7,2), comm NUMBER(7,2), dept REF dept_t); / 100 Oracle8i ORDBMS : Page 100
: pkoid CREATE VIEW dept_view OF dept_t AS SELECT deptno, dname, loc FROM dept; 101 Oracle8i ORDBMS : Page 101
: pkoid (pkoid) CREATE VIEW dept_view OF dept_t WITH OBJECT OID (deptno) AS SELECT deptno, dname, loc FROM dept; 102 Oracle8i ORDBMS : Page 102
: pkoid (pkref) pkoid REF pkoid 103 Oracle8i ORDBMS : Page 103
: pkref pkref MAKE_REF MAKE_REF CREATE VIEW emp_view OF emp_t WITH OBJECT OID (empno) AS SELECT empno, ename, job, hiredate, mgr, sal, comm, MAKE_REF(dept_view,deptno) FROM emp; 104 Oracle8i ORDBMS : Page 104
: (VARRAY / NESTED TABLE) (REF) 105 Oracle8i ORDBMS : Page 105
: DML INSTEAD OF 106 Oracle8i ORDBMS : Page 106
: NESTED TABLE CREATE TYPE emp_t AS OBJECT ( empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), hiredate DATE, mgr NUMBER(4), sal NUMBER(7,2), comm NUMBER(7,2)); / CREATE TYPE emp_set_t AS TABLE OF emp_t; / CREATE TYPE dept_t AS OBJECT ( deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13), employees emp_set_t); / 107 Oracle8i ORDBMS : Page 107
: NESTED TABLE CREATE VIEW dept_view OF dept_t WITH OBJECT OID (deptno) AS SELECT d.deptno, d.dname, d.loc, CAST( MULTISET( SELECT e.empno, e.ename, e.job, e.hiredate, e.mgr, e.sal, e.comm FROM emp e WHERE e.deptno = d.deptno ) AS emp_set_t) FROM dept d; 108 Oracle8i ORDBMS : Page 108
DML File Edit 109 Oracle8i ORDBMS : Page 109
INSERT, UPDATE, DELETE INSTEAD OF File Edit INSTEAD OF 110 Oracle8i ORDBMS : Page 110
DECLARE... BEGIN... EXCEPTION... END INSTEAD OF 111 Oracle8i ORDBMS : Page 111
(1/5) CREATE TABLE dept ( deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13) ) (CONSTRAINT pk_dept PRIMARY KEY(deptno)); CREATE TABLE emp ( empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), hiredate DATE, mgr NUMBER(4), sal NUMBER(7,2), comm NUMBER(7,2), deptno NUMBER(2)) (CONSTRAINT pk_emp PRIMARY KEY (empno), CONSTRAINT fk_emp_deptno FOREIGN KEY (deptno) REFERENCES dept); 112 Oracle8i ORDBMS : Page 112
(2/5) CREATE TYPE emp_t AS OBJECT ( empno NUMBER(4), ename VARCHAR2(10), job VARCHAR2(9), hiredate DATE, mgr NUMBER(4), sal NUMBER(7,2), comm NUMBER(7,2)); / CREATE TYPE emp_set_t AS TABLE OF emp_t; / CREATE TYPE dept_t AS OBJECT ( deptno NUMBER(2), dname VARCHAR2(14), loc VARCHAR2(13), employees emp_set_t); / 113 Oracle8i ORDBMS : Page 113
(3/5) CREATE VIEW dept_view OF dept_t WITH OBJECT OID (deptno) AS SELECT d.deptno, d.dname, d.loc, CAST( MULTISET( SELECT e.empno, e.ename, e.job, e.hiredate, e.mgr, e.sal, e.comm FROM emp e WHERE e.deptno = d.deptno ) AS emp_set_t) FROM dept d; 114 Oracle8i ORDBMS : Page 114
INSTEAD OF (4/5) CREATE OR REPLACE TRIGGER dept_ov_instr INSTEAD OF INSERT ON dept_view DECLARE emplist_tab emp_set_t; i INTEGER; BEGIN emplist_tab := :NEW.emplist; INSERT INTO dept VALUES(:NEW.deptno,:NEW.dname,:NEW.loc); IF (emplist_tab IS NOT NULL) THEN FOR i IN 1..emplist_tab.COUNT LOOP INSERT INTO emp VALUES(emplist_tab(i).empno,..., emplist_tab(i).comm, :NEW.deptno); END LOOP; END IF; END; 115 Oracle8i ORDBMS : Page 115
INSTEAD OF (5/5) CREATE OR REPLACE TRIGGER dept_ov_emplist_instr INSTEAD OF INSERT ON NESTED TABLE employees OF dept_view BEGIN INSERT INTO emp VALUES(:NEW.empno,:NEW.ename,:NEW.job, :NEW.mgr,:NEW.hiredate,:NEW.sal,:NEW.comm, :PARENT.deptno); END; 116 Oracle8i ORDBMS : Page 116
INSTEAD OF : DML INSTEAD OF Insert new objects into dept_v Insert new elements into dept_v.emplist dept_ov_instr dept_ov_emplist_instr 117 Oracle8i ORDBMS : Page 117
INSTEAD OF : INSTEAD OF :NEW :OLD :PARENT l :PARENT INSTEAD OF l :PARENT ROWID 118 Oracle8i ORDBMS : Page 118
Objects with Java ~ Manipulate objects through Java~ 119 Oracle8i ORDBMS : Page 119
JDBC, SQLJ (JPublisher) C++ (ODD : Object Database Designer) C, OCI (OTT : Object Type Translator) Oracle Precompilers OO4O (Oracle Objects for OLE) SQL, PL/SQL 120 Oracle8i ORDBMS : Page 120
API JDBC SQLJ Java API Java API Java SQL SQLJ JDBC ANSI 121 Oracle8i ORDBMS : Page 121
JDBC Driver ~APIs to access Database~ 122 Oracle8i ORDBMS : Page 122
JDBC Driver (1/3) JDBC-Thin Driver (Type 4) Java Net8 TCP/IP Web Browser 123 Oracle8i ORDBMS : Page 123
JDBC Driver (2/3) JDBC-OCI Driver (Type 2) Java Oracle OCI Net8 Application Server 124 Oracle8i ORDBMS : Page 124
JDBC Driver (3/3) JDBC-KPRB Driver (Server-side JDBC Driver) Java VM SQL PL/SQL Client-side JDBC Driver Oracle8i 125 Oracle8i ORDBMS : Page 125
JDBC Driver Web Browser JDBC-Thin Java Sockets Client Application JDBC-OCI OCI C Lib. SQL*Net SQL & PL/SQL Engines JDBC-KPRB Internal Lib. Java Stored Procedure 126 Oracle8i ORDBMS : Page 126
JDBC : JDBC Driver Statement Query Close 127 Oracle8i ORDBMS : Page 127
JDBC : DriverManager getconnection Connection createstatement Statement executequery ResultSet preparestatement executequery PreparedStatement preparecall CallableStatement setxxx getxxx getxxx Date, Time, Java 128 Oracle8i ORDBMS : Page 128
Oracle JDBC Driver JDK1.0.2,JDK1.1 JDBC1.22 Oracle : LOB REF 129 Oracle8i ORDBMS : Page 129
SQLJ ~Embeded SQL~ 130 Oracle8i ORDBMS : Page 130
SQLJ : Java SQL SQLJ JDBC Java SQL JDBC SQLJ code SQLJ Translator Java Code with JDBC calls Java Compiler Regular Java Class file Oracle8i Oracle8i 131 Oracle8i ORDBMS : Page 131
SQLJ : SQLJ Oracle, IBM, Tandem, Sybase, Javasoft,... ANSI/ISO SQLJ 132 Oracle8i ORDBMS : Page 132
JPublisher ~Utility to generate Java classes for handling objects~ 133 Oracle8i ORDBMS : Page 133
Object Type Java Class.java or.sqlj JPublisher (1/2) Java Java 3 l JDBC Mapping l Object JDBC (JDBC 2.0) Mapping : Default l Oracle Mapping 134 Oracle8i ORDBMS : Page 134
JPublisher (2/2) Command Line Imput File Java Class l get<attribute>( ), set<attribute>( ) -methods=true REF Class l <Type_Name>Ref.java 135 Oracle8i ORDBMS : Page 135
JPublisher SQL DDL Command Line Input File Oracle8i JPublisher Object Types Generated Java Classes Custom Java Classes Import Compiler Java Program 136 Oracle8i ORDBMS : Page 136
Handling Objects ~Using generated classes~ 137 Oracle8i ORDBMS : Page 137
-- (customer_tab) -- (stock_tab) -- (purchase_tab) Entity / Object Customer Stock Purchase Order Line Item List ( ),,,,, ( ) 138 Oracle8i ORDBMS : Page 138
address_t street : varchar2 city : varchar2 state : char zip : varchar2 phone_list_t varray of varchar2 line_item_list_t table of line_item_t line_item_t lineitemno : number quantity : number discount : number stockref : ref stock_info_t stockno : number cost : number tax_code : number customer_info_t purchase_order_t pono : number orddate : date shipdate : date custref : ref shiptoaddr : address_t lineitemlist : line_item_list_t address_t street : varchar2 city : varchar2 state : char zip : varchar2 line_item_list_t custono custname address phone_list : number : varchar2 : address_t : phone_list_t address_t street : varchar2 city : varchar2 state : char zip : varchar2 phone_list_t varray of varchar2 table of line_item_t 139 Oracle8i ORDBMS : Page 139
: address_t address_t street : varchar2 city : varchar2 state : char zip : varchar2 CREATE TYPE address_t AS OBJECT ( street VARCHAR2(200), <- Attribute city VARCHAR2(200), state CHAR(2), zip VARCHAR2(20) ); / 140 Oracle8i ORDBMS : Page 140
: stock_info_t stock_info_t stockno cost tax_code : number : number : number CREATE TYPE stock_info_t AS OBJECT ( stockno NUMBER, cost NUMBER, tax_code NUMBER ); / 141 Oracle8i ORDBMS : Page 141
: line_item_t line_item_t lineitemno quantity discount stockref : number : number : number : ref CREATE TYPE line_item_t AS OBJECT ( lineitemno NUMBER, stockref REF stock_info_t, quantity NUMBER, discount NUMBER ); / 142 Oracle8i ORDBMS : Page 142
: phone_list_t varray of varchar2 CREATE TYPE phone_list_t AS VARRAY(10) OF VARCHAR2(20); / line_item_list_t table of line_item_t CREATE TYPE line_item_list_t AS TABLE OF line_item_t; / 143 Oracle8i ORDBMS : Page 143
: customer_info_t customer_info_t custono : number custname : varchar2 address : address_t phone_list : phone_list_t address_t street : varchar2 city : varchar2 state : char zip : varchar2 phone_list_t varray of varchar2 CREATE TYPE customer_info_t AS OBJECT ( custno NUMBER, custname VARCHAR2(200), address address_t, phone_list phone_list_t, ORDER MEMBER FUNCTION cust_order(x IN customer_info_t) RETURN INTEGER, PRAGMA RESTRICT_REFERENCES (cust_order,wnds,wnps,rn DS,RNPS) ); / 144 Oracle8i ORDBMS : Page 144
: cust_order( ) CREATE OR REPLACE TYPE BODY custmoer_info_t AS ORDER MEMBER FUNCTION cust_order(x IN customer_info_t) RETURN INTEGER IS BEGIN RETURN custno - x.custno; END; END; / 145 Oracle8i ORDBMS : Page 145
: purchase_order_t purchase_order_t pono : number orddate : date shipdate : date custref : ref shiptoaddr : address_t lineitemlist : line_item_list_t address_t street : varchar2 city : varchar2 state : char zip : varchar2 line_item_list_t table of line_item_t CREATE TYPE purchase_order_t AS OBJECT ( pono NUMBER, custref REF customer_info_t, orderdate DATE, shipdate DATE, line_item_list line_item_list_t, shiptoaddr address_t, MEMBER FUNCTION total_value RETURN NUMBER, PRAGMA RESTRICT_REFERENCES (total_value,wnds,wnps) ); / 146 Oracle8i ORDBMS : Page 146
: total_value( ) CREATE OR REPLACE TYPE BODY purchase_order_t AS MEMBER FUNCTION total_value RETURN NUMBER IS i INTEGER; stock stock_info_t; line_item line_item_t; total NUMBER := 0; cost NUMBER; BEGIN FOR i IN SELF.line_item_list.COUNT LOOP line_item := SELF.line_item_list(i); SELECT DEREF(line_item.stockref) INTO stock FROM DUAL; total := total + line_item.quantity * stock.cost; END LOOP; RETURN total; END; END; 147 Oracle8i ORDBMS : Page 147
CREATE TABLE customer_tab OF customer_t (CONSTRAINT pk_customer_tab PRIMARY KEY(custno)); CREATE TABLE stock_tab OF stock_info_t (CONSTRAINT pk_stock_tab PRIMARY KEY(stockno)); CREATE TABLE purchase_tab OF purchase_order_t (CONSTRAINT pk_purchase_tab PRIMARY KEY(pono),CONSTRAINT fk_custref FOREIGN KEY(custref) REFERENCES customer_tab ON DELETE SET NULL) NESTED TABLE line_item_list STORE AS po_line_tab; 148 Oracle8i ORDBMS : Page 148
ALTER TABLE po_line_tab ADD ( SCOPE FOR (stockref) IS stock_tab); ALTER TABLE po_line_tab STORAGE (NEXT 10K PCTINCREASE 0 MINEXTENTS 1 MAXEXTENTS 121); CREATE INDEX po_nested_in ON po_line_tab(nested_table_id); CREATE UNIQUE INDEX po_nested ON po_line_tab(nested_table_id, lineitemno); 149 Oracle8i ORDBMS : Page 149
JPublisher #jpub -user=scott/tiger -mapping=jdbc -sql=purchase_order_t :PurchaseOrderT -methods=true purchase_order_t Java Class PurchaseOrderT.sqlj, PurchaseOrderTRef.java -mapping={jdbc objectjdbc oracle} jdbc & objectjdbc: JDBC oracle: oracle.sql.* 150 Oracle8i ORDBMS : Page 150
Sample : PurchaseOrderT.sqlj import java.sql.sqlexception;... public class PurchaseOrderT implements CustomDatum, CustomDatumFactory {... /* accessor methods */ public java.math.bigdecimal getpono() throws SQLException { return (java.math.bigdecimal) _struct.getattribute(0); }... public java.math.bigdecimal totalvalue () throws SQLException {... #sql [_ctx] {... }; return jpt_result; } } 151 Oracle8i ORDBMS : Page 151
Sample : Sample3.java... pstmt = conn.preparestatement( "SELECT VALUE(p) FROM purchase_tab p WHERE p.pono =?" ); pstmt.setint(1,1001); OracleResultSet rset = (OracleResultSet) pstmt.executequery(); PurchaseOrderT purchase = new PurchaseOrderT(); if (rset.next()) { purchase = (PurchaseOrderT) rset.getcustomdatum( 1,PurchaseOrderT.getFactory() ); }... 152 Oracle8i ORDBMS : Page 152
Sample : Sample3 > sqlj AddressT.sqlj... > javac Sample3.java > java Sample3 connected. [Name]: Jean Nance [Order No]: 1001 [Total]: 26808 disconnected. > 153 Oracle8i ORDBMS : Page 153
Sample : Sample4.sqlj... int ipono = 1001; PurchaseOrderT purchase = null; #sql {SELECT VALUE(p) INTO :purchase FROM purchase_tab p WHERE p.pono=:ipono}; if(purchase!= null) { CustomerInfoT cust = (CustomerInfoT) purchase.getcustref().getvalue(); String custname = cust.getcustname(); int purchaseno = purchase.getpono().intvalue(); BigDecimal totalvalue = purchase.totalvalue();... 154 Oracle8i ORDBMS : Page 154
Sample : Sample4.sqlj > sqlj AddressT.sqlj >... > sqlj Sample4.sqlj > java Sample4 connected. [Name]: Jean Nance [Order No]: 1001 [Total]: 26808 disconnected. > 155 Oracle8i ORDBMS : Page 155
Conclusion ~ Why don t you use Oracle Objects? ~ 156 Oracle8i ORDBMS : Page 156
Oracle8 R8.0 Oracle8i (REF) (VARRAY NESTED TABLE) SQL*Loader 157 Oracle8i ORDBMS : Page 157
Oracle8i Oracle8 R8.0 Object Type System Collection LOB REF System-generated OID Methods Object Cache Object View OTT (Object Type Translator) User-defined OID Collection Enhancements Trigger Enhancements Object View Enhancements Partitioning Support for Objects SQL*Loader Support for Objects Parallel Query Support for Objects Extensible Indexing and Optimizer Java Stored Procedure EJB & CORBA/IIOP Support JPublisher Oracle8i 158 Oracle8i ORDBMS : Page 158
Oracle8i Application : Java Database : ORDBMS (Oracle Objects) 159 Oracle8i ORDBMS : Page 159
Oracle Corporation, 1999. All right reserved. Oracle Corporation Oracle Corporation Oracle Corporation ( ) ORACLE Oracle Corporation 1999.11.16 Rev.A Oracle8i ORDBMS : Page 160