Oracle and ObjectOriented SQL Oracle specific SQL commands
Oracle and Object-Oriented SQL Oracle specific SQL commands
User-defined types • CREATE TYPE source AS OBJECT ( name VARCHAR 2(30), ra FLOAT, dec FLOAT ) NOT FINAL; /
Inheritance • CREATE TYPE bsource UNDER source ( bmag NUMBER(5, 2) ) NOT FINAL; /
Instantiating a type • An object table is a table with a single column of a user-defined type CREATE TABLE sources OF source; • INSERT INTO sources VALUES ( rsource( 'R 2 D 2', 4. 3, -22. 9, 21. 2 ) );
Accessing types • SELECT VALUE(*) FROM sources; retrieves constructors from “sources” • CREATE VIEW sources$v OF source; an object view with objects of type “source” • CREATE VIEW bsources$v OF bsource UNDER sources$v; an object view with objects of type “bsource” which, at the same time, makes those objects available from the “sources$v” view
References • CREATE TABLE mysources ( petsource VARCHAR 2(170), bestars REF source );
Methods • Include in the TYPE definition: MEMBER FUNCTION lon() RETURN NUMBER • CREATE TYPE BODY source AS MEMBER FUNCTION lon () RETURN NUMBER IS BEGIN /* left as an exercise */ RETURN lon; END;
Varying Arrays • Fixed maximum size • Fixed order • CREATE TYPE intarray AS VARRAY(20) OF INTEGER; /
Nested tables • Unlimited maximum size • Random order • CREATE TYPE intarray AS TABLE OF INTEGER; • CREATE TABLE demo ( name VARCHAR 2(37), v intarray ) NESTED TABLE v STORE AS vtable;
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
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
Python • Python “class” ~ Oracle “type” • Python DB API for direct access • DBObject for ASTRO-WISE access • from astro. database. DBMain import DBObject, persistent class 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”!
- Slides: 12