Student type
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE Student AS OBJECT (
2 ID NUMBER(5),
3 first_name VARCHAR2(20),
4 last_name VARCHAR2(20),
5 major VARCHAR2(30),
6 current_credits NUMBER(3),
7
8 MEMBER FUNCTION FormattedName
9 RETURN VARCHAR2,
10 PRAGMA RESTRICT_REFERENCES(FormattedName, RNDS, WNDS, RNPS, WNPS),
11
12 MEMBER PROCEDURE ChangeMajor(p_NewMajor IN VARCHAR2),
13 PRAGMA RESTRICT_REFERENCES(ChangeMajor, RNDS, WNDS, RNPS, WNPS),
14
15 MEMBER PROCEDURE UpdateCredits(p_CompletedClass IN Class),
16 PRAGMA RESTRICT_REFERENCES(UpdateCredits, RNDS, WNDS, RNPS, WNPS),
17
18 ORDER MEMBER FUNCTION CompareStudent(p_Student IN Student)
19 RETURN NUMBER
20 );
21 /
Type created.
SQL> show errors
No errors.
SQL>
SQL> CREATE OR REPLACE TYPE BODY Student AS
2 MEMBER FUNCTION FormattedName
3 RETURN VARCHAR2 IS
4 BEGIN
5 RETURN first_name || ' ' || last_name;
6 END FormattedName;
7
8 MEMBER PROCEDURE ChangeMajor(p_NewMajor IN VARCHAR2) IS
9 BEGIN
10 major := p_NewMajor;
11 END ChangeMajor;
12
13 MEMBER PROCEDURE UpdateCredits(p_CompletedClass IN Class) IS
14 BEGIN
15 current_credits := current_credits +
16 p_CompletedClass.num_credits;
17 END UpdateCredits;
18
19 ORDER MEMBER FUNCTION CompareStudent(p_Student IN Student)
20 RETURN NUMBER IS
21 BEGIN
22 IF p_Student.last_name = SELF.last_name THEN
23 IF p_Student.first_name < SELF.first_name THEN
24 RETURN 1;
25 ELSIF p_Student.first_name > SELF.first_name THEN
26 RETURN -1;
27 ELSE
28 RETURN 0;
29 END IF;
30 ELSE
31 IF p_Student.last_name < SELF.last_name THEN
32 RETURN 1;
33 ELSE
34 RETURN -1;
35 END IF;
36 END IF;
37 END CompareStudent;
38 END;
39 /
Type body created.
SQL> show errors
No errors.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> DECLARE
2 v_Student Student; -- This assigns NULL to v_Student by default
3 BEGIN
4 v_Student.ID := 10020;
5 END;
6 /
PL/SQL procedure successfully completed.
SQL>
SQL>
Related examples in the same category