Oracle type object Description

Oracle TYPE OBJECT ========================= recently I learned advanced PL/SQL programming by myself, learn about object type ). Www.2cto.com specifically searched for the 10g official documentation, which is not based on: ========================================================== ===================== 1. this section describes Object-oriented programming is especially suited for building reusable components and complexapplications. it is particularly suitable for building reusable components and object-oriented programming for complex applications. Www.2cto.com In PL/SQL, object-oriented programming is based on object types. In PL/SQL, object-oriented programming is based on object types. They let you model real-world objects, separate interfaces and implementation details, and storeobject-oriented data persistently in the database. they insist on letting you simulate objects in the real world, separate interfaces and implementation details, Object-oriented Data and storage in the database. 2. PL/SQL statements and initialization object types can represent real-world entities. For example, an object type can represent a student, bank account, computer screen, reasonable quantity, or data structure, such as queue, stack, or list. [SQL] CREATE OR REPLACE TYPE address_typ AS OBJECT (street VARCHAR2 (30), city VARCHAR2 (20), state CHAR (2), postal_code VARCHAR2 (6 )); [SQL] CREATE OR REPLACE TYPE employee_typ AS OBJECT (employee_id NUMBER (6), first_name VARCHAR2 (20), last_name VARCHAR2 (25), email VARCHAR2 (25 ), phone_number VARCHAR2 (25), hire_date DATE, job_id VARCHAR2 (25), salary NUMBER (8, 2), commission_pct NUMBER (2, 2), manager _ Id NUMBER (6), department_id NUMBER (4), address address_typ map member function get_idno return number, member procedure display_address (self in out nocopy employee_typ )); -- CREATE object [SQL] CREATE TYPE BODY employee_typ AS MAP MEMBER FUNCTION get_idno RETURN NUMBER IS BEGIN RETURN employee_id; END; MEMBER PROCEDURE display_address (SELF IN OUT NOCOPY employee_typ) is begin DBMS_OUTPUT.PUT_LINE (first _ Name | ''| last_name); DBMS_OUTPUT.PUT_LINE (address. street); DBMS_OUTPUT.PUT_LINE (address. city | ',' | address. state | ''| address. postal_code); END; -- Persistence object [SQL] CREATE TABLE employee_tab OF employee_typ; CREATE TYPE emp_typ as table of employee_typ; 3. DECLARE the object in the PL/SQL block: [SQL] DECLARE emp employee_typ; -- emp is atomically null BEGIN -- call the constructor for employee_typ emp: = em Ployee_typ (315, 'francis ', 'logan', 'flogan', '123456. 777.2222 ', to_date ('2017-12-24', 'yyyy-mm-dd'), 'sa _ man', 2012 ,. 15,101,110, address_typ ('376 mission', 'san Francisco ', 'CA', '000000'); DBMS_OUTPUT.PUT_LINE (emp. first_name | ''| emp. last_name); -- display details emp. display_address (); -- call object method to display details END; 4. how to handle uninitialized objects in PL/SQL: [SQL] DECLARE emp employee_typ; -- e Mp is atomically null begin if emp is null then DBMS_OUTPUT.PUT_LINE ('EMP is NULL # 1'); end if; IF emp. employee_id is null then DBMS_OUTPUT.PUT_LINE ('emp. employee_id is NULL # 1'); end if; emp. employee_id: = 330; IF emp is null then DBMS_OUTPUT.PUT_LINE ('empis NULL # 2'); end if; IF emp. employee_id is null then DBMS_OUTPUT.PUT_LINE ('emp. employee_id is NULL # 2'); end if; emp: = employee_typ (NULL, NU LL, NULL, address_typ (NULL, NULL); -- emp: = NULL; -- this wocould have made the following IF statement true if emp is null then DBMS_OUTPUT.PUT_LINE ('empis NULL # 3'); end if; IF emp. employee_id is null then DBMS_OUTPUT.PUT_LINE ('emp. employee_id is NULL #3 '); end if; exception when access_assist_null THEN DBMS_OUTPUT.PUT_LINE ('cannot assign Value to NULL object'); END; 5. manipulate objects in PL/SQL: 5. 1. calling Object Constructors and Methods [SQL] DECLARE emp employee_typ; BEGIN INSERT INTO employee_tab VALUES (employee_typ (310, 'evers', 'boston ', 'eboston ', '2017. 111.2222 ', to_date ('2017-12-24', 'yyyy-mm-dd'), 'sa _ REP ', 2012 ,. 15,101,110, address_typ ('123 main', 'san Francisco ', 'CA', '000000'); insert into employee_tab VALUES (em Ployee_typ (320, 'martha ', 'dunn', 'mdunn', '123456. 111.3333 ', to_date ('2017-11-5', 'yyyy-mm-dd'), 'ac _ MGR ', 2012, 12500, address_typ ('2017 Broadway ', 'redwood city', 'CA', '20140901'); END; 94065 update and delete objects: [SQL] DECLARE emp employee_typ; begin insert into employee_tab VALUES (employee_typ (370, 'Robert ', 'myers', 'rmyers', '2017. 111.2277 ', to_date ('2014-3-7', 'yyyy-mm-dd'), 'sa _ REP ', 2012 ,. 12,101, 110, address_typ ('2014 fillmore', 'san Francisco ', 'CA', '000000'); UPDATE employee_tab e SET e. address. street = '2014 California 'WHERE e. employee_id = 370; delete from employee_tab e WHERE e. employee_id = 310; END; 6. use the REF modifier to manipulate objects: [SQL] DECLARE emp employee_typ; emp_ref REF employee_typ; emp_name VARCHAR2 (50); BEGIN SELECT REF (e) INTO emp_ref FROM employee_tab e WHERE e. employee_id = 370 ;-- The following assignment raises an error, not allowed in PL/SQL -- emp_name: = emp_ref.first_name | ''| emp_ref.last_name; -- emp: = DEREF (emp_ref); not allowed, cannot use DEREF in procedural statements select deref (emp_ref) INTO emp from dual; -- use dummy table DUAL emp_name: = emp. first_name | ''| emp. last_name; DBMS_OUTPUT.PUT_LINE (emp_name); END; 7. define a Defining SQL statement Types Equivalent to PL/SQL Collection Types) 7.1 define a nested TABLE: -- CREATE TYPE CourseList AS TABLE OF VARCHAR2 (10) -- define type -- create object type create type student AS object (-- create object id_num INTEGER (4), name VARCHAR2 (25), address VARCHAR2 (35), status CHAR (2 ), courses CourseList); -- declare nested table as attribute -- create table sophomores of student nested table courses store as courses_n T; -- insert data into sophomores values (1, 'dylan', 'Carl STREET ', 'active', CourseList ('math1020'); -- Query SELECT. *, B. * from sophomores a, TABLE (. courses) B; select/* + nested_table_get_refs */* from courses_nt t; 7.2 define an array: -- declare the array type (Each project has a 16-character code name) -- We will store up to 50 projects at a time in a database column. create type ProjectList as varray (50) OF VARCHAR2 (16); -- CREATE Table C Reate table dept_projects (-- create database table dept_id NUMBER (2), name VARCHAR2 (15), budget NUMBER (11, 2), -- Each department can have up to 50 projects. projects ProjectList); -- INSERT data: insert into dept_projects VALUES (60, 'security', 750400, ProjectList ('new Badges ', 'track Computers', 'check exists ')); 8. use the object: 8.1 in dynamic SQL to define the object types person_typ and the array TYPE hobbies_var, and CREATE the report TEAMS: [SQL] CREATE TYPE person_typ As object (name VARCHAR2 (25), age NUMBER); create type hobbies_var as varray (10) OF VARCHAR2 (25 ); create or replace package teams AUTHID CURRENT_USER as procedure create_table (tab_name VARCHAR2); PROCEDURE insert_row (tab_name VARCHAR2, p person_typ, h rows); PROCEDURE print_table (tab_name VARCHAR2); END; create or replace package body teams as procedure create_table (tab_name VARCHAR2) I S begin execute immediate 'create table' | tab_name | '(pers person_typ, hobbs hobbies_var)'; END; PROCEDURE insert_row (tab_name VARCHAR2, p person_typ, h hobbies_var) is begin execute immediate 'insert INTO '| tab_name | 'values (: 1,: 2)' USING p, h; END; PROCEDURE print_table (tab_name VARCHAR2) is type refcurtyp is ref cursor; v_cur refcurtyp; p person_typ; h hobbies_var; begin open v _ Cur FOR 'select pers, hobbs FROM '| tab_name; loop fetch v_cur INTO p, h; exit when v_cur % NOTFOUND; -- print attributes of 'p' and elements of 'H' DBMS_OUTPUT.PUT_LINE ('name: '| p. name | '-Age:' | p. age); FOR I IN h. FIRST .. h. last loop DBMS_OUTPUT.PUT_LINE ('hobby' | I | '):' | h (I); END LOOP; CLOSE v_cur; END; 8.2 call the stored procedure in the TEAMS package: [SQL] DECLARE team_name VARCHAR2 (15 ); BEGIN team_name: = 'notables '; TEAMS. create_table (team_name); TEAMS. insert_row (team_name, person_typ ('john', 31), hobbies_var ('skiing', 'coin collecting', 'tennis '); TEAMS. insert_row (team_name, person_typ ('Mary ', 28), hobbies_var ('golf', 'quilting', 'Rock climbing ', 'fencing'); TEAMS. print_table (team_name); END; ========================================================== ========= output: name: John-Age: 31 Hoby (1): Skiingholobby (2): coin collectingholobby (3): tennisName: Mary-Age: 28 holobby (1): golfholobby (2): quiltingholobby (3): rock climbingholobby (4 ): the fencing PL/SQL process has been completed successfully.