CREATE OR REPLACE TYPE STUDENTI AS OBJECT(
    S_NUM   INT,
    S_UZV   VARCHAR2(30)
);

CREATE OR REPLACE TYPE PASNIEDZEJI AS OBJECT(
    P_NUM   INT,
    P_UZV   VARCHAR2(30)
);

CREATE OR REPLACE TYPE ATS_ST AS OBJECT(
    ID  INT,
    ST_ID REF STUDENTI
);

CREATE OR REPLACE TYPE ATS_PN AS OBJECT(
    ID  INT,
    PN_ID REF PASNIEDZEJI
);

CREATE OR REPLACE TYPE ATS_P AS TABLE OF ATS_PN;
CREATE OR REPLACE TYPE ATS_S AS TABLE OF ATS_ST;

CREATE OR REPLACE TYPE O_STUDENTI AS OBJECT(
    STUD STUDENTI,
    PASN ATS_P
);

CREATE OR REPLACE TYPE O_PASNIEDZEJI AS OBJECT(
    PASN PASNIEDZEJI,
    STUD ATS_S
);

CREATE TABLE T_STUDENTI OF O_STUDENTI
NESTED TABLE PASN STORE AS P_TBL;

CREATE TABLE T_PASNIEDZEJI OF O_PASNIEDZEJI
NESTED TABLE STUD STORE AS S_TBL;

INSERT INTO T_STUDENTI VALUES (STUDENTI(191, 'KOMAROVS'), NULL);

INSERT INTO T_PASNIEDZEJI VALUES (PASNIEDZEJI(53, 'GUSEVS'), NULL);
INSERT INTO T_PASNIEDZEJI VALUES (PASNIEDZEJI(54, 'HIKAEVS'), NULL);

UPDATE T_STUDENTI A SET A.PASN.P_NUM = (SELECT REF(A) FROM T_PASNIEDZEJI A WHERE a.pasn.P_NUM = 53) WHERE A.stud.S_NUM = 191;

UPDATE T_STUDENTI A SET A.PASN = ATS_PN(1, (SELECT REF(A) FROM T_PASNIEDZEJI A WHERE a.pasn.P_NUM = 53)) WHERE A.stud.S_NUM = 191;

UPDATE T_STUDENTI A SET A.PASN = ATS_P(ATS_PN(1, (SELECT REF(A) FROM T_PASNIEDZEJI A WHERE a.pasn.P_NUM = 53))) WHERE A.stud.S_NUM = 191;

CREATE OR REPLACE TYPE STUDENTI AS OBJECT(
    S_NUM   INT,
    S_UZV   VARCHAR2(30)
);

CREATE OR REPLACE TYPE PASNIEDZEJI AS OBJECT(
    P_NUM   INT,
    P_UZV   VARCHAR2(30)
);

CREATE OR REPLACE TYPE ATS_ST AS OBJECT(
    ID  INT,
    ST_ID REF STUDENTI
);

CREATE OR REPLACE TYPE ATS_PN AS OBJECT(
    ID  INT,
    PN_ID REF PASNIEDZEJI
);

CREATE OR REPLACE TYPE ATS_P AS TABLE OF ATS_PN;
CREATE OR REPLACE TYPE ATS_S AS TABLE OF ATS_ST;

CREATE TABLE T_STUDENTI OF STUDENTI;

CREATE TABLE T_PASNIEDZEJI OF PASNIEDZEJI;

CREATE OR REPLACE TYPE O_CLASS AS OBJECT(
    K_NUM NUMBER, -- номер класса (параллель)
    K_APZ CHAR(1), -- буква класса
    STUD ATS_S, -- много (коллекция REF)
    PASN ATS_P -- много (коллекция REF)
);

CREATE TABLE T_CLASS OF O_CLASS
NESTED TABLE STUD STORE AS C1
NESTED TABLE PASN STORE AS C2;

INSERT INTO T_STUDENTI VALUES (191, 'GUSEVS');

INSERT INTO T_PASNIEDZEJI VALUES (14, 'JAUNCIEMS');
INSERT INTO T_PASNIEDZEJI VALUES (15, 'JANUKS');

INSERT INTO T_CLASS VALUES (1, 'S', ATS_S(ATS_ST(1, (SELECT REF(A) FROM T_STUDENTI A WHERE A.S_NUM = 191))), NULL);

UPDATE T_CLASS A SET A.PASN = ATS_P(ATS_PN(1, (SELECT REF(A) FROM T_PASNIEDZEJI A WHERE A.P_NUM = 14)), ATS_PN(2, (SELECT REF(A) FROM T_PASNIEDZEJI A WHERE A.P_NUM = 15)));