Oracle Composite data type example

-- Recode data type declarev_deptinfoscott.dept % rowtype; typedept_recordisRECORD (v1scott. dept. deptno % type, v2sc

-- Recode data type declare v_deptinfo scott. dept % rowtype; type dept_record is RECORD (v1 scott. dept. deptno % type, v2 SC

-- Recode Data Type

Declare

V_deptinfo scott. dept % rowtype;

Type dept_record is RECORD (

V1 scott. dept. deptno % type,

V2 scott. dept. dname % type,

V3 scott. dept % rowtype-- The ROWTYPE can be declared.

);

V_deptrecord dept_record;

Begin

-- The following assignment method is incorrect: Because V3 is of the ROWTYPE type, and there are five columns in the queried row record, Type Mismatch will occur when assigning values to v3.

Select deptno, dname, t. * into v_deptrecord from dept t where deptno = 10;

-- Solution: assign values to v1 and v2, and then assign values to v3 using another statement.

Dbms_output.put_line (v_deptrecord.v3.dname | ''| v_deptrecord.v3.deptno );

End;

-- Index table 1

Declare

Type my_index_table1 is table of scott. dept. dname % type-- Any data type can be used to represent the Data Type stored in the index table.

Index by binary_integer;

My1 my_index_table1;

C number (2 );

Begin

Select count (*) into c from dept;

For I in 1. c loop

Select dname into my1 (I) from

(Select rownum rn, t. * from dept t) x

Where x. rn = I;

End loop;

-- Each set has the COUNT attribute, which indicates the total number of valid elements stored in the set.

For I in 1 .. my1.count loop

Dbms_output.put_line (my1 (I ));

End loop;

End;

-- Index table 2

Declare

Type my_index_table1 is table of scott. dept. dname % type

Index by varchar2 (20 );-- For Versions later than Oracle 9i, the following table of the index table can be of Data Type 3 (BINARY_INTEGER, PLS_INTEGER, VVARCHAR2 (length ));

My1 my_index_table1;

Begin

Select loc into my1 ('nanchang ') from dept where deptno = 10;

Dbms_output.put_line (my1 ('nanchang '));

End;

-- Nested table 1

Declare

Type my_index_table1 is table of scott. dept. dname % type;

My1 my_index_table1: = my_index_table1 (null, null); -- the value of null can be used for initialization.

Begin

Select dname into my1 (1) from dept where deptno = 10;

Select dname into my1 (2) from dept where deptno = 20;

Select dname into my1 (3) from dept where deptno = 30;

Select dname into my1 (4) from dept where deptno = 40;

-- After an element is deleted from A nested table, the subscript still exists and can be assigned again.

My1.delete (3 );

Dbms_output.put_line (my1.count );

Select dname into my1 (3) from dept where deptno = 30;

Dbms_output.put_line (my1.count );

For I in 1 .. my1.count loop

Dbms_output.put_line (my1 (I ));

End loop;

End;

-- Nested table 2

Create type phone_type is table of varchar2 (20 );

Create table employee (

Eid number (4 ),

Ename varchar2 (10 ),

Phone phone_type

) Nested table phone store as phone_table;

Insert into employee

Values (1, 'XX', phone_type ('2017-100', '20170901 '));

Insert into employee

Values (2, 'XX', phone_type ('2017-123456', '20140901', 'saasf '));

-- Variable Length Array

Declare

Type my_index_table1 is varray (3) of scott. dept. dname % type;

My1 my_index_table1: = my_index_table1 ('A', 'B', 'C ');-- Initialization

Begin

Select dname into my1 (1) from dept where deptno = 10;

Select dname into my1 (3) from dept where deptno = 20;

For I in 1 .. my1.count loop

Dbms_output.put_line (my1 (I ));

End loop;

End;

-- Record table 2

Declare

-- Custom RECORD can store the columns you want, without the rigidity of ROWTYPE. You can flexibly customize which columns to store.

Type dept_record is RECORD (

V1 scott. dept. deptno % type,

V2 scott. dept. dname % type,

V3 scott. dept. loc % type

);

Type my_index_table1 is table of dept_record

Index by binary_integer;

My1 my_index_table1;

C number (2 );

-- Query all data in the dept table and put it into the Custom Data Type

Begin

-First, the total number of records in the table is queried, And the dept table is queried one by one based on the total number of records and the WHERE Condition Based on rownum.

Select count (*) into c from dept;

For I in 1. c loop

Select x. deptno, x. dname, x. loc into my1 (I) from

(Select rownum rn, t. * from dept t) x

Where x. rn = I;

End loop;

-- Cyclically output the data represented by the v2 field of the my1 type in the DEPT table;

For I in 1 .. my1.count loop

Dbms_output.put_line (my1 (I). v2 );

End loop;

End;

For more information about Oracle, see the Oracle topic page? Tid = 12