在Oracle中具有char数据类型的查询列 [英] Query column with char data type in oracle

问题描述

我有以下情况:

示例代码:

        create table abc(aa char(10));
        insert into abc values('ABC'); 
        --ABC will be padded with 7 blank spaces
        Issue:
        Select * from abc where aa in ('ABC'); 
        --This above statement returns one row with value ABC
        Declare
        v_temp varchar2(10);
        v_aa varchar2(10) := 'ABC';
        Begin
        select aa into v_temp from abc where aa in (v_aa);
        dbms_output.put_line(v_temp);
        end;

-上面的pl/sql块一次执行不输出任何内容，但是如果我用从abc中选择aa到v_temp中，而aa在('ABC')中替换掉aa"中选择，则什么都不打印该值将被打印.

--The above pl/sql block one execution prints nothing but if i replace the select inside that block with "select aa into v_temp from abc where aa in ('ABC');" the value will be printed.

请就行为向我提出建议.

Please advice me on the behaviour.

推荐答案

问题归结为您在查询中使用char还是varchar2比较语义.如果您具有硬编码的字符串文字或char(10)变量，则Oracle使用char比较语义将忽略尾随空白.如果您具有varchar2(10)变量，则Oracle使用varchar2比较语义，其中包括结尾的空格.因此

The issue comes down to whether you use char or varchar2 comparison semantics in your queries. If you have a hard-coded string literal or a char(10) variable, Oracle uses the char comparison semantics which are to ignore the trailing white space. If you have a varchar2(10) variable, Oracle uses the varchar2 comparison semantics which includes the trailing white space. Thus

 select aa 
   into v_temp 
   from abc 
  where aa in (v_aa);

如果将v_aa定义为char(10)(或者如果将其替换为字符串文字)，则

将返回一行，但是如果将其定义为varchar(10)，则不会返回行.

will return a row if v_aa is defined as a char(10) (or if it is replace with a string literal) but not if it is defined as a varchar(10).

这是大多数人完全避免使用char数据类型的(许多)原​​因之一.就我个人而言，我不介意偶尔使用char来存储真正的固定宽度数据(即，将char(1)用于标志，将char(2)用于状态代码)，即使在那些情况下使用char而不是varchar2也没有好处.场景.但是，对于任何非固定宽度的内容，使用char都是没有意义的.您只是在强迫Oracle占用比其所需更多的空间，并为自己处理两组字符串比较语义(还有其他问题)创建更多的工作.

This is one of the (many) reasons that most people avoid char data types entirely. Personally, I don't mind the occasional char for truly fixed-width data (i.e. char(1) for flags and char(2) for state codes) even though there is no benefit to using char over varchar2 in those scenarios. For anything that is not fixed-width, however, using a char makes no sense. You're just forcing Oracle to consume more space than it needs to and creating more work for yourself dealing with two sets of string comparison semantics (among other issues).

这篇关于在Oracle中具有char数据类型的查询列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！