demonstrates SQL operations on object types. : Select « Object Oriented Database « Oracle PL / SQL






demonstrates SQL operations on object types.

 
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE Point AS OBJECT (
  2    x NUMBER,
  3    y NUMBER,
  4
  5    MEMBER FUNCTION ToString RETURN VARCHAR2,
  6    PRAGMA RESTRICT_REFERENCES(ToString, RNDS, WNDS, RNPS, WNPS),
  7
  8    MEMBER FUNCTION Distance(p IN Point DEFAULT Point(0,0))
  9      RETURN NUMBER,
 10    PRAGMA RESTRICT_REFERENCES(Distance, RNDS, WNDS, RNPS, WNPS),
 11
 12    MEMBER FUNCTION Plus(p IN Point) RETURN Point,
 13    PRAGMA RESTRICT_REFERENCES(Plus, RNDS, WNDS, RNPS, WNPS),
 14
 15    MEMBER FUNCTION Times(n IN NUMBER) RETURN Point,
 16    PRAGMA RESTRICT_REFERENCES(Times, RNDS, WNDS, RNPS, WNPS)
 17  );
 18  /

Type created.

SQL> show errors
No errors.
SQL>
SQL> CREATE OR REPLACE TYPE BODY Point AS
  2    MEMBER FUNCTION ToString RETURN VARCHAR2 IS
  3      myResult VARCHAR2(20);
  4      v_xString VARCHAR2(8) := SUBSTR(TO_CHAR(x), 1, 8);
  5      v_yString VARCHAR2(8) := SUBSTR(TO_CHAR(y), 1, 8);
  6    BEGIN
  7      myResult := '(' || v_xString || ', ';
  8      myResult := myResult || v_yString || ')';
  9      RETURN myResult;
 10    END ToString;
 11
 12    MEMBER FUNCTION Distance(p IN Point DEFAULT Point(0,0))
 13      RETURN NUMBER IS
 14    BEGIN
 15      RETURN SQRT(POWER(x - p.x, 2) + POWER(y - p.y, 2));
 16    END Distance;
 17
 18    MEMBER FUNCTION Plus(p IN Point) RETURN Point IS
 19      myResult Point;
 20    BEGIN
 21      myResult := Point(x + p.x, y + p.y);
 22      RETURN myResult;
 23    END Plus;
 24
 25    MEMBER FUNCTION Times(n IN NUMBER) RETURN Point IS
 26      myResult Point;
 27    BEGIN
 28      myResult := Point(x * n, y * n);
 29      RETURN myResult;
 30    END Times;
 31  END;
 32  /

Type body created.

SQL> show errors
No errors.
SQL>
SQL> set serveroutput on
SQL>
SQL> DECLARE
  2    v_Point1 Point := Point(1, 2);
  3    v_Point2 Point;
  4    v_Point3 Point;
  5  BEGIN
  6    v_Point2 := v_Point1.Times(4);
  7    v_Point3 := v_Point1.Plus(v_Point2);
  8    DBMS_OUTPUT.PUT_LINE('Point 2: ' || v_Point2.ToString);
  9    DBMS_OUTPUT.PUT_LINE('Point 3: ' || v_Point3.ToString);
 10    DBMS_OUTPUT.PUT_LINE('Distance between origin and point 1: ' ||
 11      v_Point1.Distance);
 12    DBMS_OUTPUT.PUT_LINE('Distance between point 1 and point 2: ' ||
 13      v_Point1.Distance(v_Point2));
 14  END;
 15  /
Point 2: (4, 8)
Point 3: (5, 10)
Distance between origin and point 1: 2.23606797749978969640917366873127623544
Distance between point 1 and point 2: 6.70820393249936908922752100619382870632

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL>
SQL> CREATE TABLE point_object_tab OF Point;

Table created.

SQL>
SQL>
SQL> CREATE TABLE point_column_tab (
  2    key VARCHAR2(20),
  3    value Point);

Table created.

SQL>
SQL> set serveroutput on
SQL>
SQL> DECLARE
  2    v_Point Point := Point(1, 1);
  3    v_NewPoint Point;
  4    v_Key point_column_tab.key%TYPE;
  5    v_XCoord NUMBER;
  6    v_YCoord NUMBER;
  7  BEGIN
  8    INSERT INTO point_object_tab VALUES (v_Point);
  9    INSERT INTO point_column_tab VALUES ('My Point', v_Point);
 10
 11    SELECT *
 12      INTO v_XCoord, v_YCoord
 13      FROM point_object_tab;
 14    DBMS_OUTPUT.PUT_LINE('Relational query of object table: ' ||
 15      v_XCoord || ', ' || v_YCoord);
 16
 17    SELECT VALUE(ot)
 18      INTO v_NewPoint
 19      FROM point_object_tab ot;
 20    DBMS_OUTPUT.PUT_LINE('object table: ' || v_NewPoint.ToString);
 21
 22    SELECT key, value
 23      INTO v_Key, v_NewPoint
 24      FROM point_column_tab;
 25    DBMS_OUTPUT.PUT_LINE('column table: ' || v_NewPoint.ToString);
 26
 27  END;
 28  /
Relational query of object table: 1, 1
object table: (1, 1)
column table: (1, 1)

PL/SQL procedure successfully completed.

SQL>
SQL> DECLARE
  2    v_PointRef REF Point;
  3    v_Point Point;
  4  BEGIN
  5    DELETE FROM point_object_tab;
  6
  7    INSERT INTO point_object_tab (x, y)
  8      VALUES (0, 0);
  9    INSERT INTO point_object_tab (x, y)
 10      VALUES (1, 1);
 11
 12    SELECT REF(ot)
 13      INTO v_PointRef
 14      FROM point_object_tab ot
 15      WHERE x = 1 AND y = 1;
 16
 17    SELECT DEREF(v_PointRef)
 18      INTO v_Point
 19      FROM dual;
 20    DBMS_OUTPUT.PUT_LINE('Selected reference ' ||
 21      v_Point.ToString);
 22
 23    INSERT INTO point_object_tab ot (x, y)
 24      VALUES (10, 10)
 25      RETURNING REF(ot) INTO v_PointRef;
 26  END;
 27  /
Selected reference (1, 1)

PL/SQL procedure successfully completed.

SQL>
SQL> DROP TABLE point_column_tab;

Table dropped.

SQL> DROP TABLE point_object_tab;

Table dropped.

SQL>

 








Related examples in the same category

1.Display the New Table (SELECT * and SELECT by Column Name)
2.Query object table
3.Use type member function in select statement
4.SELECT Only One Column in the Composite
5.SELECT with a WHERE Clause
6.Query data from table based on object
7.SELECTing Individual Columns in TCROs
8.Use the function we created: use the table alias in our SELECT as well as the qualifier
9.Use * to reference column with user-defined type
10.Select the object type from the table, rather than the columns.
11.Use %ROWTYPE to select from the relational table.