Oracle PL/SQL Tutorial/PL SQL Data Types/CHAR
Содержание
- 1 Assigning an empty string to the character variable is exactly the same as assigning NULL to it.
- 2 CHAR
- 3 CHAR type variable
- 4 Compare char against varchar, and the trailing spaces do matter.
- 5 Compare CHAR and VARVHAR32 variables for equality
- 6 Compare fixed length string and a literal
- 7 Constants are compared using blank-padded comparison semantics, so the trailing spaces won"t affect the result.
- 8 Fixed length strings are compared with blank-padded comparison semantic
Assigning an empty string to the character variable is exactly the same as assigning NULL to it.
<source lang="sql">
SQL> SQL> declare
2 v_text CHAR(1) :=""; -- the same as NULL 3 begin 4 NULL; 5 End; 6 /
PL/SQL procedure successfully completed. SQL> SQL></source>
CHAR
- The CHAR datatype is used to hold fixed-length character string data.
- A CHAR string always contains the maximum number of characters.
- Strings shorter than the maximum length are padded with spaces.
- The CHAR datatype typically uses 1 byte per character.
- The CHAR datatype has a maximum length of 32767 bytes.
The Syntax for the CHAR Datatype
<source lang="sql">
variable_name CHAR(size);</source>
CHAR type variable
<source lang="sql">
SQL> SQL> SET SERVEROUTPUT ON SQL> SET ECHO ON SQL> DECLARE
2 employee_name_c CHAR(32); 3 employee_name_v VARCHAR2(32); 4 BEGIN 5 --Assign the same value to each string. 6 employee_name_c := "CHAR"; 7 employee_name_v := "VARCHAR"; 8 9 --Test the strings for equality. 10 IF employee_name_c = employee_name_v THEN 11 DBMS_OUTPUT.PUT_LINE("The names are the same"); 12 ELSE 13 DBMS_OUTPUT.PUT_LINE("The names are NOT the same"); 14 END IF; 15 END; 16 /
The names are NOT the same PL/SQL procedure successfully completed. SQL> SQL></source>
Compare char against varchar, and the trailing spaces do matter.
<source lang="sql">
SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> DECLARE
2 fixed_length_10 CHAR(10); 3 fixed_length_20 CHAR(20); 4 var_length_10 VARCHAR2(10); 5 var_length_20 VARCHAR2(20); 6 BEGIN 7 8 fixed_length_10 := "ZZZZ"; 9 10 11 var_length_10 := "ZZZZ"; 12 IF fixed_length_10 = var_length_10 THEN 13 DBMS_OUTPUT.PUT_LINE("Char and Varchar2: """ 14 || fixed_length_10 || """ = """ 15 || var_length_10 || """"); 16 ELSE 17 DBMS_OUTPUT.PUT_LINE("Char and Varchar2: """ 18 || fixed_length_10 || """ NOT = """ 19 || var_length_10 || """"); 20 END IF; 21 22 END; 23 /
Char and Varchar2: "ZZZZ " NOT = "ZZZZ" PL/SQL procedure successfully completed. SQL> SQL> SQL></source>
Compare CHAR and VARVHAR32 variables for equality
<source lang="sql">
SQL> SQL> SET SERVEROUTPUT ON SQL> SET ECHO ON SQL> DECLARE
2 employee_name_c CHAR(32); 3 employee_name_v VARCHAR2(32); 4 BEGIN 5 --Assign the same value to each string. 6 employee_name_c := "CHAR"; 7 employee_name_v := "VARCHAR"; 8 9 --Test the strings for equality. 10 IF employee_name_c = employee_name_v THEN 11 DBMS_OUTPUT.PUT_LINE("The names are the same"); 12 ELSE 13 DBMS_OUTPUT.PUT_LINE("The names are NOT the same"); 14 END IF; 15 END; 16 /
The names are NOT the same PL/SQL procedure successfully completed. SQL> SQL></source>
Compare fixed length string and a literal
<source lang="sql">
SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> DECLARE
2 fixed_length_10 CHAR(10); 3 fixed_length_20 CHAR(20); 4 var_length_10 VARCHAR2(10); 5 var_length_20 VARCHAR2(20); 6 BEGIN 7 8 fixed_length_10 := "ZZZZ"; 9 10 11 IF fixed_length_10 = "ZZZZ" THEN 12 DBMS_OUTPUT.PUT_LINE("Char and constant: """ 13 || fixed_length_10 || """ = """ || "ZZZZ" || """"); 14 END IF; 15 16 END; 17 /
Char and constant: "ZZZZ " = "ZZZZ" PL/SQL procedure successfully completed.</source>
Constants are compared using blank-padded comparison semantics, so the trailing spaces won"t affect the result.
<source lang="sql">
SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> DECLARE
2 fixed_length_10 CHAR(10); 3 fixed_length_20 CHAR(20); 4 var_length_10 VARCHAR2(10); 5 var_length_20 VARCHAR2(20); 6 BEGIN 7 8 9 IF "AAAA" = "AAAA " THEN 10 DBMS_OUTPUT.PUT_LINE 11 ("Constant: ""AAAA"" = ""AAAA """); 12 END IF; 13 14 END; 15 /
Constant: "AAAA" = "AAAA " PL/SQL procedure successfully completed. SQL></source>
Fixed length strings are compared with blank-padded comparison semantic
<source lang="sql">
SQL> SQL> SET ECHO ON SQL> SET SERVEROUTPUT ON SQL> DECLARE
2 fixed_length_10 CHAR(10); 3 fixed_length_20 CHAR(20); 4 var_length_10 VARCHAR2(10); 5 var_length_20 VARCHAR2(20); 6 BEGIN 7 8 9 10 fixed_length_10 := "ZZZZ"; 11 fixed_length_20 := "ZZZZ"; 12 IF fixed_length_20 = fixed_length_10 THEN 13 DBMS_OUTPUT.PUT_LINE("Char: """ || fixed_length_10 || """ = """ 14 || fixed_length_20 || """"); 15 END IF; 16 17 END; 18 /
Char: "ZZZZ " = "ZZZZ " PL/SQL procedure successfully completed. SQL></source>