Analysis on the storage format of Oracle nested tables and oracle nesting

Analysis on the storage format of Oracle nested tables and oracle nesting

Oracle nested tables are rarely used. The following describes how to store them. For example, a user corresponds to a department.

SQL> select * from v $ version;
BANNER
--------------------------------------------------------------------------------
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 Linux: Version 11.2.0.1.0-Production
NLSRTL Version 11.2.0.1.0-Production

SQL> create type t_dept as object (

Dept_id number,
Dept_name varchar2 (20)
)
/
SQL> create type t_dept_arry as varray (100) of t_dept
/
SQL> create table t_user (
User_id number,
User_name varchar2 (10 ),
Depts t_dept_arry
)
/
SQL> insert into t_user values (1, 'zhang san', t_dept_arry (t_dept (100, 'developer group'), t_dept (200, 'developer group ')));
SQL> insert into t_user values (2, 'lily', t_dept_arry (t_dept (300, 'design a group'), t_dept (400, 'Design two group ')));

SQL> commit;

-- To traverse all the department information of a user, you must use the table format.

SQL> select user_id, user_name, d. dept_id, d. dept_name from t_user u, table (u. depts) d;
USER_ID USER_NAME DEPT_ID DEPT_NAME
--------------------------------------------------
1 Zhang San 100 develop a group
1 zhangsan 200 developer Group 2
2. Design a group by Li Si 300
2 Li Si 400 Design Group 2
Dump block:
Block_row_dump:
Tab 0, row 0, @ 0x3f47
Tl: 81 fb: -- H-FL -- lb: 0x1 cc: 3
Col 0: [2] c1 02
Col 1: [4] d5 c5 c8 fd
Col 2: [69]
00 01 00 00 00 00 01 00 00 00 00 0a e4 23 00 37 09 00 00 00 00 00 00 27 00
00 00 00 00 01 88 01 27 01 01 00 02 0f 84 01 0f 02 c2 02 08 bf aa b7 a2 d2
Bb d7 e9 0f 84 01 0f 02 c2 03 08 bf aa b7 a2 b6 fe d7 e9
Tab 0, row 1, @ 0x3ef6
Tl: 81 fb: -- H-FL -- lb: 0x1 cc: 3
Col 0: [2] c1 03
Col 1: [4] c0 ee cb c4
Col 2: [69]
00 01 00 00 00 00 01 00 00 00 00 0a e4 24 00 37 09 00 00 00 00 00 00 27 00
00 00 00 00 01 88 01 27 01 01 00 02 0f 84 01 0f 02 c2 04 08 c9 e8 bc c6 d2
Bb d7 e9 0f 84 01 0f 02 c2 05 08 c9 e8 bc c6 b6 fe d7 e9
End_of_block_dump

Insert into t_user values (3, 'wang wu', t_dept_arry (t_dept (500, 'home ')));
Commit;

Block_row_dump:
Tab 0, row 0, @ 0x3f5d
Tl: 59 fb: -- H-FL -- lb: 0x1 cc: 3
Col 0: [2] c1 04
Col 1: [4] cd f5 ce e5
Col 2: [47]
00 01 00 00 00 00 01 00 00 00 00 0a e4 49 00 21 09 00 00 00 00 00 00 11 00
00 00 00 00 01 88 01 11 01 01 01 01 09 84 01 09 02 c2 06 02 bc d2
End_of_block_dump
-- After reading the above dump results, I am confused and don't know how to store them. However, I found a clue in user_objects that there is a lob field.
SQL> select object_name, object_type from user_objects s;
OBJECT_NAME OBJECT_TYPE
-------------------------------------------------
SYS_LOB0000082685C00003 $ LOB
T_USER TABLE
T_DEPT_ARRY TYPE
T_DEPT TYPE

SQL> select column_name, segment_name, index_name
From user_lobs s
Where s. table_name = 't_ user ';
COLUMN_NAM SEGMENT_NAME INDEX_NAME
----------------------------------------------------------------------

DEPTS SYS_LOB0000082685C00003 $ SYS_IL0000082685C00003 $

-- Test what is the difference with a common clob?

Drop table test purge;
Create table test
(
Id number,
T_clob clob
);
Insert into test values (1, 'developer group 2 group ');
Insert into test values (1, 'Design one group design two group ');
Insert into test values (1, 'home ');

Commit;

