Please disable Adblockers and enable JavaScript for domain CEWebS.cs.univie.ac.at! We have NO ADS, but they may interfere with some of our course material.

SQL Tutorial (samt häufig genutzte Kommandos) (up)

 
Zum Arbeiten auf dem almighty.cs.univie.ac.at verwenden Sie bitte ein SSH-Tool, um sich von Ihrem Heimrechner zum Laborrechner zu verbinden. Auf der Zugangsdaten Seite finden Sie dazu die nötigen Hinweis zum Einloggen in die Laborumgebung.  
 
Als äußerst praktisch hat es sich erwiesen alle eigenen SQL-Skripte (*.sql) immer in einer Art "Arbeitsverzeichnis" abzulegen und diese aus diesem Verzeichnis heraus auszuführen. In diesem Tutorial legen wir hierzu ein Verzeichnis "sql" im eigenen Heimatverzeichnis am almighty.cs.univie.ac.at an mittels: 
 
mkdir sql
 
Danach wechseln wir in dieses Verzeichnis mittels 
 
cd sql
 
und legen dort unsere SQL-Skripts (*.sql) ab.  
 
Danach loggen Sie sich mittels sqlplus Tool (Kommando) in die Oracle Datenbank ein (siehe auch Zugangsdaten): 
 
sqlplus
 
Geben Sie als Benutzernamen a+<MatrNr> und als "default" Passwort "dbs13" an. 
 
user: a+<MatrNR>
pass: dbs13
 
Bitte ändern Sie nach erfolgtem ersten Login Ihr Passwort mittels: 
 
passw
 
Kommando (innerhalb der Oracle SQL Umgebung, SQL>), damit keine anderen Studierenden in die Versuchung kommen Ihr Datenbank zu manipulieren. 
 
Anmerkung: Starten Sie das sqlplus Tool immer von dem Verzeichnis aus in dem die ".sql" Dateien liegen, dann funktioniert die Ausführung eines SQL-Skripts ganz einfach mit @:  
 
SQL> @<skript>   // mit oder ohne sql-Dateiendung
 
So jetzt nun zu den einzelnen Skripts: 

Ausgangsbasis: ER-Diagramm (up)

Beispiel ER-Diagramm
Abbildung 1: Beispiel ER-Diagramm

env.sql (Zeilenlänge und/oder Seitenbreite ändern) (up)

Damit bei SQL Statements, welche auf der Oracle Datenbank ausgeführt werden, kein "unschöner" Zeilenumbruch nach ca. 80 Zeichen auftritt, kann man entweder die Zeilenlänge (LINE) und/oder die Seitenbreite (PAGESIZE) ändern. Die verwendeten Werte können an die eigenen Anforderungen (Bildschirmgröße/-auflösung, usw.) angepasst werden.  
 
SET LINE 120
SET PAGESIZE 200
 
Ausführen mittels: 
 
SQL> @env

showt.sql (Alle Tabellen der Datenbank anzeigen) (up)

Im Gegensatz zu anderen SQL Varianten (MySQL, MSSQL, …) funktioniert das Kommando "show tables;" zur Anzeige aller momentan erstellten Benutzertabellen unter Oracle NICHT und es muss hierzu die "user_tables" Tabelle der Oracle Datenbank abgefragt werden, was so funktioniert: 
 
SELECT table_name FROM user_tables;
 
Damit erhalten Sie dann eine Auflistung aller momentan existierenden eigens erstellten Tabellen.  
 
Ausführen mittels: 
 
SQL> @showt

showc.sql (Alle Constraints meiner Tabellen anzeigen) (up)

Um sich alle momentan existierenden Constraints (NOT NULL, FOREIGN KEY, PRIMARY KEY, CHECK, …) alle Tabellen anzeigen zu lassen benötigen Sie folgendes Kommando: 
 
SELECT table_name, constraint_name, r_constraint_name, constraint_type
FROM user_constraints
WHERE length(constraint_name) < 30
ORDER BY constraint_type, constraint_name;
 
Ausführen mittels: 
 
SQL> @showc

drop.sql (Tabelle löschen) bzw. Teil eines größeren Skripts (up)

