i'm trying to understand object-relational technology and created parent type:
CREATE OR REPLACE TYPE STUDENT
AS OBJECT(
FIO VARCHAR2(200),
Bday DATE,
Pas_Id NUMBER(20),
Address VARCHAR2(50))
NOT INSTANTIABLE
NOT FINAL;
child type:
CREATE OR REPLACE TYPE BACH
UNDER STUDENT(
fieldOfStud varchar2(200),
Group_N number(4),
ege number(4),
GPA number(4),
MEMBER FUNCTION year_N RETURN NUMBER);
--тело типа бакалавр
CREATE OR REPLACE TYPE BODY BACH IS
MEMBER FUNCTION year_N RETURN NUMBER IS
BEGIN
IF SUBSTR(Group_N, 1, 1) BETWEEN 1 and 4 THEN
RETURN SUBSTR(Group_N, 1, 1);
END IF;
RETURN 0;
END;
END;
created table:
CREATE TABLE Students_Table(
SID NUMBER CONSTRAINT id_pk PRIMARY KEY,
stdt STUDENT
);
and inserted data. I am 100% sure it exists. I want to update table and change group, for example, from 5231 to 6231:
update Students_Table st
set TREAT(stdt as maga).Group_N = TREAT(stdt as maga).Group_N+1000
where TREAT(stdt AS MAGA).Pas_Id = 241122
but get error
ORA-00927: missing equal sign
You need to apply the conversion earlier in the statement, so that the update only sees the subtype:
update ( select s.sid, treat(stdt as bach) as stdt from students_table s ) st
set st.stdt.group_n = st.stdt.group_n + 1000
where st.stdt.pas_id = 241122;