Oracle PL/SQL Tutorial/PL SQL Data Types/CHAR

Материал из SQL эксперт
Перейти к: навигация, поиск

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

  1. The CHAR datatype is used to hold fixed-length character string data.
  2. A CHAR string always contains the maximum number of characters.
  3. Strings shorter than the maximum length are padded with spaces.
  4. The CHAR datatype typically uses 1 byte per character.
  5. 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>