1 / 12

Oracle and Object-Oriented SQL

Learn about creating user-defined types, inheritance, and accessing types in Oracle using object-oriented SQL commands.

odiss
Download Presentation

Oracle and Object-Oriented SQL

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 and Object-Oriented SQL Oracle specific SQL commands

  2. User-defined types • CREATE TYPE sourceAS OBJECT( name VARCHAR2(30), ra FLOAT, dec FLOAT ) NOT FINAL; /

  3. Inheritance • CREATE TYPE bsource UNDER source ( bmag NUMBER(5, 2) ) NOT FINAL; /

  4. Instantiating a type • An object table is a table with a single column of a user-defined typeCREATE TABLE sources OF source; • INSERT INTO sourcesVALUES( rsource( 'R2D2', 4.3, -22.9, 21.2 ));

  5. Accessing types • SELECT VALUE(*) FROM sources;retrieves constructors from “sources” • CREATE VIEW sources$vOF source;an object view with objects of type “source” • CREATE VIEW bsources$v OF bsourceUNDER sources$v;an object view with objects of type “bsource” which, at the same time, makes those objects available from the “sources$v”view

  6. References • CREATE TABLE mysources( petsource VARCHAR2(170), bestars REF source);

  7. Methods • Include in the TYPE definition:MEMBER FUNCTION lon() RETURN NUMBER • CREATE TYPE BODY sourceAS MEMBER FUNCTION lon ()RETURN NUMBERIS BEGIN /* left as an exercise */ RETURN lon; END;END;

  8. Varying Arrays • Fixed maximum size • Fixed order • CREATE TYPE intarrayAS VARRAY(20) OF INTEGER; /

  9. Nested tables • Unlimited maximum size • Random order • CREATE TYPE intarrayAS TABLE OF INTEGER; • CREATE TABLE demo( name VARCHAR2(37), v intarray )NESTED TABLE v STORE AS vtable;

  10. Command-line usage • sqlplus username@databaseservice • @script - start script named script.sql • edit – edit the buffer • describe – gives definition of datastructures • spool – write results to file • !command – run unix command in a shell • ! – spawn a unix shell

  11. GUI access • oemapp worksheet • oemapp console (requires SELECT ANY DICTIONARY privilige) • Follow the built-in quick tours for an overview of database concepts and functions. • Use the ‘Show SQL’ button to see what the equivalent SQL query is for a GUI operation

  12. Python • Python “class” ~ Oracle “type” • Python DB API for direct access • DBObject for ASTRO-WISE access • from astro.database.DBMain import DBObject, persistentclass Source(DBObject): name = persistent(‘The name’, str, ‘Nameless’) ra = persistent(‘R.A.’, float, 0.0) dec = persistent(‘Declination’, float, 0.0) • The class is mapped to a “TYPE”, a “TABLE” and a “VIEW”!

More Related