22-1:
	CREATE OR REPLACE TYPE employee_type AS OBJECT(
	  eno NUMBER(6),name VARCHAR2(10),salary NUMBER(6,2),
	  job VARCHAR2(10),dno NUMBER(2),
	  MEMBER PROCEDURE change_job(new_job VARCHAR2),
	  MEMBER PROCEDURE change_salary(new_sal NUMBER),
	  MEMBER PROCEDURE change_dept(new_dno VARCHAR2),
	  MEMBER FUNCTION get_sal RETURN NUMBER
	);
	/
22-2:
	CREATE OR REPLACE TYPE BODY employee_type AS
	  MEMBER PROCEDURE change_job(new_job VARCHAR2) IS
	  BEGIN
	    job:=new_job;
	  END;
	  MEMBER PROCEDURE change_salary(new_sal NUMBER) IS
	  BEGIN
	    salary:=new_sal;
	  END;
	  MEMBER PROCEDURE change_dept(new_dno VARCHAR2) IS
	  BEGIN
	    dno:=new_dno;
	  END;
	  MEMBER FUNCTION get_sal RETURN NUMBER IS
	  BEGIN
	    RETURN salary;
	  END;
	END;
	/
22-3:
	CREATE OR REPLACE TYPE employee_type AS OBJECT(
	  eno NUMBER(6),name VARCHAR2(10),salary NUMBER(6,2),
	  job VARCHAR2(10),dno NUMBER(2),
	  CONSTRUCTOR FUNCTION employee_type(eno NUMBER,name VARCHAR2) RETURN SELF AS RESULT,
	  MEMBER PROCEDURE change_sal(new_sal NUMBER),
	  MEMBER FUNCTION get_sal RETURN NUMBER,
	  STATIC FUNCTION get_time RETURN VARCHAR2,
	  MAP MEMBER FUNCTION sal_sort RETURN NUMBER
	);
	/
	CREATE OR REPLACE TYPE BODY employee_type AS
	  CONSTRUCTOR FUNCTION employee_type
	    (eno NUMBER,name VARCHAR2) RETURN SELF AS RESULT
	  IS
	  BEGIN
	    SELF.eno:=eno;
	    SELF.name:=name;
	    RETURN;
	  END;
	  MEMBER PROCEDURE change_sal(new_sal NUMBER) IS
	  BEGIN
	    salary:=new_sal;
	  END;
	  MEMBER FUNCTION get_sal RETURN NUMBER IS
	  BEGIN
	    RETURN salary;
	  END;
	  STATIC FUNCTION get_time RETURN VARCHAR2 IS
	  BEGIN
	    RETURN TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS');
	  END;
	  MAP MEMBER FUNCTION sal_sort RETURN NUMBER IS
	  BEGIN
	    RETURN salary;
	  END;
	END;
	/
22-4:
	DECLARE
	  TYPE emp_table_type IS TABLE OF employee_type INDEX BY BINARY_INTEGER;
	  emp_table emp_table_type;
	  index_max INT;  index_min INT;
	  temp_max INT:=0; temp_min INT:=9999999;
	BEGIN
	  emp_table(1):=employee_type(1,'SCOTT');
	  emp_table(2):=employee_type(2,'SMITH');
	  emp_table(3):=employee_type(3,'BLAKE');
	  emp_table(1).change_sal(&sal1);
	  emp_table(2).change_sal(&sal2);
	  emp_table(3).change_sal(&sal3);
	  FOR i IN 1..emp_table.COUNT LOOP
	    dbms_output.put_line(':'||emp_table(i).name
	      ||',:'||emp_table(i).get_sal);
	    IF emp_table(i).sal_sort>temp_max THEN
	       temp_max:=emp_table(i).sal_sort; index_max:=i;
	    END IF;
	    IF emp_table(i).sal_sort<temp_min THEN
	       temp_min:=emp_table(i).sal_sort; index_min:=i;
	    END IF;
	  END LOOP;
	  dbms_output.put_line('ߵĹԱ:'
	    ||emp_table(index_max).name);
	  dbms_output.put_line('͵ĹԱ:'
	    ||emp_table(index_min).name);
	END;
	/
22-5:
	CREATE TABLE employee OF employee_type;
	DESC employee
	INSERT INTO employee (eno,name) VALUES(1111,'SCOTT');
	INSERT INTO employee VALUES(employee_type(1112,'SMITH'));
	UPDATE employee SET salary=1000 WHERE eno=1111;
	DELETE FROM employee WHERE eno=1112;
	SELECT * FROM employee;
	SELECT value(a) FROM employee a;
