Several Concepts of PL/SQL

1: differences between char and varchar2:

Varchar2: variable-length CHAR: fixed-length the memory is allocated when a fixed-length char is defined. The following example shows the differences between varchar2 and dynamic memory allocation:

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] PL/SQL process has been completed successfully.

The output shows that the memory is allocated when the char type variable is defined. The allocated memory may also exceed the size defined by the variable. The varchar2 type variable dynamically allocates the memory based on the size of the Set variable.

The following example also describes this:

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)||']'); 10    dbms_output.put_line('v is ['||LENGTH(v)||']'); 11    dbms_output.put_line('var1 is ['||LENGTH(var1)||']'); 12    dbms_output.put_line('var2 is ['||LENGTH(var2)||']'); 13    dbms_output.put_line('var3 is ['||LENGTH(var3)||']'); 14    dbms_output.put_line('var4 is ['||LENGTH(var4)||']'); 15    v := v || ' '; 16    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]

2: differences between character and char: The character type is the subtype of char, and the character type is of the same size as its base class. It is implicitly converted between the char and character data types. The following is a subtype definition: subtype subtype_name is base_type [(maximum_size [byte | char])] [not null];

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/PL/SQL process completed successfully.

3: The difference between Timestamp and date: timestamp is the subtype of date, providing more accurate time.
The following example illustrates the differences between them:

SQL> declare 2 d Date: = systimestamp; 3 T timestamp (8): = systimestamp; 4 Begin 5 dbms_output.put_line ('date ['| d |'] '); 6 dbms_output.put_line ('to _ char ['| to_char (D, 'dd-MON-YY hh24: MI: ss') |'] '); 7 dbms_output.put_line ('timestamp ['| T |'] '); 8 end; 9/date [12-6 months-12] to_char [12-6 months-12 17:50:17] timestamp [12-6 months-12 05.50.17.40600000 afternoon] the PL/SQL process has been completed successfully.

Note: Timestamp (8) indicates the precision. You can modify this value to see that the time zone of the output result is useless. I don't know what the function is. Here is an example of rewriting: PL/SQL programing:

The difference between these timestamps is that those with time zones append the time zone

To the timestamp. the time zone qualifier returns the standard time and an indicator whether
Time zone is using Daylight Saving Time. The local time zone qualifier returns the difference
Between the local time and Greenwich Mean Time (GMT)

SQL> declare 2 var1 timestamp with local time zone; 3 var2 timestamp with local time zone: = systimestamp; 4 var3 timestamp (3) with local time zone; 5 var4 timestamp (3) with local time zone: = systimestamp; 6 begin 7 dbms_output.put_line ('var1 ['| var1 |'] '); 8 dbms_output.put_line ('var2 ['| var2 |'] '); 9 dbms_output.put_line ('var3 [' | var3 | ']'); 10 dbms_output.put_line ('var4 ['| var4 |'] '); 11 en D; 12/var1 [] var2 [12-6 months-12 05.51.44.750000 PM] var3 [] var4 [12-6 months-12 05.51.44.750 PM] the PL/SQL process has been completed successfully.

An effective way to use the trunc (date_variable) function to set the system Timestamp

SQL> declare 2 d Date: = sysdate; 3 begin 4 dbms_output.put_line (to_char (trunc (D), 'dd-MON-YY hh24: MI: ss'); 5 end; 6/12-6 months-12 00: 00: 00pl/SQL process has been completed successfully.

4: differences between nchar and char nvarchar and varchar: The nchar data type adopts the unified character encoding equivalent to the char data type, And the nvarchar2 data type adopts the unified character encoding equivalent to the varchar2 data type, we should use nchar or nvarchar because multiple character set encoding is supported in the same database. Character encoding supports multiple character sets,

A character in al16utf16 occupies three bytes, while UTF-8 encoding occupies two bytes. Nchar and nvarchar are used to unify character encoding for future extension.
5: Relationship between string, varchar, and varcahr2; string, and varchar are subtypes of varcahr2. 6: difference between pls_integer and integer:

Pls_integer uses the mathematical function library of the operating system. integer is the subtype of number.

Pls_integer uses the local mathematical function library. After the statement is declared, no memory is allocated until the variable is assigned a value. You can use the length () function to perform a side test;

SQL> declare 2 var1 pls_integer; -- a null value requires no space. 3 var2 pls_integer: = 11; -- an integer requires space for each character. 4 Begin 5 dbms_output.put_line ('var1 ['| length (var1) |'] '); 6 dbms_output.put_line ('var2 [' | length (var2) | ']'); 7 end; 8/var1 [] var2 [2] PL/SQL process completed successfully.