Einfuehrung in ORACLE ===================== Historie und Produkte: ---------------------- - Oracle Corporation war der erste kommerzielle Anbieter fuer relationale Datenbanksysteme - 1979 Einfuehrung des Oracle-RDBMS - heute (einer) der groessten Anbieter weltweit - Unterstuetzung fuer etwa 120 Plattformen vom PC bis Mainframe - Portabilitaet der Daten und Anwendungen - Verbindung ueber Netz-Tools - Moeglichkeit zum Zugriff auf DB2 und andere DBs / Dateisysteme - Anbindung von 3GL-Hochsprachen ueber Precompiler oder Procedure-Calls - neben DB-Kern werden diverse Produkte und Tools angeboten (u.a. SQL*Plus, Developer (Forms, Reports, ...), CASE-Tools) am Informatik-RZ ist z.Z. verfuegbar: - RDBMS Vers. 10g (mit PL/SQL, JavaVM, XLM, ...) - SQL*Plus - JDBC, SQLJ, EJB, XML-UnterstŸtzung - Pro*C - diverse Tools (Ex- und Import, Loader, Administration, ...) SQL*Plus: interaktive (auch Batch) Schnittstelle auf Basis von (ANSI-) SQL und Erweiterungen (PL/SQL sowie spezielle Oracle-Kommandos) JDBC, SQLJ, EJB: Java-Schnittstellen in verschiedenen Varianten Ÿber z.T. standar- disierte Packages bis hin zu Enterprise Java Beans (Corba). Pro*C und OCI: Einbindung von SQL-Anwendungen in die Sprache C. Dabei kann entweder ein Precompiler oder eine Library-Anbindung verwendet werden. Ersterer ist einfacher zu bedienen. Achtung: zur Gross-/Kleinschreibung ----------------------------------- In Oracle ist die Schreibweise der Datenbankobjekt-NAMEN (Tabellen, Views, Trigger, ...) und syntaktischen Konstrukte irrelevant (anders z.B. als in MySQL). Zu beachten ist sie jedoch insbes. beim Vergleich von (Text-) WERTEN!!! Ggf. sind Funktionen wie upper(...), lower(...) zu verwenden, um case-insensitive Vergleiche zu machen! Hinweise zu JDBC und SQLJ: -------------------------- Es gibt eine Reihe von Varianten mit Vor- und Nachteilen bei der Programmierung und Performanz. Meist bietet sich JDBC im thin-Modus an (ggf. kombiniert mit SQLJ), um dann auch Applets benutzen zu kšnnen. Beispiele und Doku: s.o. ! Achtung: Bei Applets muss die Codebase richtig gesetzt sein und ausserdem mŸssen ALLE relevanten Classes etc. in dem Verzeichnis liegen! Nach AusfŸhren von def_oracle.sh sind die Oracle-classes eingebunden im Classpath! Nachfolgend die Kernzeilen fŸr den Zugriff auf die DB im "Thin"-Modus: >>>>! // Load the Oracle JDBC driver DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); // Connect to the database // You must put a database name after the @ sign in the connection URL. // You can use either the fully specified SQL*net syntax or a short cut // syntax as ::. The example uses the short cut syntax. Connection conn = DriverManager.getConnection ("jdbc:oracle:thin:@rzdspc9:1521:edu", "user", "passwd"); // Create a Statement Statement stmt = conn.createStatement (); // Select the ENAME column from the EMP table ResultSet rset = stmt.executeQuery ("select ENAME from EMP"); // Iterate through the result and print the employee names while (rset.next ()) System.out.println (rset.getString (1)); <<< System R (1977), SQL/DS, DB2, Oracle) * alle Informationen ueber Objekte sind in Relationen dargestellt * jede Relation stellt eine Beziehung zwischen Mengen dar und wir durch einen Namen identifiziert (z.B. PERSON) * jede Menge ist definiert durch eine Liste von Eigenschaften oder Werten, die die Elemente der Menge haben muessen (Domain) * jedes Attribut einer Relation bezieht sich auf eine Domain * jede Relation besitzt eine Anzahl solcher Attribute, sie ist gleich dem cartesischen Produkt seiner Attribute * jede Attribut-Kombination (Tupel) tritt nur einmal auf * bereits ein Teil der Attribute einer Relation koennen zur eindeutigen Beschreibung eines Tupels genuegen (Schluessel), eine solche minimale Attribut-Kombination nennt man Primaerschluessel (haeufig kuenstlich hinzugefuegt, z.B. lfd.Nr.) * Fremdschluessel sind solche Attribute, die auf den Schluessel einer anderen Relation verweisen - Bsp: Relation PERSON (NAME, ORT, PERSNR, ABNR) Relation ABTLG (ABNAME, CHEF, ABTNR) NAME, ORT, ... sind Attribute PERSNR hat die Domain {1,2, ...,9999} (PERSNR,NAME) ist Schluessel von PERSON PERSNR ist Primaerschluessel "" ABNR ist Fremdschluessel "" PERSON | NAME | ORT | PERSNR | ABNR ------------------------------------------ | Meier | Hamburg | 33 | 2 | Mueller | Kiel | 10 | 1 | Meier | Wedel | 46 | 2 ... ABTLG | ANAME | CHEF | ABTNR ---------------------------------- | Einkauf | 10 | 2 | Verkauf | 5 | 1 | Werbung | 15 | 3 ... + zusaetzliche Integritaetsregeln * Entity-Integritaet: jede Relation hat einen Primaerschluessel * Referentielle Integritaet: jeder Fremdschluessel verweist auf einen Primaerschluessel einer anderen Relation * Benutzerdefinierte Integritaet: die Zulaessigkeit von Werten einer Domaene kann eingeschraenkt werden (Bsp.: die Auslieferung kann erst nach der Herstellung erfolgen; ein "Abteilungsleiter hat PERSNR < 30") + wichtige Operationen * SELEKTION: Auswahl einer Teilmenge von Tupeln einer Relation Bsp.: PERSON (NAME="Meier") * PROJEKTION: Auswahl einer Teilmenge von Attributen einer Relation Bsp.: PERSON (ORT) * CARTESISCHES PRODUKT: Verknuepfung mehrerer Relationen zu einer Ergebnisrelation; das Ergebnis weist alle Attribute der Ursprungsrelationen auf und enthaelt alle(!) Kombinationen der Tupel Bsp.: PERSON x ABTLG * VERBUND: Kombination aus Produktbildung und Selektion; Vergleich von Spalten bei der Produktbildung, z.B. werden nur die Tupel verknuepft, bei denen die Attributwerte zweier ausgewaehlter Spalten gleich sind (Equijoin) Bsp.: PERSON x ABTLG (PERSON.ABNR = ABTLG.ABTNR) - je nach Art der Spezifikation der gewuenschten Relation (durch eine Folge von Operationen auf Tupel oder die Angabe von Bedingungen, die diese erfuellen muessen) sind die Relationen-Algebra oder das Relationen-Kalkuel gleichwertige und vollstaendige Methoden zum Umgang mit Relationen - Zur Vermeidung von Redundanz und sog. Anomalien beim Lesen, Einfuegen, Loeschen etc. gibt es den Vorgang der Normalisierung (-> 1., 2., 3. Normalform,...). Darauf gehen wir nicht ein. Praktische Sichtweise: ---------------------- - Relationen sind (ungeordnete) Tabellen - die Spalten einer Tabelle sind die Attribute - die Zeilen einer Tabelle sind die Tupel - Operationen auf Tabellen ergeben wieder neue Tabellen, d.h. das Ergebnis einer Operation ist i.a. eine Menge(!) von Tupeln und nicht ein einzelner Wert oder Tupel; hierin unterscheidet sich eine relationale DB von anderen Typen! Einige technische Details: -------------------------- - Oracle laesst den parallelen Zugriff vieler Benutzer zu; ggf. sogar von verschiedenen Rechnern im Netzwerk - es laufen mehrere Prozesse im Hintergrund, die mit den Oracle-Benutzern und Tools kommunizieren - ausgefeilte Sicherungsmassnahmen garantieren die Konsistenz und den konfliktfreien Zugriff auf die gespeicherten Daten - im Fehlerfall koennen die letzten Operationen automatisch rueckgaengig gemacht werden; auch komplette Systemabstuerze sind durch spezielle Sicherungsverfahren (meist) ohne Auswirkung auf den Datenbestand - jeder Benutzer erhaelt einen eigenen Benutzernamen und Passwort; diese sind unabhaengig vom darunterliegenden Betriebssystem - Zugriffsrechte (Privilegien) koennen auf alle Arten von Datenbankobjekten erteilt oder auch entzogen werden, jeder Benutzer kann das fuer seine eigenen Objekte tun - alle Tabellen und anderen DB-Objekte werden vom Oracle-System-Kern in einem oder mehreren Betriebssystem-Files verwaltet; von Unix aus gibt es keine direkte Einflussmoeglichkeit - SQL-Anfragen werden vor der Ausfuehrung analysiert und optimiert, um moeglichst kurze Antwortzeiten bei gleichzeitig geringem Speicher- bedarf zu erzielen SQL*PLUS: --------- - Aufruf von SQL*PLUS: $ sqlplus [user [/passw]] [options] - Beendigung von SQL*PLUS: Crtl-d oder EXIT - Protokoll vom Bildschirm: SPOOL ; Ende mit SPOOL OFF - SQL-Kommandos koennen ueber mehrere durch RETURN abgeschlossene Zeilen gehen (Subqueries etc.) und werden erst durch ";" abgeschlossen. Eine Aenderung des Kommandos sollte dann mittels Editor erfolgen! - Editor-Benutzung (default: vi): EDIT [.SQL] Ohne wird der der aktuelle Pufferinhalt in ein Dummy-File uebernommen. Nach dem Sichern stehen die geaenderten Zeilen wieder im Puffer und koennen mit RUN gestartet werden. - Externe SQL-Prozeduren koennen durch START ausgefuehrt werden. - beim Zugriff auf FREMDE Tabellen (anderer User) MUSS der Owner-Name vorangestellt werden, z.B. SCOTT.EMP, bei eigenen Tabellen ist das nicht noetig, aber erlaubt! - man kann dies umgehen durch -- Kopieren der Tabelle in den eigenen Bereich (aufwendig, Platz!) -- Kreieren eines SYNONYMS (z.B. CREATE SYNONYM myemp FOR scott.emp;) -- Kreieren eines VIEWS (z.B. CREATE VIEW myemp AS SELECT * FROM scott.emp;) - Voraussetzung ist die Erteilung des Zugriffsrechtes durch den Owner! z.B. GRANT SELECT,INSERT,UPDATE ON myemp TO demo, ops$meier; - Fuer ALLE User kann mittels GRANT ... TO PUBLIC; erteilt werden. Durch Anhaengen von ...WITH GRANT OPTION wird das Recht zur Weiter- gabe des Rechts ebenfalls erteilt! - REVOKE UPDATE,... ON myemp FROM user; entzieht das Recht. - String-Vergleiche (CHAR-Typ) sind CASE-SENSITIVE !! Mittels UPPER(...) und LOWER (..) kann dies umgangen werden. - Der Operator LIKE ermoeglicht die Wildcard-Suche z.B. ...WHERE name LIKE 'MEI%'; sucht alles, was mit 'MEI' beginnt, ...WHERE name LIKE 'M_IER'; sucht alles, was an zweiter Position irgendein Zeichen hat und den Rest genau erfuellt, wie 'MEIER', 'MAIER'; - Um Informationen ueber eigene und fremde, aber zugreifbare Datenbank- objekte zu erhalten sind systemweit viele Tabellen und Views definiert, die mit dem Praefix ALL_ bzw. USER_ beginnen. Ausserdem gibt es ein paar Abkuerzungen, die ebenfalls jedermann benutzen kann: CAT, COLS, DICT, SYN, IND Mittels SELECT ... kann man sich deren Inhalte ansehen. - (Zwischen-) Ergebnisse von Queries koennen nicht zurueckgeholt werden. Man kann sie lediglich in geeignete Tabellen laden oder im Protokoll-File (-> Spool) textuell festhalten. Nach Ausfuehrung einer Query sind alle temporaeren Daten geloescht. - Alle lesenden Zugriffe auf Informationen erfolgen ueber SELECT (und DESCRIBE)! - Veraenderung von Tabellendefinitionen: Eine existierende Tabelle kann in ihrer Struktur veraendert werden mit ALTER TABLE name ... Dabei sind nicht alle Optionen stets zulaessig, z.B. kann immer eine zusaetzliche Spalte erzeugt werden, eine Spaltendefinition vergroessert werden; nicht zulaessig sind die Verkleinerung einer Spalte oder die Aenderung des Spaltentyps, wenn bereits Werte eingetragen wurden. Aehnlich verhaelt es sich mit Einschraenkungen auf Tabellen oder Spalten (z.B. NOT NULL). - ALIAS-Namen: Bei SELECT-Statements kann (manchmal muss) ein Alias-Name fuer Tabellen oder Spalten definiert werden. Er dient der Aufloesung von Namenskonflikten, zum Abkuerzen von Namen und zur Formulierung von "correlated subqueries". Darueberhinaus kann die Spaltenueberschrift nach Wunsch veraendert werden. Bsp.: SELECT a.ename name_1, b.ename name_2, b.deptno "Dept. No." FROM scott.emp a meier.emp b WHERE a.ename = b.ename; -> a steht fuer Tabelle scott.emp -> name_1 steht als Ueberschrift fuer scott.emp.ename -> "Dept. No." ist die Ueberschrift fuer b.deptno Auch bei der Erzeugung von VIEWS kann man die ausgewaehlten Attribute (Spalten) umbenennen: CREATE VIEW name (alias,..) AS query.. - Ausgabeformatierung: Neben der der oben erwaehnten Moeglichkeit zur Aenderung von Spalten- ueberschriften gibt es unter SQL*PLUS diverse Optionen zur Formatierung der Ueberschriften und Ausgabe der Spaltenwerte (erweitertes SQL!). Mit der SQL*Plus-Anweisung COL[UMN] lassen sich die Spaltenformate definieren: COL[UMN] [{col|expr} [option...]] Dabei kann option enthalten - ALIAS -- Ersatzname, der bei anderen Kommandos benutzt werden kann - CLEAR -- Ruecksetzen auf Defaultwerte - FORMAT -- Anzeigeformat der Spalte, z.B. An -- CHAR mit n Stellen 9990 -- NUMBER 4-stell., kein Blank bei Wert Null 99.99 -- NUMBER mit 2 Vor-, 2 Nachkommastellen $9999 -- NUMBER mit Dollarzeichen vorweg und HEA[DING] text -- mit folgender Ueberschrift... JUS[TIFY] {L[eft] | C[enter] | R[ight]} -- Ausrichtung WRA[pped] -- Umbruch bei zu langem String TRU[ncated] -- Abschneiden des zu langen Strings Beispiele: SQL> COL ename FORMAT A10 WRAP JUS RIGHT HEADING 'EMPLOYEE |NAME' -- die Spalte ename haette 10 Stellen rechtsbuendig, die Ueberschrift wird auf zwei Zeilen verteilt (Trennstrich), der Inhalt bei Bedarf umgebrochen SQL> COL sal+bonus-tax-insurance ALIAS income SQL> COL income FORMAT $999.990.99 -- der Ausdruck sal... wird durch income ersetzt und formatiert Die Anzahl der insgesamt verfuegbaren Optionen ist sehr gross und wird fuer unsere Zwecke kaum benoetigt. Sie wirken z.T. auch mit anderen Voreinstellungen zusammen. Bei Bedarf kann man gelegentlich noch einmal darauf eingehen. Eine weitere, sehr maechtige Moeglichkeit, das Format der Ausgaben zu aendern bietet SQL*PLUS mit den Konvertierungs- und Transformations- routinen. Dazu gehoeren z.B. - TO_CHAR (...) - TO_DATE (...) - TO_NUMBER (...) - DECODE (...) - NVL (...) - REPLACE (...) - SUBSTR (...) - UPPER (...) - LOWER (...) - INITCAP (...) - LTRIM (...) - RTRIM (...) Beispiel: SELECT ename, DECODE(mgr,7839,'KING',7251,'JONES', 'NONE') mgr FROM emp; ENAME MGR ---------- ------------ JONES KING CLARK KING OAKLEY NONE FORD JONES Die Beschreibung dieser und vieler weiterer Funktionen sollte im Help enthalten sein und kann bei Bedarf nachgefragt werden. Sie bieten z.T. sehr umfangreiche Optionen zur Manipulation von Daten an, die den Rahmen dieser Einfuehrung sprengen wuerden. - Zu den obigen Funktionen kommen noch Pseudo-Spaltennamen hinzu wie SYSDATE, UID, USER, USERENV u.a., die Zugriff auf Systemvariablen ermoeglichen. Man kann diese Werte ansehen durch Formulierung von SELECT-Statements auf eine Dummy-Tabelle. Sinn und Zweck einer solchen Formulierung ist nur die Einhaltung der syntaktischen Vorgaben. Bsp.: SELECT SYSDATE, USER FROM DUAL; - Definition von SQL*PLUS-Voreinstellungen: SQLPLUS verwendet viele Parameter/Variablen zur Festlegung von Bildschirmformat, Puffergroessen, Zeilenumbruch, Seitenlaenge etc. Fuer uns sind eventuell folgende Kommandos von Interesse: - SET arraysize n -- Anz. Rows pro Zugriff - SET autocommit {OFF | ON} -- Uebernahme von Aenderungen sofort - SET echo {OFF | ON} -- Anzeige der Kommandos bei START file - SET heading {ON | OFF} -- Ausgabe der Spaltenueberschriften - SET linesize {80 | n} -- Zeilenlaenge bei Ausgabe - SET long {80 | n} -- Anzeigelaenge von LONG-Daten - SET pagesize {14 | n} -- Anz. Zeilen pro Seite - SET pause {OFF | ON} -- Ausgabestopp nach einer Seite - SET termout {ON | OFF} -- Ausgabe auf Bildschirm an/aus - SET underline {ON | OFF | c} -- Unterstreichung der Spaltenheader - SET verify {ON | OFF} -- Anzeige von Variablenersetzungen - SET wrap {ON | OFF} -- Abschneiden der Ausgabe auf Linesize - Kommentare in SQL-Statements: In jeder SQL-Anweisung duerfen Kommentare eingestreut werden. Sie haben die Form /* Kommentar */ In SQL-Prozeduren koennen auch Zeilenkommentare mittels des Kommandos REM[ARK] text eingefuegt werden. - SUBQUERIES: Subqueries sind SELECT-Statements die im Bedingungsteil einer anderen Query auftreten. Bsp.: SELECT ename FROM emp WHERE deptno IN ( SELECT deptno FROM DEPT WHERE ....); CORRELATED QUERIES sind solche Queries, bei denen die verschachtelte Subquery jeweils EINMAL PRO ZEILE der einhuellenden Query ausgefuehrt werden soll und nicht nur einmal fuer alle Rows gleichzeitig. Bsp.: SELECT ename,sal FROM emp x WHERE sal = (SELECT MAX (sal) FROM emp WHERE x.deptno = deptno); Durch die Verwendung einer Spalte aus der aeusseren Query in der innerern Query (x.deptno) wird dieses Anliegen ausgedrueckt. - Behandlung von NULL-Werten: Beim Eintragen von Tupeln in eine Tabelle koennen auch einzelne Attribute (Spalten) weggelassen werden (s. INSERT-Syntax), jedoch nur dann, wenn die Spalten bei der Tabellendefinition nicht mit NOT NULL eingeschraenkt wurden. Weggelassene Werte werden NICHT mit Defaults (z.B. 0 oder Blank) besetzt, sondern erhalten den speziellen Eintrag NULL. Vergleiche auf diesen Feldern ergeben daher haeufig andere Ergebnisse, als erwartet, z.B. ergibt der Vergleich eines NULL-Feldes mit 0 (NUMBER) immer FALSE! Der Operator IS NULL und diverse Vergleiche zwischen NULL- und anderen Werten sollte ausgiebig getestet werden! Neben NOT NULL gibt es diverse andere Einschraenkung, die von Oracle bei der Manipulation von Tabellen geprueft wird. Andere Constraints (UNIQUE, REFERENCES,...) (ab Oracle7) koennen in der Doku nachgelesen werden. Hinweise zur C-Schnittstelle: Pro*C: ------------------------------------ Man kann auf Oracle-DBs aus C-Programmen heraus zugreifen. Es gibt zwei Methoden: 1) Verwendung eines Precompilers (PRO*C), 2) Verwendung einer Bibliothek von DB-Funktionen (OCI). Die Methode 1) ist meist einfacher zu handhaben und daher zu empfehlen. Zu 1): Man erstellt C-Quellen, die spezielle Makros enthalten. Diese werden per Makefile (proc.mk) voruebersetzt (expandiert) und an den C-Compiler weitergereicht. Zu 2): Unter Einbindung diverser Header-Files muss "zu Fuss" eine Kommunikationsumgebung aufgebaut werden, um schliesslich per Funktionsaufruf die entsprechenden Oracle-Zugriffe umzusetzen. Z.Z. ist offiziell nur Sun-cc-Compiler erlaubt. Die Anbindung von C-Programmen an die Datenbank-Schnittstelle erfolgt (am einfachsten, s.o.) ueber einen PreCompiler, der in den Quelltext einge- fuegte Makros expandiert. Diese Makros enthalten im wesentlichen SQL-Anweisungen wie sie unter Sqlplus verwendet werden. Syntaktisch unterscheiden sie sich im wesentlichen dadurch, dass sie die zu uebertragenden Daten an bzw. von sog. Host-Varibalen des C-Programms uebergeben bzw. holen. Diese Host-Varibalen werden im C-Quelltext deklariert und sind in den Makros durch den Praefix ":" gekennzeichnet. Hinzu kommen ggf. sog. Indikatorvariablen, die zur Fehlererkennung (NULL- Werte u.ae.) dienen. Wesentlicher Unterschied zwischen dem interaktiven SQLPLUS und Pro*C: Im interaktiven Sqlplus werden ggf. Mengen (d.h. mehrere Trefferzeilen) gleichzeitig bhandelt (ausgegeben), in C kann man nur jeweils eine Zeile zur Zeit behandeln. Man erreicht die Bearbeitung von Mengen durch Definition eines CURSORs, der z.B. das SELECT-Statement "verarbeitet" und die Treffermenge bereit- haelt, die einzelne Trefferzeilen werden in einer Schleife mittels FETCH geholt und verarbeitet. Ablauf: OPEN CURSOR..., FETCH..., CLOSE CURSOR. Bespiele fuer verschiedene Zugriffsarten und Methoden sind in den Dateien sample*.pc zu finden. Sonstiges: ---------- Achtung bei den Datentypen: CHAR ist ein FIXED-LENGTH-Typ VARCHAR2 ist ein Variable-Length-Typ Wichtige Tabellen: allgemein: USER_ .... Objekte des (aktiven) Benutzers ALL_ .... Objekte aller Benutzer, auf denen Zugriff erlaubt ist speziell: DICT Dictionary-table ueber alle User USER_CATALOG -"- USER_TABLES eigene Tabellen USER_OBJECTS eigene Objekte usw. Hinweis zum LISTENER: Die Kommunikation durch SQL*Net wird ueber einen sogen. LISTENER- Prozess abgewickelt. Dieser nimmt VerbindungswŸnsche zur DB an und ordnet dem Prozess einen eigenen Kanal zu (Dispatcher). Ab dann lŠuft die Kommunikation direkt. Thin-JDBC arbeitet unabhŠngig von SQL*Net!