08_BusinessLogic

Code-Dateien

Dateiname	Aktion
CODECode_Meal.zip	Download
CODECode_Taxi.zip	Download
CODECode_Train.zip	Download

PDF-Dokumente

Dateiname	Aktion
PDFUebung_Meal.pdf	Öffnen

Videos

Dateiname	Aktion
VIDEOVideo_Meal_E_Part1	Abspielen
VIDEOVideo_Meal_E_Part2	Abspielen
VIDEOVideo_Taxi_D	Abspielen
VIDEOVideo_Train1_SELECT	Abspielen
VIDEOVideo_Train2_Function	Abspielen
VIDEOVideo_Train3_Cursor	Abspielen

Lernmaterialien

Visual Studio Code

Create

Drop User and Tablespace

DROP TABLESPACE TBS_GRIESMAYER_PLSQL INCLUDING CONTENTS AND DATAFILES;
DROP USER GRIESMAYER CASCADE;

Der Benutzer und Tablespace wird gelöscht. Bei der 1. Durchführung liefern die beiden Statements einen Fehler!

SELECT sid, serial#, status, machine, program
FROM v$session
WHERE username = 'GRIESMAYER';

ALTER SYSTEM KILL SESSION '46,27496' IMMEDIATE;

Löscht die bestehenden Datenbankverbindungen.

Create Tablespace

CREATE SMALLFILE TABLESPACE TBS_GRIESMAYER_PLSQL
DATAFILE '/opt/oracle/oradata/XE/XEPDB1/griesmayer_plsql1.dbf' SIZE 200M
LOGGING EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO;

Legt einen neuen Tablespace an.

$ docker exec -it oracle21c /bin/bash
bash-4.4$ cd oradata/ 
bash-4.4$ cd XE
bash-4.4$ cd XEPDB1/
bash-4.4$ ls -l
total 2099648
-rw-r----- 1 oracle oinstall 209723392 Dec  4 14:51 griesmayer_plsql1.dbf

Create User

CREATE USER GRIESMAYER PROFILE "DEFAULT" IDENTIFIED BY "oracle"
DEFAULT TABLESPACE TBS_GRIESMAYER_PLSQL
TEMPORARY TABLESPACE TEMP
ACCOUNT UNLOCK;

Legt den neuen Benutzer an.

GRANT SELECT ANY DICTIONARY TO GRIESMAYER;

Der Benutzer kann das Datadictionary auslesen.

Ein Data Dictionary ist eine zentrale Sammlung von Metadaten – also Daten über Daten – in einer Datenbank. Es beschreibt die Struktur, Organisation und Eigenschaften aller Objekte in der Datenbank.

Beispiel:

Tabellen ALL_TABLES

GRANT UNLIMITED TABLESPACE TO GRIESMAYER;

Der User griesmayer kann im Tablespace TBS_GRIESMAYER_PLSQL beliebig viele Daten einfügen,

GRANT CONNECT TO GRIESMAYER;

Der User kann eine neue Datenbankverbindung aufbauen.

GRANT RESOURCE TO GRIESMAYER;

Der User kann in seinem eingenen Schema beliebig arbeiten.

Testdaten

DROP TABLE KOSTEN_KM;
DROP TABLE CUSTOMER;
DROP TABLE TAXI;
DROP TABLE TAXIUNTERNEHMEN;

CREATE TABLE TAXIUNTERNEHMEN (
    UNTERNEHMEN_ID    NUMBER(10)       PRIMARY KEY,
    UNTERNEHMEN_NAME  VARCHAR2(100)    NOT NULL,
    KASSA             NUMBER(10,2)
);

INSERT INTO TAXIUNTERNEHMEN (UNTERNEHMEN_ID, UNTERNEHMEN_NAME, KASSA)
VALUES (1, 'CityTaxi Wien', 1500.00);

INSERT INTO TAXIUNTERNEHMEN (UNTERNEHMEN_ID, UNTERNEHMEN_NAME, KASSA)
VALUES (2, 'Schnell Sicher Taxi', 2200.50);

INSERT INTO TAXIUNTERNEHMEN (UNTERNEHMEN_ID, UNTERNEHMEN_NAME, KASSA)
VALUES (3, 'Taxi Express GmbH', 980.75);

COMMIT;

SELECT *
FROM   TAXIUNTERNEHMEN;

