CODEWITHSUNDEEP
sqloracle-databaseoracle12c
P. K. Tharindu
P. K. Tharindu

Reputation: 2730

Oracle SQL statements to modify the object type by adding a member method

I want to add a member method called credcomp to calculate the number of credits completed by a student. Only units where student received a mark of at least 50 are counted as completed. Can anyone tell me how to create this method? This is what I have done so far:

ALTER TYPE student_t REPLACE AS OBJECT (
sid integer,
name varchar(15),
phone varchar(10),
course ref course_t,
enrolments enrolled_list,
MEMBER FUNCTION crdcomp RETURN NUMBER);
/

CREATE OR REPLACE TYPE BODY student_t AS
  MEMBER FUNCTION crdcomp RETURN NUMBER IS
  pcount integer;
  BEGIN

  ---still not done part

  END;
END;
/

my tables and object types are as below:

--Object types

CREATE TYPE course_t AS OBJECT (cid integer, title varchar(15), credits_req integer);
/

CREATE TYPE unit_t AS OBJECT (unitid integer, credits integer);
/

CREATE TYPE offering_t AS OBJECT (unit ref unit_t, semester number(1), year number(4));
/

CREATE TYPE enrolled_t AS OBJECT (unitoffer ref offering_t, mark integer);
/

CREATE TYPE enrolled_list AS TABLE OF enrolled_t;
/

CREATE TYPE student_t AS OBJECT 
(sid integer, name varchar(15), phone varchar(10), course ref course_t , enrolments enrolled_list);
/

--Tables

CREATE TABLE Courses OF course_t (PRIMARY KEY (cid));
/

CREATE TABLE Units OF unit_t (PRIMARY KEY (unitid));
/

CREATE TABLE Offered OF offering_t (FOREIGN KEY (unit) REFERENCES Units, unit NOT NULL);
/

CREATE TABLE Students OF student_t (PRIMARY KEY(sid), FOREIGN KEY (course) REFERENCES Courses)
NESTED TABLE enrolments STORE AS enrolledlist_ntab;
/

Thank you for your help in advance. :)

Upvotes: 1

Views: 136

Answers (1)

JazzBass
JazzBass

Reputation: 21

You should use alter type ... add member

ALTER TYPE student_t 
ADD MEMBER FUNCTION crdcomp RETURN NUMBER);
/

And then your create body

check also oracle docs alter_type

Upvotes: 0

Related Questions