Objektorientierte PL/SQL-Programmierung
Werbung
Objektorientierte PL/SQL-Programmierung
Andriy Terletskyy
Berenberg Bank
Hamburg
Schlüsselworte:
Aufbau eines virtuellen ODBMS auf der Basis einer RDBMS, Objektorientierter ROWTYPE,
Typehierarchie
Zusammenfassung
Es wird pro Tabelle ein objektorientierter ROWTYPE (OO-ROWTYPE) erzeugt, der einen
einfachen Zugriff auf einzelne Datensätze erlaubt. Dieser OO-ROWTYPE beinhaltet
Methoden und Konstruktoren, die die üblichen DML-Anweisungen abbilden.
Auf der Basis vom SCOTT-Schema werden OO-ROWTYPEs gebaut und die Stärke der
objektorientierten PL/SQL-Programmierung hervorgehoben.
Folgende Schritte sind notwendig, um die PL/SQL-Programmierung auf dem relationalen
Datenbankmodell objektorieniert (OO) zu verwirklichen:
• Einführung des Object Type TYPE_OBJECT, analog zur Object Klasse in Java. Dies
ist die Basisklasse für alle abgeleiteten Object Types.
• Einführung von OO-ROWTYPEs, die vom TYPE_OBJECT abgeleitet sind.
• Aufbau von Business Object Types (BO-TYPEs), die Objekte der realen Welt
abbilden.
Einleitung
Für den traditionellen Einsatz der objektorientierte PL/SQL-Programmierung bietet Oracle ein
ODBMS. Bei diesem Ansatz werden erst die Oracle Object Types definiert, auf denen
objektorientierte Tabellen erzeugt werden. Für die Datennormalisierung verwendet man dort
die OROWID. Um unter der Verwendung von OROWID den Inhalt des referenzierten
Objektes zu bekommen oder eine Methode aufzurufen, verwendet man eine DEREF –
Funktion. Für die Datenmanipulation sind wie früher die üblichen DML-Anweisungen
verfügbar.
Im Gegensatz zur OO-PL/SQL-Programmierung für ODBMS, werden die hier vorgestellten
Objekt Typen auf der Basis eines bestehenden relationalen DB-Models gebaut. Damit ist ein
objektorientierter Zugriff auf die Tabelleninhalte und auf die in PL/SQL realisierten
Prozeduren möglich. Die erstellten Object Types sind ein Analogon zum Oracle
<TABLE_NAME>%ROWTYPE. Sie sind zusätzlichen mit Konstruktoren und Methoden
ausgestattet, die den üblichen DML-Anweisungen wie SELECT, INSERT, UPDATE,
DELETE, bzw. MERGE entsprechen. Somit werden die Datenmanipulationen vereinfacht. Es
stehen Konstruktoren für Primary Key und Unique Key zur Verfügung, um basierend auf
einer Id des RDBMS einen referenzierten Datensatz zu erhalten. Die TO_STRING – Methode
gibt die Representation des Types aus. Die Ausgabe von TO_STRING erfolgt über die
DBMS_OUTPUT Methode.
BO-TYPES
OO-ROWTYPES
RDBMS
Abb. 1: Schichtenmodel der Object Types für RDBMS
Mit den genannten Funktionalitäten können die Daten wie üblich auf der Basis eines
relationalen Datenbankmodells in dem RDBMS gespeichert werden. Über das virtuelle
objektorientierte Modell (OO-Schicht) sind die Zugriffe auf Tabellenspalten und die
Ausführung der sämtlichen DML-Anweisungen vereinfacht.
Auf Grund dieser OO-ROWTYPEs kann man mittels der Vererbung und der Komposition,
die so genannten Business-Objekte (BO) aufbauen, die die reale Welt abbilden. Diese BOTypes können durch Ableitung und Kombination von mehreren OO-ROWTYPEs realisiert
werden.
Realisierung
1. Einführung des Object Type TYPE_OBJECT
Oracle gibt keinerlei Ursprungs Objekthierarchie vor, d.h. es gibt kein Objekt, der Basisklasse
für alle abgeleiteten Object Types ist. Daher wird zunächst ein TYPE_OBJECT erstellt:
CREATE
CREATE OR REPLACE
REPLACE TYPE
TYPE SCOTT.TYPE_OBJECT
SCOTT.TYPE_OBJECT AS OBJECT(
OBJECT(
-- attributes
attributes
object_type_name
object_type_name VARCHAR2(100)
VARCHAR2(100)
-- member
member functions
functions and
and procedures
procedures
, MEMBER
MEMBER PROCEDURE
PROCEDURE DBMS_OUTPUT
DBMS_OUTPUT
, MEMBER
MEMBER FUNCTION
FUNCTION TO_STRING
TO_STRING RETURN
RETURN VARCHAR
VARCHAR2
, MEMBER
MEMBER FUNCTION
FUNCTION COMPARE(in_type1
COMPARE(in_type1 TYPE_OBJECT,
TYPE_OBJECT, in_type2
in_type2 TYPE_OBJECT
TYPE_OBJECT
) RETURN
RETURN INTEGER
INTEGER
, ORDER
MEMBER
ORDER
MEMBER FUNCTION
FUNCTION COMPARE2(in_other
COMPARE2(in_other TYPE_OBJECT)
TYPE_OBJECT) RETURN
RETURN INTEGER
INTEGER
) NOT
NOT FINAL
FINAL NOT
NOT INSTANTIABLE
INSTANTIABLE
Hiermit werden folgende Vorteile erzielt:
- Ein standardisiertes Interface für alle abgeleiteten Objekte, wie TO_STRING oder
DBMS_OUTPUT;
- Einfache Übergabe abgeleiteter Typen als Parameter in beliebige Prozeduren oder
Funktionen (Ursprungshierarchie);
- Definition der ORDER Funktion, die wegen der Überschreibung der CompareMethode das Vergleich die Instanzen beliebigen Typen ermöglicht.
2. Einführung von OO-ROWTYPE
Für die SCOTT-Schema Tabellen EMP und DEPT hätten die oben genannten OOROWTYPE in der ODBMS folgende Umsetzung:
CREATE
CREATE OR REPLACE
REPLACE TYPE
TYPE SCOTT.ROW_DEPT
SCOTT.ROW_DEPT UNDER
UNDER SCOTT.TYPE_OBJECT(
SCOTT.TYPE_OBJECT(
-- attributes
attributes
deptno
deptno NUMBER(2)
NUMBER(2)
, dname
VARCHAR2(14)
dname
VARCHAR2(14)
, loc
VARCHAR2(13)
loc
VARCHAR2(13)
-- define
define constructors
constructors
, CONSTRUCTOR
CONSTRUCTOR FUNCTION
FUNCTION ROW_DEPT
ROW_DEPT RETURN
RETURN SELF
SELF AS RESULT
RESULT
, CONSTRUCTOR
CONSTRUCTOR FUNCTION
FUNCTION ROW_DEPT(
ROW_DEPT( in_deptno
in_deptno NUMBER,
UMBER, in_dname
in_dname VARCHAR2
VARCHAR2
, in_loc
in_loc VARCHAR2)
VARCHAR2) RETURN
RETURN SELF
SELF AS RESULT
RESULT
, CONSTRUCTOR
CONSTRUCTOR FUNCTION
FUNCTION ROW_DEPT(in_deptno
ROW_DEPT(in_deptno NUMBER)
NUMBER) RETURN
RETURN SELF
SELF AS RESULT
RESULT
-- member
member functions
functions
, MEMBER
MEMBER FUNCTION
FUNCTION ROW_EXISTS(in_deptno
ROW_EXISTS(in_deptno NUMBER)
NUMBER) RETURN
RETURN BOOLEAN
BOOLEAN
, OVERRIDING
OVERRIDING MEMBER
MEMBER FUNCTION
FUNCTION COMPARE
COMPARE(
RE( in_type1
in_type1 GLOBAL.TYPE_OBJECT
GLOBAL.TYPE_OBJECT
, in_type2
in_type2 GLOBAL.TYPE_OBJECT
GLOBAL.TYPE_OBJECT
) RETURN
RETURN INTEGER
INTEGER
-- member
member procedures
procedures
, MEMBER
MEMBER PROCEDURE
PROCEDURE ROW_INSERT
ROW_INSERT
, MEMBER
MEMBER PROCEDURE
PROCEDURE ROW_UPDATE
ROW_UPDATE
, MEMBER
MEMBER PROCEDURE
PROCEDURE ROW_MERGE
ROW_MERGE
, MEMBER
MEMBER PROCEDURE
PROCEDURE ROW_SAVE
ROW_SAVE
, MEMBER
MEMBER PROCEDURE
PROCEDURE ROW_DELETE
ROW_DELETE
, MEMBER
MEMBER PROCEDURE
PROCEDURE ROW_SELECT(in_deptno
ROW_SELECT(in_deptno NUMBER)
NUMBER)
, MEMBE
MEMBER
R PROCEDURE
PROCEDURE ROW_DEFAULT
ROW_DEFAULT
) NOT
NOT FINAL
FINAL
CREATE
CREATE OR REPLACE
REPLACE TYPE
TYPE SCOTT.ROW_EMP
SCOTT.ROW_EMP UNDER
UNDER SCOTT.TYPE_OBJECT(
SCOTT.TYPE_OBJECT(
-- attributes
attributes
empno
NUMBER(4)
empno
NUMBER(4)
, ename
VARCHAR2(10)
ename
VARCHAR2(10)
, job
VARCHAR2(9)
job
VARCHAR2(9)
, mgr
NUMBER(4)
mgr
NUMBER(4)
, hiredat
hiredate
date DATE
DATE
, sal
NUMBER(7,2)
sal
NUMBER(7,2)
, comm
NUMBER(7,2)
comm
NUMBER(7,2)
, deptno
NUMBER(2)
deptno
NUMBER(2)
-- constructors
constructors
, CONSTRUCTOR
CONSTRUCTOR FUNCTION
FUNCTION ROW_EMP
ROW_EMP RETURN
RETURN SELF
SELF AS RESULT
RESULT
, CONSTRUCTOR
CONSTRUCTOR FUNCTION
FUNCTION ROW_EMP(
ROW_EMP( in_empno
in_empno NUMBER,
NUMBER, in_ename
in_ename VARCHAR2
VARCHAR2
, in_job
in_job VARCHAR2,
VARCHAR2, in_mgr
in_mgr NUMBER
NUMBER
, in_hiredate
in_hiredate DATE
DATE,
TE, in_sal
in_sal NUMBER
NUMBER
, in_comm
in_comm NUMBER,
NUMBER, in_deptno
in_deptno NUMBER
NUMBER
) RETURN
RETURN SELF
SELF AS RESULT
RESULT
, CONSTRUCTOR
CONSTRUCTOR FUNCTION
FUNCTION ROW_EMP(in_empno
ROW_EMP(in_empno NUMBER)
NUMBER) RETURN
RETURN SELF
SELF AS RESULT
RESULT
-- member
member functions
functions
, MEMBER
MEMBER FUNCTION
FUNCTION ROW_EXISTS(in_empno
ROW_EXISTS(in_empno NUMBER)
NUMBER) RETURN
RETURN BOOLEAN
BOOLEAN
, OVERRIDING
OVERRIDING MEMBER
MEMBER FUNCTIO
FUNCTION
ION compare(
compare( in_type1
in_type1 GLOBAL.TYPE_OBJECT
GLOBAL.TYPE_OBJECT
, in_type2
in_type2 GLOBAL.TYPE_OBJECT
GLOBAL.TYPE_OBJECT
) RETURN
RETURN INTEGER
INTEGER
-- member
member procedures
procedures
, MEMBER
MEMBER PROCEDURE
PROCEDURE ROW_INSERT
ROW_INSERT
, MEMBER
MEMBER PROCEDURE
PROCEDURE ROW_UPDATE
ROW_UPDATE
, MEMBER
MEMBER PROCEDURE
PROCEDURE ROW_MERGE
ROW_MERGE
, MEMBER
MEMBER PROCEDURE
PROCEDURE ROW_SAVE
ROW_SAVE
, MEMBER
MEMBER PROCEDURE
PROCEDURE ROW_DELETE
ROW_DELETE
, MEMBER
MEMBER PROCEDURE
PROCEDURE ROW_SELECT(in_empno
ROW_SELECT(in_empno NUMBER)
NUMBER)
, MEMBER
MEMBER PROCEDURE
PROCEDURE ROW_DEFAULT
ROW_DEFAULT
) NOT
NOT FINAL
FINAL
und die dazu gehörigen Container Typen und Packages:
CREATE
CREATE OR REPLACE
REPLACE TYPE
TYPE
CREATE
CREATE OR REPLACE
REPLACE TYPE
TYPE
SCOTT.ROW_DEPT;
COTT.ROW_DEPT;
TABLE_EMP
TABLE_EMP AS TABLE
TABLE OF SCOTT.ROW_EMP;
SCOTT.ROW_EMP;
TABLE_DEPT
TABLE_DEPT AS TABLE
TABLE OF
CREATE
CREATE OR REPLACE
REPLACE PACKAGE
PACKAGE PA_DEPT
PA_DEPT IS
FUNCTION
FUNCTION FU_SELECT
FU_SELECT RETURN
RETURN
TABLE_DEPT;
TABLE_DEPT;
END;
END;
CREATE
CREATE OR REPLACE
REPLACE PACKAGE
PACKAGE PA_EMP
PA_EMP IS
FUNCTION
FUNCTION FU_SELECT
FU_SELECT RETURN
RETURN
TABLE_EMP;
TABLE_EMP;
FUNCTION
FUNCTION FS_DEPTNO(IN_DEPTNO
FS_DEPTNO(IN_DEPTNO IN EMP.DEPTNO%TYPE)
EMP.DEPTNO%TYPE) RETURN
RETURN
TABLE_EMP;
TABLE_EMP; FUNCTION
FUNCTION FS_MGR(IN_MG
FS_MGR(IN_MGR
MGR IN EMP.MGR%TYPE)
EMP.MGR%TYPE) RETURN
RETURN
TABLE_EMP;
TABLE_EMP;
END;
END;
Man erkennt, dass die gebauten Typen vergleichbar zu den <TABLE_NAME>%ROWTYPE
sind, aber weitere Funktionalitäten abbilden.
Zu diesen gehören :
- Konstruktoren für Primary- und Unique Keys;
- Funktionen:
o ROW_EXIST - prüft ob die Datensatz mit angegeben Primary oder Unique
Key in der Datenbank schon vorhanden ist;
o COMPARE – vergleicht zweier Instanzen.
- Prozeduren:
o ROW_INSERT,
ROW_UPDATE,
ROW_DELETE, ROW_MERGE,
ROW_SELECT – die entsprechende DML-Anweisungen ausüben;
o ROW_DEFAULT – setzt aus der Tabellendefinition die Defaultwerte in die
dazu gehörige Attribute;
o ROW_SAVE – ersetzt die MERGE- Anweisung, und enthält im Gegensatz
zum Oracle-MERGE eine RETURNING Clause.
Zusätzlich wird für jeden OO-ROWTYPE ein Container Type und eine oder mehrere DataCartridges für die Foreign Keys und die gesamte Tabelle erzeugt. Mit Data-Cartridge ist eine
Funktionen gemeint, welche die Container Tabelle zurück liefert. Leider erlaubt Oracle nicht,
im Type eigene Container Types zu verwenden. Deswegen sind diese Data-Cartridges in
einem seperaten Package hingelegt.
Die manuelle Erstellung die OO-ROWTYPEs ist sehr aufwendig und fehlerträchtig, hier hilft
ein in Java selbstgeschriebener Generator, der die Oracle Dictionary Definitionen ausliest und
diese Arbeit erledigt. Der Generator ist in das DBMS eingebunden und wird über ein Wrapper
Skript aufgerufen.
3. Aufbau der Business Object Types (BO-TYPEs)
Auf der Basis des generierten Sources, der Vererbung und Komposition wird über so
genannte Busines Objekte, der Aufbau der realen Welt abgebildet.
Dies ist im vorliegende Fall:
-
TYPE_MANAGER – ist vom ROW_EMP vererbt und beinhaltet alle untergeordneten
Mitarbeiter, die er leitet.
CREATE
OR
REPLACE
TYPE
TYPE_MANAGER
UNDER
CREATE
REPLACE
TYPE
TYPE_MANAGER
UNDER
ROW_EMP(
ROW_EMP(
-- attributes
attributes
EMPLOYEES
EMPLOYEES TABLE_EMP
TABLE_EMP
-- constructors
constructors
, CONSTRUCTOR
AS
CONSTRUCTOR FUNCTION
FUNCTION TYPE_MANAGER
TYPE_MANAGER RETURN
RETURN SELF
SELF
RESULT
RESULT
, CONSTRUCTOR
CONSTRUCTOR FUNCTION
FUNCTION TYPE_MANAGER(IN_EMPNO
TYPE_MANAGER(IN_EMPNO NUMBER)
NUMBER) RETURN
RETURN
RESULT
RESULT
) NOT
NOT FINAL
FINAL
-
SELF
SELF
AS
TYPE_DEPARTMENT – ist vom ROW_DEPT abgeleitet und beinhaltet alle Mitarbeiter
die in der Abteilung tätig sind, so wie den leitenden Manager.
CREATE
OR
REPLACE
TYPE
TYPE_DEPARTMENT
UNDER
CREATE
REPLACE
TYPE
TYPE_DEPARTMENT
UNDER
ROW_DEPT(
ROW_DEPT(
-- attributes
attributes
EMPLOYEES
EMPLOYEES TABLE_EMP
TABLE_EMP
-- constructor
constructors
, CONSTRUCTOR
AS
CONSTRUCTOR FUNCTION
FUNCTION TYPE_DEPARTMENT
TYPE_DEPARTMENT RETURN
RETURN SELF
SELF
RESULT
RESULT
, CONSTRUCTOR
CONSTRUCTOR FUNCTION
FUNCTION TYPE_DEPARTMENT(in_deptno
TYPE_DEPARTMENT(in_deptno NUMBER)
NUMBER) RETURN
RETURN SELF
SELF
AS
RESULT
RESULT
-- member
member functions
functions
,
MEMBER
FUNCTION
MEMBER
FUNCTION
TYPE_MANAGER
TYPE_MANAGER
) NOT
NOT FINAL
FINAL
GET_MANAGER
GET_MANAGER
RETURN
RETURN
-
TYPE_ENTERPRISE – ist vom TYPE_OBJECT
vererbt und besteht
Unternehmungsname, Vorstandvorsitzendem, Abteilungen und Mitarbeitern.
aus
CREATE
UNDER
R TYPE_OBJECT(
CREATE OR REPLACE
REPLACE TYPE
TYPE TYPE_ENTERPRISE
TYPE_ENTERPRISE UNDE
TYPE_OBJECT(
-- attributes
attributes
NAME
VARCHAR2(100)
NAME
VARCHAR2(100)
, PRESIDENT
TYPE_MANAGER
PRESIDENT
TYPE_MANAGER
, DEPARTMENT
DEPARTMENTS TABLE_DEPT
TABLE_DEPT
, EMPLOYEES
TABLE_EMP
EMPLOYEES
TABLE_EMP
-- constructors
constructors
, CONSTRUCTOR
CONSTRUCTOR FUNCTION
FUNCTION TYPE_ENTERPRISE
TYPE_ENTERPRISE RETURN
RETURN SELF
SELF AS RESULT
RESULT
) NOT
NOT FINAL
FINAL
Die erzeugten Objekt-Typen und Container lassen sich einfach mit SELECT –Anweisungen
aufrufen:
SQL>
SQL>
SELECT
SELECT TYPE_DEPARTMENT(20)
TYPE_DEPARTMENT(20) FROM
FROM
SQL>
SQL>
SELECT
SELECT VALUE(e)
VALUE(e) FROM
FROM
DUAL;
DUAL;
TYPE_ENTERPRISE()(OBJECT_TYPE_NAME, DEPTNO, DNAME, LOC, NAME, PRESIDENT(OBJECT_T
------------------------------------------------------------------------------------------------------------------TYPE_DEPARTMENT('TYPE_DEPARTMENT', 20, 'RESEARCH', 'DALLAS',
TABLE_EMP(
ROW_EMP('ROW_EMP', 7369, 'SMITH', 'CLERK', 7902, '17.12.80', 800, NULL, 20),
ROW_EMP('ROW_EMP', 7566, 'JONES', 'MANAGER', 7839, '02.04.81', 2975, NULL, 20),
ROW_EMP('ROW_EMP', 7788, 'SCOTT', 'ANALYST', 7566, '19.04.87', 3000, NULL, 20),
ROW_EMP('ROW_EMP', 7876, 'ADAMS', 'CLERK', 7788, '23.05.87', 1100, NULL, 20),
ROW_EMP('ROW_EMP', 7902, 'FORD', 'ANALYST', 7566, '03.12.81', 3000, NULL, 20)
)
)
TABLE
TABLE
(TYPE_MANAGER(7698).EMPLOYEES)
(TYPE_MANAGER(7698).EMPLOYEES) e;
VALUE(E)(OBJECT_TYPE_NAME, EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
------------------------------------------------------------------------------------------------------------------ROW_EMP('ROW_EMP', 7499, 'ALLEN', 'SALESMAN', 7698, '20.02.81', 1600, 300, 30)
ROW_EMP('ROW_EMP', 7521, 'WARD', 'SALESMAN', 7698, '22.02.81', 1250, 500, 30)
ROW_EMP('ROW_EMP', 7654, 'MARTIN', 'SALESMAN', 7698, '28.09.81', 1250, 1400, 30)
ROW_EMP('ROW_EMP', 7844, 'TURNER', 'SALESMAN', 7698, '08.09.81', 1500, 0, 30)
ROW_EMP('ROW_EMP', 7900, 'JAMES', 'CLERK', 7698, '03.12.81', 950, NULL, 30)
Oder mit PL/SQL-Skript:
DECLARE
e TYPE_ENTERPRISE := TYPE_ENTERPRISE();
BEGIN
e.dbms_output;
END;
Entsprechendes DBMS Output:
SCOTT.TYPE_ENTERPRISE
(
OBJECT_TYPE_NAME = TYPE_ENTERPRISE
NAME
= King Corporation
PRESIDENT
= SCOTT.TYPE_MANAGER
(
OBJECT_TYPE_NAME =
EMPNO
=
ENAME
=
JOB
=
MGR
=
HIREDATE
=
SAL
=
COMM
=
DEPTNO
=
EMPLOYEES
=
TYPE_MANAGER
7839
KING
PRESIDENT
17.11.1981 00:00:00
5000
10
< SCOTT.TABLE_EMP >
)
)
DEPARTMENTS
= < SCOTT.TABLE_DEPT >
EMPLOYEES
= <
SCOTT.TABLE_EMP >
Die PL/SQL-Programmierung würde jetzt so ähnlich aussehen:
DECLARE
m TYPE_MANAGER;
BEGIN
m := TYPE_MANAGER(7839);
m.sal := m.sal * 2;
m.row_update;
m.dbms_output;
END;
Entsprechendes DBMS Output:
SCOTT.TYPE_MAN
AGER (
OBJECT_TYPE_NAME =
TYPE_MANAGER EMPNO
7839
ENAME
=
JOB
=
MGR
=
HIREDATE
=
SAL
=
COMM
=
DEPTNO
=
EMPLOYEES
=
)
=
KING
PRESIDENT
17.11.1981 00:00:00
10000
10
< SCOTT.TABLE_EMP >
Hiermit gelingt es, eine virtuelle ODBMS auf dem Basis eines RDBMS aufzubauen
und
PL/SQL –Programmierung für RBDMS objectorientiert zu machen.
Das gebaute Model hat folgende Vorteile :
- keine Funktionalitätsverluste gegeüber der bestehenden ODBMS Lösung von Oracle
- Das Objektmodel entspricht dem relationalen Datenmodel und kann ohne Änderung
am relationalen Datenmodel erzeugt und verwendet werden
- Vereinfachung der DML-Anweisungen
- Zugriffe auf manuell geschriebenen Business Objekte sind unabhängig von
der unterliegenden Datenquelle
- Auf die erstellten Objekte und deren Methoden lassen sich über Generatoren (wie
z.B.
JPublisher oder selbst geschriebene) die passenden Zugriffsklassen für z.B. Java
erstellen
Zur Nachbesserung von Architekturnachteilen und Bugs, wären gewünscht:
- die Oracle Object-Typen unterstützen nicht ROWID, %TYPE und INDEX BY TABLE
- ein Type kann nicht Container von eigenen Instanzen referenzieren
- ein SUPER –Attribut, wie es z.B. in Java existiert, fehlt
- die entsprechendes Gegenstück der z.B. in Java existierenden Interfaces fehlt
(deswegen beinhaltet TYPE_OBJECT das überflüssige Attribute
OBJECT_TYPE_NAME)
- ein Type, der von anderen Type vererbt ist, oder von Container referenziert ist,
kann mittels REPLACE nicht mehr ersetzt werden (nur ALTER TYPE
Anweisung ist für
solche Änderungen vorgesehen). Momentan ist nur ein Umweg über DROP TYPE
force möglich.
Vollständige Sourcen:
CREATE OR REPLACE TYPE TYPE_OBJECT AS OBJECT(
-- Attributes
object_type_name VARCHAR2(100)
-- Member functions and procedures
, MEMBER PROCEDURE DBMS_OUTPUT
, MEMBER FUNCTION TO_STRING RETURN VARCHAR2
, MEMBER FUNCTION COMPARE(in_type1 TYPE_OBJECT, in_type2 TYPE_OBJECT
JECT)
CT)
INTEGER
, ORDER
MEMBER FUNCTION COMPARE2(in_other TYPE_OBJECT) RETURN INTEGER
) NOT FINAL NOT INSTANTIABLE
/
CREATE OR REPLACE TYPE BODY TYPE_OBJECT
IS
MEMBER PROCEDURE DBMS_OUTPUT
IS
str
VARCHAR2(32767) := SELF.TO_STRING();
sub
VARCHAR2(32767);
len
PLS_INTEGER
:= LENGTH(str);
von
PLS_INTEGER
:= 1;
bis
PLS_INTEGER
:= INSTR(str,CHR(10),von);
BEGIN
LOOP
IF bis = 0 THEN bis := len+1; END IF;
EXIT WHEN von>bis;
sub := SUBSTR(str,von,bis-von);
FOR j IN 0..(LENGTH(sub)/255) LOOP
SYS.DBMS_OUTPUT.PUT_LINE(SUBSTR(sub,j*255+1,255));
END LOOP;
von := bis+1;
bis := INSTR(str,CHR(10),von);
END LOOP;
END;
-----------------------------------------MEMBER FUNCTION TO_STRING RETURN VARCHAR2
IS
res
VARCHAR2(32767);
TYPE ANYTYPE_INFO IS RECORD (prec PLS_INTEGER,
scale PLS_INTEGER,
len PLS_INTEGER,
csid PLS_INTEGER,
csfrm PLS_INTEGER,
schema_name VARCHAR2(35),
type_name VARCHAR2(35),
version varchar2(10)
10),
count PLS_INTEGER);
TYPE ATTRIBUTE_INFO IS RECORD (pos PLS_INTEGER,
prec PLS_INTEGER,
scale PLS_INTEGER,
len PLS_INTEGER,
csid PLS_INTEGER,
csfrm PLS_INTEGER,
attr_elt_type ANYTYPE,
aname VARCHAR2(50));
t_info
ANYTYPE_INFO;
o_info
ANYTYPE_INFO;
t_att
ATTRIBUTE_INFO;
v_AnyData
SYS.ANYDATA;
v_AnyType
SYS.ANYTYPE;
v_TypeCode
PLS_INTEGER;
v_ObjCode
PLS_INTEGER;
v_maxAttSize
PLS_INTEGER := 0;
v_char
CHAR(4000);
v_varchar
VARCHAR(4000);
v_raw
RAW(4000);
v_varchar2
VARCHAR2(4000);
v_number
NUMBER;
v_date
DATE;
RETURN
v_object
TYPE_OBJECT;
v_value
VARCHAR2(4000);
v_clob
CLOB;
v_blob
BLOB;
BEGIN
v_AnyData := AnyData.ConvertObject(SELF);
v_TypeCode := v_AnyData.Gettype(v_AnyType);
v_TypeCode
:=
v_anytype.GetInfo(t_info.prec,
t_info.scale,
t_info.len,
t_info.csid, t_info.csfrm, t_info.schema_name, t_info.type_name, t_info.version,
t_info.count);
res := v_AnyData.GetTypeName();
res := res||CHR(10)||'('||CHR(10);
-- max Attribute size übermitteln
FOR pos IN 1..t_info.COUNT LOOP
v_TypeCode
:=
v_anytype.GetAttrElemInfo(pos,
t_att.prec,
t_att.scale,
t_att.len, t_att.csid, t_att.csfrm, t_att.attr_elt_type, t_att.aname);
v_maxAttSize := GREATEST(v_maxAttSize,LENGTH(t_att.aname));
END LOOP;
--sets the mode of access of the current data value to be an attribute at a time
v_AnyData.PieceWise;
-- Attribute Name übermitteln
FOR pos IN 1..t_info.COUNT LOOP
v_TypeCode
:=
v_anytype.GetAttrElemInfo(pos,
t_att.prec,
t_att.scale,
t_att.len, t_att.csid, t_att.csfrm, t_att.attr_elt_type, t_att.aname);
BEGIN
-- Attribute Value übermitteln
CASE v_TypeCode
WHEN DBMS_TYPES.TYPECODE_VARCHAR2 THEN -- 2archar2
IF v_AnyData.GetVarchar2(v_varchar2) = dbms_types.success THEN
v_value := v_varchar2;
END IF;
WHEN DBMS_TYPES.TYPECODE_CHAR THEN -- Char
IF v_AnyData.GetChar(v_Char) = dbms_types.success THEN
v_value := RTRIM(v_Char);
END IF;
WHEN DBMS_TYPES.TYPECODE_RAW THEN -- Raw
Raw
IF v_AnyData.GetRaw(v_Raw) = dbms_types.success THEN
v_value := v_Raw;
END IF;
WHEN DBMS_TYPES.TYPECODE_VARCHAR THEN -- Varchar
IF v_AnyData.GetVarchar(v_Varchar) = dbms_types.success THEN
v_value := v_Varchar;
END IF;
WHEN DBMS_TYPES.TYPECODE_NUMBER THEN -- Number
IF v_AnyData.GetNumber(v_number) = dbms_types.success THEN
v_value := TO_CHAR(v_number);
END IF;
WHEN DBMS_TYPES.TYPECODE_DATE THEN -- Date
IF v_AnyData.GetDate(v_date) = dbms_types.success THEN
v_value := TO_CHAR(v_date,'DD.MM.YYYY HH24:MI:SS');
END IF;
WHEN DBMS_TYPES.TYPECODE_OBJECT THEN -- Object
IF v_AnyData.GetObject(v_Object) = dbms_types.success THEN
v_value
:=
REPLACE(v_Object.TO_STRING(),CHR(10),RPAD(CHR(10),v_maxAttSize+5));
END IF;
WHEN DBMS_TYPES.TYPECODE_CLOB THEN -- CLO
CLOB
IF v_AnyData.GetCLOB(v_clob) = dbms_types.success THEN
v_value := '<CLOB> size = '||DBMS_LOB.Getlength(v_clob);
END IF;
WHEN DBMS_TYPES.TYPECODE_BLOB THEN -- BLO
BLOB
IF v_AnyData.GetBLOB(v_blob) = dbms
dbms_
ms_types.success THEN
v_value := '<BLOB> size = '||DBMS_LOB.Getlength(v_blob);
END IF;
WHEN DBMS_TYPES.TYPECODE_TABLE THEN -- TABLE
v_value := '<NESTED TABLE>';
WHEN DBMS_TYPES.TYPECODE_VARRAY THEN -- VAR
VARRAY
v_value := '<VARRAY>';
WHEN DBMS_TYPES.TYPECODE_NAMEDCOLLECTION THEN -- NAMEDCOLLECTION
v_ObjCode :=
t_att.attr_elt_type.GetInfo(o_info.prec, o_info.scale,
o_info.len,
o_info.csid,
o_info.csfrm,
o_info.schema_name,
o_info.type_name,
o_info.version, o_info.count);
v_value := '< '||o_info.schema_name||'.'||o_info.type_name||' > ';
-- v_AnyData.GetCollection Engpass !!! Abstract Collection
Interface
mangelt
ELSE -- others
v_value := '!!! UNBEKANNTE DATEN
TYP !!! DBM
DBMS_TYPES.TYPECODE = '||
v_TypeCode;
END CASE;
EXCEPTION
WHEN OTHERS THEN
v_value := ' ERROR:'||SQLERRM;
END;
IF
LENGTH(res||'
'||RPAD(t_att.aname,v_maxAttSize)||'
=
'||v_value||CHR(10))< 32700 THEN
res := res||' '||RPAD(t_att.aname,v_maxAttSize)||' = '||v_value||CHR(10);
ELSE
res := res||CHR(10)||'ERROR : Maximale Größe VARCHAR2(32767) erreicht.';
END IF;
END LOOP;
res := res||')';
RETURN res;
EXCEPTION
WHEN OTHERS THEN
RETURN 'ERROR : '|| SQLERRM;
END;
-----------------------------------------MEMBER FUNCTION
compare(in_type1 TYPE_OBJECT, in_type2 TYPE_OBJECT) RETURN
INTEGER
IS
BEGIN
RETURN 1; -- default immer ungleich
END;
-----------------------------------------ORDER MEMBER FUNCTION compare2(in_other TYPE_OBJECT)
RETURN INTEGER
IS
BEGIN
RETURN compare(SELF,in_other);
END;
END;
/
CREATE OR REPLACE TYPE ROW_DEPT UNDER TYPE_OBJECT(
-- attributes
deptno NUMBER(2)
, dname
VARCHAR2(14)
, loc
VARCHAR2(13)
-- constructors
, CONSTRUCTOR FUNCTION ROW_DEPT RETURN SELF AS RESULT
, CONSTRUCTOR FUNCTION ROW_DEPT(in_deptno NUMBER, in_dname VARCHAR2, in_loc
VARCHAR2) RETURN SELF AS RESULT
, CONSTRUCTOR FUNCTION ROW_DEPT(in_deptno NUMBER) RETURN SELF AS RESULT
-- member functions
, MEMBER FUNCTION ROW_EXISTS(in_deptno NUMBER) RETURN BOOLEAN
, OVERRIDING MEMBER FUNCTION compare(in_type1 TYPE_OBJECT,in_type2
TYPE_
YPE_O
E_OBJECT)
RETURN INTEGER
-- member procedures
, MEMBER PROCEDURE ROW_INSERT
, MEMBER PROCEDURE ROW_UPDATE
, MEMBER PROCEDURE ROW_MERGE
, MEMBER PROCEDURE ROW_SAVE
, MEMBER PROCEDURE ROW_DELETE
, MEMBER PROCEDURE ROW_SELECT(in_deptno NUMBER)
, MEMBER PROCEDURE ROW_DEFAULT
) NOT FINAL
/
CREATE OR REPLACE TYPE BODY ROW_DEPT IS
-- constructors
CONSTRUCTOR FUNCTION ROW_DEPT RETURN SELF AS RESULT
IS
BEGIN
SELF.OBJECT_TYPE_NAME := 'ROW_DEPT';
ROW_DEFAULT();
RETURN;
END;
----------------------------------------------------------------------------------CONSTRUCTOR FUNCTION ROW_DEPT(in_deptno NUMBER, in_dname VARCHAR2, in_loc
VARCHAR2) RETURN SELF AS RESULT
IS
BEGIN
SELF.OBJECT_TYPE_NAME := 'ROW_DEPT';
SELF.DEPTNO := IN_DEPTNO;
SELF.DNAME := IN_DNAME;
SELF.LOC
:= IN_LOC;
RETURN;
END;
----------------------------------------------------------------------------------CONSTRUCTOR FUNCTION ROW_DEPT(in_deptno NUMBER) RETURN SELF AS RESULT
IS
BEGIN
SELF.OBJECT_TYPE_NAME := 'ROW_DEPT';
ROW_SELECT(IN_DEPTNO => IN_DEPTNO);
RETURN;
END;
----------------------------------------------------------------------------------MEMBER FUNCTION ROW_EXISTS(in_deptno NUMBER) RETURN BOOLEAN
IS
v_count PLS_INTEGER;
BEGIN
SELECT COUNT(*)
INTO v_count
FROM SCOTT.DEPT
WHERE DEPTNO = IN_DEPTNO;
RETURN (v_count <> 0);
END;
------------------------------------------------------------------------------------ member functions
OVERRIDING MEMBER FUNCTION compare(in_type1 TYPE_OBJECT,in_type2 TYPE_OBJECT)
RETURN INTEGER
IS
type1
ROW_DEPT := TREAT(in_type1 AS ROW_DEPT);
type2
ROW_DEPT := TREAT(in_type2 AS ROW_DEPT);
BEGIN
IF
type1.DEPTNO = type2.DEPTNO
AND ( type1.DNAME = type2.DNAME OR (type1.DNAME IS NULL AND type2.DNAME
IS NULL) )
AND ( type1.LOC
= type2.LOC
OR (type1.LOC
IS NULL AND type2.LOC
IS NULL) )
THEN
RETURN 0; --gleich
ELSE
RETURN 1; --ungleich
END IF;
END;
------------------------------------------------------------------------------------ member procedures
MEMBER PROCEDURE ROW_INSERT
IS
BEGIN
INSERT INTO SCOTT.DEPT ( DEPTNO
,DNAME
,LOC
) VALUES ( SELF.DEPTNO
,SELF.DNAME
,SELF.LOC
) RETURNING
DEPTNO
,DNAME
,LOC
INTO
SELF.DEPTNO
,SELF.DNAME
,SELF.LOC
;
END;
----------------------------------------------------------------------------------MEMBER PROCEDURE ROW_UPDATE
IS
BEGIN
UPDATE SCOTT.DEPT
SET DNAME
= SELF.DNAME
,LOC
= SELF.LOC
WHERE DEPTNO = SELF.DEPTNO
RETURNING DEPTNO
,DNAME
,LOC
INTO SELF.DEPTNO
,SELF.DNAME
,SELF.LOC
;
IF SQL%ROWCOUNT <> 1 THEN
RAISE NO_DATA_FOUND;
END IF;
END;
----------------------------------------------------------------------------------MEMBER PROCEDURE ROW_MERGE
IS
BEGIN
MERGE INTO SCOTT.DEPT A
USING ( SELECT SELF.DEPTNO AS DEPTNO
,SELF.DNAME AS DNAME
,SELF.LOC
AS LOC
FROM DUAL ) B
ON (
A.DEPTNO = B.DEPTNO)
WHEN MATCHED THEN UPDATE SET
DNAME
= B.DNAME
,LOC
= B.LOC
WHEN NOT MATCHED THEN INSERT ( DEPTNO
,DNAME
,LOC
) VALUES ( B.DEPTNO
,B.DNAME
,B.LOC
);
END;
----------------------------------------------------------------------------------MEMBER PROCEDURE ROW_SAVE
IS
BEGIN
IF ROW_EXISTS(IN_DEPTNO => SELF.DEPTNO) THEN
ROW_UPDATE;
ELSE
ROW_INSERT;
END IF;
END;
----------------------------------------------------------------------------------MEMBER PROCEDURE ROW_DELETE
IS
BEGIN
DELETE FROM SCOTT.DEPT
WHERE DEPTNO = SELF.DEPTNO;
IF SQL%ROWCOUNT <> 1 THEN
RAISE NO_DATA_FOUND;
END IF;
END;
----------------------------------------------------------------------------------MEMBER PROCEDURE ROW_SELECT(in_deptno NUMBER)
IS
BEGIN
SELECT DEPTNO
,DNAME
,LOC
INTO SELF.DEPTNO
,SELF.DNAME
,SELF.LOC
FROM SCOTT.DEPT
WHERE DEPTNO = IN_DEPTNO;
END;
-------------------------------------------------------------- MEMBER PROCEDURE ROW_DEFAULT
IS
BEGIN
NULL;
END;
END;
/
CREATE OR REPLACE TYPE ROW_EMP UNDER SCOTT.TYPE_OBJECT(
-- attributes
empno
NUMBER(4)
, ename
VARCHAR2(10)
, job
VARCHAR2(9)
, mgr
NUMBER(4)
, hiredate DATE
, sal
NUMBER(7,2)
, comm
NUMBER(7,2)
, deptno
NUMBER(2)
-- constructors
, CONSTRUCTOR FUNCTION ROW_EMP RETURN SELF AS RESULT
, CONSTRUCTOR FUNCTION ROW_EMP(in_empno NUMBER, in_ename VARCHAR2, in_job
VARCHAR2,
in_mgr NUMBER, in_hiredate DATE, in_sal NUMBER, in_comm NUMBER, in_deptno
NUMBER)
RETURN SELF AS RESULT
, CONSTRUCTOR FUNCTION ROW_EMP(in_empno NUMBER) RETURN SELF AS RESULT
-- member functions
, MEMBER FUNCTION ROW_EXISTS(in_empno NUMBER) RETURN BOOLEAN
, OVERRIDING MEMBER FUNCTION compare(in_type1 TYPE_OBJECT,in_type2
TYPE_OBJECT)
RETURN INTEGER
-- member procedures
, MEMBER PROCEDURE ROW_INSERT
, MEMBER PROCEDURE ROW_UPDATE
, MEMBER PROCEDURE ROW_MERGE
, MEMBER PROCEDURE ROW_SAVE
, MEMBER PROCEDURE ROW_DELETE
, MEMBER PROCEDURE ROW_SELECT(in_empno NUMBER)
, MEMBER PROCEDURE ROW_DEFAULT
) NOT FINAL
/
CREATE OR REPLACE TYPE BODY ROW_EMP IS
-- constructors
CONSTRUCTOR FUNCTION ROW_EMP RETURN SELF AS RESULT
IS
BEGIN
SELF.OBJECT_TYPE_NAME := 'ROW_EMP';
ROW_DEFAULT();
RETURN;
END;
----------------------------------------------------------------------------------CONSTRUCTOR FUNCTION ROW_EMP(in_empno NUMBER, in_ename VARCHAR2, in_job
VARCHAR2,
in_mgr NUMBER, in_hiredate DATE, in_sal NUMBER, in_comm NUMBER, in_deptno
NUMBER)
RETURN SELF AS RESULT
IS
BEGIN
SELF.OBJECT_TYPE_NAME := 'ROW_EMP';
SELF.EMPNO
:= EMPNO;
SELF.ENAME
:= ENAME;
SELF.JOB
:= JOB;
SELF.MGR
:= MGR;
SELF.HIREDATE := HIREDATE;
SELF.SAL
:= SAL;
SELF.COMM
:= COMM;
SELF.DEPTNO
:= DEPTNO;
RETURN;
END;
----------------------------------------------------------------------------------CONSTRUCTOR FUNCTION ROW_EMP(in_empno NUMBER) RETURN SELF AS RESULT
IS
BEGIN
SELF.OBJECT_TYPE_NAME := 'ROW_EMP';
ROW_SELECT(IN_EMPNO =>
IN_EMPNO); RETURN;
END;
----------------------------------------------------------------------------------MEMBER FUNCTION ROW_EXISTS(in_empno NUMBER) RETURN BOOLEAN
IS
v_count PLS_INTEGER;
BEGIN
SELECT COUNT(*)
INTO v_count
FROM SCOTT.EMP
WHERE EMPNO = IN_EMPNO;
RETURN (v_count <> 0);
END;
------------------------------------------------------------------------------------ member functions
OVERRIDING MEMBER FUNCTION compare(in_type1 TYPE_OBJECT,in_type2 TYPE_OBJECT
JECT)
CT)
RETURN INTEGER
IS
type1
ROW_EMP := TREAT(in_type1 AS ROW_EMP);
type2
ROW_EMP := TREAT(in_type2 AS ROW_EMP);
BEGIN
IF
type1.EMPNO
= type2.EMPNO
AND ( type1.ENAME
= type2.ENAME
OR (type1.ENAME
IS NULL AND
type2.ENAME
IS NULL) )
AND ( type1.JOB
= type2.JOB
OR (type1.JOB
IS NULL AND
type2.JOB
IS NULL) )
AND ( type1.MGR
= type2.MGR
OR (type1.MGR
IS NULL AND
type2.MGR
IS NULL) )
AND
( type1.HIREDATE = type2.HIREDATE OR (type1.HIREDATE IS NULL
AND
type2.HIREDATE IS NULL) )
AND ( type1.SAL
= type2.SAL
OR (type1.SAL
IS NULL AND
type2.SAL
IS NULL) )
AND ( type1.COMM
= type2.COMM
OR (type1.COMM
IS NULL AND
type2.COMM
IS NULL) )
AND ( type1.DEPTNO
= type2.DEPTNO
OR (type1.DEPTNO
IS NULL AND
type2.DEPTNO
IS NULL) )
THEN
RETURN 0; --gleich
ELSE
RETURN 1; --ungleich
END IF;
END;
------------------------------------------------------------------------------------ member procedures
MEMBER PROCEDURE ROW_INSERT
IS
BEGIN
INSERT INTO SCOTT.EMP ( EMPNO
,ENAME
,JOB
,MGR
,HIREDATE
,SAL
,COMM
,DEPTNO
) VALUES ( SELF.EMPNO
,SELF.ENAME
,SELF.JOB
,SELF.MGR
,SELF.HIREDATE
,SELF.SAL
,SELF.COMM
,SELF.DEPTNO
) RETURNING
EMPNO
,ENAME
,JOB
,MGR
,HIREDATE
,SAL
INTO
,COMM
,DEPTNO
SELF.EMPNO
,SELF.ENAME
,SELF.JOB
,SELF.MGR
,SELF.HIREDATE
,SELF.SAL
,SELF.COMM
,SELF.DEPTNO
;
END;
----------------------------------------------------------------------------------MEMBER PROCEDURE ROW_UPDATE
IS
BEGIN
UPDATE SCOTT.EMP
SET ENAME
= SELF.ENAME
,JOB
= SELF.JOB
,MGR
= SELF.MGR
,HIREDATE = SELF.HIREDATE
,SAL
= SELF.SAL
,COMM
= SELF.COMM
,DEPTNO
= SELF.DEPTNO
WHERE EMPNO = SELF.EMPNO
RETURNING EMPNO
,ENAME
,JOB
,MGR
,HIREDATE
,SAL
,COMM
,DEPTNO
INTO SELF.EMPNO
,SELF.ENAME
,SELF.JOB
,SELF.MGR
,SELF.HIREDATE
,SELF.SAL
,SELF.COMM
,SELF.DEPTNO
;
IF SQL%ROWCOUNT <> 1 THEN
RAISE NO_DATA_FOUND;
END IF;
END;
----------------------------------------------------------------------------------MEMBER PROCEDURE ROW_MERGE
IS
BEGIN
MERGE INTO SCOTT.EMP A
USING ( SELECT SELF.EMPNO
AS EMPNO
,SELF.ENAME
AS ENAME
,SELF.JOB
AS JOB
,SELF.MGR
AS MGR
,SELF.HIREDATE AS HIREDATE
,SELF.SAL
AS SAL
,SELF.COMM
AS COMM
,SELF.DEPTNO
AS DEPTNO
FROM DUAL ) B
ON (
A.EMPNO = B.EMPNO)
WHEN MATCHED THEN UPDATE SET
ENAME
= B.ENAME
,JOB
= B.JOB
,MGR
= B.MGR
MGR
,HIREDATE = B.HIREDATE
,SAL
= B.SAL
,COMM
= B.COMM
,DEPTNO
= B.DEPTNO
WHEN NOT MATCHED THEN INSERT ( EMPNO
,ENAME
,JOB
,MGR
,HIREDATE
,SAL
,COMM
,DEPTNO
) VALUES ( B.EMPNO
,B.ENAME
,B.JOB
,B.MGR
,B.HIREDATE
,B.SAL
,B.COMM
,B.DEPTNO
);
END;
----------------------------------------------------------------------------------MEMBER PROCEDURE ROW_SAVE
IS
BEGIN
IF ROW_EXISTS(IN_EMPNO => SELF.EMPNO) THEN
ROW_UPDATE;
ELSE
ROW_INSERT;
END IF;
END;
----------------------------------------------------------------------------------MEMBER PROCEDURE ROW_DELETE
IS
BEGIN
DELETE FROM SCOTT.EMP
WHERE EMPNO = SELF.EMPNO;
IF SQL%ROWCOUNT <> 1 THEN
RAISE NO_DATA_FOUND;
END IF;
END;
----------------------------------------------------------------------------------MEMBER PROCEDURE ROW_SELECT(in_empno NUMBER)
IS
BEGIN
SELECT EMPNO
,ENAME
,JOB
,MGR
,HIREDATE
,SAL
,COMM
,DEPTNO
INTO SELF.EMPNO
,SELF.ENAME
,SELF.JOB
,SELF.MGR
,SELF.HIREDATE
,SELF.SAL
,SELF.COMM
,SELF.DEPTNO
FROM SCOTT.EMP
WHERE EMPNO = IN_EMPNO;
END;
-------------------------------------------------------------------------------------MEMBER PROCEDURE ROW_DEFAULT
IS
BEGIN
NULL;
END;
END;
/
CREATE OR REPLACE TYPE TABLE_DEPT AS TABLE OF SCOTT.ROW_DEPT;
/
CREATE OR REPLACE TYPE TABLE_EMP AS TABLE OF SCOTT.ROW_EMP;
/
CREATE OR REPLACE PACKAGE PA_EMP IS
FUNCTION FU_SELECT RETURN
TABLE_EMP;
FUNCTION FS_DEPTNO(IN_DEPTNO IN EMP.DEPTNO%TYPE) RETURN
TABLE_EMP; FUNCTION FS_MGR(IN_MGR IN EMP.MGR%TYPE) RETURN
TABLE_EMP;
END;
/
CREATE OR REPLACE PACKAGE BODY PA_EMP IS
FUNCTION FU_SELECT RETURN TABLE_EMP
IS
result TABLE_EMP;
BEGIN
SELECT ROW_EMP('ROW_EMP'
,EMPNO
,ENAME
,JOB
,MGR
,HIREDATE
,SAL
,COMM
,DEPTNO
)
BULK COLLECT INTO result
FROM SCOTT.EMP;
RETURN result;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN TABLE_EMP();
WHEN OTHERS THEN
RAISE;
END;
----------------------------------------------------------------FUNCTION FS_DEPTNO(IN_DEPTNO IN EMP.DEPTNO%TYPE) RETURN TABLE_EMP
IS
result TABLE_EMP;
BEGIN
SELECT ROW_EMP('ROW_EMP'
,EMPNO
,ENAME
,JOB
,MGR
,HIREDATE
,SAL
,COMM
,DEPTNO
)
BULK COLLECT INTO result
FROM SCOTT.EMP
WHERE (DEPTNO = IN_DEPTNO OR (DEPTNO IS NULL AND IN_DEPTNO IS NULL));
RETURN result;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN TABLE_EMP();
WHEN OTHERS THEN
RAISE;
END;
----------------------------------------------------------------FUNCTION FS_MGR(IN_MGR IN EMP.MGR%TYPE) RETURN TABLE_EMP
IS
result TABLE_EMP;
BEGIN
SELECT ROW_EMP('ROW_EMP'
,EMPNO
,ENAME
,JOB
,MGR
,HIREDATE
,SAL
,COMM
,DEPTNO
)
BULK COLLECT INTO result
FROM SCOTT.EMP
WHERE (MGR = IN_MGR OR (MGR IS NULL AND IN_MGR IS NULL));
RETURN result;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN TABLE_EMP();
WHEN OTHERS THEN
RAISE;
END;
END;
/
CREATE OR REPLACE PACKAGE PA_DEPT IS
FUNCTION FU_SELECT RETURN TABLE_DEPT;
END;
/
CREATE OR REPLACE PACKAGE BODY PA_DEPT IS
FUNCTION FU_SELECT RETURN TABLE_DEPT
IS
result TABLE_DEPT;
BEGIN
SELECT ROW_DEPT('ROW_DEPT'
,DEPTNO
,DNAME
,LOC
)
BULK COLLECT INTO result
FROM SCOTT.DEPT;
RETURN result;
EXCEPTION
WHEN NO_DATA_FOUND THEN
RETURN TABLE_DEPT();
WHEN OTHERS THEN
RAISE;
END;
END;
/
CREATE OR REPLACE TYPE TYPE_MANAGER UNDER ROW_EMP(
-- attributes
EMPLOYEES TABLE_EMP
-- constructors
, CONSTRUCTOR FUNCTION TYPE_MANAGER RETURN SELF AS RESULT
, CONSTRUCTOR FUNCTION TYPE_MANAGER(IN_EMPNO NUMBER) RETURN SELF AS RESULT
) NOT FINAL
/
CREATE OR REPLACE TYPE BODY TYPE_MANAGER IS
-- constructors
CONSTRUCTOR FUNCTION TYPE_MANAGER RETURN SELF AS RESULT
IS
BEGIN
SELF.OBJECT_TYPE_NAME := 'TYPE_MANAGER';
SELF.ROW_DEFAULT();
SELF.EMPLOYEES := TABLE_EMP();
RETURN;
END;
---------------------------------------------------------------------------------------------CONSTRUCTOR FUNCTION TYPE_MANAGER(IN_EMPNO NUMBER) RETURN SELF AS RESULT
IS
BEGIN
SELF.OBJECT_TYPE_NAME := 'TYPE_MANAGER';
SELF.ROW_SELECT(IN_EMPNO => IN_EMPNO);
SELF.EMPLOYEES := PA_EMP.FS_MGR(IN_EMPNO);
RETURN;
END;
END;
/
CREATE OR REPLACE TYPE TYPE_DEPARTMENT UNDER ROW_DEPT(
-- attributes
EMPLOYEES TABLE_EMP
-- constructors
, CONSTRUCTOR FUNCTION TYPE_DEPARTMENT RETURN SELF AS RESULT
, CONSTRUCTOR FUNCTION TYPE_DEPARTMENT(in_deptno NUMBER) RETURN SELF AS RESULT
-- member functions
, MEMBER FUNCTION GET_MANAGER RETURN TYPE_MANAGER
) NOT FINAL
/
CREATE OR REPLACE TYPE BODY TYPE_DEPARTMENT IS
-- constructors
CONSTRUCTOR FUNCTION TYPE_DEPARTMENT RETURN SELF AS RESULT
IS
BEGIN
SELF.OBJECT_TYPE_NAME := 'TYPE_DEPARTMENT';
SELF.ROW_DEFAULT();
SELF.EMPLOYEES := TABLE_EMP();
RETURN;
END;
----------------------------------------------------------------------------------CONSTRUCTOR FUNCTION TYPE_DEPARTMENT(in_deptno NUMBER) RETURN SELF
IS
BEGIN
SELF.OBJECT_TYPE_NAME := 'TYPE_DEPARTMENT';
SELF.ROW_SELECT(IN_DEPTNO => IN_DEPTNO);
SELF.EMPLOYEES := PA_EMP.FS_DEPTNO(IN_DEPTNO);
RETURN;
END;
-- member functions
MEMBER FUNCTION GET_MANAGER RETURN TYPE_MANAGER
IS
t_manager TYPE_MANAGER;
BEGIN
SELECT TYPE_MANAGER(e.empno)
INTO t_manager
FROM EMP e
WHERE e.deptno = SELF.DEPTNO
AND e.job = 'MANAGER'
;
RETURN t_manager;
END;
END;
/
CREATE OR REPLACE TYPE TYPE_ENTERPRISE UNDER TYPE_OBJECT(
-- attributes
NAME
VARCHAR2(100)
, PRESIDENT
TYPE_MANAGER
, DEPARTMENTS TABLE_DEPT
, EMPLOYEES
TABLE_EMP
-- constructors
, CONSTRUCTOR FUNCTION TYPE_ENTERPRISE RETURN SELF AS RESULT
) NOT FINAL
/
CREATE OR REPLACE TYPE BODY TYPE_ENTERPRISE IS
-- constructors
CONSTRUCTOR FUNCTION TYPE_ENTERPRISE RETURN SELF AS RESULT
IS
BEGIN
SELF.OBJECT_TYPE_NAME := 'TYPE_ENTERPRISE';
SELF.NAME := 'King Corporation';
SELF.PRESIDENT := TYPE_MANAGER(7839);
SELF.DEPARTMENTS := PA_DEPT.FU_SELECT;
SELF.EMPLOYEES := PA_EMP.FU_SELECT;
RETURN;
END;
END;
/
AS RESULT
