add batch
remove batch
split batch
comment selection
show hidden batches
hide batch
highlight batch
db<>fiddle
Db2
Firebird
MariaDB
MySQL
Node.js
Oracle
Postgres
SQL Server
SQLite
TimescaleDB
YugabyteDB
Developer-C 11.1
3.0
4.0
10.2
10.3
10.4
10.5
10.6
10.7
10.8
10.9
10.11
11.4
5.5
5.6
5.7
8.0
8.4
18
11g Release 2
18c
21c
23c
8.4
9.3
9.4
9.5
9.6
10
11
12
13
14
15
16
17
2012
2014
2016
2017
2017 (Linux)
2019
2019 (Linux)
2022
3.8
3.16
3.27
3.39
2.11
2.14
2.6
2.8
2.18
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
Sakila
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
AdventureWorks
no sample DB
no sample DB
AdventureWorks
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
no sample DB
run
abort
markdown
donate
feedback
about
By using db<>fiddle, you agree to license everything you submit by
Creative Commons CC0
.
CREATE OR REPLACE TYPE Transferencia_udt; /
CREATE OR REPLACE TYPE Cuenta_udt; /
CREATE OR REPLACE TYPE Operacion_udt AS OBJECT ( Cod_Num NUMBER, Descripcion VARCHAR(100), Hora NUMBER, Fecha DATE, Cantidad FLOAT, Cuenta_IBAN VARCHAR(28), Cuenta_ord REF Cuenta_udt ) NOT INSTANTIABLE NOT FINAL; /
CREATE OR REPLACE TYPE Cuentas_array AS TABLE OF REF Cuenta_udt; /
CREATE OR REPLACE TYPE Cliente_udt AS OBJECT ( DNI VARCHAR(9), Telefono NUMBER, Direccion VARCHAR(70), Email VARCHAR(50), Edad NUMBER, Apellidos VARCHAR(40), Nombre VARCHAR(30), Cuentas cuentas_array) FINAL; /
CREATE OR REPLACE TYPE Oficina_bancaria_udt; /
CREATE OR REPLACE TYPE clientes_array AS TABLE OF REF Cliente_udt; /
CREATE OR REPLACE TYPE operaciones_array AS TABLE OF REF Operacion_udt; /
CREATE OR REPLACE TYPE transferencias_array AS TABLE OF REF Transferencia_udt; /
CREATE OR REPLACE TYPE Cuenta_udt AS OBJECT ( IBAN VARCHAR(28), Saldo FLOAT, Numero_de_cuenta NUMBER, Fecha_creacion DATE, Clientes clientes_array, Operaciones operaciones_array, Transferencias transferencias_array) NOT INSTANTIABLE NOT FINAL; /
CREATE OR REPLACE TYPE De_ahorro_udt UNDER Cuenta_udt ( Interes FLOAT, Ultimo_devengo DATE ) FINAL; /
CREATE OR REPLACE TYPE Transferencia_udt UNDER Operacion_udt ( Cuenta_IBAN_receptora VARCHAR(28), Cuenta_receptora REF Cuenta_udt ) FINAL; /
CREATE OR REPLACE TYPE Corriente_udt UNDER Cuenta_udt ( Oficina_bancaria_Codigo NUMBER, Oficina_bancaria_Direccion VARCHAR(70), Oficina_bancaria REF Oficina_bancaria_udt ) FINAL; /
CREATE OR REPLACE TYPE Ingreso_udt UNDER Operacion_udt ( Oficina_bancaria_Cod_ing NUMBER, Oficina_bancaria_Dir_ing VARCHAR(70), Oficina_bancaria REF Oficina_bancaria_udt ) FINAL; /
CREATE OR REPLACE TYPE ingresos_array AS TABLE OF REF Ingreso_udt; /
CREATE OR REPLACE TYPE Retirada_udt UNDER Operacion_udt ( Oficina_bancaria_Cod_ret NUMBER, Oficina_bancaria_Dir_ret VARCHAR(70), Oficina_bancaria REF Oficina_bancaria_udt ) FINAL; /
CREATE OR REPLACE TYPE retiradas_array AS TABLE OF REF Retirada_udt; /
CREATE OR REPLACE TYPE Corriente_udt UNDER Cuenta_udt ( Oficina_bancaria_Codigo NUMBER, Oficina_bancaria_Direccion VARCHAR(70), Oficina_bancaria REF Oficina_bancaria_udt ) FINAL; /
CREATE OR REPLACE TYPE corrientes_array AS TABLE OF REF Corriente_udt; /
CREATE OR REPLACE TYPE Oficina_bancaria_udt AS OBJECT ( Codigo NUMBER, Direccion VARCHAR(70), Telefono NUMBER, Cuentas corrientes_array, Ingresos ingresos_array, Retiradas retiradas_array ) FINAL; /
CREATE TABLE Cuenta OF Cuenta_udt ( IBAN PRIMARY KEY, Saldo NOT NULL, Numero_de_cuenta NOT NULL, Fecha_creacion NOT NULL ) OBJECT IDENTIFIER IS SYSTEM GENERATED NESTED TABLE Clientes STORE AS Clientes_tab NESTED TABLE Operaciones STORE AS Operaciones_tab NESTED TABLE Transferencias STORE AS Transferencias_tab;
CREATE TABLE Oficina_bancaria OF Oficina_bancaria_udt ( Telefono NOT NULL, PRIMARY KEY(Codigo, Direccion) ) OBJECT IDENTIFIER IS SYSTEM GENERATED NESTED TABLE Cuentas STORE AS Corrientes_tab NESTED TABLE Ingresos STORE AS Ingresos_tab NESTED TABLE Retiradas STORE AS Retiradas_tab;
UPDATE Oficina_bancaria SET Cuentas = COALESCE( Cuentas, Corrientes_array() ) MULTISET UNION Corrientes_array( ( SELECT TREAT( REF(c) AS REF Corriente_udt ) FROM Cuenta c WHERE c.IBAN = '1654ES6639071895270420369756' ) ) WHERE Codigo = 6356 AND Direccion = 'Cuesta Hector Montes 15 Puerta 5 Cuenca, 02539'
MERGE INTO Oficina_bancaria o USING ( SELECT TREAT( REF(c) AS REF Corriente_udt ) AS Corriente_ref FROM Cuenta c WHERE c.IBAN = '1654ES6639071895270420369756' ) c ON ( o.Codigo = 6356 AND o.Direccion = 'Cuesta Hector Montes 15 Puerta 5 Cuenca, 02539' AND c.Corriente_ref IS NOT NULL ) WHEN MATCHED THEN UPDATE SET o.Cuentas = COALESCE( o.Cuentas, Corrientes_array() ) MULTISET UNION Corrientes_array( c.Corriente_ref );