Oracle FreeSQL
Oracle bietet mit FreeSQL (https://freesql.com/) die Möglichkeit, mit den Oracle-Datenbankprodukten in einer Browser-Oberfläche zu experimentieren.
Nach der Registrierung müssen ggfs. erst 24h vergehen, bis das Konto nutzbar ist.
Bisher wird zur Suche von Datensätzen eine Datenbanksicht genutzt. Durch den JOIN untergeordneter Datensätze sind Suchertreffer mehrfach enthalten. Mit Hilfe von JSON-Strukturen soll die Ansicht so geändert werden, dass in den untergeordneten Datensätzen gesucht werden kann, ohne dass die Kopfdaten mehrfach auftreten.
Dazu werden die Funktionen JSON_ARRAYAGG und JSON_TABLE benutzt.
rechnung /insert 4
name /nn /values Jane,Alex,Chris,Fred
Rechnungsposition /insert 23
location /nn
menge num /between 1 and 99ergibt dieses SQL
-- create tables
create table rechnung (
id number default on null to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')
constraint rechnung_id_pk primary key,
name varchar2(255 char) not null
);
create table Rechnungsposition (
id number default on null to_number(sys_guid(), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')
constraint rechnungsposition_id_pk primary key,
rechnung_id number constraint Rechnungsposition_rechnung_id_fk
references rechnung,
location varchar2(4000 char) not null,
menge number constraint rechnungsposition_menge_bet
check (menge between 1 and 99)
);
-- table index
create index Rechnungsposition_i1 on Rechnungsposition (rechnung_id);
-- load data
insert into rechnung (
id,
name
) values (
1,
'Chris'
);
insert into rechnung (
id,
name
) values (
2,
'Fred'
);
insert into rechnung (
id,
name
) values (
3,
'Jane'
);
insert into rechnung (
id,
name
) values (
4,
'Fred'
);
commit;
insert into Rechnungsposition (
id,
rechnung_id,
location,
menge
) values (
1,
4,
'Ohecajun',
84
);
insert into Rechnungsposition (
id,
rechnung_id,
location,
menge
) values (
2,
4,
'Buglitma',
58
);
insert into Rechnungsposition (
id,
rechnung_id,
location,
menge
) values (
3,
1,
'Jalnotse',
79
);
insert into Rechnungsposition (
id,
rechnung_id,
location,
menge
) values (
4,
1,
'Nojambew',
27
);
insert into Rechnungsposition (
id,
rechnung_id,
location,
menge
) values (
5,
3,
'Osoladgu',
8
);
insert into Rechnungsposition (
id,
rechnung_id,
location,
menge
) values (
6,
4,
'Kirefe',
96
);
insert into Rechnungsposition (
id,
rechnung_id,
location,
menge
) values (
7,
1,
'Cikhawgob',
13
);
insert into Rechnungsposition (
id,
rechnung_id,
location,
menge
) values (
8,
4,
'Iwuototo',
45
);
insert into Rechnungsposition (
id,
rechnung_id,
location,
menge
) values (
9,
3,
'Wimovfuj',
49
);
insert into Rechnungsposition (
id,
rechnung_id,
location,
menge
) values (
10,
4,
'Jobkezpo',
22
);
insert into Rechnungsposition (
id,
rechnung_id,
location,
menge
) values (
11,
2,
'Wiziduc',
58
);
insert into Rechnungsposition (
id,
rechnung_id,
location,
menge
) values (
12,
4,
'Fihfeze',
6
);
insert into Rechnungsposition (
id,
rechnung_id,
location,
menge
) values (
13,
3,
'Ojemapwen',
68
);
insert into Rechnungsposition (
id,
rechnung_id,
location,
menge
) values (
14,
3,
'Cekkuswuj',
27
);
insert into Rechnungsposition (
id,
rechnung_id,
location,
menge
) values (
15,
1,
'Godinac',
82
);
insert into Rechnungsposition (
id,
rechnung_id,
location,
menge
) values (
16,
1,
'Doradudew',
21
);
insert into Rechnungsposition (
id,
rechnung_id,
location,
menge
) values (
17,
3,
'Ivowotu',
68
);
insert into Rechnungsposition (
id,
rechnung_id,
location,
menge
) values (
18,
4,
'Taarfi',
14
);
insert into Rechnungsposition (
id,
rechnung_id,
location,
menge
) values (
19,
4,
'Azwosal',
78
);
insert into Rechnungsposition (
id,
rechnung_id,
location,
menge
) values (
20,
2,
'Mabtefjo',
2
);
insert into Rechnungsposition (
id,
rechnung_id,
location,
menge
) values (
21,
1,
'Horosku',
88
);
insert into Rechnungsposition (
id,
rechnung_id,
location,
menge
) values (
22,
3,
'Mujodo',
45
);
insert into Rechnungsposition (
id,
rechnung_id,
location,
menge
) values (
23,
2,
'Dehobe',
25
);
commit;
Darauf eine View, die je Rechnung eine Zeile enthält. Jede Rechnung hat im Feld Rechnungspositionen eine JSON-Struktur mit den dazugehörigen Einträgen.
CREATE OR REPLACE VIEW RECHNUNGMITPOSITIONEN AS
SELECT
R.ID,
R.NAME,
JSON_ARRAYAGG(
JSON_OBJECT(
'ID' VALUE RP.ID,
'Location' VALUE RP.LOCATION,
'Menge' VALUE RP.MENGE
)
) AS RECHNUNGSPOSITIONEN
FROM
RECHNUNG R
LEFT JOIN RECHNUNGSPOSITION RP ON R.ID = RP.RECHNUNG_ID
GROUP BY
R.ID,
R.NAME;Die View sieht so aus:
| ID | NAME | RECHNUNGSPOSITIONEN |
| 1 | Jane | [{"ID":2,"Location":"Imaerosed","Menge":97},{"ID":21,"Location":"Horosku","Menge":88},{"ID":16,"Location":"Doradudew","Menge":21},{"ID":15,"Location":"Godinac","Menge":82},{"ID":7,"Location":"Cikhawgob","Menge":13},{"ID":4,"Location":"Nojambew","Menge":27}] |
| 2 | Alex | [{"ID":11,"Location":"Wiziduc","Menge":58},{"ID":23,"Location":"Dehobe","Menge":25},{"ID":20,"Location":"Mabtefjo","Menge":2}] |
| 3 | Chris | [{"ID":1,"Location":"Garukme","Menge":97},{"ID":22,"Location":"Mujodo","Menge":45},{"ID":17,"Location":"Ivowotu","Menge":68},{"ID":14,"Location":"Cekkuswuj","Menge":27},{"ID":13,"Location":"Ojemapwen","Menge":68},{"ID":9,"Location":"Wimovfuj","Menge":49},{"ID":5,"Location":"Osoladgu","Menge":8}] |
| 4 | Fred | [{"ID":3,"Location":"Ohecajun","Menge":84},{"ID":19,"Location":"Azwosal","Menge":78},{"ID":18,"Location":"Taarfi","Menge":14},{"ID":12,"Location":"Fihfeze","Menge":6},{"ID":10,"Location":"Jobkezpo","Menge":22},{"ID":8,"Location":"Iwuototo","Menge":45},{"ID":6,"Location":"Kirefe","Menge":96}] |
Eine Abfrage soll alle Rechnungspositionen je Rechnung summieren, wenn es eine Rechnungsposition gibt, deren Location mit I beginnt.
Die Abfrage sieht so aus:
SELECT
R.ID,
R.NAME,
SUM(RP.MENGE) AS GESAMT_MENGE
FROM
RECHNUNGMITPOSITIONEN R
LEFT JOIN RECHNUNGSPOSITION RP ON R.ID = RP.RECHNUNG_ID
WHERE
EXISTS (
SELECT
1
FROM
JSON_TABLE ( R.RECHNUNGSPOSITIONEN, '$[*]'
COLUMNS (
LOCATION VARCHAR2 ( 255 ) PATH '$.Location'
)
)
JT
WHERE
JT.LOCATION LIKE 'I%'
)
GROUP BY
R.ID,
R.NAME;Das Ergebnis enthält folgende Zeilen:
| ID | NAME | GESAMT_MENGE |
|---|---|---|
| 1 | Jane | 328 |
| 3 | Chris | 362 |
| 4 | Fred | 345 |
Einfache Liste
Für den Fall, dass im JSON-Feld nur eine Array von Strings vorgehalten werden muss, kann das Feld in der View so angelegt werden:
JSON_ARRAYAGG(RP.Location) AS Rechnungslocations
Darin wird dann mit der folgenden Bedingung gesucht:
JSON_CONTAINS(R.Rechnungslocations, '"%wo%"', '$') = 1;