MariaDB を Oracle に変換

-- Written by IntoSQL from a MySQL dump. Load it with: NLS_LANG=.AL32UTF8 sqlplus -S -L <user>/<password>@<database> @<file>
WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
SET DEFINE OFF;
SET SQLBLANKLINES ON;
SET FEEDBACK OFF;
SET SERVEROUTPUT ON;
CREATE OR REPLACE FUNCTION "INTOSQL_CLOB" (p SYS.ODCIVARCHAR2LIST) RETURN CLOB IS
    r CLOB;
BEGIN
    DBMS_LOB.CREATETEMPORARY(r, TRUE);
    FOR i IN 1 .. p.COUNT LOOP
        IF p(i) IS NOT NULL THEN
            DBMS_LOB.WRITEAPPEND(r, LENGTH2(p(i)), p(i));
        END IF;
    END LOOP;
    RETURN r;
END;
/
CREATE OR REPLACE FUNCTION "INTOSQL_BLOB" (p SYS.ODCIVARCHAR2LIST) RETURN BLOB IS
    r BLOB;
    piece RAW(2000);
BEGIN
    DBMS_LOB.CREATETEMPORARY(r, TRUE);
    FOR i IN 1 .. p.COUNT LOOP
        piece := HEXTORAW(p(i));
        DBMS_LOB.WRITEAPPEND(r, UTL_RAW.LENGTH(piece), piece);
    END LOOP;
    RETURN r;
END;
/

CREATE SEQUENCE "INVOICE_NUMBERS" START WITH 2000 INCREMENT BY 1 MINVALUE 1 MAXVALUE 9223372036854775806;
CREATE TABLE "CUSTOMERS" (
    "ID" NUMBER(10) GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 1001),
    "NAME" VARCHAR2(100 CHAR) NOT NULL,
    "EMAIL" VARCHAR2(150 CHAR) NOT NULL,
    "COUNTRY" VARCHAR2(2 CHAR) DEFAULT 'US' NOT NULL,
    "IS_ACTIVE" NUMBER(1) DEFAULT 1 NOT NULL,
    "SIGNED_UP" DATE,
    "CREATED_AT" TIMESTAMP(0) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
    "LAST_IP" VARCHAR2(39 CHAR),
    CONSTRAINT "CK_CUSTOMERS_ID" CHECK ("ID" >= 0),
    CONSTRAINT "CK_CUSTOMERS_IS_ACTIVE" CHECK ("IS_ACTIVE" IN (0, 1))
);

CREATE TABLE "ORDERS" (
    "ID" NUMBER(10) GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH 5001),
    "CUSTOMER_ID" NUMBER(10) NOT NULL,
    "STATUS" VARCHAR2(9 CHAR) DEFAULT 'pending',
    "TOTAL" NUMBER(10,2) NOT NULL,
    "ITEMS" NUMBER(5) DEFAULT 1 NOT NULL,
    "ORDERED_AT" TIMESTAMP(0) NOT NULL,
    "SHIPPED_AT" TIMESTAMP(0),
    "NOTE" VARCHAR2(255 CHAR),
    "ORDER_REF" VARCHAR2(36 CHAR) NOT NULL,
    "INVOICE" NUMBER(10) DEFAULT ("INVOICE_NUMBERS".NEXTVAL) NOT NULL,
    "DETAILS" CLOB,
    "UPDATED_AT" TIMESTAMP(0) WITH TIME ZONE DEFAULT SYSTIMESTAMP NOT NULL,
    CONSTRAINT "CK_ORDERS_ID" CHECK ("ID" >= 0),
    CONSTRAINT "CK_ORDERS_CUSTOMER_ID" CHECK ("CUSTOMER_ID" >= 0),
    CONSTRAINT "CK_ORDERS_STATUS" CHECK ("STATUS" IN ('pending', 'paid', 'shipped', 'cancelled')),
    CONSTRAINT "CK_ORDERS_ITEMS" CHECK ("ITEMS" >= 0)
);

