|
SQL>
SQL> CREATE OR REPLACE TYPE address AS OBJECT (
2 street_1 VARCHAR2(40),
3 street_2 VARCHAR2(40),
4 city VARCHAR2(40),
5 state_abbr VARCHAR2(2),
6 zip_code VARCHAR2(5),
7 phone_number VARCHAR2(10),
8 MEMBER PROCEDURE ChangeAddress (
9 st_1 IN VARCHAR2, st_2 IN VARCHAR2, cty IN VARCHAR2,
10 state IN VARCHAR2, zip IN VARCHAR2),
11 MEMBER FUNCTION getStreet (line_no IN number) RETURN VARCHAR2,
12 MEMBER FUNCTION getCity RETURN VARCHAR2,
13 MEMBER FUNCTION getStateAbbr RETURN VARCHAR2,
14 MEMBER FUNCTION getPostalCode RETURN VARCHAR2,
15 MEMBER FUNCTION getPhone RETURN VARCHAR2,
16 MEMBER PROCEDURE setPhone (newPhone IN VARCHAR2)
17 );
18 /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE BODY address AS
2 MEMBER PROCEDURE ChangeAddress (
3 st_1 IN VARCHAR2, st_2 IN VARCHAR2, cty IN VARCHAR2,
4 state IN VARCHAR2, zip IN VARCHAR2) IS
5 BEGIN
6 IF (st_1 IS NULL) THEN
18 RAISE_application_error(-20001,'The new Address is invalid.');
19 ELSE
20 street_1 := st_1;
21 street_2 := st_2;
22 city := cty;
23 state_abbr := upper(state);
24 zip_code := zip;
25 END IF;
26 END;
27
28 MEMBER FUNCTION getStreet (line_no IN number)
29 RETURN VARCHAR2 IS
30 BEGIN
31 IF line_no = 1 THEN
32 RETURN street_1;
33 ELSIF line_no = 2 THEN
34 RETURN street_2;
35 ELSE
36 RETURN ' ';
37 END IF;
38 END;
39
40 MEMBER FUNCTION getCity RETURN VARCHAR2 IS
41 BEGIN
42 RETURN city;
43 END;
44
45 MEMBER FUNCTION getStateAbbr RETURN VARCHAR2 IS
46 BEGIN
47 RETURN state_abbr;
48 END;
49
50 MEMBER FUNCTION getPostalCode RETURN VARCHAR2 IS
51 BEGIN
52 RETURN zip_code;
53 END;
54
55 MEMBER FUNCTION getPhone RETURN VARCHAR2 IS
56 BEGIN
57 RETURN phone_number;
58 END;
59
60 MEMBER PROCEDURE setPhone (newPhone IN VARCHAR2) IS
61 BEGIN
62 phone_number := newPhone;
63 END;
64 END;
65 /
Type body created.
SQL> set echo on
SQL>
SQL>
SQL> CREATE OR REPLACE TYPE appartment AS OBJECT (
2 BldgName VARCHAR2(40),
3 BldgAddress address,
4 BldgMgr INTEGER,
5 MEMBER PROCEDURE ChangeMgr (NewMgr IN INTEGER),
6 ORDER MEMBER FUNCTION Compare (OtherAppartment IN appartment)
7 RETURN INTEGER
8 );
9 /
Type created.
SQL>
SQL> CREATE OR REPLACE TYPE BODY appartment AS
2 MEMBER PROCEDURE ChangeMgr(NewMgr IN INTEGER) IS
3 BEGIN
4 BldgMgr := NewMgr;
5 END;
6
7 ORDER MEMBER FUNCTION Compare (OtherAppartment IN appartment)
8 RETURN INTEGER IS
9 BldgName1 VARCHAR2(40);
10 BldgName2 VARCHAR2(40);
11 BEGIN
12 BldgName1 := upper(ltrim(rtrim(BldgName)));
13 BldgName2 := upper(ltrim(rtrim(OtherAppartment.BldgName)));
14
15 IF BldgName1 = BldgName2 THEN
16 RETURN 0;
17 ELSIF BldgName1 < BldgName2 THEN
18 RETURN -1;
19 ELSE
20 RETURN 1;
21 END IF;
22 END;
23 END;
24 /
Type body created.
SQL> CREATE TABLE employee(
2 emp_id INTEGER,
3 emp_name VARCHAR2(32),
4 supervised_by INTEGER,
5 pay_rate NUMBER(9,2),
6 pay_type CHAR,
7 emp_bldg REF appartment);
Table created.
SQL>
SQL> CREATE TABLE appartments OF appartment;
Table created.
SQL>
SQL> set echo on
SQL>
SQL> INSERT INTO appartments
2 values (appartment('Victor Building',
3 address('203 Washington Square',' ','Lansing',
4 'MI','48823',' '),
5 597));
1 row created.
SQL>
SQL> INSERT INTO appartments
2 values (appartment('East Storage Shed',
3 address('1400 Abbott Rd','','Lansing','MI','48823',''),
4 598));
1 row created.
SQL> INSERT INTO appartments
2 values (appartment('Headquarters Building',
3 address('150 West Jefferson','','Detroit','MI','48226',''),
4 599));
1 row created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE AssignEmpToBldg (
2 EmpNumIn IN employee.emp_id%TYPE,
3 BldgNameIn IN appartments.BldgName%TYPE
4 ) AS
5 BEGIN
6 UPDATE employee
7 SET emp_bldg = (SELECT REF(b)
8 FROM appartments B
9 WHERE BldgName = BldgNameIn)
10 WHERE emp_id = EmpNumIn;
11 IF SQL%NOTFOUND THEN
12 RAISE_application_error(-20000,'Employee ' || EmpNumIn
13 || ' could not be assigned to appartment '
14 || BldgNameIn);
15 END IF;
16 END;
17 /
Procedure created.
SQL>
SQL> BEGIN
2 AssignEmpToBldg (598,'Victor Building');
3 AssignEmpToBldg (597,'East Storage Shed');
4 END;
5 /
BEGIN
*
ERROR at line 1:
ORA-20000: Employee 598 could not be assigned to
appartment Victor Building
ORA-06512: at "JAVA2S.ASSIGNEMPTOBLDG", line 12
ORA-06512: at line 2
SQL>
SQL> drop table employee;
Table dropped.
SQL> drop table appartments;
Table dropped.
SQL> drop type appartment;
Type dropped.
SQL>
SQL> --
|