plsql-index-by table

5. index-by table

5.1 Definition and operation of index-by table

Definition: A composite data type consisting of a set of homogeneous elements similar to an array
Features: The elements in the collection are sparse, without qualified boundaries, but concatenated together by integers as indexes, which can be positive, negative integers, or 0

(1) Defining and assigning values

A defines the type of the index-by table for numeric types

Declare
Type num_table is table of number index by Binary_integer; --binary_integer: Integer
V_example_tab num_table;
V_num number:=13;
Begin
V_example_tab (1): = 1001;
V_example_tab (2): = 1002;
V_example_tab (10): = 1003;
V_example_tab (-10): = 1004;
V_example_tab (0): = 1005;
V_example_tab (v_num): = 1006;
Dbms_output.put_line (To_char (v_example_tab (0)));
End

B defines the type of the Index-by table for the string type

Declare
Type char_table is Table of VARCHAR2 (a) index by Binary_integer; --binary_integer: Integer
V_example_tab char_table;
V_num number:=13;
Begin
V_example_tab (1): = ' xxxx ';
V_example_tab (2): = ' yyhy ';
V_example_tab (Ten): = ' asdf ';
V_example_tab ( -10): = ' asdfasd ';
V_example_tab (0): = ' ASD ';
V_example_tab (v_num): = ' aseas1 ';
Dbms_output.put_line (To_char (V_example_tab (V_num)));
End

C defines the type of the index-by table for the date type
Declare
Type char_table is a table of date index by Binary_integer; --binary_integer: Integer
V_example_tab char_table;
V_change VARCHAR2 (30);
Begin
V_example_tab (1): =sysdate;
V_example_tab (2): =sysdate+1;
V_change:=to_char (V_example_tab (2), ' yyyy-mm-dd ');
Dbms_output.put_line (V_change);
End

D can also store composite data types

Declare
Type Hrc_org_rec is record (Hrc_code number,hrc_descr varchar2 (20));
Type num_table is table of Hrc_org_rec index by Binary_integer;
V_example_tab num_table;
Begin
V_example_tab (1). hrc_code:=1002;
V_example_tab (1). hrc_descr:= ' ADSFASDG '; --Assignment method
V_example_tab (2). hrc_code:=1003;
V_example_tab (2). hrc_descr:= ' QWEQR ';
Dbms_output.put_line (To_char (V_example_tab (1). Hrc_code));
Dbms_output.put_line (V_example_tab (1). HRC_DESCR);
End

(2) To access undefined rows

Declare
Type num_table is table of number index by Binary_integer; --binary_integer: Integer
V_example_tab num_table;
V_num number:=13;
Begin
V_example_tab (1): = 1001;
V_example_tab (2): = 1002;
V_example_tab (10): = 1003;
V_example_tab (-10): = 1004;
V_example_tab (0): = 1005;
V_example_tab (v_num): = 1006;
Dbms_output.put_line (To_char (v_example_tab (0)));
Dbms_output.put_line (To_char (V_example_tab (1))); --the statement before the error is not affected
Dbms_output.put_line (To_char (V_example_tab (5))); --Access to undefined Guild error, program termination, report No_data_found's fault
Dbms_output.put_line (To_char (V_example_tab (-10)));
End

(3) Creating rows for index-by tables by assigning elements

Declare
Type num_table is Table of VARCHAR2 (a) index by Binary_integer;
V_example_tab num_table;
V_num number:=13;
Begin
The loop result for the for IDX in 1..10 loop--for loop can be a collection of numbers
V_example_tab (IDX): =to_char (2*idx+1);
End Loop;
For IDX in 1..10 loop
Dbms_output.put_line (V_example_tab (IDX));
End Loop;
End

(4) Delete the contents of the Index-by table and delete the entire index-by

Declare
Type num_table is table of number index by Binary_integer;
V_example_tab num_table;
V_EXAMPLE_TAB1 num_table; --not initialized, empty index-by table
V_num number:=13;
Begin
V_example_tab (1): = 1001;
V_example_tab (2): = 1002;
V_example_tab (10): = 1003;
V_example_tab (-10): = 1004;
V_example_tab (0): = 1005;
V_example_tab (v_num): = 1006;
V_EXAMPLE_TAB:=V_EXAMPLE_TAB1; --Delete with uninitialized index-by table assignment
Dbms_output.put_line (To_char (v_example_tab (0))); --Error: No_data_found
End

###########################################################################################

Related methods of 5.2 index-by table

(1) Exists Method--determining whether an element exists

Declare
Type num_table is table of number index by Binary_integer;
V_example_tab num_table;
V_num number:=13;
Begin
V_example_tab (1): = 1001;
V_example_tab (2): = 1002;
V_example_tab (10): = 1003;
V_example_tab (-10): = 1004;
V_example_tab (0): = 1005;
V_example_tab (v_num): = 1006;
If V_example_tab.exists ( -5) Then
Dbms_output.put_line (' YES ');
Else
Dbms_output.put_line (' NO ');
End If;
End

(2) Count Method--The number of index-by table elements is counted, which is the number of rows (indexes, subscripts) that have been defined for index-by
Declare
Type num_table is table of number index by Binary_integer;
V_example_tab num_table;
V_num number:=13;
V number;
Begin
V_example_tab (1): = 1001;
V_example_tab (2): = 1002;
V_example_tab (10): = 1003;
V_example_tab (-10): = 1004;
V_example_tab (0): = 1005;
V_example_tab (v_num): = 1006;
V:=v_example_tab.count;
Dbms_output.put_line (To_char (v));
End

