Oracle object operation example

Oracle object operation example select * from v $ version; /* BANNER Upgrade Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production PL/SQL Release 11.2.0.1.0-Production CORE 11.2.0.1.0 Production TNS for 64-bit Windows: version 11.2.0.1.0-Production NLSRTL Version 11.2.0.1.0-Production */-- create an object 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 transaction (Partition NUMBER), member function get_sal return number, role function employee_type (eno number, name VARCHAR2) return self as RESULT, map MEMBER function sal _ Sort return number, static function get_time return varchar2); -- 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 NUMBER) is begin dno: = new_dno; end; member function get_sal return number is begin return sa Lary; end; constructor function employee_type (eno number, name VARCHAR2) return self as RESULT is begin self. eno: = eno; self. name: = name; return; end; map MEMBER function sal_sort 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; -- view type object desc employee_type -- use static object example select employee_type.get_time () From dual; -- create a table using an object. The object is used as the row create table t_employee_object of employee_type; desc t_employee_object insert into t_employee_object (eno, name) values (1111, 'Scott '); insert into t_employee_object values (employee_type (2222, 'hr'); commit; select value (a) from t_employee_object a -- column object, the object is used as a table field create table t_employee_ex (basic_info employee_type, sex varchar2 (6); insert into t_employee_ex values (Employee_type (3333, 'wallimn'), 'male'); insert into t_employee_ex values (employee_type (1112, 'Smith ', 1000, 'cler', 10 ), 'female'); update t_employee_ex a set. basic_info.salesary = 2000 where. basic_info.eno = 3333; commit; select. basic_info.eno eno,. basic_info.name name,. basic_info.salary salary from t_employee_ex a; -- this query is not supported. Select value (. basic_info), sex from t_employee_ex a; -- this supports select. basic_info, sex from t_employee_ex a -- object reference type 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; create table t_housemaster of hm_type; insert into t_housemaster values ('beijing', 'beijing', 'changan Street ', '20', 'zhangpeng'); insert into t_housemaster values ('shanghai', 'shanghai ', 'bund ', '20', 'wang sheng'); commit; select * from t_housemaster; create table t_housemaster_ex (id number (8) primary key, name varchar2 (10 ), master ref hm_type); insert into t_housemaster_ex select 1, 'zhang Peng ', Ref (a) from t_housemaster a where. name = 'zhang peng'; select. id,. name, rawtohex (. master), deref (. master ). info () mi from t_housemaster_ex a; -- only raw addresses exist in the master field. Point to the location where the actually stored content is located. -- Check the objects of the reference type. Update t_housemaster set street = '23 'where name = 'zhang peng'; select. id,. name, deref (. master ). info () mi from t_housemaster_ex a; -- added the object ATTRIBUTE alter type employee_type add attribute remark varchar2 (50) cascade; -- The table structure created by the object is automatically changed. desc t_employee_object -- the field defined by the object is also automatically added to this attribute. The value is NULL select a. basic_info.remark from t_employee_ex a; select dump (a. basic_info) from t_employee_ex a; select a. Rows, a. basic_info.name from t_employee_ex a where a. rows = 3333;