BEGIN
INSERT INTO "CUSTOMERS" (
"ID",
"NAME",
"EMAIL",
"COUNTRY",
"IS_ACTIVE",
"SIGNED_UP",
"CREATED_AT",
"LAST_IP"
) VALUES (
1,
'Ana Martínez',
'ana@example.com',
'MX',
1,
DATE '2023-01-15',
TIMESTAMP '2023-01-15 10:24:00 +00:00',
'::ffff:203.0.113.7'
);
INSERT INTO "CUSTOMERS" (
"ID",
"NAME",
"EMAIL",
"COUNTRY",
"IS_ACTIVE",
"SIGNED_UP",
"CREATED_AT",
"LAST_IP"
) VALUES (
2,
'Zoë Müller',
'zoe@example.de',
'DE',
1,
DATE '2023-02-03',
TIMESTAMP '2023-02-03 08:11:42 +00:00',
'2001:db8::1'
);
INSERT INTO "CUSTOMERS" (
"ID",
"NAME",
"EMAIL",
"COUNTRY",
"IS_ACTIVE",
"SIGNED_UP",
"CREATED_AT",
"LAST_IP"
) VALUES (
3,
'José Peña',
'jose@example.es',
'ES',
0,
DATE '2023-03-21',
TIMESTAMP '2023-03-21 19:05:17 +00:00',
NULL
);
INSERT INTO "CUSTOMERS" (
"ID",
"NAME",
"EMAIL",
"COUNTRY",
"IS_ACTIVE",
"SIGNED_UP",
"CREATED_AT",
"LAST_IP"
) VALUES (
4,
'李雷',
'lilei@example.cn',
'CN',
1,
DATE '2023-05-09',
TIMESTAMP '2023-05-09 02:40:00 +00:00',
'::ffff:198.51.100.23'
);
INSERT INTO "CUSTOMERS" (
"ID",
"NAME",
"EMAIL",
"COUNTRY",
"IS_ACTIVE",
"SIGNED_UP",
"CREATED_AT",
"LAST_IP"
) VALUES (
5,
'Noah O''Brien',
'noah@example.ie',
'IE',
1,
NULL,
TIMESTAMP '2023-06-30 14:00:00 +00:00',
NULL
);
INSERT INTO "CUSTOMERS" (
"ID",
"NAME",
"EMAIL",
"COUNTRY",
"IS_ACTIVE",
"SIGNED_UP",
"CREATED_AT",
"LAST_IP"
) VALUES (
6,
'Amélie Laurent',
'amelie@example.fr',
'FR',
1,
DATE '2024-01-02',
TIMESTAMP '2024-01-02 09:30:00 +00:00',
'2001:db8:85a3::8a2e:370:7334'
);
INSERT INTO "CUSTOMERS" (
"ID",
"NAME",
"EMAIL",
"COUNTRY",
"IS_ACTIVE",
"SIGNED_UP",
"CREATED_AT",
"LAST_IP"
) VALUES (
7,
'Ravi Patel',
'ravi@example.in',
'IN',
0,
DATE '2024-02-29',
TIMESTAMP '2024-02-29 23:59:59 +00:00',
'::ffff:192.0.2.200'
);
INSERT INTO "CUSTOMERS" (
"ID",
"NAME",
"EMAIL",
"COUNTRY",
"IS_ACTIVE",
"SIGNED_UP",
"CREATED_AT",
"LAST_IP"
) VALUES (
8,
'Sofia Rossi 🌻',
'sofia@example.it',
'IT',
1,
DATE '2024-04-12',
TIMESTAMP '2024-04-12 12:12:12 +00:00',
NULL
);
COMMIT;
END;
/
BEGIN
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"ORDER_REF",
"INVOICE",
"DETAILS",
"UPDATED_AT"
) VALUES (
1,
1,
'shipped',
129.90,
3,
TIMESTAMP '2024-03-01 10:00:00',
TIMESTAMP '2024-03-02 16:30:00',
NULL,
'0b7a4c2e-3f1d-4e8a-9c55-1a2b3c4d5e6f',
1000,
'{"gift": false}',
TIMESTAMP '2024-03-02 16:30:00 +00:00'
);
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"ORDER_REF",
"INVOICE",
"DETAILS",
"UPDATED_AT"
) VALUES (
2,
2,
'paid',
54.00,
1,
TIMESTAMP '2024-03-02 11:15:00',
NULL,
'Gift wrap 🎁',
'1c8b5d3f-4a2e-4f9b-8d66-2b3c4d5e6f70',
1001,
'{"gift": true, "wrap": "red"}',
TIMESTAMP '2024-03-02 11:20:00 +00:00'
);
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"ORDER_REF",
"INVOICE",
"DETAILS",
"UPDATED_AT"
) VALUES (
3,
1,
'cancelled',
12.50,
1,
TIMESTAMP '2024-03-03 09:45:00',
NULL,
'Customer changed their mind',
'2d9c6e4a-5b3f-4a0c-9e77-3c4d5e6f7081',
1002,
NULL,
TIMESTAMP '2024-03-03 10:00:00 +00:00'
);
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"ORDER_REF",
"INVOICE",
"DETAILS",
"UPDATED_AT"
) VALUES (
4,
4,
'shipped',
980.00,
12,
TIMESTAMP '2024-03-05 02:10:00',
TIMESTAMP '2024-03-07 08:00:00',
'请小心轻放',
'3e0d7f5b-6c4a-4b1d-8f88-4d5e6f708192',
1003,
'{"fragile": true}',
TIMESTAMP '2024-03-07 08:00:00 +00:00'
);
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"ORDER_REF",
"INVOICE",
"DETAILS",
"UPDATED_AT"
) VALUES (
5,
5,
'pending',
45.75,
2,
TIMESTAMP '2024-03-08 17:20:00',
NULL,
'Leave at the door; ring twice',
'4f1e806c-7d5b-4c2e-9a99-5e6f708192a3',
1004,
NULL,
TIMESTAMP '2024-03-08 17:20:00 +00:00'
);
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"ORDER_REF",
"INVOICE",
"DETAILS",
"UPDATED_AT"
) VALUES (
6,
6,
'shipped',
310.40,
5,
TIMESTAMP '2024-03-10 13:00:00',
TIMESTAMP '2024-03-11 09:00:00',
'Fragile: "glass"',
'50f2917d-8e6c-4d3f-8baa-6f708192a3b4',
1005,
'{"fragile": true}',
TIMESTAMP '2024-03-11 09:00:00 +00:00'
);
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"ORDER_REF",
"INVOICE",
"DETAILS",
"UPDATED_AT"
) VALUES (
7,
8,
'paid',
76.20,
2,
TIMESTAMP '2024-03-11 18:45:00',
NULL,
NULL,
'6103a28e-9f7d-4e40-9cbb-708192a3b4c5',
1006,
NULL,
TIMESTAMP '2024-03-11 18:46:00 +00:00'
);
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"ORDER_REF",
"INVOICE",
"DETAILS",
"UPDATED_AT"
) VALUES (
8,
2,
NULL,
19.99,
1,
TIMESTAMP '2024-03-12 08:00:00',
NULL,
'Status lost in an old import',
'7214b39f-a08e-4f51-8dcc-8192a3b4c5d6',
1007,
NULL,
TIMESTAMP '2024-03-12 08:00:00 +00:00'
);
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"ORDER_REF",
"INVOICE",
"DETAILS",
"UPDATED_AT"
) VALUES (
9,
3,
'shipped',
64.00,
2,
TIMESTAMP '2024-03-14 15:30:00',
NULL,
'Shipping date never recorded',
'8325c4a0-b19f-4062-9edd-92a3b4c5d6e7',
1008,
'{"parcels": [1, 2]}',
TIMESTAMP '2024-03-15 10:00:00 +00:00'
);
INSERT INTO "ORDERS" (
"ID",
"CUSTOMER_ID",
"STATUS",
"TOTAL",
"ITEMS",
"ORDERED_AT",
"SHIPPED_AT",
"NOTE",
"ORDER_REF",
"INVOICE",
"DETAILS",
"UPDATED_AT"
) VALUES (
10,
7,
'paid',
1499.00,
1,
TIMESTAMP '2024-03-15 21:05:00',
NULL,
'C:\Users\ravi\invoice.pdf',
'9436d5b1-c2a0-4173-8fee-a3b4c5d6e7f8',
1009,
NULL,
TIMESTAMP '2024-03-15 21:05:00 +00:00'
);
COMMIT;
END;
/

