1 / 71

Oracle 8

Oracle 8. Oracle 8. New features. Oracle 8. New features Abstract data types. Oracle 8. New features Abstract data types Nested tables. Oracle 8. New features Abstract data types Nested tables Varying arrays. Oracle 8. New features Abstract data types Nested tables

tori
Download Presentation

Oracle 8

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Oracle 8

  2. Oracle 8 • New features

  3. Oracle 8 • New features • Abstract data types

  4. Oracle 8 • New features • Abstract data types • Nested tables

  5. Oracle 8 • New features • Abstract data types • Nested tables • Varying arrays

  6. Oracle 8 • New features • Abstract data types • Nested tables • Varying arrays • Large objects

  7. Oracle 8 • New features • Abstract data types • Nested tables • Varying arrays • Large objects • References

  8. Oracle 8 • New features • Abstract data types • Nested tables • Varying arrays • Large objects • References • Object views

  9. Oracle 8 • Abstract data types

  10. Oracle 8 • Abstract data types • CREATE TYPE statement CREATE TYPE ADDRESS_TYPE AS OBJECT (Street VARCHAR2(50), City VARCHAR2(25), State VARCHAR(3), Zip NUMBER(4) );

  11. Oracle 8 • Abstract data types • CREATE TYPE statement CREATE TYPE ADDRESS_TYPE AS OBJECT (Street VARCHAR2(50), City VARCHAR2(25), State VARCHAR(3), Zip NUMBER(4) ); CREATE TYPE PERSON_TYPE AS OBJECT (Name VARCHAR2(30), Address ADDRESS_TYPE );

  12. Oracle 8 • Abstract data types • CREATE TABLE statement CREATE TABLE CUSTOMER (Customer_ID NUMBER(6), Person PERSON_TYPE );

  13. Oracle 8 • Abstract data types • CREATE TABLE statement CREATE TABLE CUSTOMER (Customer_ID NUMBER(6), Person PERSON_TYPE ); CREATE TABLE DEPARTMENT (Dept_Name VARCHAR2(20), Address ADDRESS_TYPE) );

  14. Oracle 8 • Abstract data types • INSERT INTO statement INSERT INTO CUSTOMER VALUES ( 1, PERSON_TYPE(‘Mike Jones’, ADDRESS_TYPE(‘Penny St.’, ‘London’, ‘HK’, 1234)));

  15. Oracle 8 • Abstract data types • INSERT INTO statement INSERT INTO CUSTOMER VALUES ( 1, PERSON_TYPE(‘Mike Jones’, ADDRESS_TYPE(‘Penny St.’, ‘London’, ‘HK’, 1234))); INSERT INTO DEPARTMENT VALUES( ‘SITACS’, ADDRESS_TYPE(‘Northfields Ave.’, ‘Wollongong’, ‘NSW’, 2522) );

  16. Oracle 8 • Abstract data types • SELECT statement • SELECT Customer_id, Person.Name • FROM Customer • WHERE Person.Address.City = ‘London’; • SELECT * • FROM Department;

  17. Oracle 8 • Abstract data types • DROP TYPE statement DROP TYPE PERSON_TYPE

  18. Oracle 8 • Abstract data types • DROP TYPE statement DROP TYPE PERSON_TYPE • CREATE OR REPLACE TYPE statement CREATE OR REPLACE TYPE ADDRESS_TYPE AS OBJECT (Street VARCHAR2(50), City VARCHAR2(25) );

  19. Oracle 8 • Indexing abstract data type attributes

  20. Oracle 8 • Indexing abstract data type attributes • CREATE INDEX statement CREATE INDEX CUST_IDX ON Customer(Person.Address.City) ;

  21. Oracle 8 • Object views

  22. Oracle 8 • Object views CREATE TABLE CUSTOMER (Customer_Id NUMBER(6) PRIMARY KEY, Name VARCHAR2(30), Street VARCHAR2(50), City VARCHAR2(20), State CHAR(3), Zip NUMBER(4) );

  23. Oracle 8 • Object views CREATE TYPE ADDRESS_TYPE AS OBJECT Street VARCHAR2(50), City VARCHAR2(20), State CHAR(3), Zip NUMBER(4) );

  24. Oracle 8 • Object views CREATE TYPE ADDRESS_TYPE AS OBJECT Street VARCHAR2(50), City VARCHAR2(20), State CHAR(3), Zip NUMBER(4) ); CREATE TYPE PERSON_TYPE AS OBJECT (Name VARCHAR2(30), Address ADDRESS_TYPE );

  25. Oracle 8 • Object views CREATE VIEW CUSTOMER_V (Customer_ID, Person) AS SELECT Customer_ID, PERSON_TYPE(Name, ADDRESS_TYPE(Street, City, State,Zip) ) FROM CUSTOMER WHERE State = ‘NSW’;

  26. Oracle 8 • Object views • Create flat relational table

  27. Oracle 8 • Object views • Create flat relational table • Create abstract data types

  28. Oracle 8 • Object views • Create flat relational table • Create abstract data types • Create object view

  29. Oracle 8 • Methods

  30. Oracle 8 • Methods CREATE TYPE PERSON_TYPE AS OBJECT (Name VARCHAR2(30), Address ADDRESS_TYPE, BithDate DATE, MEMBER FUNCTION Age( BirthDate IN DATE) RETURN NUMBER );

  31. Oracle 8 • Methods CREATE TYPE BODY PERSON_TYPE AS MEMBER FUNCTION Age( BirthDate IN DATE) RETURN NUMBER IS BEGIN RETURN ROUND(SysDate - BirthDate); END; END; /

  32. Oracle 8 • Methods CREATE TABLE STUDENT (SID NUMBER(6), PERSON PERSON_TYPE ); SELECT STUDENT.Age(Student.BirthDate) FROM STUDENT;

  33. Oracle 8 • Collectors

  34. Oracle 8 • Collectors • Varying arrays

  35. Oracle 8 • Collectors • Varying arrays • Nested tables

  36. Oracle 8 • Varying arrays

  37. Oracle 8 • Varying arrays CREATE TYPE TOOL_TYPE AS OBJECT (ToolName VARCHAR2(30) );

  38. Oracle 8 • Varying arrays CREATE TYPE TOOL_TYPE AS OBJECT (ToolName VARCHAR2(30) ); CREATE TYPE TOOLS_VA AS VARRAY(5) OF TOOL_TYPE;

  39. Oracle 8 • Varying arrays CREATE TYPE TOOL_TYPE AS OBJECT (ToolName VARCHAR2(30) ); CREATE TYPE TOOLS_VA AS VARRAY(5) OF TOOL_TYPE; CREATE TABLE MECHANIC (Name VARCHAR2(30) PRIMARY KEY, Tools TOOLS_VA );

  40. Oracle 8 • Varying arrays INSERT INTO MECHANIC VALUES (‘Mike Jones’, TOOLS_VA(TOOL_TYPE(‘HAMMER’), TOOL_TYPE(‘SLEDGE’), TOOL_TYPE(‘AX’)));

  41. Oracle 8 • Varying arrays DECLARE CURSOR MECHANIC_CURSOR IS SELECT * FROM MECHANIC; MECHANIC_REC MECHANIC_CURSOR%ROWTYPE; BEGIN FOR MECHANIC_REC IN MECHANIC_CURSOR LOOP DBMS_OUTPUT.PUT_LINE(MECHANIC_REC.Name); FOR I IN 1..MECHANIC_REC.Tools.Count LOOP DBMS_OUTPUT.PUT_LINE(MECHANIC_REC.Tools(I)); END LOOP; END LOOP; END;

  42. Oracle 8 • Nested tables

  43. Oracle 8 • Nested tables CREATE TYPE ANIMAL_TYPE AS OBJECT (Breed VARCHAR2(30), Name VARCHAR2(20), Birthdate DATE );

  44. Oracle 8 • Nested tables CREATE TYPE ANIMAL_TYPE AS OBJECT (Breed VARCHAR2(30), Name VARCHAR2(20), Birthdate DATE ); CREATE TYPE ANIMAL_TABLE AS TABLE OF ANIMAL_TYPE;

  45. Oracle 8 • Nested tables CREATE TYPE ANIMAL_TYPE AS OBJECT (Breed VARCHAR2(30), Name VARCHAR2(20), Birthdate DATE ); CREATE TYPE ANIMAL_TABLE AS TABLE OF ANIMAL_TYPE; CREATE TABLE BREEDER (BreederName VARCHAR2(30), Animals ANIMAL_TABLE) NESTED TABLE ANIMALS STORE AS ANIMALS_NT;

  46. Oracle 8 • Nested tables INSERT INTO BREEDER VALUES (‘Mike Jones’, ANIMAL_TABLE( ANIMAL_TYPE( ‘DOG’, ‘BUTCH’, ‘31-MAR-97’), ANIMAL_TYPE( ‘CAT’, ‘ROVER’, ‘1-MAR-98’), ANIMAL_TYPE( ‘RAT’, ‘JULIO’, 12-MAR-79’) ) );

  47. Oracle 8 • Nested tables SELECT NT.BirthDate FROM THE( SELECT Animals FROM BREEDER WHERE BreederName = ‘Jane James’; ) NT WHERE NT.name = ‘Julio’;

  48. Oracle 8 • Nested tables INSERT INTO THE( SELECT Animals FROM BREEDER WHERE BreederName =‘Mike Jones’) VALUES (ANIMAL_TYPE( ‘DOG, ‘MARCUS’, ‘01-AUG-97’) );

  49. Oracle 8 • Nested tables INSERT INTO BREEDER VALUES (‘Joan Thomas’, cast(multiset( SELECT * FROM THE( SELECT Animals FROM BREEDER WHERE BreederName = ‘Jane James’)) AS ANIMAL_TABLE ) );

  50. Oracle 8 • Object tables

More Related