What are the tables in the Oracle composite type?

The following articles mainly describe Oracle composite types and the detailed introduction of several types of variables in Oracle databases. The following is a detailed description of the article. I hope you will be able to learn more about Oracle composite types.

There are several types of variables in Oracle: Numeric

Character Type

Reference Type

Compound

Oracle composite types include: table and array

Table tables can also be divided into: 1. index table 2. nested table

I. index table:

Definition: The table names in the exercise are v_table)

Type index table Name is table of type index by binary_integer;

Usage: Because the index table name cannot be used directly, a variable is defined first.

 
 

  
  
v_temptable_table v_table;  
 
 

Index Table features:

① There are only two columns in the index table

② It can only be stored in memory

③ You cannot use DML operations.

④ Easy to use

Index table exercise

Declare

Define index table

 
 

  
  
type v_table is table of emp%rowtype index by binary_integer; 
 
 

Define index table Variables

 
 

  
  
v_emp v_table;  
  
  
cursor cur_emp is select * from emp;  
  
  
v_num number:=0;  
  
  
begin 
 
 

Put every piece of data in EMP into the index table q

 
 

  
  
for v_e in cur_emp loop  
  
  
v_numv_num:=v_num+1;  
  
  
select * into v_emp(v_num) from emp where ename=v_e.ename;  
  
  
end loop; 
 
 

Output the name of each record

 
 

  
  
for I in 1..v_emp.count loop  
  
  
dbms_output.put_line(v_emp(i).ename);  
  
  
end loop;  
  
  
end;  
  
  
/  
 
 

Ii. nested tables:

Definition: (The table names in the exercise are v_nested)

Type table name is table of type;

Use: define variables and initialize them.

 
 

  
  
v_my_nested v_nested := v_nested(‘aa’,’bb’);  
 
 

Features:

1. DML operations are supported.

2. Initialization is required before use.

3. Extended using the EXTEND Method

Exercise:

 
 

  
  
declare  
  
  
type v_nested is table of varchar2(20);  
  
  
v_my_nested v_nestedv_nested:=v_nested('aa','bb'); 
 
 

Initialization

 
 

  
  
begin  
  
  
v_my_nested.extend(3);  
  
  
v_my_nested(5):='ee';  
  
  
end;  
  
  
/ 
 
 

3. array: array

Definition:

Type array name is varry (maximum value limit) of type;

Note: initialization is performed first and DML operations can be performed.

 
 

  
  
v_my_varry[100]:=v_varray('aa','bb');   
  
  
declare  
 
 

Defines the maximum limit of 100 for an array.

 
 

  
  
type v_array is array(100) of emp.ename%type; 
 
 

Define array variables and Open Space

 
 

  
  
v_arr v_arrayv_array:=v_array();  
  
  
cursor cur_emp is select * from emp;  
  
  
v_num number:=0;  
  
  
begin  
  
  
v_arr.extend(100);   
  
  
for v_e in cur_emp loop  
  
  
v_numv_num:=v_num+1;  
  
  
select ename into v_arr(v_num) from emp where ename=v_e.ename;  
  
  
end loop;  
  
  
for I in 1..v_arr.count loop  
  
  
dbms_output.put_line(v_arr(i));  
  
  
end loop;  
  
  
end;  
  
  
/  
 
 

Iv. Oracle composite type Method:

The keyword extend is only used in nested tables and arrays.

Count is used to calculate the length.

First points to the first

Extended Space count

Last points to the Last

Exist to determine whether there is a is not null does not exist is null

Next move down

Priev move up

Delete (n) delete record n you can specify a specific row)