22-6:
	CREATE TABLE person(basic_info employee_type,sex VARCHAR2(6));
	DESC PERSON
	INSERT INTO person VALUES(employee_type(1111,'SCOTT'),'Male');
	INSERT INTO person VALUES(employee_type(1112,'SMITH',1000,'CLERK',10),'Female');
	UPDATE person a SET a.basic_info.salary=2000 WHERE a.basic_info.eno=1111;
	SELECT a.basic_info.name name ,a.basic_info.salary salary FROM person a WHERE a.basic_info.eno=1112;
	DELETE FROM person a WHERE a.basic_info.eno=1112;
22-7:
	CREATE OR REPLACE PROCEDURE add_person(
	  eno NUMBER,name VARCHAR2,salary NUMBER DEFAULT 1000,job VARCHAR2 DEFAULT 'CLERK',dno NUMBER DEFAULT 10,sex VARCHAR2 DEFAULT 'Female')
	IS
	BEGIN
	  INSERT INTO person VALUES(employee_type(eno,name,salary,job,dno),sex);
	END;
	/
	exec add_person(1113,'MARY')
22-8:
	CREATE OR REPLACE PROCEDURE update_salary(eno NUMBER,salary NUMBER)
	IS
	  employee employee_type;
	BEGIN
	  SELECT a.basic_info INTO employee FROM person a
	  WHERE a.basic_info.eno=eno;
	  employee.change_sal(salary);
	  UPDATE person a SET basic_info=employee WHERE a.basic_info.eno=eno;
	END;
	/
	exec update_salary(1113,3000)
22-9:
	CREATE OR REPLACE FUNCTION get_sal(eno NUMBER) RETURN NUMBER
	IS
	  employee employee_type;
	BEGIN
	  SELECT a.basic_info INTO employee FROM person a
	  WHERE a.basic_info.eno=eno;
	  RETURN employee.get_sal;
	END;
	/
	VAR salary NUMBER
	exec :salary:=get_sal(1113)
	PRINT salary
22-10:
	CREATE OR REPLACE PROCEDURE delete_person(name VARCHAR2)
	IS
	BEGIN
	  DELETE FROM person a WHERE LOWER(a.basic_info.name)=LOWER(name);
	END;
	/
	exec delete_person('mary')
22-11:
	CREATE TYPE employee_table_type IS TABLE OF employee_type;
	/
	CREATE TYPE department_type AS OBJECT(
	  dno NUMBER(2),dname VARCHAR2(10),
	  employee_table employee_table_type
	);
	/
	CREATE TABLE department OF department_type NESTED TABLE employee_table STORE AS employee_table;
	DESC department
22-12:
	INSERT INTO department VALUES(10,'SALES',
	employee_table_type(
	  employee_type(1111,'SCOTT'),employee_type(1112,'SMITH'),
	  employee_type(1113,'BLAKE'),employee_type(1114,'CLARK')
	));
	UPDATE TABLE(SELECT employee_table FROM department WHERE dno=10) a
	SET a.salary=2000,a.job='MANAGER' WHERE a.eno=1111;
	SELECT eno,name,salary,job FROM TABLE (SELECT employee_table FROM department WHERE dno=10);
	DELETE FROM TABLE (SELECT employee_table FROM department WHERE dno=10) a WHERE a.eno=1114;
22-13:
	CREATE OR REPLACE PROCEDURE add_employee(
	  deptno NUMBER,eno NUMBER,name VARCHAR2,
	  salary NUMBER DEFAULT 1000,job VARCHAR2 DEFAULT 'CLERK'
	) IS
	  emp_tab employee_table_type:=employee_table_type(null);
	  e_22908 EXCEPTION;
	  PRAGMA EXCEPTION_INIT(e_22908,-22908);
	BEGIN
	  emp_tab(1):=employee_type(eno,name,salary,job,NULL);
	  INSERT INTO TABLE
	    (SELECT employee_table FROM department WHERE dno=deptno)
	    VALUES(emp_tab(1));
	EXCEPTION
	  WHEN e_22908 THEN
	    UPDATE department SET employee_table=emp_tab WHERE dno=deptno;
	END;
	/
	INSERT INTO department(dno,dname) VALUES(20,'RESEARCH');
	exec add_employee(20,1115,'KING')
	exec add_employee(20,1116,'MILLER',5000,'MANAGER')
	SELECT * FROM TABLE (SELECT employee_table FROM department WHERE dno=20);
22-14:
	CREATE OR REPLACE PROCEDURE update_salary
	(deptno NUMBER,empno NUMBER,sal NUMBER)
	IS
	BEGIN
	  UPDATE TABLE(SELECT employee_table
	    FROM department WHERE dno=deptno) a
	  SET a.salary=sal
	  WHERE a.eno=empno;
	END;
	/
	exec update_salary(20,1115,2000)
	SELECT * FROM TABLE(SELECT employee_table FROM department WHERE dno=20);