Löschen der Tabelle student wobei darauf geachtet wird, dass zuerst die Fremdschlüsselbeziehungen (FOREIGN KEY constraints) "abgebaut" und dann erst die eigentliche Tabelle gelöscht wird, sofern diese nicht mehr von anderen Tabellen referenziert wird. 
 
DROP TABLE student CASCADE CONSTRAINTS;
 
Existiert die Tabelle nicht, dann erhalten Sie in etwa folgende Fehlermeldung:  
 
ORA-00942: table or view does not exist
 
Wenn die Tabelle erfolgreich gelöscht wurde erhalten Sie folgende Antwort:  
 
Table dropped.
 
Unter MySQL existiert für diese Fallunterscheidung (Tabelle existiert bzw. existiert nicht) das Kommando "IF EXISTS", also etwa: 
 
DROP TABLE student CASCADE CONSTRAINTS IF EXISTS;
 
Unter Oracle funktioniert diese Fallunterscheidung etwas anders, um auch bei einer vorher nicht vorhandenen Tabelle und dem dazu passenden Löschversuch keine Fehlermeldung zu erhalten: 
 
BEGIN
  EXECUTE IMMEDIATE 'DROP TABLE student CASCADE CONSTRAINTS';
EXCEPTION
  WHEN OTHERS THEN NULL;
END;
/

delete.sql (Löschen der Tabellendaten) bzw. Teil eines größeren Skripts (up)

Um nur die Inhalte einer Tabelle zu löschen, ohne jedoch das Tabellenschema zu beeinflussen eignet sich folgendes Statement: 
 
DELETE FROM student;
 
Sollte durch das Löschen der Datensätze ein Constraint verletzt werden (z.B. FOREIGN KEY Beziehung), dann erscheint etwa folgende Fehlermeldung:  
 
ORA-02292: integrity constraint (BERAN.SYS_C00275584) violated - child record found
 
Sollte das Löschen prinzipiell möglich sein, dann wird diese Aktion auch durchgeführt und die Zahl der gelöschten Datensätze zurück-/ausgegeben:  
 
30 rows deleted.

Anlegen einer neuen Tabelle (up)

Funktioniert nur, solange die neu anzulegenden Tabellen noch nicht existieren oder Sie diese zuvor mittels "DROP …" gelöscht haben. 
 
Anlegen der Tabelle "person", wobei bei Befüllung der Tabelle mit Daten bei jeder Prrson zumindest die svnr (Primärschlüssel) als auch der "vorname", "nachname" und die "plz" angegeben werden muss. Die Randbedingunge (constraints) zur Gewährleistung, dass "vorname" und "nachname" nicht leer (null) angegeben werden tragen zudem eine eigene Bezeichnung. Der Primärschlüssel wird in diesem Beispiel durch den "PRIMARY KEY" constraint festgelegt. 
 
CREATE TABLE person (
   svnr       NUMBER(10),
   vorname    VARCHAR2(20) CONSTRAINT pers_vorname NOT NULL,
   nachname   VARCHAR2(20) CONSTRAINT pers_nachname NOT NULL,
   ort        VARCHAR(20),
   plz        NUMBER(5) NOT NULL,
   strasse    VARCHAR2(80),
   gebdatum   DATE,
   gebort     VARCHAR(20),
   PRIMARY KEY (svnr)  
 );
 
Hier wird nun die "abteilung" Tabelle angelegt, wobei der "PRIMARY KEY" constraint gleich in der Zeile des PK-Attributes angegeben wird. Zusätzlich gibt es aber auch einen "CHECK" constraint, welcher gewährleistet, dass das "budget" immer mit einem Wert über 100.000 angegeben wird. Die "plz" ist mittels NUMBER(5) auf ein fünfstellige Zahl begrenzt, d.h. es können etwa auch deutsche Postleitzahlen gespeichert werden. 
 
CREATE TABLE abteilung (
   abtnr        INTEGER
     CONSTRAINT abt_pk PRIMARY KEY,
   bezeichnung  VARCHAR(15) CONSTRAINT abt_bez NOT NULL,
   ort          VARCHAR(20) CONSTRAINT abt_ort NOT NULL,
   plz          NUMBER(5) NOT NULL,
   strasse      VARCHAR2(80),
   budget       INTEGER,
   leiter       INTEGER NULL,
   CONSTRAINT abt_budget CHECK (budget >= 100000)
 );
 
