108 lines
4.7 KiB
MySQL
108 lines
4.7 KiB
MySQL
-- Demo-Daten für eine Oracle-Datenbank-Verbindung (lesender Zugriff), wie demo_shop.sql für PostgreSQL.
|
|
-- Nur SQL, das es in Oracle 19c schon gibt. Anlegen im Test-Container (docker compose --profile oracle up -d oracle):
|
|
-- docker compose exec -T oracle sqlplus -s system/aiw@FREEPDB1 < beispiele/demo_shop_oracle.sql
|
|
-- Im Workspace unter Einstellungen → MCP-Server → Datenbank hinzufügen: Typ Oracle, Host localhost, Port 55434, Datenbank (Service-Name) FREEPDB1,
|
|
-- Benutzer shop_leser, Passwort shop-leser-demo, TLS aus (nur lokal).
|
|
-- Voraussetzung in Bifröst: der DB-Dienst für Oracle (services/db-mcp), registriert unter Administration →
|
|
-- Datenbank-Treiber (scripts/dev.sh --oracle startet ihn und zeigt URL und Schlüssel).
|
|
|
|
WHENEVER SQLERROR EXIT FAILURE
|
|
SET FEEDBACK OFF
|
|
|
|
BEGIN
|
|
FOR u IN (SELECT username FROM all_users WHERE username IN ('SHOP', 'SHOP_LESER')) LOOP
|
|
EXECUTE IMMEDIATE 'DROP USER ' || u.username || ' CASCADE';
|
|
END LOOP;
|
|
END;
|
|
/
|
|
|
|
CREATE USER shop IDENTIFIED BY "shop-demo-intern" QUOTA UNLIMITED ON USERS;
|
|
CREATE USER shop_leser IDENTIFIED BY "shop-leser-demo";
|
|
GRANT CREATE SESSION TO shop_leser;
|
|
|
|
CREATE TABLE shop.kunden (
|
|
id NUMBER(10) PRIMARY KEY,
|
|
name VARCHAR2(100) NOT NULL,
|
|
stadt VARCHAR2(50) NOT NULL,
|
|
segment VARCHAR2(20) NOT NULL CHECK (segment IN ('Privat', 'Gewerbe', 'Öffentlich')),
|
|
seit DATE NOT NULL
|
|
) TABLESPACE USERS;
|
|
COMMENT ON TABLE shop.kunden IS 'Kundenstamm';
|
|
|
|
CREATE TABLE shop.produkte (
|
|
id NUMBER(10) PRIMARY KEY,
|
|
name VARCHAR2(100) NOT NULL,
|
|
kategorie VARCHAR2(30) NOT NULL,
|
|
listenpreis NUMBER(10, 2) NOT NULL
|
|
) TABLESPACE USERS;
|
|
COMMENT ON TABLE shop.produkte IS 'Sortiment mit Listenpreis in Euro';
|
|
|
|
CREATE TABLE shop.bestellungen (
|
|
id NUMBER(10) PRIMARY KEY,
|
|
kunde_id NUMBER(10) NOT NULL REFERENCES shop.kunden,
|
|
bestellt_am DATE NOT NULL,
|
|
status VARCHAR2(20) NOT NULL CHECK (status IN ('offen', 'versendet', 'storniert'))
|
|
) TABLESPACE USERS;
|
|
COMMENT ON TABLE shop.bestellungen IS 'Bestellköpfe; Positionen in SHOP.POSITIONEN';
|
|
|
|
CREATE TABLE shop.positionen (
|
|
bestellung_id NUMBER(10) NOT NULL REFERENCES shop.bestellungen,
|
|
produkt_id NUMBER(10) NOT NULL REFERENCES shop.produkte,
|
|
menge NUMBER(5) NOT NULL,
|
|
preis NUMBER(10, 2) NOT NULL,
|
|
PRIMARY KEY (bestellung_id, produkt_id)
|
|
) TABLESPACE USERS;
|
|
COMMENT ON TABLE shop.positionen IS 'Bestellpositionen mit tatsächlichem Stückpreis (nach Rabatt)';
|
|
|
|
INSERT INTO shop.produkte
|
|
SELECT 1, 'Laptop Pro 14', 'Hardware', 1499 FROM dual UNION ALL
|
|
SELECT 2, 'Monitor 27"', 'Hardware', 329 FROM dual UNION ALL
|
|
SELECT 3, 'Dockingstation', 'Zubehör', 189 FROM dual UNION ALL
|
|
SELECT 4, 'Headset Office', 'Zubehör', 79.90 FROM dual UNION ALL
|
|
SELECT 5, 'Office-Lizenz', 'Software', 129 FROM dual UNION ALL
|
|
SELECT 6, 'Cloud-Speicher 1 TB', 'Software', 99 FROM dual UNION ALL
|
|
SELECT 7, 'Wartungsvertrag', 'Service', 450 FROM dual UNION ALL
|
|
SELECT 8, 'Schulung (Tag)', 'Service', 890 FROM dual;
|
|
|
|
-- Feste Werte statt Zufall: jeder Lauf ergibt dieselben Daten
|
|
INSERT INTO shop.kunden
|
|
SELECT level,
|
|
CASE mod(level, 10) WHEN 0 THEN 'Müller' WHEN 1 THEN 'Schmidt' WHEN 2 THEN 'Schneider' WHEN 3 THEN 'Fischer'
|
|
WHEN 4 THEN 'Weber' WHEN 5 THEN 'Meyer' WHEN 6 THEN 'Wagner' WHEN 7 THEN 'Becker' WHEN 8 THEN 'Hoffmann'
|
|
ELSE 'Koch' END
|
|
|| CASE mod(level, 5) WHEN 0 THEN ' GmbH' WHEN 1 THEN ' AG' WHEN 2 THEN ' KG' WHEN 3 THEN '' ELSE ' e.K.' END,
|
|
CASE mod(level * 7, 8) WHEN 0 THEN 'Hamburg' WHEN 1 THEN 'München' WHEN 2 THEN 'Köln' WHEN 3 THEN 'Berlin'
|
|
WHEN 4 THEN 'Leipzig' WHEN 5 THEN 'Stuttgart' WHEN 6 THEN 'Dresden' ELSE 'Bremen' END,
|
|
CASE mod(level * 3, 4) WHEN 0 THEN 'Privat' WHEN 3 THEN 'Öffentlich' ELSE 'Gewerbe' END,
|
|
DATE '2019-01-01' + mod(level * 37, 2000)
|
|
FROM dual CONNECT BY level <= 120;
|
|
|
|
INSERT INTO shop.bestellungen
|
|
SELECT level,
|
|
1 + mod(level * 13, 120),
|
|
DATE '2025-01-01' + mod(level * 11, 365),
|
|
CASE mod(level, 6) WHEN 4 THEN 'offen' WHEN 5 THEN 'storniert' ELSE 'versendet' END
|
|
FROM dual CONNECT BY level <= 800;
|
|
|
|
INSERT INTO shop.positionen
|
|
SELECT b.id, p.id, 1 + mod(b.id + p.id, 5),
|
|
round(p.listenpreis * CASE mod(b.id * p.id, 5) WHEN 3 THEN 0.95 WHEN 4 THEN 0.9 ELSE 1 END, 2)
|
|
FROM shop.bestellungen b
|
|
JOIN shop.produkte p ON mod(b.id + p.id * 3, 8) < 1 + mod(b.id, 3);
|
|
|
|
COMMIT;
|
|
|
|
BEGIN
|
|
DBMS_STATS.GATHER_SCHEMA_STATS('SHOP');
|
|
END;
|
|
/
|
|
|
|
-- Nutzer, der nur lesen darf
|
|
GRANT SELECT ON shop.kunden TO shop_leser;
|
|
GRANT SELECT ON shop.produkte TO shop_leser;
|
|
GRANT SELECT ON shop.bestellungen TO shop_leser;
|
|
GRANT SELECT ON shop.positionen TO shop_leser;
|
|
|
|
SELECT 'Demo-Daten angelegt: ' || (SELECT count(*) FROM shop.bestellungen) || ' Bestellungen, '
|
|
|| (SELECT count(*) FROM shop.positionen) || ' Positionen' AS ergebnis FROM dual;
|
|
EXIT
|