CREATE TABLE TAXI (
    TAXI_ID        NUMBER(10)      PRIMARY KEY,
    UNTERNEHMEN_ID NUMBER(10)      NOT NULL,
    KENNZEICHEN    VARCHAR2(20)    NOT NULL,
    FREI           CHAR(1)         DEFAULT 'J' CHECK (FREI IN ('J','N')),
    CONSTRAINT fk_unternehmen
        FOREIGN KEY (UNTERNEHMEN_ID)
        REFERENCES TAXIUNTERNEHMEN(UNTERNEHMEN_ID)
);

INSERT INTO TAXI VALUES (101, 1, 'W-123TX', 'J');
INSERT INTO TAXI VALUES (102, 1, 'W-456TX', 'N');
INSERT INTO TAXI VALUES (103, 1, 'W-789TX', 'J');
INSERT INTO TAXI VALUES (104, 1, 'W-555TX', 'J');

INSERT INTO TAXI VALUES (201, 2, 'W-321SS', 'N');
INSERT INTO TAXI VALUES (202, 2, 'W-654SS', 'N');
INSERT INTO TAXI VALUES (203, 2, 'W-987SS', 'N');

INSERT INTO TAXI VALUES (301, 3, 'W-111EX', 'N');
INSERT INTO TAXI VALUES (302, 3, 'W-222EX', 'J');
INSERT INTO TAXI VALUES (303, 3, 'W-333EX', 'J');
INSERT INTO TAXI VALUES (304, 3, 'W-444EX', 'N');
INSERT INTO TAXI VALUES (305, 3, 'W-555EX', 'J');

COMMIT;

SELECT *
FROM   TAXI;

CREATE TABLE CUSTOMER (
    CUSTOMER_ID  NUMBER(10)       PRIMARY KEY,
    VORNAME      VARCHAR2(50)     NOT NULL,
    GELD         NUMBER(10,2)     DEFAULT 0,
    TAXI_ID      NUMBER(10),
    CONSTRAINT fk_taxi
        FOREIGN KEY (TAXI_ID)
        REFERENCES TAXI(TAXI_ID)
);


INSERT INTO CUSTOMER VALUES (1, 'Anna',     25.50, 101);
INSERT INTO CUSTOMER VALUES (2, 'Markus',   40.00, NULL);  -- wartet noch
INSERT INTO CUSTOMER VALUES (3, 'Julia',    12.00, NULL);  -- wartet noch
INSERT INTO CUSTOMER VALUES (4, 'Sandro',   75.10, NULL);  -- wartet noch
INSERT INTO CUSTOMER VALUES (5, 'Patrick', 150.00, 104);

COMMIT;

SELECT *
FROM   CUSTOMER;



CREATE TABLE KOSTEN_KM (
    TAGFAHRT   CHAR(1)         CHECK (TAGFAHRT IN ('J','N')),  -- J = Tagfahrt, N = Nachtfahrt
    KOSTENKM   NUMBER(5,2)     NOT NULL
);


INSERT INTO KOSTEN_KM VALUES ('J', 1.25);  -- Tagfahrt
INSERT INTO KOSTEN_KM VALUES ('N', 2.60);  -- Nachtfahrt

COMMIT;

Die Tabellen werden im Schema griesmayer angelegt.

PLSQL

Kopf definieren

CREATE OR REPLACE PROCEDURE ORDER_TAXI
(
    v_CUSTOMER_ID        IN  CUSTOMER.CUSTOMER_ID%TYPE,
    v_UNTERNEHMEN_ID     IN  TAXIUNTERNEHMEN.UNTERNEHMEN_ID%TYPE,
    v_TAGFAHRT           IN  KOSTEN_KM.TAGFAHRT%TYPE,
    v_RESERVATION_NO     OUT INTEGER
)
AS
    s_RETURN       NUMBER(1);
BEGIN
    v_RESERVATION_NO := 17;
END;

IN-Parameter

Wird in die Prozedur/Funktion hineingegeben.

Die Prozedur darf den Wert lesen, aber nicht verändern (bzw. Änderungen wirken sich nicht auf den Aufrufer aus).

Beispiel:

v_TAGFAHRT IN KOSTEN_KM.TAGFAHRT%TYPE

→ Die Prozedur bekommt einen Wert für TAGFAHRT übergeben.

OUT-Parameter

Wird von der Prozedur nach draußen zurückgegeben.

Er ist initial leer, die Prozedur muss einen Wert zuweisen.

Beispiel:

v_RESERVATION_NO OUT INTEGER

→ Die Prozedur liefert die erzeugte Reservationsnummer zurück.

Aufruf

DECLARE
    v_RESERVATION_NO    INTEGER;
BEGIN
    GRIESMAYER.ORDER_TAXI(2, 1, 'N', v_RESERVATION_NO);
    dbms_output.put_line('Res. Number: ' || v_RESERVATION_NO);
END;

Sequence

Eine Sequence (deutsch: Sequenz) ist ein Datenbankobjekt, das automatisch fortlaufende Zahlen generiert. Sie wird häufig verwendet, um Primärschlüssel wie eindeutige IDs zu erzeugen.

CREATE SEQUENCE SEQ_RESERVATION START WITH 1000;

Erstellt eine neu Sequence mit dem Startwert von 1000.

SELECT SEQ_RESERVATION.NEXTVAL
FROM   DUAL;

Prüft die Sequence.

    v_RESERVATION_NO := SEQ_RESERVATION.NEXTVAL;

Es wird anstelle der 17 die fortlaufende Nummer zurückgegeben.

DECLARE
    v_RESERVATION_NO    INTEGER;
BEGIN
    GRIESMAYER.ORDER_TAXI(2, 1, 'N', v_RESERVATION_NO);
    dbms_output.put_line('Res. Number: ' || v_RESERVATION_NO);
END;


Res. Number: 1006
PL/SQL procedure successfully completed.

Prüfungen

Die Überprüfung kann nur am Server passieren, wenn ich konsistente Daten garantieren muss.

    SELECT count(*)
    INTO   s_RETURN
    FROM   CUSTOMER
    WHERE  CUSTOMER_ID = v_CUSTOMER_ID;

    IF (s_RETURN < 1) THEN
        RAISE_APPLICATION_ERROR(-20100, 'Error: Customer does not exist!');
    END IF;

DECLARE
    v_RESERVATION_NO    INTEGER;
BEGIN
    GRIESMAYER.ORDER_TAXI(2, 1, 'N', v_RESERVATION_NO);
    dbms_output.put_line('Res. Number: ' || v_RESERVATION_NO);
END;

Fehler

DECLARE
    v_RESERVATION_NO    INTEGER;
BEGIN
    GRIESMAYER.ORDER_TAXI(-55, 1, 'N', v_RESERVATION_NO);
    dbms_output.put_line('Res. Number: ' || v_RESERVATION_NO);
END;


ERROR at line 1:
ORA-20100: Error: Customer does not exist!
ORA-06512: at "GRIESMAYER.ORDER_TAXI", line 18
ORA-06512: at line 4

    SELECT count(*)
    INTO   s_RETURN
    FROM   CUSTOMER
    WHERE  CUSTOMER_ID = v_CUSTOMER_ID AND
           TAXI_ID is NULL;

    IF (s_RETURN < 1) THEN
        RAISE_APPLICATION_ERROR(-20100, 'Error: Customer already booked!');
    END IF;

DECLARE
    v_RESERVATION_NO    INTEGER;
BEGIN
    GRIESMAYER.ORDER_TAXI(5, 1, 'N', v_RESERVATION_NO);
    dbms_output.put_line('Res. Number: ' || v_RESERVATION_NO);
END;


ERROR at line 1:
ORA-20100: Error: Customer already booked!
ORA-06512: at "GRIESMAYER.ORDER_TAXI", line 29
ORA-06512: at line 4

Transaction

BEGIN
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    ...

    IF (s_RETURN < 1) THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR(-20100, 'Error: Customer does not exist!');
    END IF;

    ...

    IF (s_RETURN < 1) THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR(-20100, 'Error: Customer already booked!');
    END IF;

    ...

    COMMIT;

    v_RESERVATION_NO := SEQ_RESERVATION.NEXTVAL;
END;

Achtung die Transaktionen müssen IMMER abgeschlossen sein! Notfalls händisch COMMIT. Fehler: SET TRANSACTION MIUST BE THE FIRST STATEMENT.

    SELECT GELD
    INTO   s_GELD
    FROM   CUSTOMER
    WHERE  CUSTOMER_ID = v_CUSTOMER_ID;

    SELECT min(TAXI_ID) AS TAXI_ID,
           min(KOSTENKM) * 10 as KOSTEN_TOTAL
    INTO   s_TAXI_ID,
           s_KOSTEN_TOTAL
    FROM   TAXI,
           KOSTEN_KM
    WHERE  UNTERNEHMEN_ID = v_UNTERNEHMEN_ID AND
           FREI = 'J' AND
           TAGFAHRT = v_TAGFAHRT;

    IF (s_GELD < s_KOSTEN_TOTAL) THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR(-20100, 'Error: Customer has no money!');
    END IF;

