[字符集编码] Oracle 语言区域字符集相关

NLS = National Language Support --- 国家语言支持

1. 说明
部分 nls_* 参数可以通过客户端同名环境变量设置, 以改变客户端软件 (sqlplus, WebApp 等)的动作环境
环境变量 NLS_LANG ({NLS_LANGUAGE}_{NLS_TERRITORY}.{NLS_CHARACTERSET})
但设置 NLS_CHARACTERSET 环境变量没有效果
Oracle 提供若干 NLS 参数定制数据库和客户端系统以适应本地格式
例如有 NLS_LANGUAGE, NLS_DATE_FORMAT, NLS_CALENDER 等, 可以通过查询以下数据字典或v$视图查看

NLS_DATABASE_PARAMETERS
显示数据库当前 NLS 参数取值, 包括数据库字符集取值, 其来源于 props$

NLS_SESSION_PARAMETERS
显示由 NLS_LANG 设置的参数, 或经过 alter session 改变后的参数值 (不包括由NLS_LANG 设置的客户端字符集)
其来源于 v$nls_parameters

NLS_INSTANCE_PARAMETE
显示由参数文件 init<SID>.ora 定义的参数, 其来源于 v$parameter

V$NLS_PARAMETERS
显示数据库当前 NLS 参数取值

超集: 包含子字符集, 但字符编码不一定相同
严格超集: 包含子字符集, 并且字符编码也相同

1-1. 语言
客户端环境变量 NLS_LANGUAGE
约定 oracle 返回提示信息等给客户端时用什么语言来显示消息, 排序的规则, 日期月份名称
NLS_LANG=American_xxx.xxx (sqlplus 中显示英文)
NLS_LANG=JAPANESE_xxx.xxx (sqlplus 中显示日文)

1-2. 地域
客户端环境变量 NLS_TERRITORY
约定 oracle 用什么格式显示日期, 货币, 数字

1-3. 字符集
客户端环境变量 NLS_CHARACTERSET, 应该设置为与客户端操作系统编码方式相同
约定字符的编码方式, 客户端和服务器都有字符集的设置, oracle 会自动实现字符转换, 转换失败时用某个替代字符代替如 ? 或其他乱码来代替
NLS_CHARACTERSET == 数据库字符集, 存储和访问均不进行任何转换
NLS_CHARACTERSET != 数据库字符集, 存储和访问均需要进行转换

1-3-1. Oracle 字符集
Oracle 中包括以下字符集

1-3-1-1. 数据库字符集
用来存储 CHAR, VARCHAR2, CLOB, LONG 等类型数据
用来标示诸如表名, 列名以及 PL/SQL 变量等
用来存储 SQL 和 PL/SQL 程序单元等

查看数据库所有可用字符集
select * from V$NLS_VALID_VALUES;

1-3-1-2. 国家字符集
用以存储 NCHAR, NVARCHAR2, NCLOB 等类型数据

1-3-2. 字符集命名
Oracle的字符集命名遵循以下命名规则
<Language><bit size><encoding> -- <语言><比特位数><编码>
ZHS16GBK 表示采用 GBK 编码格式, 16 位, 简体中文字符集

select nls_charset_name(to_number('0354','xxxx')) from dual;
select to_char(nls_charset_id('ZHS16GBK'), 'xxxx') from dual;
select dump('中文',1016) from dual;

2. 各优先级的设置
SQL > SESSION > CLIENT > INSTANCE > DATABASE

2-1. 语句级 SQL
select
    to_char(sysdate,'yyyy month dd day','nls_date_language=american') as american,
    to_char(sysdate,'yyyy month dd day','nls_date_language=german') as german
from dual;

2-2. 会话级 SESSION
用户请求连接时设置的参数

2-2-1. 设置
可通过 alter session 或 dbms_session.set_nls 修改
默认的 NLS_LANGUAGE 与 NLS_TERRITORY 依赖于客户端设置, 而 NLS_CHARACTERSET 保持数据库设置
如果连接时未指定, 则使用客户端设置, 如果客户端没指定, 则使用实例级设置

2-2-2. 查看
select * from nls_session_parameters;
select userenv('lang') from dual; (显示 <language> 的缩写)
select userenv('language') from dual;
select sys_context('userenv','language') from dual;

2-3. 客户端级 CLIENT

2-3-1. 设置
通过环境变量 NLS_LANG 来设置
oracle 通过此设置来识别客户端程序 (sqlplus webapp 等) 正在使用的语言字符集, 因此需要将其设置为与客户端操作系统一致

2-3-2. 查看
操作系统使用的字符集
env | grep nls_lang -i
echo $nls_lang
set nls_lang
locale
locale -a
chcp

2-4. 实例级 INSTANCE
读取实例启动时的参数, initfile 或 spfile 文件

2-4-1. 设置
通过 alter system 修改

2-4-2. 查看
select * from nls_instance_parameters;
select name,value from v$parameter where name like '%nls%';
show parameter nls

2-5. 数据库级 DATABASE

2-5-1. 设置
数据库创建时参数 (create database), 创建后不建议修改

2-5-2. 查看
select * from nls_database_parameters;
select name,value$ from sys.props$ where name like '%NLS%';


3. 可能涉及到字符集的地方
客户端系统字符集
终端软件字符集 (Terminal)
NLS_LANG 设置
服务器端系统字符集 --- 一般可以忽略
Oracle 数据库字符集

3-1. Terminal 的编码方式对数据的影响
显示, 以什么编码方式来显示字符
插入, 插入时以以什么方式来编码字符

3-2. 数据插入 (更新) 过程

2-2-1. 数据库编码与 NLS_LANG 相同
不需要编码转换, 直接将输入字符在终端的编码二进制 (不是 NLS_LANG, 而是执行语句的终端, 如Terminal等), 更新到数据库中

2-2-2. 数据库编码与 NLS_LANG 不同
要将字符的终端编码转换为数据库编码, 此时如果数据库编码不是终端编码的严格超集, 转换时会出现乱码 (编码丢失)