日期:2014-05-16  浏览次数:20476 次

the type of String literal in oracle_10
'北京' 在sql语句中的类型是varchar2;
接下来验证一下:
create table province(name nvarchar2(10)); --name的类型是nvarchar2
insert into province values('天府');
insert into province values('国家首都');
select (CASE  WHEN name LIKE '%首都%' THEN  ('北京')  
     ELSE (name) END) as name from province; --ORA-12704:字符集不匹配

此处确定'北京'这样的字面值不是NVARCHAR2类型的;
TO_CHAR()把NCHAR, NVARCHAR2, CLOB, NCLOB 转换成VARCHAR2类型;
TO_NCHAR()把CHAR, VARCHAR2, CLOB, NCLOB 转换成NVARCHAR2类型;
select (CASE  WHEN name LIKE '%首都%' THEN  ('北京')  
     ELSE to_char(name) END) as name from province; --ok 

这句可以确定'北京'是VARCHAR2类型;
下面这句同样不会报错;
select (CASE  WHEN name LIKE '%首都%' THEN  to_nchar('北京')  
      ELSE (name) END) as name from province; --ok