Oracle learning notes-optimized model and Processing Method

Like other programming languages, Oracle also has data types. Oracle has two main application scenarios: one is to specify the type of columns in a data table, and the other is to declare variables in PL/SQL programming. Oracle data types include character, number, date, and LOB ). Oracle also provides built-in functions for data types. This chapter focuses on Oracle's character type and related functions.

1. Summary

Oracle has three types: char (n), varchar (n), and varchar2 (n ).

1.1 fixed length string -- char (n)

Char (n) specifies that the data type of a variable or column is a fixed-length string. N indicates the length of the string. When the actual string length is less than n, Oracle uses spaces to fill the right end. Of course, Oracle does not allow the actual string length to be greater than n.

When the column in the database is specified as char (n) type, the maximum value of n cannot exceed 2000. Otherwise, Oracle will throw an error

When char (n) is the data type of a column, test its maximum length.

SQL> create table test_char (f_char char (2001 ));

1.2 varchar (n)

Oracle provides varchar (n) data types. This type is developed by Oracle to cater to varchar in industrial standards. This number

The data type is actually a variable-length string type. That is to say, when the actual string length is insufficient, spaces are not used for filling. Similarly, the actual string length cannot exceed n.

When the data type of a column appears, the maximum length of varchar cannot exceed 4000, as shown below.

SQL> alter table test_char add f_varchar varchar (4001 );

1.3 varchar2 (n)

Varchar2 (n) is also a variable-length string type. Oracle customizes this data type in addition to industrial standards. Oracle also reminds users to try to use varchar2 (n) instead of varchar (n ). Because varchar2 (n) can be used to guarantee Oracle backward compatibility.

When the data type of a column appears, the varchar2 length cannot exceed 4000, as shown below.

SQL> alter table test_char add f_varchar2 varchar2 (4001 );

2. balanced analysis

For data tables of general purpose, the most common string type is varchar2 (n ).

2.1 differences between varchar2 (n) and char (n)

Varchar2 (n) is a variable string type, while char (n) is a fixed string type. The difference between the two lies in whether to use spaces to fill the deficiencies.

In the test_char table, the lengths of column f_char and column f_varchar2 are 2000 and 4000, respectively. Insert a new record into the record and assign the same value to the two columns.

SQL> insert into test_char (f_char, f_varchar2)

2 values ('000', '000 ');

2.2 selection of varchar2 (n) and char (n)

Char (n) columns usually occupy a large storage space, while varchar2 (n) columns consume less space. Therefore, the varchar2 (n) type is a general choice for database design. But this does not mean that the char (n) type should be discarded. In contrast, char (n) is more efficient than varchar2 (n ). This is because the variable-length string type always needs to adjust the storage space when the actual data length changes. Especially when the data is frequently modified and the data length is constantly changing, this loss of efficiency is particularly significant.

Most applications do not require database efficiency as the primary concern. Most of them use varchar2 (n) to define columns. Char (n) is a typical example of "changing space for time", which can be selected as appropriate in actual development.

2.3 string type in variable Declaration

Three string types: char (n), varchar (n), and varchar2 (n) can be used to declare variables. However, when the three statements are used, the maximum length is 32767.

Test the maximum length of char (n), varchar (n), and varchar2 (n) used for variable declaration.

SQL & gt; declare s char (32768 );

3 balanced Processing

3.1 left-side completion string-lpad () function

The lpad () function is used to complete the string to the left. This function is mainly used for string formatting. The format is to format the string to the specified length. If there are deficiencies, fill in specific characters on the left side of the string. The calling method is as follows.

Lpad (string, padded_length, [pad_string])

The first parameter specifies the original string, the second parameter specifies the length of the formatted string, and the third parameter specifies the character used to fill in the insufficient digits.

3.2 Right completion string -- rpad () function

Similar to the lpad () function, the rpad () function returns a string formatted into a specific number of digits. This function is only used to complete the number of digits in the right.

Use the rpad () function to complete the string on the right side.

SQL> select rpad ('1', 4, '*') empplyee_no from dual;

3.3 return the string in lower case -- lower () function

The lower () function is used to return the string in lowercase. This function has only one parameter, that is, the original string.

In the database, when the query condition can ignore the case-sensitive format, we usually use the lower () function to unify the string case-sensitive format.

SQL> select username, password

From dba_users

Where lower (username) = 'system ';

3.4 return the string in upper case -- upper () function

Opposite to the lower () function, the upper () function is used to return the string in uppercase. This function has only one parameter, that is, the original string.

We can also rewrite the query statement using the upper () function.

SQL> select username, password

From dba_users

Where upper (username) = 'system ';

3.5 uppercase words -- initcap () function

The initcap () function is used to convert words into uppercase letters for the first character and lowercase letters for other characters.

Use the initcap () function to format words.

SQL> select initcap ('like') new_word from dual;

SQL> select initcap ('like') new_word from dual;

 

3.6 returns the string length -- length () function.

The length () function can be used to return the length of a string.

The length () function is used to return the length of characters.

SQL> select length ('20140901') len from dual;

1 2 Next Page