DECLARE
    v_RESERVATION_NO    INTEGER;
BEGIN
    GRIESMAYER.ORDER_TAXI(3, 1, 'N', v_RESERVATION_NO);
    dbms_output.put_line('Res. Number: ' || v_RESERVATION_NO);
END;

ERROR at line 1:
ORA-20100: Error: Customer has no money!
ORA-06512: at "GRIESMAYER.ORDER_TAXI", line 54
ORA-06512: at line 4

Tabelleninhalt

    UPDATE CUSTOMER
    SET    GELD = GELD - s_KOSTEN_TOTAL,
        TAXI_ID = s_TAXI_ID
    WHERE  CUSTOMER_ID = v_CUSTOMER_ID;
    
    UPDATE TAXIUNTERNEHMEN
    SET    KASSA = KASSA + s_KOSTEN_TOTAL
    WHERE  UNTERNEHMEN_ID = v_UNTERNEHMEN_ID;

    UPDATE TAXI
    SET    FREI = 'N'
    WHERE  TAXI_ID = s_TAXI_ID;

    COMMIT;

Allgemeine Exception

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR(-20100, 'Error: No Taxi found!');
END;

Code

CREATE OR REPLACE PROCEDURE ORDER_TAXI
(
    v_CUSTOMER_ID        IN  CUSTOMER.CUSTOMER_ID%TYPE,
    v_UNTERNEHMEN_ID     IN  TAXIUNTERNEHMEN.UNTERNEHMEN_ID%TYPE,
    v_TAGFAHRT           IN  KOSTEN_KM.TAGFAHRT%TYPE,
    v_RESERVATION_NO     OUT INTEGER
)
AS
    s_RETURN       NUMBER(1);
    s_GELD         CUSTOMER.GELD%TYPE;
    s_TAXI_ID      TAXI.TAXI_ID%TYPE;
    s_KOSTEN_TOTAL KOSTEN_KM.KOSTENKM%TYPE;
BEGIN
    SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    SELECT count(*)
    INTO   s_RETURN
    FROM   CUSTOMER
    WHERE  CUSTOMER_ID = v_CUSTOMER_ID;

    IF (s_RETURN < 1) THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR(-20100, 'Error: Customer does not exist!');
    END IF;

    SELECT count(*)
    INTO   s_RETURN
    FROM   CUSTOMER
    WHERE  CUSTOMER_ID = v_CUSTOMER_ID AND
           TAXI_ID is NULL;

    IF (s_RETURN < 1) THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR(-20100, 'Error: Customer already booked!');
    END IF;

    SELECT GELD
    INTO   s_GELD
    FROM   CUSTOMER
    WHERE  CUSTOMER_ID = v_CUSTOMER_ID;

    SELECT min(TAXI_ID) AS TAXI_ID,
           min(KOSTENKM) * 10 as KOSTEN_TOTAL
    INTO   s_TAXI_ID,
           s_KOSTEN_TOTAL
    FROM   TAXI,
           KOSTEN_KM
    WHERE  UNTERNEHMEN_ID = v_UNTERNEHMEN_ID AND
           FREI = 'J' AND
           TAGFAHRT = v_TAGFAHRT;

    IF (s_GELD < s_KOSTEN_TOTAL) THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR(-20100, 'Error: Customer has no money!');
    END IF;

    UPDATE CUSTOMER
    SET    GELD = GELD - s_KOSTEN_TOTAL,
        TAXI_ID = s_TAXI_ID
    WHERE  CUSTOMER_ID = v_CUSTOMER_ID;
    
    UPDATE TAXIUNTERNEHMEN
    SET    KASSA = KASSA + s_KOSTEN_TOTAL
    WHERE  UNTERNEHMEN_ID = v_UNTERNEHMEN_ID;

    UPDATE TAXI
    SET    FREI = 'N'
    WHERE  TAXI_ID = s_TAXI_ID;

    COMMIT;

    v_RESERVATION_NO := SEQ_RESERVATION.NEXTVAL;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR(-20100, 'Error: No Taxi found!');
END;
/

Semester_5