Oracle Data Type Analysis

Oracle databases have the following data types: sclare, composite, and reference ).

1. scalar (char, number, varchar2, long, clob, blob, nclob, bfile, date)

Char (Num) is a fixed length of up to 2000 characters. The query efficiency of char is extremely high. However, if char is used, the length of the character is not automatically recognized and how many characters are defined, it will take up to a few characters, even if the stored characters are only two, and the defined char is four characters, it will fill the last two characters with spaces.

Number is a data type. If only number is defined, it indicates an integer. If number () is defined, it indicates 7 valid numbers with two decimal places, for example, the number 12345.67 conforms to the number () Definition. Number ranges from-(Power 38 of 10) to power 38 of 10.

Varchar2 (Num) is an upgraded version of varchar. Oracle has optimized varchar2. The difference between varchar and varchar is that varchar2 treats null strings with a maximum length of 4000 characters. varchar2 can save characters, A striking difference with Char is that varchar2 recognizes the length of stored characters and does not occupy extra space. However, the query efficiency is not as high as char. As for a varchar2 character to use several bytes to store, to see the character set used by the database, such as GBK, Chinese characters will occupy two bytes, one English, if it is UTF-8, Chinese characters generally occupy three bytes, english or 1.

Long can be used in database storage to store up to 2 GB of data. As a variable, it can represent a variable string with a maximum length of 32760 bytes.

The maximum clob bytes type object, which stores single-byte character data and can store 4 GB of capacity. (Do not use Chinese characters ).

Blob binary data, which can store images, videos, and other resources, up to 4 GB.

The nclob data type is used to store large data blocks with a fixed width of single-byte or multi-byte characters in the database. character sets with different widths are not supported. The maximum size that can be stored is 4 GB.

When the size of a large binary object is large or 4 GB, The bfile data type is used to store it in an operating system file outside the database; when the size is less than 4 GB, then it is stored in the operating system file inside the database, and the bfile column stores the file locating program, which points to a large binary file on the server.

Date data type, including year, month, day, hour, minute, and second.

Ii. Combination (record, table)

Scalar types are predefined and can be used to derive some composite types. There are mainly records and tables.

A record can be viewed as a combination structure of a group of tags. Its declaration method is as follows:

Declare

Type record_type_name is record (

Parameter 1 parameter type

Parameter 2 parameter type

......);

Record_type_name indicates the name of the record type.

Example:

Declare

-- Declare a record of the record type myrecord

Type myrecord is record (

-- Define parameters

Id varchar2 (10 );

Name varchar2 (10 ));

-- Declare a variable of the myrecord type real_record

Real_record myrecord;

Begin

-- Store the Retrieved Data in real_record

Select Eid, ename into real_record from EMP where eid = '001 ';

-- Output through real_record. Variable

Dbms_output.put_line (real_record.id | ',' | real_record.name );

End;

A table is not a table that stores data physically. It is a variable type, also known as a PL/SQL table. It is similar to an array in C language and is processed in a similar way. The statement is as follows:

Declare

Type table_type_name is table of scalar_type index by binary_integer;

Table_type_name indicates the type name, scalar_type indicates a type declaration of the scalar type, and index by binary_integer indicates that the subscript is an integer. Related Variables must also be defined during reference. Different from an array, a table has two columns: Key and value. Key is the binary_integer declared during definition, and value is the scalar_type declared during definition.

Example:

Declare

-- Declare a table Type table sp_table_type
Type sp_table_type is table of Student. ID % Type Index by binary_integer;

-- Declare a sp_table_type variable sp_table
Sp_table sp_table_type;
Begin

-- Save the queried data in sp_table (0)
Select ID into sp_table (0) from student where id = 4;
Dbms_output.put_line (sp_table (0 ));
End;

Iii. References

Before PL/sql8.0, there is only one type-ref cursor, that is, cursor. Its definition is relatively simple,

Declare

Type cursor_type_name is ref cursor;

After PL/sql8.0, the ref type is introduced, which points to an object.

Example:

Declare

-- Declare a cursor type cursor sp_emp_cursor

Type sp_emp_cursor is ref cursor;

-- Declare a sp_emp_cursor variable test_cursor.

Test_cursor sp_emp_cursor;

-- Declare two variables of the % Type type

V_ename EMP. ename % type;

V_sal EMP. Sal % type;

Begin

-- Open the cursor

Open test_cursor for select ename, Sal from EMP where depart = & No;

Loop

-- Extract cursor

Fetch test_cursor into v_ename, v_sal;

Exit when test_cursor % notfound;

Dbms_output.put_line (v_ename | ''| v_sal );

End loop;

-- Close the cursor

Close test_cursor;

End;