Several conceptual differences of Pl/sql

The difference between 1:char and VARCHAR2:

The varchar2:variable-length becomes longer

Char:fixed-length fixed length

Allocate memory when char is defined, while varchar2 dynamically allocates memory

The following example clearly illustrates the difference between the two:

Sql> DECLARE

2 c CHAR (32767): = ';

3 v VARCHAR2 (32767): = ';

4 BEGIN

5 Dbms_output.put_line (' C is [' | | LENGTH (c) | | '];

6 Dbms_output.put_line (' V is [' | | LENGTH (v) | | '];

7 V: = V | | ' ';

8 Dbms_output.put_line (' V is [' | | LENGTH (v) | | ']; End;

9/

c is [32767]

V is [1]

V is [2]

The PL/SQL process has completed successfully.

The output shows that the char type variable allocates memory at the time it is defined, the allocated memory may exceed the variable definition size, and the VARCHAR2 type variable dynamically allocates memory, depending on the size of the variable being set.

The following examples also illustrate this point:

Sql> DECLARE

2 c CHAR (32767): = ';

3 v VARCHAR2 (32767): = ';

4 var1 CHAR: = '; --implicitly sized at 1 byte.

5 var2 CHAR (1): = '; --Explicitly sized at 1 byte.

6 Var3 CHAR (1 BYTE): = '; --Explicitly sized at 1 byte.

7 VAR4 char (1 char): = '; --explicitly sized at 1 character.

8 BEGIN

9 Dbms_output.put_line (' C is [' | | LENGTH (c) | | '];

Dbms_output.put_line (' V is [' | | LENGTH (v) | | '];

One dbms_output.put_line (' var1 is [' | | LENGTH (var1) | | '];

Dbms_output.put_line (' var2 is [' | | LENGTH (var2) | | '];

Dbms_output.put_line (' Var3 is [' | | LENGTH (VAR3) | | '];

Dbms_output.put_line (' VAR4 is [' | | LENGTH (VAR4) | | '];

V: = V | | ' ';

Dbms_output.put_line (' V is [' | | LENGTH (v) | | ']; End;

17/

c is [32767]

V is [1]

VAR1 is [1]

VAR2 is [1]

VAR3 is [1]

VAR4 is [1]

V is [2]

The difference between 2:character and char:

The character type is the subtype,character type of char and the same size as its base class, implicitly converting between char and character data types. The following is a subtype definition:

Subtype Subtype_name is base_type[(maximum_size [BYTE | CHAR])] [not NULL];

application Example:

Sql> DECLARE

2 subtype Code is CHAR (1 char);

3 C char (1 char): = ' A ';

4 d CODE;

5 BEGIN

6 d: = C;

7 End;

8/

The PL/SQL process has completed successfully.