-- Keys, indexes, foreign keys, counters and triggers, after the rows.
ALTER TABLE "CUSTOMERS" ADD CONSTRAINT "PK_CUSTOMERS" PRIMARY KEY ("ID");
ALTER TABLE "CUSTOMERS" ADD CONSTRAINT "UQ_CUSTOMERS_UQ_EMAIL" UNIQUE ("EMAIL");
ALTER TABLE "ORDERS" ADD CONSTRAINT "PK_ORDERS" PRIMARY KEY ("ID");
CREATE INDEX "IX_ORDERS_IDX_CUSTOMER" ON "ORDERS" ("CUSTOMER_ID");
CREATE INDEX "IX_ORDERS_IDX_STATUS_DATE" ON "ORDERS" ("STATUS", "ORDERED_AT");
ALTER TABLE "ORDERS" ADD CONSTRAINT "FK_ORDERS_CUSTOMER" FOREIGN KEY ("CUSTOMER_ID") REFERENCES "CUSTOMERS" ("ID") ENABLE NOVALIDATE;
BEGIN
    EXECUTE IMMEDIATE 'ALTER TABLE "ORDERS" ENABLE VALIDATE CONSTRAINT "FK_ORDERS_CUSTOMER"';
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Foreign key ' || '"FK_ORDERS_CUSTOMER"' || ' has rows without a parent: it stays unvalidated, and new rows are checked.');
END;
/
DECLARE
    next_id NUMBER;
BEGIN
    SELECT NVL(MAX("ID"), 0) + 1 INTO next_id FROM "CUSTOMERS";
    IF next_id < 1001 THEN
        next_id := 1001;
    END IF;
    EXECUTE IMMEDIATE 'ALTER TABLE "CUSTOMERS" MODIFY ("ID" GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH ' || next_id || '))';
END;
/
COMMENT ON TABLE "CUSTOMERS" IS 'People who bought at least once';
DECLARE
    next_id NUMBER;
BEGIN
    SELECT NVL(MAX("ID"), 0) + 1 INTO next_id FROM "ORDERS";
    IF next_id < 5001 THEN
        next_id := 5001;
    END IF;
    EXECUTE IMMEDIATE 'ALTER TABLE "ORDERS" MODIFY ("ID" GENERATED BY DEFAULT ON NULL AS IDENTITY (START WITH ' || next_id || '))';
END;
/
CREATE OR REPLACE TRIGGER "TR_ORDERS_UPDATED_AT" BEFORE UPDATE ON "ORDERS" FOR EACH ROW
BEGIN
    IF NOT UPDATING('UPDATED_AT') THEN
        :NEW."UPDATED_AT" := SYSTIMESTAMP;
    END IF;
END;
/
DROP FUNCTION "INTOSQL_CLOB";
DROP FUNCTION "INTOSQL_BLOB";
COMMIT;
EXIT;