本文共 4493 字,大约阅读时间需要 14 分钟。
与其他编程语言一样,Oracle中同样存在着数据类型。Oracle中的数据类型主要有两个应用场景:一是用于指定数据表中列的类型;二是用于PL/SQL编程中声明变量。Oracle的数据类型主要包括:字符型(character)、数值型(number)、日期型(date)和大对象型(LOB)。同时,Oracle提供了针对数据类型的内置函数。本章着重讲述Oracle中的字符型及其相关的函数。
1字符型简介
Oracle中的字符型有三种,分别是char(n)、varchar(n)、varchar2(n)。
1.1固定长度字符串——char(n)
char(n)指定变量或列的数据类型为固定长度的字符串。其中,n代表字符串的长度。当实际字符串的长度不足n时,Oracle利用空格在右端补齐。当然,Oracle不允许实际字符串的长度大于n。
数据库中列指定为char(n)类型时,n的最大值不能大于2000。否则,Oracle将抛出错误
当char(n)作为列的数据类型时,测试其最大长度。
SQL>createtabletest_char(f_charchar(2001));
1.2varchar(n)
Oracle中提供了varchar(n)的数据类型。该类型是Oracle迎合工业标准中的varchar而制定的。该数
据类型实际是一个可变长度字符串类型。也就是说,当实际字符串的长度不足时,不会使用空格进行填充。同样,实际字符串的长度也不允许超出n。
当作为列的数据类型出现时,varchar的最大长度不能大于4000,如下所示。
SQL>altertabletest_charaddf_varcharvarchar(4001);
1.3varchar2(n)
与varchar(n)类型,varchar2(n)同样是可变长度的字符串类型。Oracle在工业标准之外,自定义了该数据类型。同时Oracle也提醒用户,尽量使用varchar2(n),而非varchar(n)。因为使用varchar2(n)可以获得Oracle向后兼容性的保证。
当作为列的数据类型出现时,varchar2的长度同样不能大于4000,如下所示。
SQL>altertabletest_charaddf_varchar2varchar2(4001);
2字符型分析
对于一般用途的数据表来说,最常用的字符串类型为varchar2(n)。
2.1varchar2(n)与char(n)的区别
varchar2(n)为可变字符串类型,而char(n)为固定字符串类型。这二者的区别在于是否使用空格来补齐不足的部分。
在表test_char中,列f_char和f_varchar2的长度分别为2000和4000。先向其中插入新的记录,并为两列赋予相同的值。
SQL>insertintotest_char(f_char,f_varchar2)
2values('000','000');
2.2varchar2(n)与char(n)的选择
char(n)类型的列通常占用较大的存储空间;而varchar2(n)类型的列占用的空间较小。所以,varchar2(n)类型是我们在进行数据库设计时的一般选择。但这并不意味着char(n)类型应该被摒弃。相反,char(n)在效率方面要高于varchar2(n)。这是因为可变长度的字符串类型在实际数据长度发生改变时,总需要不断调整存储空间。尤其是频繁修改数据,而数据长度也不断改变的情况下,这种效率的损耗尤其明显。
大多数的应用程序,并不将数据库端的效率作为首要考虑的需求。而更倾向于较小的空间代价,因此大多使用varchar2(n)来定义列。而char(n)则是典型的“以空间换时间”,读者可以在实际开发中酌情选择。
2.3变量声明中的字符串类型
三种字符串类型——char(n)、varchar(n)、varchar2(n),都可用于声明变量。但是,利用三者声明时,最大长度均为32767。
测试char(n)、varchar(n)及varchar2(n)用于变量声明时的最大长度。
SQL>declareschar(32768);
3字符型处理
3.1向左补全字符串——lpad()函数
lpad()函数用于向左补全字符串。该函数主要用于字符串的格式化。格式化的方式为,将字符串格式化为指定长度,如有不足部分,则在字符串的左端填充特定字符。其调用方式如下所示。
lpad(string,padded_length,[pad_string])
其中,第一个参数指定原始字符串;第二个参数指定格式化之后的字符串长度;第三个参数指定使用哪个字符来填充不足位数。
3.2向右补全字符串——rpad()函数
与lpad()函数相似,rpad()函数返回字符串格式化为特定位数的操作。只是该函数自右端补全不足位数。
利用rpad()函数实现右端补全字符串。
SQL>selectrpad('1',4,'*')empplyee_nofromdual;
3.3返回字符串的小写形式——lower()函数
lower()函数用于返回字符串的小写形式。该函数仅有一个参数,即原始字符串。
在数据库中,当查询条件可以忽略大小写形式时,我们通常利用lower()函数进行字符串大小写形式的统一。
SQL>selectusername,password
fromdba_users
wherelower(username)='system';
3.4返回字符串的大写形式——upper()函数
lower()函数相反,upper()函数用于返回字符串的大写形式。该函数仅有一个参数,即原始字符串。
我们同样可以upper()函数来改写的查询语句。
SQL>selectusername,password
fromdba_users
whereupper(username)='SYSTEM';
3.5单词首字符大写——initcap()函数
initcap()函数用于将单词转换为首字符大写、其它字符小写的形式。
利用initcap()函数格式化单词。
SQL>selectinitcap('like')new_wordfromdual;
SQL>selectinitcap('LIKE')new_wordfromdual;
3.6返回字符串长度——length()函数
length()函数可用于返回字符串长度。
利用length()函数返回字符长度。
SQL>selectlength('12345')lenfromdual;
3.7截取字符串——substr()函数
ubstr()函数用于截取字符串。该函数的一般调用形式如下所示。
substr(string,start_index,length)
其中,第一个参数string指定原始字符串;第二个参数start_index指定开始截取的位置;第三个参数指定截取的长度。需要注意的是,Oracle中字符串中第一个字符的位置为1。这与很多编程语言,如Java中自0开始的习惯不同。
3.8获得字符串出现的位置——instr()函数
instr()函数用于返回子字符串在父字符串中出现的位置。如果子字符串未出现在父字符串中,该函数将返回0。其调用形式如下所示。
instr(string,sub_string[,start_index][,times])
其中,string为父字符串;sub_string为子字符串;start_index为可选参数,指定进行搜寻的起始位置;times为可选参数,表示第几次获得子字符串。
利用instr()函数获得子字符串的出现位置。
SQL>selectinstr('123456789','56')positionfromdual;
3.9删除字符串左侧空格——ltrim()函数
ltrim()函数用于删除字符串的左侧空格。
利用ltrim()函数删除字符串的左侧空格。
SQL>selectltrim('000')new_strfromdual;
3.10删除字符串右侧空格——rtrim()函数
rtrim()函数用于删除字符串的右侧空格。
利用rtrim()函数删除字符串右侧空格。
SQL>selectrtrim('000')new_strfromdual;
selectrtrim('000')用于删除字符串“000”的右侧空格。分析查询结果可知,虽然原字符串左右两侧均含有空格,但是rtrim()函数仅删除了右侧空格。
3.11删除字符串两侧空格——trim()函数
trim()函数可以返回字符串两侧的空格。它实际是ltrim()函数与rtrim()的综合操作结果。
利用trim()函数删除字符串两侧空格。
SQL>selecttrim('000')new_strfromdual;
3.12串联字符串——concat()函数
concat()函数可以将两个字符串进行连接。
利用concat()函数连接字符串。
SQL>selectconcat('Hello','World')new_strfromdual;
3.13翻译字符串——translate()函数
translate()函数的执行过程非常类似于翻译的过程。
translate(string,from_str,to_str)
执行时,translate依次检查string中的每个字符 然后查找这个字符是否在from_str中存在 如果不存在,那么这个string中的字符被保留,也就是被返回, 如果存在,那么,translate会记下这个字符在from_str中的位置, 然后用to_str的同样位置的字符代替string中的这个字符 from_str可以比to_str长,也就是from_str的字符数目可以比to_str多 在from_str中多出来的字符称为"额外字符",也就是from_str中的位置在 to_str中找不到的,比如 from_str'abc123' to_str'abc' 这里from_str中的1的位置是4,但是to_str的总长度是3,没有4, 所以在from_STR中的123都是额外字符 如果from_str中的额外字符在string中出现,那么string中这些字符将在返回时被删除 举例如下 SQL>selecttranslate('ab12','ab2','00')fromdual; TRA --- 001 简单一点,也就是 1、如果string中的字符如果在from_string中没有,那么返回时被保留 2、如果string中的字符是from_string中的"额外字符",那么返回时被删除 3、如果string中的字符在from_string中找到,且在to_string中有相应位置的字符, 那么返回时用to_string中的字符替换string中的字符转载地址:http://cpoyc.baihongyu.com/