In der Tabelle "mitarbeiter" kann man nun sehen, wie die Fremdschlüsselbeziehungen (FOREIGN KEY constraints) angelegt werden; 1) entweder in der entsprechenden Zeile ("arbeitet") oder für die Attribute "chef" und "svnr". Für diese, und alle anderen Constraints, kann man zudem Namen vergeben. 
 
CREATE TABLE mitarbeiter (
   persnr      INTEGER
     CONSTRAINT ma_pk PRIMARY KEY,
   arbeitet    INTEGER
     CONSTRAINT ma_fk_abteilung REFERENCES abteilung(abtnr) ON DELETE CASCADE,
   chef        INTEGER,
   punkte      NUMBER(3) NULL,
   klappe      VARCHAR(4) CONSTRAINT ma_klappe UNIQUE,
   svnr        NUMBER(10),
   CONSTRAINT ma_fk_person FOREIGN KEY (svnr) REFERENCES person ON DELETE CASCADE,
   CONSTRAINT ma_fk_chef FOREIGN KEY (chef) REFERENCES mitarbeiter(persnr)
 );
 
Zu guter Letzt die "student" Tabelle, welche einen CHECK constraint bzw. das "einkommen" als Gleitkommazahl (8-stellig, 2-Nachkommastellen) beinhaltet. 
 
CREATE TABLE student (
   matrnr      VARCHAR2(7),
   inskription CHAR(6) CONSTRAINT stud_insk NOT NULL,
   geschlecht  CHAR(1),
   einkommen   NUMBER(8,2),
   svnr        NUMBER(10),
   CONSTRAINT stud_pk PRIMARY KEY (matrnr),
   CONSTRAINT stud_fk_person FOREIGN KEY (svnr) REFERENCES person ON DELETE CASCADE,
   CONSTRAINT stud_geschlecht CHECK(geschlecht in ('m','w'))
 );
 
Ein Constraint konnte bislang nicht realisiert werden, da zwischen "abteilung" und "mitarbeiter" eine zyklische Abhängigkeit besteht (Abteilung besitzt einen Leiter vom Typ Mitarbeiter, Mitarbeiter hat ein Arbeitsverhältnis zu/in einer Abteilung). Deswegen wird der "leiter" constraint erst im Nachhinein definiert.  
 
ALTER TABLE abteilung ADD
  CONSTRAINT abt_fk_leiter FOREIGN KEY (leiter) REFERENCES mitarbeiter(persnr);
 
Um nun die "persnr" für neue Mitarbeiter aufsteigend zu realisieren bedienen wir uns einer SEQUENCE und eines TRIGGERS, da in Oracle keine "auto_increment" Anweisung wie etwa in MySQL existiert. Die verwendete Sequenz dient dabei als Zahlengenerator, wobei diese bei 1 beginnt und in 1er Schritten weiter geht. Der Trigger setzt dann die "persnr" vor jedem INSERT Statement, welches auf "mitarbeiter" ausgeführt wird. 
 
Die Sequenz: 
 
CREATE SEQUENCE seq_persnr
  INCREMENT BY 1
  START WITH 1;
 
Der Trigger: 
 
CREATE OR REPLACE TRIGGER trigger_persnr
  BEFORE INSERT ON mitarbeiter
  FOR EACH ROW
    DECLARE
      my_seq mitarbeiter.persnr%type;
    BEGIN
      SELECT seq_persnr.nextval INTO my_seq FROM dual;
      :new.persnr := my_seq;
    END;
/
 
Alternativ dazu kann der Trigger aber auch nur einen Werte-Check durchführen. Der folgende Trigger überprüft, ob die Inskription im Format ([W|S]S\d{4}) angegeben wurde (also mit 'WS' oder 'SS' gefolgt von einer Jahreszahl größer gleich 1900). 
 
CREATE OR REPLACE TRIGGER trigger_inskription
AFTER INSERT OR UPDATE ON student
FOR EACH ROW
DECLARE
  semester char(2);
  jahr char(4);
