oracle之字符集
全球化特性与字符集
数据库的全球化特性是数据库发展的必然结果，位于不同地区、不同国家、不用语言而使用同一数据库越来越普遍。Oracle数据库提供
了对全球化数据库的支持，消除不同文字、语言环境、历法货币等所带来的差异、使得更容易、更方便来使用数据库。
21.1 Oracle全球化特性内容
Language support
Territory support
Character set support
Linguistic sorting
Message support
Date and time formats
Numeric formats
Monetary formats
在Oracle全球化特性中最重要的则是字符集，即使用何种字符集将数据存储在数据库中
21.2 什么是字符集
主要是讨论两个问题，一是字符如何存储，二是字符如何显示。比如单个英文字符、单个阿拉伯数据字，#、$等，美国ANSI使用的标准字符集则使用了一个单字节(7位)来表示。由于世界各国和各个地区使用的符号的多样性，仅有2的7次方（128）个单字节的码点是不够用的，因此就有需要多字节来表示各自不同的字符。
正是由于上述原因产生了不同的字符集的概念, 如美国使用的为US7ASCII，西欧则使用的是WE8ISO8859P1，中国则是ZHS16GBK。
为了统一世界各国字符编码，统一编码字符集的概念应运而生，这就是Unicode。
在Oracle中，几种常用的Unicode为UTF-8，AL16UTF16，AL32UTF8
21.3 Oracle所支持的字符集及分类
Oracle支持两百多种字符集，包含了单字节、可变字节以及通用字符集等。
字符集通常根据使用的字节数来分类,主要分为以下几类
a.单字节字符集，如US7ASCII(7bit)，WE8ISO8859P1(8bit)，WE8DEC(8bit)
b.可变长多字节字符集，如JEUC，CGB2312-80
c.固定长多字节字符集，AL16UTF16
21.4 Oracle数据库支持的Unicode字符集
1）数据库字符集和国家字符集特性
Database Character Sets
主要是用作描述字符如何保存。
可存储列的类型为CHAR,VARCHAR2,CLOB,LONG
National Character Set：
主要是用于辅助Database Character Set。因为早期的数据库中很多使用了单字节字符集，但随着业务的发展，需要使用到诸如nchar,nvarchar等Unicode字符或者需要扩展到世界各地存储不同的字符，因此辅助字符集应运而生。
可存储的类型为NCHAR,NVARCHAR2,NCLOB
2） Oracle数据库支持的Unicode字符集
Character Set Unicode Encoding Database Character Set National Character Set
-----------------------------------------------------------------------------------------------
UTF8 UTF-8 Yes Yes(Oracle 9i and 10g only)
AL32UTF8 UTF-8 Yes No
AL16UTF16 UTF-16 No Yes
3) 字符集影响的数据类型
对于二进制数据类型，字符集的设置不影响该类型数据的存储，如视频、音频等
受影响的数据类型为：char,varchar2,nchar,nvarchar2,blob,clob,long,nclob
4)客户端设置字符集的三个级别,优先级依次递增。
Database Server< Instance< alter session command
21.5 相关NLS参数的设定
1.查看NLS参数，有三个视图：
a.查看数据库服务器中设定的NLS参数使用nls_database_parameters视图;
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
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 11.1.0.6.0
已选择20行。
b.查看本次会话中设定及使用的NLS参数，nls_session_parameters视图决定了session显示信息的形式：
SQL> select * from nls_session_parameters;
c.查看视图nls_instance_parameters，结果来自实例引导初始化参数文件有关，它和show parameter nls相应。
SQL> select * from nls_instance_parameters;
几个重要的参数：
1）语言参数，nls_language：
受影响的参数有：
NLS_DATE_LANGUAGE
NLS_SORT
2）区域参数，nls_territory：
受影响的参数有：
NLS_CURRENCY
NLS_DUAL_CURRENCY
NLS_ISO_CURRENCY
NLS_NUMERIC_CHARACTERS
NLS_DATE_FORMAT
NLS_TIMESTAMP_FORMAT
NLS_TIMESTAMP_TZ_FORMAT
通常，在nls_language设定后，应为nls_territory设定合理的值，假如语言设定为简体中文，地区设定为澳大利亚则不太合理
对于使用同样的语言不同国家或地区,比如英语,澳大利亚和英国,则nls_territory设定不同,则同样影响相关参数如currency等
3）排序参数: nls_sort：
Order by指定字段的排序方法，缺省的是Binary, 一般是支持单字节字符集 而多字节的字符集排序则引入Linguistic Sorting
基于Binary排序，根据encode的二进制代码排序。
基于语言排序，又分单一语言和多重语言
ALTER SESSION SET NLS_SORT=BINARY;
SELECT num,word,def FROM list ORDER BY NLSSORT(word,'NLS_SORT=FRENCH_M');
4）NLS_LANG变量的设定（客户端环境变量）
NLS_LANG变量为一个总控参数，控制了前面描述的nls_language和nls_territory的行为
该参数可以用于设定服务器和客户端的language和territory值，也可设置客户端输入数据和显示的字符集
只要设定了该参数，则其它参数就确定了。当然也可以只设定其中的一部分，另外，特别注意NLS_LANG只能在环境变量中设定。
该参数的格式为：NLS_LANG = language_territory.charset 如：NLS_LANG=French_France.UTF8
在我们的虚机环境下，环境变量文件/home/oracle/.bash_profile中描述了作为客户端的NLS_LANG
NLS_LANG="simplified chinese"_china.zhs16gbk，该参数分为几个部分来设定
NLS_LANG=AMERICAN_AMERICA.zhs16gbk 英文字符集
第一部分为language，为simplified chinese。
第二部分为territory，为china。一二两部分必须用下划线连接。
第三部分为character set，为zhs16gbk 二三两部分必须用小数点连接。
其含义是语言是简体中文，区域是中国，数据库字符集是zhs16gbk。
另外日期格式缺省的是DD-MON-RR，我们单独定义了适合中国人使用的格式'YYYY-MM-DD HH24:MI:SS'
21.6 改变字符集
9i之前无法改变字符集，9i后Oracle提供了扫描字符集的工具，但无法保证其有效。
对于Database Character Set 在Unix平台上Oracle提供的实用工具是：
数据库字符扫描工具csscan
语言与文字扫描工具lcsscan
如：csscan system/systempassword full=y tocher=utf8
1）转换数据库字符集，前提是csscan成功
使用alter database character set命令。
2）转换国家字符集，前提是转换后的字符集必须是转换前的字符集的超集。
使用alter database national character Set命令。
第二十三章 正则表达式
21.1 ORACLE中的支持正则表达式的函数主要有四个：
1，REGEXP_LIKE ：与LIKE的功能相似
2，REGEXP_INSTR ：与INSTR的功能相似
3，REGEXP_SUBSTR ：与SUBSTR的功能相似
4，REGEXP_REPLACE ：与REPLACE的功能相似
它们在用法上与Oracle SQL 函数LIKE、INSTR、SUBSTR 和REPLACE 用法相同，
但是它们使用POSIX 正则表达式代替了老的百分号（%）和通配符（_）字符。
21.2 POSIX 正则表达式由标准的元字符（metacharacters）所构成：
'^' 匹配输入字符串的开始位置，在方括号表达式中使用，此时它表示不接受该字符集合。
'$' 匹配输入字符串的结尾位置。如果设置了 RegExp 对象的 Multiline 属性，则 $ 也匹配 '\n' 或 '\r'。
'.' 匹配除换行符之外的任何单字符。
'?' 匹配前面的子表达式零次或一次。
'+' 匹配前面的子表达式一次或多次。
'*' 匹配前面的子表达式零次或多次。
'|' 指明两项之间的一个选择。例子'^([a-z]+|[0-9]+)$'表示所有小写字母或数字组合成的字符串。
'( )' 标记一个子表达式的开始和结束位置。
'[]' 标记一个中括号表达式。
'{m,n}' 一个精确地出现次数范围，m=<出现次数<=n，'{m}'表示出现m次，'{m,}'表示至少出现m次。
\num 匹配 num，其中 num 是一个正整数。对所获取的匹配的引用。
21.3 字符簇：
[[:alpha:]] 任何字母。
[[:digit:]] 任何数字。
[[:alnum:]] 任何字母和数字。
[[:space:]] 任何白字符。
[[:upper:]] 任何大写字母。
[[:lower:]] 任何小写字母。
[[:punct:]] 任何标点符号。
[[:xdigit:]] 任何16进制的数字，相当于[0-9a-fA-F]。
各种操作符的运算优先级
\转义符
(), (?:), (?=), [] 圆括号和方括号
*, +, ?, {n}, {n,}, {n,m} 限定符
^, $, anymetacharacter 位置和顺序
|
*/
21.4 Oracle REGEXP_LIKE介绍和例子
--创建表
create table fzq (id varchar2(4),value varchar2(10));
--数据插入
insert into fzq values ('1','1234560');
insert into fzq values ('2','1234560');
insert into fzq values ('3','1b3b560');
insert into fzq values ('4','abc');
insert into fzq values ('5','abcde');
insert into fzq values ('6','ADREasx');
insert into fzq values ('7','123 45');
insert into fzq values ('8','adc de');
insert into fzq values ('9','adc,.de');
insert into fzq values ('10','1B');
insert into fzq values ('10','abcbvbnb');
insert into fzq values ('11','11114560');
insert into fzq values ('11','11124560');
--regexp_like
--查询value中以1开头60结束的记录并且长度是7位
select * from fzq where value like '1____60';
select * from fzq where regexp_like(value,'1....60');
--查询value中以1开头60结束的记录并且长度是7位并且全部是数字的记录。
--使用like就不是很好实现了。
select * from fzq where regexp_like(value,'1[0-9]{4}60');
-- 也可以这样实现，使用字符集。
select * from fzq where regexp_like(value,'1[[:digit:]]{4}60');
-- 查询value中不是纯数字的记录
select * from fzq where not regexp_like(value,'^[[:digit:]]+$');
-- 查询value中不包含任何数字的记录。
select * from fzq where regexp_like(value,'^[^[:digit:]]+$');
--查询以12或者1b开头的记录.不区分大小写。
select * from fzq where regexp_like(value,'^1[2b]','i');
--查询以12或者1b开头的记录.区分大小写。
select * from fzq where regexp_like(value,'^1[2B]');
-- 查询数据中包含空白的记录。
select * from fzq where regexp_like(value,'[[:space:]]');
--查询所有包含小写字母或者数字的记录。
select * from fzq where regexp_like(value,'^([a-z]+|[0-9]+)$');
--查询任何包含标点符号的记录。
select * from fzq where regexp_like(value,'[[:punct:]]');
21.5 REGEXP_REPLACE(字符串替换函数)
REPLACE 函数是用另外一个值来替代串中的某个值。例如，可以用一个匹配数字来替代字母的每一次出现。REPLACE 的格式如下所示：
原型：regexp_replace(x,pattern[,replace_string[,start[,occurence[match_option]]]])
每个参数的意思分别是：
x 待匹配的函数
pattern 正则表达式元字符构成的匹配模式
replace_string 替换字符串
start 开始位置
occurence 匹配次数
match_option 匹配参数，这里的匹配参数和regexp_like是完全一样的，可参考前面的一篇文章。
举例来讲：
SQL> select regexp_replace('hello everybody,051courses will be over soon,thanks.','b[[:alpha:]]{3}','one') from dual;
REGEXP_REPLACE('HELLOEVERYBODY,047COURSESWILLBEOVER
---------------------------------------------------
hello everyone,051courses will be over soon,thanks.
