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