Tab 0, row 0, @ 0x3f5d
Tl: 59 fb: -- H-FL -- lb: 0x1 cc: 2
Col 0: [2] c1 02
Col 1: [52]
00 54 00 01 02 0c 80 00 00 00 00 00 01 00 00 0a e4 4a 00 20 09 00 00
00 00 00 00 10 00 00 00 00 01 5f 00 53 d1 4e 00 7e c4 5f 00 53 d1 4e 8c
7e c4
LOB
Locator:
Length: 84 (52)
Version: 1
Byte Length: 2
LobID: 00.00.002.161.00.00.002.16a.e4.4a
Flags [0x02 0x0c 0x80 0x00]:
Type: CLOB
Storage: BasicFile
Enable Storage in Row
Characterset Format: IMPLICIT
Partitioned Table: No
Options: VaringWidthReadWrite
Inode:
Size: 32
Flag: 0x09 [Valid DataInRow]
Future: 0x00 (shocould be '0x00 ')
Blocks: 0
Bytes: 16
Version: 00000.0000000001
Inline data [16]
Dump of memory from 0x00002AE680C155EC to 0x00002AE680C155FC
2AE680C155E0 D153005F [_. S.]
2AE680C155F0 C47E004E D153005F C47E8C4E [N .~. _. S. N. ~.]
Tab 0, row 1, @ 0x3f22
Tl: 59 fb: -- H-FL -- lb: 0x1 cc: 2
Col 0: [2] c1 02
Col 1: [52]
00 54 00 01 02 0c 80 00 00 00 00 00 01 00 00 0a e4 4b 00 20 09 00 00
00 00 00 00 10 00 00 00 00 00 01 8b be 8b a1 4e 00 7e c4 8b be 8b a1 4e 8c
7e c4
LOB
Locator:
Length: 84 (52)
Version: 1
Byte Length: 2
LobID: 00.00.002.161.00.00.002.16a.e4.4b
Flags [0x02 0x0c 0x80 0x00]:
Type: CLOB
Storage: BasicFile
Enable Storage in Row
Characterset Format: IMPLICIT
Partitioned Table: No
Options: VaringWidthReadWrite
Inode:
Size: 32
Flag: 0x09 [Valid DataInRow]
Future: 0x00 (shocould be '0x00 ')
Blocks: 0
Bytes: 16
Version: 00000.0000000001
Inline data [16]
Dump of memory from 0x00002AE680C155B1 to 0x00002AE680C155C1
2AE680C155B0 8BBE8B01 7E004EA1 8BBE8BC4 7E8C4EA1 [...... N .~..... N. ~]
2AE680C155C0 02012CC4 [...]
Tab 0, row 2, @ 0x3ef5
Tl: 45 fb: -- H-FL -- lb: 0x1 cc: 2
Col 0: [2] c1 02
Col 1: [38]
00 54 00 01 02 0c 80 00 00 00 00 00 01 00 00 0a e4 4c 00 12 09 00 00
00 00 00 00 02 00 00 00 00 01 5b b6
LOB
Locator:
Length: 84 (38)
Version: 1
Byte Length: 2
LobID: 00.00.0020.1.00.00.0020.a.e4.4c
Flags [0x02 0x0c 0x80 0x00]:
Type: CLOB
Storage: BasicFile
Enable Storage in Row
Characterset Format: IMPLICIT
Partitioned Table: No
Options: VaringWidthReadWrite
Inode:
Size: 18
Flag: 0x09 [Valid DataInRow]
Future: 0x00 (shocould be '0x00 ')
Blocks: 0
Bytes: 2
Version: 00000.0000000001
Inline data [2]

Select the dump content twice, but you still cannot determine whether the two are equivalent:

00 01 00 00 00 00 01 00 00 00 00 0a e449 00 21 09 00 00 00 00 00 11 00
00 00 00 00 01 88 01 11 01 01 01 01 09 84 01 09 02 c2 06 02 bc d2

00 54 00 01 02 0c 80 00 00 0200 00 00 01 00 00 0a e4 4c 00 12 09 00 00
00 00 00 00 02 00 00 00 00 01 5b b6

Summary: nesting stores nested content through lob. TOM said that as a storage mechanism, he prefers to create parent and child tables and create another view, like a real nested table, a nested table is suitable for programming.

How to nest two if statements when writing a stored procedure in oracle? Format

IF condition 1 THEN
Statement sequence 1;
ELSIF condition 2 THEN
Statement sequence 2;
End if;

Assignment of nested oracle tables

First, you need to know how to use extend. It is used to extend elements for nested tables and variable-length data,
Extend: add an empty element to the end of the set.
Extend (n): Add n null elements to the end of the set.
Extend (n, I): copy the nth element and add it to the end of the set.
In addition, if the set is not initialized, extends cannot be used for expansion. If the expansion is complete, a null element is appended. t_tab ('cc', 1, null) is required. If you have any questions, follow these questions.