Oracle中的字符型及.ppt

第第6 6章章 Oracle Oracle中的字符型及处理方法中的字符型及处理方法与其他编程语言一样，与其他编程语言一样，Oracle中同样存在着数据类型。中同样存在着数据类型。Oracle中的数据类型主要有两个应用场景：一是用于指定数中的数据类型主要有两个应用场景：一是用于指定数据表中列的类型；二是用于据表中列的类型；二是用于PL/SQL编程中声明变量。编程中声明变量。Oracle的数据类型主要包括：字符型（的数据类型主要包括：字符型（character）、数值型）、数值型（number）、日期型（）、日期型（date）和大对象型（）和大对象型（LOB）。同时，）。同时，Oracle提供了针对数据类型的内置函数。本章着重讲述提供了针对数据类型的内置函数。本章着重讲述Oracle中的字符型及其相关的函数。本章的主要内容包括：中的字符型及其相关的函数。本章的主要内容包括：字符型简介字符型简介字符型函数字符型函数6.1 6.1 字符型简介字符型简介Oracle中的字符型有三种，分别是中的字符型有三种，分别是char(n)、varchar(n)、varchar2(n)。本节将分别针对这三种类型进行介绍。本节将分别针对这三种类型进行介绍。6.1.1 6.1.1 固定长度字符串固定长度字符串char(n)char(n)char(n)指定变量或列的数据类型为固定长度的字符串。指定变量或列的数据类型为固定长度的字符串。其中，其中，n代表字符串的长度。当实际字符串的长度不足代表字符串的长度。当实际字符串的长度不足n时，时，Oracle利用空格在右端补齐。当然，利用空格在右端补齐。当然，Oracle不允许实际字符不允许实际字符串的长度大于串的长度大于n。数据库中列指定为数据库中列指定为char(n)类型时，类型时，n的最大值不能大于的最大值不能大于2000。否则，。否则，Oracle将抛出错误，如示例将抛出错误，如示例6-1所示。所示。【示例【示例6-1】当】当char(n)作为列的数据类型时，测试其最作为列的数据类型时，测试其最大长度。大长度。SQL create table test_char(f_char char(2001);create table test_char(f_char char(2001)ORA-00910:specified length too long for its datatype6.1.2 varchar(n)6.1.2 varchar(n)Oracle中提供了中提供了varchar(n)的数据类型。该类型是的数据类型。该类型是Oracle迎合工业标准中的迎合工业标准中的varchar而制定的。该数而制定的。该数据类型实际是一个可变长度字符串类型。也就是说，当实据类型实际是一个可变长度字符串类型。也就是说，当实际字符串的长度不足时，不会使用空格进行填充。同样，实际际字符串的长度不足时，不会使用空格进行填充。同样，实际字符串的长度也不允许超出字符串的长度也不允许超出n。【示例【示例6-2】当作为列的数据类型出现时，】当作为列的数据类型出现时，varchar的最大的最大长度不能大于长度不能大于4000，如下所示。，如下所示。SQL alter table test_char add f_varchar varchar(4001);alter table test_char add f_varchar varchar(4001)ORA-00910:specified length too long for its datatype SQL alter table test_char add f_varchar varchar(4000);Table altered6.1.3 varchar2(n)6.1.3 varchar2(n)与与varchar(n)类型，类型，varchar2(n)同样是可变长度的字符串同样是可变长度的字符串类型。类型。Oracle在工业标准之外，自定义了该数据类型。同时在工业标准之外，自定义了该数据类型。同时Oracle也提醒用户，尽量使用也提醒用户，尽量使用varchar2(n)，而非，而非varchar(n)。因为使用因为使用varchar2(n)可以获得可以获得Oracle向后兼容性的保证。向后兼容性的保证。【示例【示例6-3】当作为列的数据类型出现时，】当作为列的数据类型出现时，varchar2的长的长度同样不能大于度同样不能大于4000，如下所示。，如下所示。SQL alter table test_char add f_varchar2 varchar2(4001);alter table test_char add f_varchar2 varchar2(4001)ORA-00910:specified length too long for its datatypeSQL alter table test_char add f_varchar2 varchar2(4000);Table altered6.2 6.2 字符型分析字符型分析对于一般用途的数据表来说，最常用的字符串类型为对于一般用途的数据表来说，最常用的字符串类型为varchar2(n)。本节将着重分析。本节将着重分析char(n)与与varchar2(n)的区别。的区别。6.2.1 varchar2(n)6.2.1 varchar2(n)与与char(n)char(n)的区别的区别varchar2(n)为可变字符串类型，而为可变字符串类型，而char(n)为固定字符串为固定字符串类型。这二者的区别在于是否使用空格来补齐不足的部分。类型。这二者的区别在于是否使用空格来补齐不足的部分。【示例【示例6-4】在表】在表test_char中，列中，列f_char和和f_varchar2的的长度分别为长度分别为2000和和4000。先向其中插入新的记录，并为两列。先向其中插入新的记录，并为两列赋予相同的值。赋予相同的值。SQL insert into test_char(f_char,f_varchar2)2 values(000,000);1 row inserted6.2.2 varchar2(n)6.2.2 varchar2(n)与与char(n)char(n)的选择的选择通过示例通过示例6-5可以看出，可以看出，char(n)类型的列通常占用较大的类型的列通常占用较大的存储空间；而存储空间；而varchar2(n)类型的列占用的空间较小。所以，类型的列占用的空间较小。所以，varchar2(n)类型是我们在进行数据库设计时的一般选择。但类型是我们在进行数据库设计时的一般选择。但这并不意味着这并不意味着char(n)类型应该被摒弃。相反，类型应该被摒弃。相反，char(n)在效率在效率方面要高于方面要高于varchar2(n)。这是因为可变长度的字符串类型在。这是因为可变长度的字符串类型在实际数据长度发生改变时，总需要不断调整存储空间。尤其是实际数据长度发生改变时，总需要不断调整存储空间。尤其是频繁修改数据，而数据长度也不断改变的情况下，这种效率的频繁修改数据，而数据长度也不断改变的情况下，这种效率的损耗尤其明显。损耗尤其明显。大多数的应用程序，并不将数据库端的效率作为首要考大多数的应用程序，并不将数据库端的效率作为首要考虑的需求。而更倾向于较小的空间代价，因此大多使用虑的需求。而更倾向于较小的空间代价，因此大多使用varchar2(n)来定义列。而来定义列。而char(n)则是典型的则是典型的“以空间换时间以空间换时间”，读者可以在实际开发中酌情选择。，读者可以在实际开发中酌情选择。6.2.3 6.2.3 变量声明中的字符串类型变量声明中的字符串类型三种字符串类型三种字符串类型char(n)、varchar(n)、varchar2(n)，都可用于声明变量。但是，利用三者声明时，最大长度均，都可用于声明变量。但是，利用三者声明时，最大长度均为为32767。【示例【示例6-5】测试】测试char(n)、varchar(n)及及varchar2(n)用于用于变量声明时的最大长度。变量声明时的最大长度。SQL declare s char(32768);2 begin 3 null;4 end;5 /6.3 6.3 字符型处理字符型处理Oracle提供了丰富的字符串函数来处理字符型数据。提供了丰富的字符串函数来处理字符型数据。本节将着重介绍这些函数。本节将着重介绍这些函数。向左补全字符串向左补全字符串lpad()lpad()函数函数lpad()函数用于向左补全字符串。该函数主要用于字符串函数用于向左补全字符串。该函数主要用于字符串的格式化。格式化的方式为，将字符串格式化为指定长度，如的格式化。格式化的方式为，将字符串格式化为指定长度，如有不足部分，则在字符串的左端填充特定字符。其调用方式如有不足部分，则在字符串的左端填充特定字符。其调用方式如下所示。下所示。lpad(string,padded_length,pad_string)其中，第一个参数指定原始字符串；第二个参数指定格其中，第一个参数指定原始字符串；第二个参数指定格式化之后的字符串长度；第三个参数指定使用哪个字符来填充式化之后的字符串长度；第三个参数指定使用哪个字符来填充不足位数。不足位数。6.3.2 6.3.2 向右补全字符串向右补全字符串rpad()rpad()函数函数与与lpad()函数相似，函数相似，rpad()函数返回字符串格式化为特函数返回字符串格式化为特定位数的操作。只是该函数自右端补全不足位数。定位数的操作。只是该函数自右端补全不足位数。【示例【示例6-7】利用】利用rpad()函数实现右端补全字符串。函数实现右端补全字符串。SQL select rpad(1,4,*)empplyee_no from dual;EMPPLYEE_NO-1*6.3.3 6.3.3 返回字符串的小写形式返回字符串的小写形式lower()lower()函函数数lower()函数用于返回字符串的小写形式。该函数仅有函数用于返回字符串的小写形式。该函数仅有一个参数，即原始字符串。一个参数，即原始字符串。【示例【示例6-8】在数据库中，当查询条件可以忽略大小写】在数据库中，当查询条件可以忽略大小写形式时，我们通常利用形式时，我们通常利用lower()函数进行字符串大小写形式函数进行字符串大小写形式的统一。的统一。SQL select username,password 2 from dba_users 3 where lower(username)=system;6.3.4 6.3.4 返回字符串的大写形式返回字符串的大写形式upper()upper()函函数数与与lower()函数相反，函数相反，upper()函数用于返回字符串的大函数用于返回字符串的大写形式。该函数仅有一个参数，即原始字符串。写形式。该函数仅有一个参数，即原始字符串。【示例【示例6-9】我们同样可以】我们同样可以upper()函数来改写示例函数来改写示例6-8的查询语句。的查询语句。SQL select username,password 2 from dba_users 3 where upper(username)=SYSTEM;6.3.5 6.3.5 单词首字符大写单词首字符大写initcap()initcap()函数函数initcap()函数用于将单词转换为首字符大写、其它字函数用于将单词转换为首字符大写、其它字符小写的形式。符小写的形式。【示例【示例6-10】利用】利用initcap()函数格式化单词。函数格式化单词。SQL select initcap(like)new_word from dual;NEW_WORD-Like SQL select initcap(LIKE)new_word from dual;NEW_WORD-Like6.3.6 6.3.6 返回字符串长度返回字符串长度length()length()函数函数length()函数可用于返回字符串长度。函数可用于返回字符串长度。【示例【示例6-11】利用】利用length()函数返回字符长度。函数返回字符长度。SQL select length(12345)len from dual;LEN-56.3.7 6.3.7 截取字符串截取字符串substr()substr()函数函数substr()函数用于截取字符串。该函数的一般调用形式函数用于截取字符串。该函数的一般调用形式如下所示。如下所示。substr(string,start_index,length)其中，第一个参数其中，第一个参数string指定原始字符串；第二个参指定原始字符串；第二个参数数start_index指定开始截取的位置；第三个参数指定截取指定开始截取的位置；第三个参数指定截取的长度。需要注意的是，的长度。需要注意的是，Oracle中字符串中第一个字符的中字符串中第一个字符的位置为位置为1。这与很多编程语言，如。这与很多编程语言，如Java中自中自0开始的习惯不开始的习惯不同。同。6.3.8 6.3.8 获得字符串出现的位置获得字符串出现的位置instr()instr()函函数数instr()函数用于返回子字符串在父字符串中出现的位置。函数用于返回子字符串在父字符串中出现的位置。如果子字符串未出现在父字符串中，该函数将返回如果子字符串未出现在父字符串中，该函数将返回0。其调用。其调用形式如下所示。形式如下所示。instr(string,sub_string,start_index,times)其中，其中，string为父字符串；为父字符串；sub_string为子字符串；为子字符串；start_index为可选参数，指定进行搜寻的起始位置；为可选参数，指定进行搜寻的起始位置；times为为可选参数，表示第几次获得子字符串。可选参数，表示第几次获得子字符串。【示例【示例6-13】利用】利用instr()函数获得子字符串的出现位置。函数获得子字符串的出现位置。SQL select instr(123456789,56)position from dual;POSITION-56.3.9 6.3.9 删除字符串左侧空格删除字符串左侧空格ltrim()ltrim()函数函数ltrim()函数用于删除字符串的左侧空格。函数用于删除字符串的左侧空格。【示例【示例6-14】利用】利用ltrim()函数删除字符串的左侧空格。函数删除字符串的左侧空格。SQL select ltrim(000)new_str from dual;NEW_STR-000ltrim(000)用于删除字符串用于删除字符串“000”左侧的所有空格，左侧的所有空格，结果返回结果返回“000”。6.3.10 6.3.10 删除字符串右侧空格删除字符串右侧空格rtrim()rtrim()函函数数rtrim()函数用于删除字符串的右侧空格。函数用于删除字符串的右侧空格。【示例【示例6-15】利用】利用rtrim()函数删除字符串右侧空格。函数删除字符串右侧空格。SQL select rtrim(000 )new_str from dual;NEW_STR-000select rtrim(000 )用于删除字符串用于删除字符串“000 ”的右的右侧空格。分析查询结果可知，虽然原字符串左右两侧均含侧空格。分析查询结果可知，虽然原字符串左右两侧均含有空格，但是有空格，但是rtrim()函数仅删除了右侧空格。函数仅删除了右侧空格。6.3.11 6.3.11 删除字符串两侧空格删除字符串两侧空格trim()trim()函数函数trim()函数可以返回字符串两侧的空格。它实际是函数可以返回字符串两侧的空格。它实际是ltrim()函数与函数与rtrim()的综合操作结果。的综合操作结果。【示例【示例6-16】利用】利用trim()函数删除字符串两侧空格。函数删除字符串两侧空格。SQL select trim(000 )new_str from dual;NEW_STR-000trim(000 )用于删除字符串用于删除字符串“000 ”左右两侧的左右两侧的空格，结果返回字符串空格，结果返回字符串“000”。6.3.12 6.3.12 串联字符串串联字符串concat()concat()函数函数concat()函数可以将两个字符串进行连接。函数可以将两个字符串进行连接。【示例【示例6-17】利用】利用concat()函数连接字符串。函数连接字符串。SQL select concat(Hello,World)new_str from dual;NEW_STR-Hello World6.3.13 6.3.13 翻译字符串翻译字符串translate()translate()函数函数translate()函数的执行过程非常类似于翻译的过程。函数的执行过程非常类似于翻译的过程。【示例【示例6-18】利用】利用translate()函数翻译字符串。函数翻译字符串。SQL select translate(+-*,1+23-4*,abcdefghijklmnopqistuvwxqz)trans from dual;TRANS-beg6.4 6.4 本章实例本章实例对于字符型函数来说，如果传入的参数是一个空值，对于字符型函数来说，如果传入的参数是一个空值，那么函数的返回值往往也是空值。对于那么函数的返回值往往也是空值。对于Oracle函数来说，函数来说，这是一个非常重要的特点，因此要格外注意。这是一个非常重要的特点，因此要格外注意。【示例【示例6-19】查看参数为空值时，函数的返回值。】查看参数为空值时，函数的返回值。SQL select length()from dual;LENGTH()-6.5 6.5 本章小结本章小结本章着重讲述了本章着重讲述了Oracle中常用的字符型及与之相关的内中常用的字符型及与之相关的内置函数。对于字符型函数，需要注意以下两点：一，字符型置函数。对于字符型函数，需要注意以下两点：一，字符型函数将函数将null作为参数时，往往返回值为作为参数时，往往返回值为null，而无关理论上，而无关理论上的返回值是什么；二，即使字符型函数所处理的是一个变量，的返回值是什么；二，即使字符型函数所处理的是一个变量，在内部处理时，都是针对参数的一个副本，而不会真正改变在内部处理时，都是针对参数的一个副本，而不会真正改变变量的值。变量的值。