22-15:
	CREATE OR REPLACE PROCEDURE fire_employee
	(deptno NUMBER,ename VARCHAR2)
	IS
	BEGIN
	  DELETE TABLE(SELECT employee_table FROM department WHERE dno=deptno) a
	  WHERE lower(a.name)=LOWER(ename);
	END;
	/
	exec fire_employee(20,'miller')
	SELECT * FROM TABLE(SELECT employee_table FROM department WHERE dno=20);
22-16:
	CREATE OR REPLACE FUNCTION get_job
	(deptno NUMBER,ename VARCHAR2) RETURN VARCHAR2
	IS
	  title VARCHAR2(10);
	BEGIN
	  SELECT job INTO title FROM TABLE (SELECT employee_table FROM department WHERE dno=deptno)
	  WHERE LOWER(name)=LOWER(ename);
	  RETURN title;
	END;
	/
	SELECT get_job(20,'king') FROM dual;
22-17:
	CREATE OR REPLACE TYPE goods_type AS OBJECT(
	  id NUMBER(6),name VARCHAR2(10),description VARCHAR2(40),
	  MEMBER FUNCTION info RETURN VARCHAR2
	) NOT FINAL NOT INSTANTIABLE;
	/
	CREATE OR REPLACE TYPE BODY goods_type AS
	  MEMBER FUNCTION info RETURN VARCHAR2
	  IS
	  BEGIN
	    RETURN name||': '||description;
	  END;
	END;
	/
22-18:
	CREATE OR REPLACE TYPE food_type UNDER goods_type(
	  unit VARCHAR2(6),price NUMBER(6,2),amount NUMBER(6),
	  CONSTRUCTOR FUNCTION food_type(id NUMBER,name VARCHAR2)
	    RETURN SELF AS RESULT,
	  MEMBER FUNCTION total_price RETURN NUMBER
	);
	/
	CREATE OR REPLACE TYPE BODY food_type AS
	  CONSTRUCTOR FUNCTION food_type(id NUMBER,name VARCHAR2)
	    RETURN SELF AS RESULT
	  IS
	  BEGIN
	    self.id:=id;  self.name:=name;
	    RETURN;
	  END;
	  MEMBER FUNCTION total_price RETURN NUMBER
	  IS
	  BEGIN
	    RETURN price*amount;
	  END;
	END;
	/
	DESC FOOD_TYPE
22-19:
	CREATE TABLE food OF food_type(id PRIMARY KEY);
	INSERT INTO food VALUES(food_type(1,''));
	INSERT INTO food VALUES(food_type(2,'',null,'',10,2));
	INSERT INTO food VALUES(3,'','ը㼦','',7.5,4);
	SELECT value(a).info() info,value(a).total_price() total FROM food a;
22-20:
	CREATE OR REPLACE TYPE hm_type AS OBJECT(
	  province VARCHAR2(20),city VARCHAR2(10),
	  street VARCHAR2(20),doorplate VARCHAR2(20),name VARCHAR2(10),
	  MEMBER FUNCTION info RETURN VARCHAR2);
	/
	CREATE OR REPLACE TYPE BODY hm_type AS
	  MEMBER FUNCTION info RETURN VARCHAR2
	  IS
	  BEGIN
	    RETURN name||':'||province||city||
	      street||doorplate;
	  END;
	END;
	/
	CREATE TABLE housemaster OF hm_type;
	INSERT INTO housemaster VALUES('ɹ','ͺ','ԫ','ǽС20','');
	INSERT INTO housemaster VALUES('ɹ','ͺ','ԫ','ǽС21','ά');
22-21:
	CREATE TABLE person(
	  id NUMBER(8) PRIMARY KEY,name VARCHAR2(10),
	  sex CHAR(2),birthdate DATE,master REF hm_type);
	INSERT INTO person SELECT 1,'','','18-5-69',REF(a) FROM housemaster a WHERE a.name='';
	INSERT INTO person SELECT 2,'','Ů','12-6-73',REF(a) FROM housemaster a WHERE a.name='';
	INSERT INTO person SELECT 3,'ΰ','','15-6-98',REF(a) FROM housemaster a WHERE a.name='';
22-22:
	SELECT deref(master).info() FROM person WHERE name='';
22-23:
	desc employee_type
22-24:
	ALTER TYPE employee_type ADD ATTRIBUTE remark VARCHAR2(50) CASCADE;
22-25:
	ALTER TYPE employee_type DROP ATTRIBUTE remark CASCADE;
22-26:
	ALTER TYPE employee_type ADD MEMBER FUNCTION info RETURN VARCHAR2 CASCADE;
22-27:
	ALTER TYPE employee_type DROP MEMBER FUNCTION info RETURN VARCHAR2 CASCADE;
22-28:
	SELECT type_name,supertype_name,final FROM user_types;
22-29:
	SELECT attr_name,attr_type_name FROM user_type_attrs WHERE type_name='GOODS_TYPE';
22-30:
	SELECT method_name FROM user_type_methods WHERE type_name='GOODS_TYPE';












	