(3) Delete method--delete element

Declare
Type num_table is Table of VARCHAR2 (a) index by Binary_integer;
V_example_tab num_table;
V_num number;
Begin
For IDX in 1..10 loop
V_example_tab (IDX): =to_char (2*idx+1);
End Loop;
V_num:=v_example_tab.count;
Dbms_output.put_line (To_char (v_num));
V_example_tab.delete (1); --delete the value of the corresponding index in the index-by table
V_example_tab.delete (5);
V_example_tab.delete (7);
V_num:=v_example_tab.count;
Dbms_output.put_line (To_char (v_num));
End

Use the elements in the output index-by table in conjunction with the Exists method

Declare
Type num_table is Table of VARCHAR2 (a) index by Binary_integer;
V_example_tab num_table;
V_num number;
Begin
For IDX in 1..10 loop
V_example_tab (IDX): =to_char (2*idx+1);
End Loop;
V_num:=v_example_tab.count;
Dbms_output.put_line (To_char (v_num));
V_example_tab.delete (1);
V_example_tab.delete (5);
V_example_tab.delete (7);
V_num:=v_example_tab.count;
Dbms_output.put_line (To_char (v_num));
For IDX in 1..10 loop
If V_example_tab.exists (IDX) Then
Dbms_output.put_line (To_char (V_example_tab (IDX)));
Else
Dbms_output.put_line (' NULL ');
End If;
End Loop;
End

(4) First last next prior method--the index value is taken

First method: Takes the index number of the index-by element (which contains the null value).
Last method: Take the index number of the final index-by element (containing the null value).
Next method: Take index-by the index number of the next valued element (containing the null value) of the specified element
Prior method: Takes index-by the index number of the previous valued element (containing the null value) of the specified element


Case:
Declare
Type num_table is Table of VARCHAR2 (a) index by Binary_integer;
V_example_tab num_table;
V_num number;
IDX number;
Begin
For IDX in 1..10 loop
V_example_tab (IDX): =to_char (2*idx+1);
End Loop;
V_num:=v_example_tab.count;
Dbms_output.put_line (To_char (v_num));
V_example_tab.delete (1);
V_example_tab.delete (5);
V_example_tab.delete (7);
V_num:=v_example_tab.count;
Dbms_output.put_line (To_char (v_num));
Idx:=v_example_tab.first;
Loop
If V_example_tab.exists (IDX) Then
Dbms_output.put_line (V_example_tab (IDX));
End If;
Exit when Idx=v_example_tab.last; --The condition for exiting the loop satisfies the index value of the last non-empty element that IDX equals
Idx:=v_example_tab.next (IDX); --Each time the loop uses the next method to push the IDX backward, pushing to the index position of the next non-null element
End Loop;
End

(3) Assignment of index-by table

The index-by table referencing records exists in the Index-by table for a record type as a stored type

A Decentralized Assignment

Declare
Type Hrc_org_rec is record (hrc_org_id number,hrc_descr varchar2, Org_short_name varchar2 (30));
Type num_table is table of Hrc_org_rec index by Binary_integer;
V_example_table num_table;
Cursor csr_hrc_org is select Hrc_org_seq.nextval hrc_org_id,h.hrc_descr,o.org_short_name
From Org_tab O,hrc_tab h
where O.hrc_code=h.hrc_code;
I integer:=1;
Begin
For IDX in csr_hrc_org loop
V_example_table (i). hrc_org_id:=idx.hrc_org_id; --Decentralized assignment
V_example_table (i). Hrc_descr:=idx.hrc_descr;
V_example_table (i). Org_short_name:=idx.org_short_name;
i:=i+1;
End Loop;
For j in 1..v_example_table.count Loop
If V_example_table.exists (j) Then
Dbms_output.put_line (To_char (V_example_table (j). hrc_org_id)); --Output separately
Dbms_output.put_line (V_example_table (j). Hrc_descr);
Dbms_output.put_line (V_example_table (j). Org_short_name);
End If;
End Loop;
End

B Aggregation Assignment

Declare
Type Hrc_org_rec is record (hrc_org_id number,hrc_descr varchar2, Org_short_name varchar2 (30));
Type num_table is table of Hrc_org_rec index by Binary_integer;
V_example_table num_table;
Cursor csr_hrc_org is select Hrc_org_seq.nextval hrc_org_id,h.hrc_descr,o.org_short_name
From Org_tab O,hrc_tab h
where O.hrc_code=h.hrc_code;
I integer:=1;
Begin
For IDX in csr_hrc_org loop
V_example_table (i): =idx;
--Aggregating the assignment method, assigning the cursor handle to index-by, equals assigning each property in the cursor field to the Index-by property of each field based on the record
i:=i+1;
End Loop;
For j in 1..v_example_table.count Loop
If V_example_table.exists (j) Then
Dbms_output.put_line (To_char (V_example_table (j). hrc_org_id));
Dbms_output.put_line (V_example_table (j). Hrc_descr);
Dbms_output.put_line (V_example_table (j). Org_short_name);
End If;
End Loop;
End

Practice:
Create a Emp_r table (empno number,sal number,hiredate date,dname varchar2 (10))
The four attribute fields of the employee are required to be stored in the record, the entire information of these employees is placed in a index-by table, and the information is stored in the Emp_r table in the form of records inserted in the circular way.

plsql-index-by table