Oracle and Object-Oriented SQL by bns26590

VIEWS: 47 PAGES: 12

									Oracle and Object-Oriented SQL

    Oracle specific SQL commands
        User-defined types
• CREATE TYPE source
  AS OBJECT
  (
     name VARCHAR2(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( 'R2D2', 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 VARCHAR2(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;
  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 VARCHAR2(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”!

								
To top