BEGIN
  -- parse out semester and year
  SELECT SUBSTR(:new.inskription, 1, 2) INTO semester FROM DUAL;
  SELECT SUBSTR(:new.inskription, 3, 4) INTO jahr FROM DUAL;
  -- test if semester is correct
  IF semester != 'WS' and semester != 'SS' THEN
    raise_application_error(-20001, 'Attribut Inskription startet nicht mit SS oder WS sondern mit ' || semester);
  END IF;
  -- test if year is correct
  IF TO_NUMBER(jahr) < 1900 THEN
    raise_application_error(-20002, 'Attribut Inskription hat ein ungueltiges Jahr, naemlich ' || jahr || ' (kleiner 1900)');
  END IF;
END;
/
 
Abschließend wird nun noch ein VIEW erstellt, welcher die Nachnamen (surname) und Postleitzahlen (regcode) aller Wiener beinhaltet. 
 
CREATE VIEW wiener AS
  SELECT nachname AS surname, plz AS regcode
  FROM   person
  WHERE  ort = 'Wien'
;
 
Anmerkung: Sehen Sie dazu auch das Oracle DDL/SQL Cheat Sheet
 

Überprüfen, ob alles "gut gegangen" ist (up)

Mittels "DESC" lässt sich die Tabellenstruktur ausgeben: 
 
DESC <table>;
 
Mittels SELECT * lassen sicht alle Testdatensätze ausgeben bzw. mit count(*) einfach nur die Anzahl. 
 
SELECT * FROM <table>;
SELECT count(*) FROM <table>;

insert.sql (Eingabe von Testdaten) (up)

Löschen aller bisherigen Daten: 
 
UPDATE abteilung SET leiter = NULL;   // wegen zyklischer Fremdschlüsselbeziehung
DELETE FROM mitarbeiter;
DELETE FROM abteilung;
DELETE FROM student;
DELETE FROM person;
 
Hinzufügen der Testdaten: 
 
INSERT INTO person VALUES ('000001011982', 'Peter Paul', 'Beran', 'Wien', 1220, 'Waldweg 1', '01-JAN-1982', 'Wien');
INSERT INTO person VALUES ('000002021980', 'Juergen', 'Mangler', 'Wien', 1070, 'Baumgasse 2', '02-FEB-1980', 'Dornbirn');
 
INSERT INTO student VALUES ('0200945', 'WS2002', 'm', 1234.56, '000001011982');
 
INSERT INTO abteilung VALUES (10, 'Institut KBE', 'Wien', 1010, 'Rathausstrasse 19/9', 8000000, NULL);
 
INSERT INTO mitarbeiter (arbeitet, chef, punkte, klappe, svnr) VALUES (10, null, 100, '39518', '000002021980');
 
Hier wird der Mitarbeiter (Mangler) gesucht, um diesen nachträglich in der "abteilung" Tabelle als Abteilungsleiter einzutragen. 
 
UPDATE abteilung SET leiter = (SELECT persnr FROM mitarbeiter WHERE svnr = '000002021980') WHERE abtnr = 10;

Erstellung von Stored Procedures (up)

Der folgende Code erstellt eine Stored Procedure namens "name_abt", welche einen Nachnamen entgegennimmt, und eine Abteilungsnummer zurückliefert. Der Inputparameter "nn" ist vom Typ Varchar2, während der Outputparameter "abt" vom Typ Number ist. Es werden alle Mitarbeiter gesucht, deren Nachname mit dem übergebenen Nachnamen "nn" übereinstimmt, und geschaut in welcher Abteilung diese arbeiten (abtnr). Das Ergebnis dieser SQL-Query wird in den Rückgabeparameter "abt" gespeichert. 
 
create or replace PROCEDURE name_abt(nn IN VARCHAR2, abt OUT NUMBER) IS
BEGIN
  Select a.abtnr INTO abt from person p,mitarbeiter m ,abteilung a
  where p.nachname=nn AND p.svnr=m.svnr AND m.arbeitet=a.abtnr;
END;

SQL Scripts (up)

Letzte Änderung: 25.10.2016, 11:21 | 1682 Worte