向表中插入中文字元，查詢時亂碼問題

當建立了某張表，向表中插入中文字元後，在查詢時插入的中文出現亂碼
[oracle@test1 ~]$ sqlplus scott/tiger
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jun 27 09:55:58 2011
Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create table t1(name varchar2(10));
Table created.
SQL> insert into t1 values('啊');
1 row created.
SQL> select * from t1;
NAME
----------
???
原因：系統與資料庫字符集不匹配
解決方法：
將系統的字符集調整為和資料庫一致
查詢資料庫字符集
SQL> show parameter nls_lang

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_language                         string      AMERICAN
SQL> col value format a40
SQL> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               ZHS16GBK
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              10.2.0.1.0

20 rows selected.
SQL>
或者
SQL> col value$ format a40
SQL> select name,value$ from props$;

NAME                           VALUE$
------------------------------ ----------------------------------------
DICT.BASE                      2
DEFAULT_TEMP_TABLESPACE        TEMP
DEFAULT_PERMANENT_TABLESPACE   USERS
DEFAULT_TBS_TYPE               SMALLFILE
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               ZHS16GBK
NLS_CALENDAR                   GREGORIAN

NAME                           VALUE$
------------------------------ ----------------------------------------
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE

NAME                           VALUE$
------------------------------ ----------------------------------------
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              10.2.0.1.0
GLOBAL_DB_NAME                 ORCL.REGRESS.RDBMS.DEV.US.ORACLE.COM
EXPORT_VIEWS_VERSION           8
DBTIMEZONE                     00:00

27 rows selected.

影響oracle資料庫字符集最重要的引數是NLS_LANG引數，它的格式如下：
NLS_LANG=language_territory.characterset
它有三個組成部分：語言、地域和字符集
language指定伺服器訊息語言，territory指定伺服器的日期和數字格式，characterset指定字符集

經上所查，資料庫的NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
將其新增到系統
$export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
或者
$ echo 'NLS_LANG=AMERICAN_AMERICA.ZHS16GBK' >> ~/.bash_profile
$ source ~/.bash_profile
然後再想表中插入資料就能正常顯示了