Student type : Object « Object Oriented Database « Oracle PL / SQL






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

1.Create Object
2.CREATE OR REPLACE TYPE
3.Create a stored type which is visible to SQL and PL/SQL.
4.reference user-defined data type in another block
5.Point type
6.Use user-defined type as parameter
7.This script demonstrates complex objects
8.Name type
9.Behavior of dependent objects.
10.Build data type with another user type
11.Create the object and collection types
12.Create type and use it in inner query
13.Create types and then use it in pl/sql block
14.Combine user-defined type to create new type
15.PriceType becomes the datatype of the price attribute in the ProductType object type
16.Use self to reference member variable in constructor
17.One to list using object references