Oracle中的常用数值运算、数据类型转换和字符串处理函数指南
最编程
2024-07-27 07:26:32
...
本文更多将会介绍三思在日常中经常会用到的,或者虽然很少用到,但是感觉挺有意思的一些函数。分二类介绍,分别是:
著名函数篇 -经常用到的函数
非著名函数篇-即虽然很少用到,但某些情况下却很实用
注:N表示数字型,C表示字符型,D表示日期型,[]表示内中参数可被忽略,fmt表示格式。
单值函数在查询中返回单个值,可被应用到select,where子句,start with以及connect by 子句和having子句。
(一).数值型函数(Number Functions)
数值型函数输入数字型参数并返回数值型的值。多数该类函数的返回值支持38位小数点,诸如:COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH 支持36位小数点。ACOS, ASIN, ATAN, and ATAN2支持30位小数点。
1、MOD(n1,n2) 返回n1除n2的余数,如果n2=0则返回n1的值。
例如:SELECT MOD(24,5) FROM DUAL;
2、ROUND(n1[,n2]) 返回四舍五入小数点右边n2位后n1的值,n2缺省值为0,如果n2为负数就舍入到小数点左边相应的位上(虽然oracle documents上提到n2的值必须为整数,事实上执行时此处的判断并不严谨,即使n2为非整数,它也会自动将n2取整后做处理,但是我文档中其它提到必须为整的地方需要特别注意,如果不为整执行时会报错的)。
例如:SELECT ROUND(23.56),ROUND(23.56,1),ROUND(23.56,-1) FROM DUAL;
3、TRUNC(n1[,n2] 返回截尾到n2位小数的n1的值,n2缺省设置为0,当n2为缺省设置时会将n1截尾为整数,如果n2为负值,就截尾在小数点左边相应的位上。
例如:SELECT TRUNC(23.56),TRUNC(23.56,1),TRUNC(23.56,-1) FROM DUAL;
(二).字符型函数返回字符值(Character Functions Returning Character Values)
该类函数返回与输入类型相同的类型。
返回的CHAR类型值长度不超过2000字节;
返回的VCHAR2类型值长度不超过4000字节;
如果上述应返回的字符长度超出,oracle并不会报错而是直接截断至最大可支持长度返回。
返回的CLOB类型值长度不超过4G;
对于CLOB类型的函数,如果返回值长度超出,oracle不会返回任何错误而是直接抛出错误。
1、LOWER(c) 将指定字符串内字符变为小写,支持CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB类型
例如:SELECT LOWER('WhaT is tHis') FROM DUAL;
2、UPPER(c) 将指定字符串内字符变为大写,支持CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB类型
例如:SELECT UPPER('WhaT is tHis') FROM DUAL;
3、LPAD(c1,n[,c2]) 返回指定长度=n的字符串,需要注意的有几点:
如果n<c1.length则从右到左截取指定长度返回;
如果n>c1.length and c2 is null,以空格从左向右补充字符长度至n并返回;
如果n>c1.length and c2 is not null,以指定字符c2从左向右补充c1长度至n并返回;
例如:SELECT LPAD('WhaT is tHis',5),LPAD('WhaT is tHis',25),LPAD('WhaT is tHis',25,'-') FROM DUAL;
最后大家再猜一猜,如果n<0,结果会怎么样
4、RPAD(c1,n[,c2]) 返回指定长度=n的字符串,基本与上同,不过补充字符是从右向左方向正好与上相反;
例如:SELECT RPAD('WhaT is tHis',5),RPAD('WhaT is tHis',25),RPAD('WhaT is tHis',25,'-') FROM DUAL;
5、TRIM([[LEADING||TRAILING||BOTH] c2 FROM] c1) 哈哈,被俺无敌的形容方式搞晕头了吧,这个地方还是看图更明了一些。
看起来很复杂,理解起来很简单:
如果没有指定任何参数则oracle去除c1头尾空格
例如:SELECT TRIM(' WhaT is tHis ') FROM DUAL;
如果指定了c2参数,则oracle去掉c1头尾c2(这个建议细致测试,有多种不同情形的哟)
例如:SELECT TRIM('W' FROM 'WhaT is tHis w W') FROM DUAL;
如果指定了leading参数则会去掉c1头部c2
例如:SELECT TRIM(leading 'W' FROM 'WhaT is tHis w W') FROM DUAL;
如果指定了trailing参数则会去掉c1尾部c2
例如:SELECT TRIM(trailing 'W' FROM 'WhaT is tHis w W') FROM DUAL;
如果指定了both参数则会去掉c1头尾c2(跟不指定有区别吗?没区别!)
例如:SELECT TRIM(both 'W' FROM 'WhaT is tHis w W') FROM DUAL;
注意:c2长度=1
6、LTRIM(c1[,c2]) 千万表以为与上面那个长的像,功能也与上面的类似,本函数是从字符串c1左侧截取掉与指定字符串c2相同的字符并返回。如果c2为空则默认截取空格。
例如:SELECT LTRIM('WWhhhhhaT is tHis w W','Wh') FROM DUAL;
7、RTRIM(c1,c2)与上同,不过方向相反
例如:SELECT RTRIM('WWhhhhhaT is tHis w W','W w') FROM DUAL;
8、REPLACE(c1,c2[,c3]) 将c1字符串中的c2替换为c3,如果c3为空,则从c1中删除所有c2。
例如:SELECT REPLACE('WWhhhhhaT is tHis w W','W','-') FROM DUAL;
9、SOUNDEX(c) 神奇的函数啊,该函数返回字符串参数的语音表示形式,对于比较一些读音相同,但是拼写不同的单词非常有用。计算语音的算法如下:
保留字符串首字母,但删除a、e、h、i、o、w、y。
将下表中的数字赋给相对应的字母:
1:b、f、p、v
2:c、g、k、q、s、x、z
3:d、t
4:l
5:m、n
6:R
如果字符串中存在拥有相同数字的2个以上(包含2个)的字母在一起(例如b和f),或者只有h或w,则删除其他的,只保留1个;
只返回前4个字节,不够用0填充
例如:SELECT SOUNDEX('dog'),soundex('boy') FROM DUAL;
10、SUBSTR(c1,n1[,n2]) 截取指定长度的字符串。稍不注意就可能充满了陷阱的函数。
n1=开始长度;
n2=截取的字符串长度,如果为空,默认截取到字符串结尾;
如果n1=0 then n1=1
如果n1>0,则oracle从左向右确认起始位置截取
例如:SELECT SUBSTR('What is this',5,3) FROM DUAL;
如果n1<0,则oracle从右向左数确认起始位置
例如:SELECT SUBSTR('What is this',-5,3) FROM DUAL;
如果n1>c1.length则返回空
例如:SELECT SUBSTR('What is this',50,3) FROM DUAL;
然后再请你猜猜,如果n2<1,会如何返回值呢
11、TRANSLATE(c1,c2,c3) 就功能而言,此函数与replace有些相似。但需要注意的一点是,translate是绝对匹配替换,这点与replace函数具有非常大区别。什么是绝对匹配替换呢?简单的说,是将字符串c1中按一定的格式c2替换为c3。如果文字形容仍然无法理解,我们通过几具实例来说明:
例如:
SELECT TRANSLATE('What is this','','-') FROM DUAL;
SELECT TRANSLATE('What is this','-','') FROM DUAL;
结果都是空。来试试这个:
SELECT TRANSLATE('What is this',' ',' ') FROM DUAL;
再来看这个:
SELECT TRANSLATE('What is this','ait','-*') FROM DUAL;
是否明白了点呢?Replace函数理解比较简单,它是将字符串中指定字符替换成其它字符,它的字符必须是连续的。而translate中,则是指定字符串c1中出现的c2,将c2中各个字符替换成c3中位置顺序与其相同的c3中的字符。明白了?Replace是替换,而translate则像是过滤
(三).字符型函数返回数字值(Character Functions Returning Number Values)
本类函数支持所有的数据类型
1、INSTR(c1,c2[,n1[,n2]]) 返回c2在c1中位置
c1:原字符串
c2:要寻找的字符串
n1:查询起始位置,正值表示从左到右,负值表示从右到左 (大小表示位置,比如3表示左面第3处开始,-3表示右面第3处开始)。黑黑,如果为0的话,则返回的也是0
n2:第几个匹配项。大于0
例如:SELECT INSTR('abcdefg','e',-3) FROM DUAL;
2、LENGTH(c) 返回指定字符串的长度。如果
例如:SELECT LENGTH('A123中') FROM DUAL;
猜猜SELECT LENGTH('') FROM DUAL;的返回值是什么
(四).日期函数(Datetime Functions)
本类函数中,除months_between返回数值外,其它都将返回日期。
1、ADD_MONTHS() 返回指定日期月份+n之后的值,n可以为任何整数。
例如:SELECT ADD_MONTHS(sysdate,12),ADD_MONTHS(sysdate,-12) FROM DUAL;
2、CURRENT_DATE 返回当前session所在时区的默认时间
例如:
SQL> alter session set nls_date_format = 'mm-dd-yyyy' ;
SQL> select current_date from dual;
3、SYSDATE 功能与上相同,返回当前session所在时区的默认时间。但是需要注意的一点是,如果同时使用sysdate与current_date获得的时间不一定相同,某些情况下current_date会比sysdate快一秒。经过与xyf_tck(兄台的大作ORACLE的工作机制写的很好,深入浅出)的短暂交流,我们认为current_date是将current_timestamp中毫秒四舍五入后的返回,虽然没有找到文档支持,但是想来应该八九不离十。同时,仅是某些情况下会有一秒的误差,一般情况下并不会对你的操作造成影响,所以了解即可。
例如:SELECT SYSDATE,CURRENT_DATE FROM DUAL;
4、LAST_DAY(d) 返回指定时间所在月的最后一天
例如:SELECT last_day(SYSDATE) FROM DUAL;
5、NEXT_DAY(d,n) 返回指定日期后第一个n的日期,n为一周中的某一天。但是,需要注意的是n如果为字符的话,它的星期形式需要与当前session默认时区中的星期形式相同。
例如:三思用的中文nt,nls_language值为SIMPLIFIED CHINESE
SELECT NEXT_DAY(SYSDATE,5) FROM DUAL;
SELECT NEXT_DAY(SYSDATE,'星期四') FROM DUAL;
两种方式都可以取到正确的返回,但是:
SELECT NEXT_DAY(SYSDATE,'Thursday') FROM DUAL;
则会执行出错,提供你说周中的日无效,就是这个原因了。
6、MONTHS_BETWEEN(d1,d2) 返回d1与d2间的月份差,视d1,d2的值大小,结果可正可负,当然也有可能为0
例如:
SELECT months_between(SYSDATE, sysdate),
months_between(SYSDATE, add_months(sysdate, -1)),
months_between(SYSDATE, add_months(sysdate, 1))
FROM DUAL;
7、ROUND(d[,fmt]) 前面讲数值型函数的时候介绍过ROUND,此处与上功能基本相似,不过此处操作的是日期。如果不指定fmt参数,则默认返回距离指定日期最近的日期。
例如:SELECT ROUND(SYSDATE,'HH24') FROM DUAL;
8、TRUNC(d[,fmt]) 与前面介绍的数值型TRUNC原理相同,不过此处也是操作的日期型。
例如:SELECT TRUNC(SYSDATE,'HH24') FROM DUAL;
(五).转换函数(Conversion Functions)
转换函数将指定字符从一种类型转换为另一种,通常这类函数遵循如下惯例:函数名称后面跟着待转换类型以及输出类型。
1、TO_CHAR() 本函数又可以分三小类,分别是
转换字符->字符TO_CHAR(c):将nchar,nvarchar2,clob,nclob类型转换为char类型;
例如:SELECT TO_CHAR('AABBCC') FROM DUAL;
转换时间->字符TO_CHAR(d[,fmt]):将指定的时间(data,timestamp,timestamp with time zone)按照指定格式转换为varchar2类型;
例如:SELECT TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss') FROM DUAL;
转换数值->字符TO_CHAR(n[,fmt]):将指定数值n按照指定格式fmt转换为varchar2类型并返回;
例如:SELECT TO_CHAR(-100, 'L99G999D99MI') FROM DUAL;
2、TO_DATE(c[,fmt[,nls]]) 将char,nchar,varchar2,nvarchar2转换为日期类型,如果fmt参数不为空,则按照fmt中指定格式进行转换。注意这里的fmt参数。如果ftm为'J'则表示按照公元制(Julian day)转换,c则必须为大于0并小于5373484的正整数。
例如:
SELECT TO_DATE(2454336, 'J') FROM DUAL;
SELECT TO_DATE('2007-8-23 23:25:00', 'yyyy-mm-dd hh24:mi:ss') FROM DUAL;
为什么公元制的话,c的值必须不大于5373484呢?因为Oracle的DATE类型的取值范围是公元前4712年1月1日至公元9999年12月31日。看看下面这个语句:
SELECT TO_CHAR(TO_DATE('9999-12-31','yyyy-mm-dd'),'j') FROM DUAL;
3、TO_NUMBER(c[,fmt[,nls]]) 将char,nchar,varchar2,nvarchar2型字串按照fmt中指定格式转换为数值类型并返回。
例如:SELECT TO_NUMBER('-100.00', '9G999D99') FROM DUAL;
(六).其它辅助函数(Miscellaneous Single-Row Functions)
1、DECODE(exp,s1,r1,s2,r2..s,r[,def]) 可以把它理解成一个增强型的if else,只不过它并不通过多行语句,而是在一个函数内实现if else的功能。
exp做为初始参数。s做为对比值,相同则返回r,如果s有多个,则持续遍历所有s,直到某个条件为真为止,否则返回默认值def(如果指定了的话),如果没有默认值,并且前面的对比也都没有为真,则返回空。
毫无疑问,decode是个非常重要的函数,在实现行转列等功能时都会用到,需要牢记和熟练使用。
例如:select decode('a2','a1','true1','a2','true2','default') from dual;
2、GREATEST(n1,n2,...n) 返回序列中的最大值
例如:SELECT GREATEST(15,5,75,8) "Greatest" FROM DUAL;
3、LEAST(n1,n2....n) 返回序列中的最小值
例如:SELECT LEAST(15,5,75,8) LEAST FROM DUAL;
4、NULLIF(c1,c2)
Nullif也是个很有意思的函数。逻辑等价于:CASE WHEN c1 = c2 THEN NULL ELSE c1 END
例如:SELECT NULLIF('a','b'),NULLIF('a','a') FROM DUAL;
5、NVL(c1,c2) 逻辑等价于IF c1 is null THEN c2 ELSE c1 END。c1,c2可以是任何类型。如果两者类型不同,则oracle会自动将c2转换为c1的类型。
例如:SELECT NVL(null, '12') FROM DUAL;
6、NVL2(c1,c2,c3) 大家可能都用到nvl,但你用过nvl2吗?如果c1非空则返回c2,如果c1为空则返回c3
例如:select nvl2('a', 'b', 'c') isNull,nvl2(null, 'b', 'c') isNotNull from dual;
7、SYS_CONNECT_BY_PATH(col,c) 该函数只能应用于树状查询。返回通过c1连接的从根到节点的路径。该函数必须与connect by 子句共同使用。
例如:
create table tmp3(
rootcol varchar2(10),
nodecol varchar2(10)
);
insert into tmp3 values ('','a001');
insert into tmp3 values ('','b001');
insert into tmp3 values ('a001','a002');
insert into tmp3 values ('a002','a004');
insert into tmp3 values ('a001','a003');
insert into tmp3 values ('a003','a005');
insert into tmp3 values ('a005','a008');
insert into tmp3 values ('b001','b003');
insert into tmp3 values ('b003','b005');
select lpad(' ', level*10,'=') ||'>'|| sys_connect_by_path(nodecol,'/')
from tmp3
start with rootcol = 'a001'
connect by prior nodecol =rootcol;
8、SYS_CONTEXT(c1,c2[,n]) 将指定命名空间c1的指定参数c2的值按照指定长度n截取后返回。
Oracle9i提供内置了一个命名空间USERENV,描述了当前session的各项信息,其拥有下列参数:
CURRENT_SCHEMA:当前模式名;
CURRENT_USER:当前用户;
IP_ADDRESS:当前客户端IP地址;
OS_USER:当前客户端操作系统用户;
等等数十项,更详细的参数列还请大家直接参考Oracle Online Documents
例如:SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL;
注:N表示数字型,C表示字符型,D表示日期型,[]表示内中参数可被忽略,fmt表示格式。
单值函数在查询中返回单个值,可被应用到select,where子句,start with以及connect by 子句和having子句。
(一).数值型函数(Number Functions)
数值型函数输入数字型参数并返回数值型的值。多数该类函数的返回值支持38位小数点,诸如:COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH 支持36位小数点。ACOS, ASIN, ATAN, and ATAN2支持30位小数点。
1、ABS(n) 返回数字的绝对值
例如:SELECT ABS(-1000000.01) FROM DUAL;
2、COS(n) 返回n的余弦值
例如:SELECT COS(-2) FROM DUAL;
3、ACOS(n) 反余弦函数,n between -1 and 1,返回值between 0 and pi。
例如:SELECT ACOS(0.9) FROM DUAL;
4、BITAND(n1,n2) 位与运算,这个太有意思了,虽然没想到可能用到哪里,详细说明一下:
假设3,9做位与运算,3的二进制形式为:0011,9的二进制形式为:1001,则结果是0001,转换成10进制数为1。
例如:SELECT BITAND(3,9) FROM DUAL;
5、CEIL(n) 返回大于或等于n的最小的整数值
例如:SELECT ceil(18.2) FROM DUAL;
考你一下,猜猜ceil(-18.2)的值会是什么呢
6、FLOOR(n) 返回小于等于n的最大整数值
例如:SELECT FLOOR(2.2) FROM DUAL;
再猜猜floor(-2.2)的值会是什么呢
7、BIN_TO_NUM(n1,n2,....n) 二进制转向十进制
例如:SELECT BIN_TO_NUM(1),BIN_TO_NUM(1,0),BIN_TO_NUM(1,1) FROM DUAL;
8、SIN(n) 返回n的正玄值,n为弧度。
例如:SELECT SIN(10) FROM DUAL;
9、SINH(n) 返回n的双曲正玄值,n为弧度。
例如:SELECT SINH(10) FROM DUAL;
10、ASIN(n) 反正玄函数,n between -1 and 1,返回值between pi/2 and -pi/2。
例如:SELECT ASIN(0.8) FROM DUAL;
11、TAN(n) 返回n的正切值,n为弧度
例如:SELECT TAN(0.8) FROM DUAL;
12、TANH(n) 返回n的双曲正切值,n为弧度
例如:SELECT TANH(0.8) FROM DUAL;
13、ATAN(n) 反正切函数,n表示弧度,返回值between pi/2 and -pi/2。
例如:SELECT ATAN(-444444.9999999) FROM DUAL;
14、EXP(n) 返回e的n次幂,e = 2.71828183 ...
例如:SELECT EXP(3) FROM DUAL;
15、LN(n) 返回n的自然对数,n>0
例如:SELECT LN(0.9) FROM DUAL;
16、LOG(n1,n2) 返回以n1为底n2的对数,n1 >0 and not 1 ,n2>0
例如:SELECT LOG(1.1,2.2) FROM DUAL;
17、POWER(n1,n2) 返回n1的n2次方。n1,n2可以为任意数值,不过如果m是负数,则n必须为整数
例如:SELECT POWER(2.2,2.2) FROM DUAL;
18、SIGN(n) 如果n<0返回-1,如果n>0返回1,如果n=0返回0.
例如:SELECT SIGN(14),SIGN(-14),SIGN(0) FROM DUAL;
19、SQRT(n) 返回n的平方根,n为弧度。n>=0
例如:SELECT SQRT(0.1) FROM DUAL;
(二).字符型函数返回字符值(Character Functions Returning Character Values)
该类函数返回与输入类型相同的类型。
返回的CHAR类型值长度不超过2000字节;
返回的VCHAR2类型值长度不超过4000字节;
如果上述应返回的字符长度超出,oracle并不会报错而是直接截断至最大可支持长度返回。
返回的CLOB类型值长度不超过4G;
对于CLOB类型的函数,如果返回值长度超出,oracle不会返回任何错误而是直接抛出错误。
1、CHR(N[ USING NCHAR_CS]) 返回指定数值在当前字符集中对应的字符
例如:SELECT CHR(95) FROM DUAL;
2、CONCAT(c1,c2) 连接字符串,等同于||
例如:SELECT concat('aa','bb') FROM DUAL;
3、INITCAP(c) 将字符串中单词的第一个字母转换为大写,其它则转换为小写
例如:SELECT INITCAP('whaT is this') FROM DUAL;
4、NLS_INITCAP(c) 返回指定字符串,并将字符串中第一个字母变大写,其它字母变小写
例如:SELECT NLS_INITCAP('中华miNZHu') FROM DUAL;
它还具有一个参数:Nlsparam用来指定排序规则,可以忽略,默认状态该参数为当前session的排序规则。
(三).字符型函数返回数字值(Character Functions Returning Number Values)
本类函数支持所有的数据类型
1、ASCII(c) 与chr函数的用途刚刚相反,本函数返回指定字符在当前字符集下对应的数值。
例如:SELECT ASCII('_') FROM DUAL;
(四).日期函数(Datetime Functions)
本类函数中,除months_between返回数值外,其它都将返回日期。
1、CURRENT_TIMESTAMP([n]) 返回当前session所在时区的日期和时间。n表示毫秒级的精度,不大于6
例如:SELECT CURRENT_TIMESTAMP(3) FROM DUAL;
2、LOCALTIMESTAMP([n]) 与上同,返回当前session所在时区的日期和时间。n表示毫秒级的精度,不大于6
例如:SELECT LOCALTIMESTAMP(3) FROM DUAL;
3、SYSTIMESTAMP([n]) 与上同,返回当前数据库所在时区的日期和时间,n表示毫秒级的精度,>0 and <6
例如:SELECT SYSTIMESTAMP(4) FROM DUAL;
4、DBTIMEZONE 返回数据库的当前时区
例如:SELECT DBTIMEZONE FROM DUAL;
5、SESSIONTIMEZONE 返回当前session所在时区
例如:SELECT SESSIONTIMEZONE FROM DUAL;
6、EXTRACT(key from date) key=(year,month,day,hour,minute,second) 从指定时间提到指定日期列
例如:SELECT EXTRACT(year from sysdate) FROM DUAL;
7、TO_TIMESTAMP(c1[,fmt]) 将指定字符按指定格式转换为timestamp格式。
例如:SELECT TO_TIMESTAMP('2007-8-22', 'YYYY-MM-DD HH:MI:SS') FROM DUAL;
(五).转换函数(Conversion Functions)
转换函数将指定字符从一种类型转换为另一种,通常这类函数遵循如下惯例:函数名称后面跟着待转换类型以及输出类型。
1、BIN_TO_NUM(n1,n2...n) 将一组位向量转换为等价的十进制形式。
例如:SELECT BIN_TO_NUM(1,1,0) FROM DUAL;
2、CAST(c as newtype) 将指定字串转换为指定类型,基本只对字符类型有效,比如char,number,date,rowid等。此类转换有一个专门的表列明了哪种类型可以转换为哪种类型,此处就不作酹述。
例如:SELECT CAST('1101' AS NUMBER(5)) FROM DUAL;
3、CHARTOROWID(c) 将字符串转换为rowid类型
例如:SELECT CHARTOROWID('A003D1ABBEFAABSAA0') FROM DUAL;
4、ROWIDTOCHAR(rowid) 转换rowid值为varchar2类型。返回串长度为18个字节。
例如:SELECT ROWIDTOCHAR(rowid) FROM DUAL;
5、TO_MULTI_BYTE(c) 将指定字符转换为全角并返回char类型字串
例如:SELECT TO_MULTI_BYTE('ABC abc 中华') FROM DUAL;
6、TO_SINGLE_BYTE(c) 将指定字符转换为半角并返回char类型字串
例如:SELECT TO_SINGLE_BYTE('ABC abc中华') FROM DUAL;
(六).其它辅助函数(Miscellaneous Single-Row Functions)
1、COALESCE(n1,n2,....n) 返回序列中的第一个非空值
例如:SELECT COALESCE(null,5,6,null,9) FROM DUAL;
2、DUMP(exp[,fmt[,start[,length]]])
dump是个功能非常强悍的函数,对于深入了解oracle存储的人而言相当有用。所以对于我们这些仅仅只是应用的人而言就不知道能将其应用于何处了。此处仅介绍用法,不对其功能做深入分析。
如上所示,dump拥有不少参数。其本质是以指定格式,返回指定长度的exp的内部表示形式的varchar2值。fmt含4种格式:8||10||16||17,分别表示8进制,10进制,16进制和单字符,默认为10进制。start参数表示开始位置,length表示以,分隔的字串数。
例如:SELECT DUMP('abcdefg',17,2,4) FROM DUAL;
3、EMPTY_BLOB,EMPTY_CLOB 这两个函数都是返回空lob类型,通常被用于insert和update等语句以初始化lob列,或者将其置为空。EMPTY表示LOB已经被初始化,只不过还没有用来存储数据。
4、NLS_CHARSET_NAME(n) 返回指定数值对应的字符集名称。
例如:SELECT NLS_CHARSET_NAME(1) FROM DUAL;
5、NLS_CHARSET_ID(c) 返回指定字符对应的字符集id。
例如:SELECT NLS_CHARSET_ID('US7ASCII') FROM DUAL;
6、NLS_CHARSET_DECL_LEN(n1,n2) 返回一个NCHAR值的声明宽度(以字符为单位).n1是该值以字节为单位的长度,n2是该值的字符集ID
例如:SELECT NLS_CHARSET_DECL_LEN(100, nls_charset_id('US7ASCII')) FROM DUAL;
7、SYS_EXTRACT_UTC(timestamp) 返回标准通用时间即格林威治时间。
例如:SELECT SYS_EXTRACT_UTC(current_timestamp) FROM DUAL;
8、SYS_TYPEID(object_type) 返回对象类型对应的id。
例如:这个这个,没有建立过自定义对象,咋做示例?
9、UID 返回一个唯一标识当前数据库用户的整数。
例如:SELECT UID FROM DUAL;
10、USER 返回当前session用户
例如:SELECT USER FROM DUAL;
11、USERENV(c) 该函数用来返回当前session的信息,据oracle文档的说明,userenv是为了保持向下兼容的遗留函数。oracle公司推荐你使用sys_context函数调用USERENV命名空间来获取相关信息,所以大家了解下就行了。
例如:SELECT USERENV('LANGUAGE') FROM DUAL;
12、VSIZE(c) 返回c的字节数。
例如:SELECT VSIZE('abc中华') FROM DUAL;
著名函数篇 -经常用到的函数
非著名函数篇-即虽然很少用到,但某些情况下却很实用
注:N表示数字型,C表示字符型,D表示日期型,[]表示内中参数可被忽略,fmt表示格式。
单值函数在查询中返回单个值,可被应用到select,where子句,start with以及connect by 子句和having子句。
(一).数值型函数(Number Functions)
数值型函数输入数字型参数并返回数值型的值。多数该类函数的返回值支持38位小数点,诸如:COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH 支持36位小数点。ACOS, ASIN, ATAN, and ATAN2支持30位小数点。
1、MOD(n1,n2) 返回n1除n2的余数,如果n2=0则返回n1的值。
例如:SELECT MOD(24,5) FROM DUAL;
2、ROUND(n1[,n2]) 返回四舍五入小数点右边n2位后n1的值,n2缺省值为0,如果n2为负数就舍入到小数点左边相应的位上(虽然oracle documents上提到n2的值必须为整数,事实上执行时此处的判断并不严谨,即使n2为非整数,它也会自动将n2取整后做处理,但是我文档中其它提到必须为整的地方需要特别注意,如果不为整执行时会报错的)。
例如:SELECT ROUND(23.56),ROUND(23.56,1),ROUND(23.56,-1) FROM DUAL;
3、TRUNC(n1[,n2] 返回截尾到n2位小数的n1的值,n2缺省设置为0,当n2为缺省设置时会将n1截尾为整数,如果n2为负值,就截尾在小数点左边相应的位上。
例如:SELECT TRUNC(23.56),TRUNC(23.56,1),TRUNC(23.56,-1) FROM DUAL;
(二).字符型函数返回字符值(Character Functions Returning Character Values)
该类函数返回与输入类型相同的类型。
返回的CHAR类型值长度不超过2000字节;
返回的VCHAR2类型值长度不超过4000字节;
如果上述应返回的字符长度超出,oracle并不会报错而是直接截断至最大可支持长度返回。
返回的CLOB类型值长度不超过4G;
对于CLOB类型的函数,如果返回值长度超出,oracle不会返回任何错误而是直接抛出错误。
1、LOWER(c) 将指定字符串内字符变为小写,支持CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB类型
例如:SELECT LOWER('WhaT is tHis') FROM DUAL;
2、UPPER(c) 将指定字符串内字符变为大写,支持CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,NCLOB类型
例如:SELECT UPPER('WhaT is tHis') FROM DUAL;
3、LPAD(c1,n[,c2]) 返回指定长度=n的字符串,需要注意的有几点:
如果n<c1.length则从右到左截取指定长度返回;
如果n>c1.length and c2 is null,以空格从左向右补充字符长度至n并返回;
如果n>c1.length and c2 is not null,以指定字符c2从左向右补充c1长度至n并返回;
例如:SELECT LPAD('WhaT is tHis',5),LPAD('WhaT is tHis',25),LPAD('WhaT is tHis',25,'-') FROM DUAL;
最后大家再猜一猜,如果n<0,结果会怎么样
4、RPAD(c1,n[,c2]) 返回指定长度=n的字符串,基本与上同,不过补充字符是从右向左方向正好与上相反;
例如:SELECT RPAD('WhaT is tHis',5),RPAD('WhaT is tHis',25),RPAD('WhaT is tHis',25,'-') FROM DUAL;
5、TRIM([[LEADING||TRAILING||BOTH] c2 FROM] c1) 哈哈,被俺无敌的形容方式搞晕头了吧,这个地方还是看图更明了一些。
看起来很复杂,理解起来很简单:
如果没有指定任何参数则oracle去除c1头尾空格
例如:SELECT TRIM(' WhaT is tHis ') FROM DUAL;
如果指定了c2参数,则oracle去掉c1头尾c2(这个建议细致测试,有多种不同情形的哟)
例如:SELECT TRIM('W' FROM 'WhaT is tHis w W') FROM DUAL;
如果指定了leading参数则会去掉c1头部c2
例如:SELECT TRIM(leading 'W' FROM 'WhaT is tHis w W') FROM DUAL;
如果指定了trailing参数则会去掉c1尾部c2
例如:SELECT TRIM(trailing 'W' FROM 'WhaT is tHis w W') FROM DUAL;
如果指定了both参数则会去掉c1头尾c2(跟不指定有区别吗?没区别!)
例如:SELECT TRIM(both 'W' FROM 'WhaT is tHis w W') FROM DUAL;
注意:c2长度=1
6、LTRIM(c1[,c2]) 千万表以为与上面那个长的像,功能也与上面的类似,本函数是从字符串c1左侧截取掉与指定字符串c2相同的字符并返回。如果c2为空则默认截取空格。
例如:SELECT LTRIM('WWhhhhhaT is tHis w W','Wh') FROM DUAL;
7、RTRIM(c1,c2)与上同,不过方向相反
例如:SELECT RTRIM('WWhhhhhaT is tHis w W','W w') FROM DUAL;
8、REPLACE(c1,c2[,c3]) 将c1字符串中的c2替换为c3,如果c3为空,则从c1中删除所有c2。
例如:SELECT REPLACE('WWhhhhhaT is tHis w W','W','-') FROM DUAL;
9、SOUNDEX(c) 神奇的函数啊,该函数返回字符串参数的语音表示形式,对于比较一些读音相同,但是拼写不同的单词非常有用。计算语音的算法如下:
保留字符串首字母,但删除a、e、h、i、o、w、y。
将下表中的数字赋给相对应的字母:
1:b、f、p、v
2:c、g、k、q、s、x、z
3:d、t
4:l
5:m、n
6:R
如果字符串中存在拥有相同数字的2个以上(包含2个)的字母在一起(例如b和f),或者只有h或w,则删除其他的,只保留1个;
只返回前4个字节,不够用0填充
例如:SELECT SOUNDEX('dog'),soundex('boy') FROM DUAL;
10、SUBSTR(c1,n1[,n2]) 截取指定长度的字符串。稍不注意就可能充满了陷阱的函数。
n1=开始长度;
n2=截取的字符串长度,如果为空,默认截取到字符串结尾;
如果n1=0 then n1=1
如果n1>0,则oracle从左向右确认起始位置截取
例如:SELECT SUBSTR('What is this',5,3) FROM DUAL;
如果n1<0,则oracle从右向左数确认起始位置
例如:SELECT SUBSTR('What is this',-5,3) FROM DUAL;
如果n1>c1.length则返回空
例如:SELECT SUBSTR('What is this',50,3) FROM DUAL;
然后再请你猜猜,如果n2<1,会如何返回值呢
11、TRANSLATE(c1,c2,c3) 就功能而言,此函数与replace有些相似。但需要注意的一点是,translate是绝对匹配替换,这点与replace函数具有非常大区别。什么是绝对匹配替换呢?简单的说,是将字符串c1中按一定的格式c2替换为c3。如果文字形容仍然无法理解,我们通过几具实例来说明:
例如:
SELECT TRANSLATE('What is this','','-') FROM DUAL;
SELECT TRANSLATE('What is this','-','') FROM DUAL;
结果都是空。来试试这个:
SELECT TRANSLATE('What is this',' ',' ') FROM DUAL;
再来看这个:
SELECT TRANSLATE('What is this','ait','-*') FROM DUAL;
是否明白了点呢?Replace函数理解比较简单,它是将字符串中指定字符替换成其它字符,它的字符必须是连续的。而translate中,则是指定字符串c1中出现的c2,将c2中各个字符替换成c3中位置顺序与其相同的c3中的字符。明白了?Replace是替换,而translate则像是过滤
(三).字符型函数返回数字值(Character Functions Returning Number Values)
本类函数支持所有的数据类型
1、INSTR(c1,c2[,n1[,n2]]) 返回c2在c1中位置
c1:原字符串
c2:要寻找的字符串
n1:查询起始位置,正值表示从左到右,负值表示从右到左 (大小表示位置,比如3表示左面第3处开始,-3表示右面第3处开始)。黑黑,如果为0的话,则返回的也是0
n2:第几个匹配项。大于0
例如:SELECT INSTR('abcdefg','e',-3) FROM DUAL;
2、LENGTH(c) 返回指定字符串的长度。如果
例如:SELECT LENGTH('A123中') FROM DUAL;
猜猜SELECT LENGTH('') FROM DUAL;的返回值是什么
(四).日期函数(Datetime Functions)
本类函数中,除months_between返回数值外,其它都将返回日期。
1、ADD_MONTHS() 返回指定日期月份+n之后的值,n可以为任何整数。
例如:SELECT ADD_MONTHS(sysdate,12),ADD_MONTHS(sysdate,-12) FROM DUAL;
2、CURRENT_DATE 返回当前session所在时区的默认时间
例如:
SQL> alter session set nls_date_format = 'mm-dd-yyyy' ;
SQL> select current_date from dual;
3、SYSDATE 功能与上相同,返回当前session所在时区的默认时间。但是需要注意的一点是,如果同时使用sysdate与current_date获得的时间不一定相同,某些情况下current_date会比sysdate快一秒。经过与xyf_tck(兄台的大作ORACLE的工作机制写的很好,深入浅出)的短暂交流,我们认为current_date是将current_timestamp中毫秒四舍五入后的返回,虽然没有找到文档支持,但是想来应该八九不离十。同时,仅是某些情况下会有一秒的误差,一般情况下并不会对你的操作造成影响,所以了解即可。
例如:SELECT SYSDATE,CURRENT_DATE FROM DUAL;
4、LAST_DAY(d) 返回指定时间所在月的最后一天
例如:SELECT last_day(SYSDATE) FROM DUAL;
5、NEXT_DAY(d,n) 返回指定日期后第一个n的日期,n为一周中的某一天。但是,需要注意的是n如果为字符的话,它的星期形式需要与当前session默认时区中的星期形式相同。
例如:三思用的中文nt,nls_language值为SIMPLIFIED CHINESE
SELECT NEXT_DAY(SYSDATE,5) FROM DUAL;
SELECT NEXT_DAY(SYSDATE,'星期四') FROM DUAL;
两种方式都可以取到正确的返回,但是:
SELECT NEXT_DAY(SYSDATE,'Thursday') FROM DUAL;
则会执行出错,提供你说周中的日无效,就是这个原因了。
6、MONTHS_BETWEEN(d1,d2) 返回d1与d2间的月份差,视d1,d2的值大小,结果可正可负,当然也有可能为0
例如:
SELECT months_between(SYSDATE, sysdate),
months_between(SYSDATE, add_months(sysdate, -1)),
months_between(SYSDATE, add_months(sysdate, 1))
FROM DUAL;
7、ROUND(d[,fmt]) 前面讲数值型函数的时候介绍过ROUND,此处与上功能基本相似,不过此处操作的是日期。如果不指定fmt参数,则默认返回距离指定日期最近的日期。
例如:SELECT ROUND(SYSDATE,'HH24') FROM DUAL;
8、TRUNC(d[,fmt]) 与前面介绍的数值型TRUNC原理相同,不过此处也是操作的日期型。
例如:SELECT TRUNC(SYSDATE,'HH24') FROM DUAL;
(五).转换函数(Conversion Functions)
转换函数将指定字符从一种类型转换为另一种,通常这类函数遵循如下惯例:函数名称后面跟着待转换类型以及输出类型。
1、TO_CHAR() 本函数又可以分三小类,分别是
转换字符->字符TO_CHAR(c):将nchar,nvarchar2,clob,nclob类型转换为char类型;
例如:SELECT TO_CHAR('AABBCC') FROM DUAL;
转换时间->字符TO_CHAR(d[,fmt]):将指定的时间(data,timestamp,timestamp with time zone)按照指定格式转换为varchar2类型;
例如:SELECT TO_CHAR(sysdate,'yyyy-mm-dd hh24:mi:ss') FROM DUAL;
转换数值->字符TO_CHAR(n[,fmt]):将指定数值n按照指定格式fmt转换为varchar2类型并返回;
例如:SELECT TO_CHAR(-100, 'L99G999D99MI') FROM DUAL;
2、TO_DATE(c[,fmt[,nls]]) 将char,nchar,varchar2,nvarchar2转换为日期类型,如果fmt参数不为空,则按照fmt中指定格式进行转换。注意这里的fmt参数。如果ftm为'J'则表示按照公元制(Julian day)转换,c则必须为大于0并小于5373484的正整数。
例如:
SELECT TO_DATE(2454336, 'J') FROM DUAL;
SELECT TO_DATE('2007-8-23 23:25:00', 'yyyy-mm-dd hh24:mi:ss') FROM DUAL;
为什么公元制的话,c的值必须不大于5373484呢?因为Oracle的DATE类型的取值范围是公元前4712年1月1日至公元9999年12月31日。看看下面这个语句:
SELECT TO_CHAR(TO_DATE('9999-12-31','yyyy-mm-dd'),'j') FROM DUAL;
3、TO_NUMBER(c[,fmt[,nls]]) 将char,nchar,varchar2,nvarchar2型字串按照fmt中指定格式转换为数值类型并返回。
例如:SELECT TO_NUMBER('-100.00', '9G999D99') FROM DUAL;
(六).其它辅助函数(Miscellaneous Single-Row Functions)
1、DECODE(exp,s1,r1,s2,r2..s,r[,def]) 可以把它理解成一个增强型的if else,只不过它并不通过多行语句,而是在一个函数内实现if else的功能。
exp做为初始参数。s做为对比值,相同则返回r,如果s有多个,则持续遍历所有s,直到某个条件为真为止,否则返回默认值def(如果指定了的话),如果没有默认值,并且前面的对比也都没有为真,则返回空。
毫无疑问,decode是个非常重要的函数,在实现行转列等功能时都会用到,需要牢记和熟练使用。
例如:select decode('a2','a1','true1','a2','true2','default') from dual;
2、GREATEST(n1,n2,...n) 返回序列中的最大值
例如:SELECT GREATEST(15,5,75,8) "Greatest" FROM DUAL;
3、LEAST(n1,n2....n) 返回序列中的最小值
例如:SELECT LEAST(15,5,75,8) LEAST FROM DUAL;
4、NULLIF(c1,c2)
Nullif也是个很有意思的函数。逻辑等价于:CASE WHEN c1 = c2 THEN NULL ELSE c1 END
例如:SELECT NULLIF('a','b'),NULLIF('a','a') FROM DUAL;
5、NVL(c1,c2) 逻辑等价于IF c1 is null THEN c2 ELSE c1 END。c1,c2可以是任何类型。如果两者类型不同,则oracle会自动将c2转换为c1的类型。
例如:SELECT NVL(null, '12') FROM DUAL;
6、NVL2(c1,c2,c3) 大家可能都用到nvl,但你用过nvl2吗?如果c1非空则返回c2,如果c1为空则返回c3
例如:select nvl2('a', 'b', 'c') isNull,nvl2(null, 'b', 'c') isNotNull from dual;
7、SYS_CONNECT_BY_PATH(col,c) 该函数只能应用于树状查询。返回通过c1连接的从根到节点的路径。该函数必须与connect by 子句共同使用。
例如:
create table tmp3(
rootcol varchar2(10),
nodecol varchar2(10)
);
insert into tmp3 values ('','a001');
insert into tmp3 values ('','b001');
insert into tmp3 values ('a001','a002');
insert into tmp3 values ('a002','a004');
insert into tmp3 values ('a001','a003');
insert into tmp3 values ('a003','a005');
insert into tmp3 values ('a005','a008');
insert into tmp3 values ('b001','b003');
insert into tmp3 values ('b003','b005');
select lpad(' ', level*10,'=') ||'>'|| sys_connect_by_path(nodecol,'/')
from tmp3
start with rootcol = 'a001'
connect by prior nodecol =rootcol;
8、SYS_CONTEXT(c1,c2[,n]) 将指定命名空间c1的指定参数c2的值按照指定长度n截取后返回。
Oracle9i提供内置了一个命名空间USERENV,描述了当前session的各项信息,其拥有下列参数:
CURRENT_SCHEMA:当前模式名;
CURRENT_USER:当前用户;
IP_ADDRESS:当前客户端IP地址;
OS_USER:当前客户端操作系统用户;
等等数十项,更详细的参数列还请大家直接参考Oracle Online Documents
例如:SELECT SYS_CONTEXT('USERENV', 'SESSION_USER') FROM DUAL;
注:N表示数字型,C表示字符型,D表示日期型,[]表示内中参数可被忽略,fmt表示格式。
单值函数在查询中返回单个值,可被应用到select,where子句,start with以及connect by 子句和having子句。
(一).数值型函数(Number Functions)
数值型函数输入数字型参数并返回数值型的值。多数该类函数的返回值支持38位小数点,诸如:COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH 支持36位小数点。ACOS, ASIN, ATAN, and ATAN2支持30位小数点。
1、ABS(n) 返回数字的绝对值
例如:SELECT ABS(-1000000.01) FROM DUAL;
2、COS(n) 返回n的余弦值
例如:SELECT COS(-2) FROM DUAL;
3、ACOS(n) 反余弦函数,n between -1 and 1,返回值between 0 and pi。
例如:SELECT ACOS(0.9) FROM DUAL;
4、BITAND(n1,n2) 位与运算,这个太有意思了,虽然没想到可能用到哪里,详细说明一下:
假设3,9做位与运算,3的二进制形式为:0011,9的二进制形式为:1001,则结果是0001,转换成10进制数为1。
例如:SELECT BITAND(3,9) FROM DUAL;
5、CEIL(n) 返回大于或等于n的最小的整数值
例如:SELECT ceil(18.2) FROM DUAL;
考你一下,猜猜ceil(-18.2)的值会是什么呢
6、FLOOR(n) 返回小于等于n的最大整数值
例如:SELECT FLOOR(2.2) FROM DUAL;
再猜猜floor(-2.2)的值会是什么呢
7、BIN_TO_NUM(n1,n2,....n) 二进制转向十进制
例如:SELECT BIN_TO_NUM(1),BIN_TO_NUM(1,0),BIN_TO_NUM(1,1) FROM DUAL;
8、SIN(n) 返回n的正玄值,n为弧度。
例如:SELECT SIN(10) FROM DUAL;
9、SINH(n) 返回n的双曲正玄值,n为弧度。
例如:SELECT SINH(10) FROM DUAL;
10、ASIN(n) 反正玄函数,n between -1 and 1,返回值between pi/2 and -pi/2。
例如:SELECT ASIN(0.8) FROM DUAL;
11、TAN(n) 返回n的正切值,n为弧度
例如:SELECT TAN(0.8) FROM DUAL;
12、TANH(n) 返回n的双曲正切值,n为弧度
例如:SELECT TANH(0.8) FROM DUAL;
13、ATAN(n) 反正切函数,n表示弧度,返回值between pi/2 and -pi/2。
例如:SELECT ATAN(-444444.9999999) FROM DUAL;
14、EXP(n) 返回e的n次幂,e = 2.71828183 ...
例如:SELECT EXP(3) FROM DUAL;
15、LN(n) 返回n的自然对数,n>0
例如:SELECT LN(0.9) FROM DUAL;
16、LOG(n1,n2) 返回以n1为底n2的对数,n1 >0 and not 1 ,n2>0
例如:SELECT LOG(1.1,2.2) FROM DUAL;
17、POWER(n1,n2) 返回n1的n2次方。n1,n2可以为任意数值,不过如果m是负数,则n必须为整数
例如:SELECT POWER(2.2,2.2) FROM DUAL;
18、SIGN(n) 如果n<0返回-1,如果n>0返回1,如果n=0返回0.
例如:SELECT SIGN(14),SIGN(-14),SIGN(0) FROM DUAL;
19、SQRT(n) 返回n的平方根,n为弧度。n>=0
例如:SELECT SQRT(0.1) FROM DUAL;
(二).字符型函数返回字符值(Character Functions Returning Character Values)
该类函数返回与输入类型相同的类型。
返回的CHAR类型值长度不超过2000字节;
返回的VCHAR2类型值长度不超过4000字节;
如果上述应返回的字符长度超出,oracle并不会报错而是直接截断至最大可支持长度返回。
返回的CLOB类型值长度不超过4G;
对于CLOB类型的函数,如果返回值长度超出,oracle不会返回任何错误而是直接抛出错误。
1、CHR(N[ USING NCHAR_CS]) 返回指定数值在当前字符集中对应的字符
例如:SELECT CHR(95) FROM DUAL;
2、CONCAT(c1,c2) 连接字符串,等同于||
例如:SELECT concat('aa','bb') FROM DUAL;
3、INITCAP(c) 将字符串中单词的第一个字母转换为大写,其它则转换为小写
例如:SELECT INITCAP('whaT is this') FROM DUAL;
4、NLS_INITCAP(c) 返回指定字符串,并将字符串中第一个字母变大写,其它字母变小写
例如:SELECT NLS_INITCAP('中华miNZHu') FROM DUAL;
它还具有一个参数:Nlsparam用来指定排序规则,可以忽略,默认状态该参数为当前session的排序规则。
(三).字符型函数返回数字值(Character Functions Returning Number Values)
本类函数支持所有的数据类型
1、ASCII(c) 与chr函数的用途刚刚相反,本函数返回指定字符在当前字符集下对应的数值。
例如:SELECT ASCII('_') FROM DUAL;
(四).日期函数(Datetime Functions)
本类函数中,除months_between返回数值外,其它都将返回日期。
1、CURRENT_TIMESTAMP([n]) 返回当前session所在时区的日期和时间。n表示毫秒级的精度,不大于6
例如:SELECT CURRENT_TIMESTAMP(3) FROM DUAL;
2、LOCALTIMESTAMP([n]) 与上同,返回当前session所在时区的日期和时间。n表示毫秒级的精度,不大于6
例如:SELECT LOCALTIMESTAMP(3) FROM DUAL;
3、SYSTIMESTAMP([n]) 与上同,返回当前数据库所在时区的日期和时间,n表示毫秒级的精度,>0 and <6
例如:SELECT SYSTIMESTAMP(4) FROM DUAL;
4、DBTIMEZONE 返回数据库的当前时区
例如:SELECT DBTIMEZONE FROM DUAL;
5、SESSIONTIMEZONE 返回当前session所在时区
例如:SELECT SESSIONTIMEZONE FROM DUAL;
6、EXTRACT(key from date) key=(year,month,day,hour,minute,second) 从指定时间提到指定日期列
例如:SELECT EXTRACT(year from sysdate) FROM DUAL;
7、TO_TIMESTAMP(c1[,fmt]) 将指定字符按指定格式转换为timestamp格式。
例如:SELECT TO_TIMESTAMP('2007-8-22', 'YYYY-MM-DD HH:MI:SS') FROM DUAL;
(五).转换函数(Conversion Functions)
转换函数将指定字符从一种类型转换为另一种,通常这类函数遵循如下惯例:函数名称后面跟着待转换类型以及输出类型。
1、BIN_TO_NUM(n1,n2...n) 将一组位向量转换为等价的十进制形式。
例如:SELECT BIN_TO_NUM(1,1,0) FROM DUAL;
2、CAST(c as newtype) 将指定字串转换为指定类型,基本只对字符类型有效,比如char,number,date,rowid等。此类转换有一个专门的表列明了哪种类型可以转换为哪种类型,此处就不作酹述。
例如:SELECT CAST('1101' AS NUMBER(5)) FROM DUAL;
3、CHARTOROWID(c) 将字符串转换为rowid类型
例如:SELECT CHARTOROWID('A003D1ABBEFAABSAA0') FROM DUAL;
4、ROWIDTOCHAR(rowid) 转换rowid值为varchar2类型。返回串长度为18个字节。
例如:SELECT ROWIDTOCHAR(rowid) FROM DUAL;
5、TO_MULTI_BYTE(c) 将指定字符转换为全角并返回char类型字串
例如:SELECT TO_MULTI_BYTE('ABC abc 中华') FROM DUAL;
6、TO_SINGLE_BYTE(c) 将指定字符转换为半角并返回char类型字串
例如:SELECT TO_SINGLE_BYTE('ABC abc中华') FROM DUAL;
(六).其它辅助函数(Miscellaneous Single-Row Functions)
1、COALESCE(n1,n2,....n) 返回序列中的第一个非空值
例如:SELECT COALESCE(null,5,6,null,9) FROM DUAL;
2、DUMP(exp[,fmt[,start[,length]]])
dump是个功能非常强悍的函数,对于深入了解oracle存储的人而言相当有用。所以对于我们这些仅仅只是应用的人而言就不知道能将其应用于何处了。此处仅介绍用法,不对其功能做深入分析。
如上所示,dump拥有不少参数。其本质是以指定格式,返回指定长度的exp的内部表示形式的varchar2值。fmt含4种格式:8||10||16||17,分别表示8进制,10进制,16进制和单字符,默认为10进制。start参数表示开始位置,length表示以,分隔的字串数。
例如:SELECT DUMP('abcdefg',17,2,4) FROM DUAL;
3、EMPTY_BLOB,EMPTY_CLOB 这两个函数都是返回空lob类型,通常被用于insert和update等语句以初始化lob列,或者将其置为空。EMPTY表示LOB已经被初始化,只不过还没有用来存储数据。
4、NLS_CHARSET_NAME(n) 返回指定数值对应的字符集名称。
例如:SELECT NLS_CHARSET_NAME(1) FROM DUAL;
5、NLS_CHARSET_ID(c) 返回指定字符对应的字符集id。
例如:SELECT NLS_CHARSET_ID('US7ASCII') FROM DUAL;
6、NLS_CHARSET_DECL_LEN(n1,n2) 返回一个NCHAR值的声明宽度(以字符为单位).n1是该值以字节为单位的长度,n2是该值的字符集ID
例如:SELECT NLS_CHARSET_DECL_LEN(100, nls_charset_id('US7ASCII')) FROM DUAL;
7、SYS_EXTRACT_UTC(timestamp) 返回标准通用时间即格林威治时间。
例如:SELECT SYS_EXTRACT_UTC(current_timestamp) FROM DUAL;
8、SYS_TYPEID(object_type) 返回对象类型对应的id。
例如:这个这个,没有建立过自定义对象,咋做示例?
9、UID 返回一个唯一标识当前数据库用户的整数。
例如:SELECT UID FROM DUAL;
10、USER 返回当前session用户
例如:SELECT USER FROM DUAL;
11、USERENV(c) 该函数用来返回当前session的信息,据oracle文档的说明,userenv是为了保持向下兼容的遗留函数。oracle公司推荐你使用sys_context函数调用USERENV命名空间来获取相关信息,所以大家了解下就行了。
例如:SELECT USERENV('LANGUAGE') FROM DUAL;
12、VSIZE(c) 返回c的字节数。
例如:SELECT VSIZE('abc中华') FROM DUAL;
推荐阅读
-
Oracle中的常用数值运算、数据类型转换和字符串处理函数指南
-
SSM三大框架基础面试题-一、Spring篇 什么是Spring框架? Spring是一种轻量级框架,提高开发人员的开发效率以及系统的可维护性。 我们一般说的Spring框架就是Spring Framework,它是很多模块的集合,使用这些模块可以很方便地协助我们进行开发。这些模块是核心容器、数据访问/集成、Web、AOP(面向切面编程)、工具、消息和测试模块。比如Core Container中的Core组件是Spring所有组件的核心,Beans组件和Context组件是实现IOC和DI的基础,AOP组件用来实现面向切面编程。 Spring的6个特征: 核心技术:依赖注入(DI),AOP,事件(Events),资源,i18n,验证,数据绑定,类型转换,SpEL。 测试:模拟对象,TestContext框架,Spring MVC测试,WebTestClient。 数据访问:事务,DAO支持,JDBC,ORM,编组XML。 Web支持:Spring MVC和Spring WebFlux Web框架。 集成:远程处理,JMS,JCA,JMX,电子邮件,任务,调度,缓存。 语言:Kotlin,Groovy,动态语言。 列举一些重要的Spring模块? Spring Core:核心,可以说Spring其他所有的功能都依赖于该类库。主要提供IOC和DI功能。 Spring Aspects:该模块为与AspectJ的集成提供支持。 Spring AOP:提供面向切面的编程实现。 Spring JDBC:Java数据库连接。 Spring JMS:Java消息服务。 Spring ORM:用于支持Hibernate等ORM工具。 Spring Web:为创建Web应用程序提供支持。 Spring Test:提供了对JUnit和TestNG测试的支持。 谈谈自己对于Spring IOC和AOP的理解 IOC(Inversion Of Controll,控制反转)是一种设计思想: 在程序中手动创建对象的控制权,交由给Spring框架来管理。IOC在其他语言中也有应用,并非Spring特有。IOC容器实际上就是一个Map(key, value),Map中存放的是各种对象。 将对象之间的相互依赖关系交给IOC容器来管理,并由IOC容器完成对象的注入。这样可以很大程度上简化应用的开发,把应用从复杂的依赖关系中解放出来。IOC容器就像是一个工厂一样,当我们需要创建一个对象的时候,只需要配置好配置文件/注解即可,完全不用考虑对象是如何被创建出来的。在实际项目中一个Service类可能由几百甚至上千个类作为它的底层,假如我们需要实例化这个Service,可能要每次都搞清楚这个Service所有底层类的构造函数,这可能会把人逼疯。如果利用IOC的话,你只需要配置好,然后在需要的地方引用就行了,大大增加了项目的可维护性且降低了开发难度。 Spring中的bean的作用域有哪些? 1.singleton:该bean实例为单例 2.prototype:每次请求都会创建一个新的bean实例(多例)。 3.request:每一次HTTP请求都会产生一个新的bean,该bean仅在当前HTTP request内有效。 4.session:每一次HTTP请求都会产生一个新的bean,该bean仅在当前HTTP session内有效。 5.global-session:全局session作用域,仅仅在基于Portlet的Web应用中才有意义,Spring5中已经没有了。Portlet是能够生成语义代码(例如HTML)片段的小型Java Web插件。它们基于Portlet容器,可以像Servlet一样处理HTTP请求。但是与Servlet不同,每个Portlet都有不同的会话。 Spring中的单例bean的线程安全问题了解吗? 概念用于理解:大部分时候我们并没有在系统中使用多线程,所以很少有人会关注这个问题。单例bean存在线程问题,主要是因为当多个线程操作同一个对象的时候,对这个对象的非静态成员变量的写操作会存在线程安全问题。 有两种常见的解决方案(用于回答的点): 1.在bean对象中尽量避免定义可变的成员变量(不太现实)。 2.在类中定义一个ThreadLocal成员变量,将需要的可变成员变量保存在ThreadLocal(线程本地化对象)中(推荐的一种方式)。 ThreadLocal解决多线程变量共享问题(参考博客):https://segmentfault.com/a/1190000009236777 Spring中Bean的生命周期: 1.Bean容器找到配置文件中Spring Bean的定义。 2.Bean容器利用Java Reflection API创建一个Bean的实例。 3.如果涉及到一些属性值,利用set方法设置一些属性值。 4.如果Bean实现了BeanNameAware接口,调用setBeanName方法,传入Bean的名字。 5.如果Bean实现了BeanClassLoaderAware接口,调用setBeanClassLoader方法,传入ClassLoader对象的实例。 6.如果Bean实现了BeanFactoryAware接口,调用setBeanClassFacotory方法,传入ClassLoader对象的实例。 7.与上面的类似,如果实现了其他*Aware接口,就调用相应的方法。 8.如果有和加载这个Bean的Spring容器相关的BeanPostProcessor对象,执postProcessBeforeInitialization方法。 9.如果Bean实现了InitializingBean接口,执行afeterPropertiesSet方法。 10.如果Bean在配置文件中的定义包含init-method属性,执行指定的方法。 11.如果有和加载这个Bean的Spring容器相关的BeanPostProcess对象,执行postProcessAfterInitialization方法。 12.当要销毁Bean的时候,如果Bean实现了DisposableBean接口,执行destroy方法。 13.当要销毁Bean的时候,如果Bean在配置文件中的定义包含destroy-method属性,执行指定的方法。 Spring框架中用到了哪些设计模式? 1.工厂设计模式:Spring使用工厂模式通过BeanFactory和ApplicationContext创建bean对象。 2.代理设计模式:Spring AOP功能的实现。 3.单例设计模式:Spring中的bean默认都是单例的。 4.模板方法模式:Spring中的jdbcTemplate、hibernateTemplate等以Template结尾的对数据库操作的类,它们就使用到了模板模式。 5.包装器设计模式:我们的项目需要连接多个数据库,而且不同的客户在每次访问中根据需要会去访问不同的数据库。这种模式让我们可以根据客户的需求能够动态切换不同的数据源。 6.观察者模式:Spring事件驱动模型就是观察者模式很经典的一个应用。 7.适配器模式:Spring AOP的增强或通知(Advice)使用到了适配器模式、Spring MVC中也是用到了适配器模式适配Controller。 还有很多。。。。。。。 @Component和@Bean的区别是什么 1.作用对象不同。@Component注解作用于类,而@Bean注解作用于方法。 2.@Component注解通常是通过类路径扫描来自动侦测以及自动装配到Spring容器中(我们可以使用@ComponentScan注解定义要扫描的路径)。@Bean注解通常是在标有该注解的方法中定义产生这个bean,告诉Spring这是某个类的实例,当我需要用它的时候还给我。 3.@Bean注解比@Component注解的自定义性更强,而且很多地方只能通过@Bean注解来注册bean。比如当引用第三方库的类需要装配到Spring容器的时候,就只能通过@Bean注解来实现。 @Configuration public class AppConfig { @Bean public TransferService transferService { return new TransferServiceImpl; } } <beans> <bean id="transferService" class="com.kk.TransferServiceImpl"/> </beans> @Bean public OneService getService(status) { case (status) { when 1: return new serviceImpl1; when 2: return new serviceImpl2; when 3: return new serviceImpl3; } } 将一个类声明为Spring的bean的注解有哪些? 声明bean的注解: @Component 组件,没有明确的角色 @Service 在业务逻辑层使用(service层) @Repository 在数据访问层使用(dao层) @Controller 在展现层使用,控制器的声明 注入bean的注解: @Autowired:由Spring提供 @Inject:由JSR-330提供 @Resource:由JSR-250提供 *扩:JSR 是 java 规范标准 Spring事务管理的方式有几种? 1.编程式事务:在代码中硬编码(不推荐使用)。 2.声明式事务:在配置文件中配置(推荐使用),分为基于XML的声明式事务和基于注解的声明式事务。 Spring事务中的隔离级别有哪几种? 在TransactionDefinition接口中定义了五个表示隔离级别的常量:ISOLATION_DEFAULT:使用后端数据库默认的隔离级别,Mysql默认采用的REPEATABLE_READ隔离级别;Oracle默认采用的READ_COMMITTED隔离级别。ISOLATION_READ_UNCOMMITTED:最低的隔离级别,允许读取尚未提交的数据变更,可能会导致脏读、幻读或不可重复读。ISOLATION_READ_COMMITTED:允许读取并发事务已经提交的数据,可以阻止脏读,但是幻读或不可重复读仍有可能发生ISOLATION_REPEATABLE_READ:对同一字段的多次读取结果都是一致的,除非数据是被本身事务自己所修改,可以阻止脏读和不可重复读,但幻读仍有可能发生。ISOLATION_SERIALIZABLE:最高的隔离级别,完全服从ACID的隔离级别。所有的事务依次逐个执行,这样事务之间就完全不可能产生干扰,也就是说,该级别可以防止脏读、不可重复读以及幻读。但是这将严重影响程序的性能。通常情况下也不会用到该级别。 Spring事务中有哪几种事务传播行为? 在TransactionDefinition接口中定义了八个表示事务传播行为的常量。 支持当前事务的情况:PROPAGATION_REQUIRED:如果当前存在事务,则加入该事务;如果当前没有事务,则创建一个新的事务。PROPAGATION_SUPPORTS: 如果当前存在事务,则加入该事务;如果当前没有事务,则以非事务的方式继续运行。PROPAGATION_MANDATORY: 如果当前存在事务,则加入该事务;如果当前没有事务,则抛出异常。(mandatory:强制性)。 不支持当前事务的情况:PROPAGATION_REQUIRES_NEW: 创建一个新的事务,如果当前存在事务,则把当前事务挂起。PROPAGATION_NOT_SUPPORTED: 以非事务方式运行,如果当前存在事务,则把当前事务挂起。PROPAGATION_NEVER: 以非事务方式运行,如果当前存在事务,则抛出异常。 其他情况:PROPAGATION_NESTED: 如果当前存在事务,则创建一个事务作为当前事务的嵌套事务来运行;如果当前没有事务,则该取值等价于PROPAGATION_REQUIRED。 二、SpringMVC篇 什么是Spring MVC ?简单介绍下你对springMVC的理解? Spring MVC是一个基于Java的实现了MVC设计模式的请求驱动类型的轻量级Web框架,通过把Model,View,Controller分离,将web层进行职责解耦,把复杂的web应用分成逻辑清晰的几部分,简化开发,减少出错,方便组内开发人员之间的配合。 Spring MVC的工作原理了解嘛? image.png Springmvc的优点: (1)可以支持各种视图技术,而不仅仅局限于JSP; (2)与Spring框架集成(如IoC容器、AOP等); (3)清晰的角色分配:前端控制器(dispatcherServlet) , 请求到处理器映射(handlerMapping), 处理器适配器(HandlerAdapter), 视图解析器(ViewResolver)。 (4) 支持各种请求资源的映射策略。 Spring MVC的主要组件? (1)前端控制器 DispatcherServlet(不需要程序员开发) 作用:接收请求、响应结果,相当于转发器,有了DispatcherServlet 就减少了其它组件之间的耦合度。 (2)处理器映射器HandlerMapping(不需要程序员开发) 作用:根据请求的URL来查找Handler (3)处理器适配器HandlerAdapter 注意:在编写Handler的时候要按照HandlerAdapter要求的规则去编写,这样适配器HandlerAdapter才可以正确的去执行Handler。 (4)处理器Handler(需要程序员开发) (5)视图解析器 ViewResolver(不需要程序员开发) 作用:进行视图的解析,根据视图逻辑名解析成真正的视图(view) (6)视图View(需要程序员开发jsp) View是一个接口, 它的实现类支持不同的视图类型(jsp,freemarker,pdf等等) springMVC和struts2的区别有哪些? (1)springmvc的入口是一个servlet即前端控制器(DispatchServlet),而struts2入口是一个filter过虑器(StrutsPrepareAndExecuteFilter)。 (2)springmvc是基于方法开发(一个url对应一个方法),请求参数传递到方法的形参,可以设计为单例或多例(建议单例),struts2是基于类开发,传递参数是通过类的属性,只能设计为多例。 (3)Struts采用值栈存储请求和响应的数据,通过OGNL存取数据,springmvc通过参数解析器是将request请求内容解析,并给方法形参赋值,将数据和视图封装成ModelAndView对象,最后又将ModelAndView中的模型数据通过reques域传输到页面。Jsp视图解析器默认使用jstl。 SpringMVC怎么样设定重定向和转发的? (1)转发:在返回值前面加"forward:",譬如"forward:user.do?name=method4" (2)重定向:在返回值前面加"redirect:",譬如"redirect:http://www.baidu.com" SpringMvc怎么和AJAX相互调用的? 通过Jackson框架就可以把Java里面的对象直接转化成Js可以识别的Json对象。具体步骤如下 : (1)加入Jackson.jar (2)在配置文件中配置json的映射 (3)在接受Ajax方法里面可以直接返回Object,List等,但方法前面要加上@ResponseBody注解。 如何解决POST请求中文乱码问题,GET的又如何处理呢? (1)解决post请求乱码问题: 在web.xml中配置一个CharacterEncodingFilter过滤器,设置成utf-8; <filter> <filter-name>CharacterEncodingFilter</filter-name> <filter-class>org.springframework.web.filter.CharacterEncodingFilter</filter-class> <init-param> <param-name>encoding</param-name> <param-value>utf-8</param-value> </init-param> </filter> <filter-mapping> <filter-name>CharacterEncodingFilter</filter-name> <url-pattern>/*</url-pattern> </filter-mapping> (2)get请求中文参数出现乱码解决方法有两个: ①修改tomcat配置文件添加编码与工程编码一致,如下: <ConnectorURIEncoding="utf-8" connectionTimeout="20000" port="8080" protocol="HTTP/1.1" redirectPort="8443"/> ②另外一种方法对参数进行重新编码: String userName = new String(request.getParamter("userName").getBytes("ISO8859-1"),"utf-8") ISO8859-1是tomcat默认编码,需要将tomcat编码后的内容按utf-8编码。 Spring MVC的异常处理 ? 统一异常处理: Spring MVC处理异常有3种方式: (1)使用Spring MVC提供的简单异常处理器SimpleMappingExceptionResolver; (2)实现Spring的异常处理接口HandlerExceptionResolver 自定义自己的异常处理器; (3)使用@ExceptionHandler注解实现异常处理; 统一异常处理的博客:https://blog.csdn.net/ctwy291314/article/details/81983103 SpringMVC的控制器是不是单例模式,如果是,有什么问题,怎么解决? 是单例模式,所以在多线程访问的时候有线程安全问题,不要用同步,会影响性能的,解决方案是在控制器里面不能写成员变量。(此题目类似于上面Spring 中 第5题 有两种解决方案) SpringMVC常用的注解有哪些? @RequestMapping:用于处理请求 url 映射的注解,可用于类或方法上。用于类上,则表示类中的所有响应请求的方法都是以该地址作为父路径。 @RequestBody:注解实现接收http请求的json数据,将json转换为java对象。 @ResponseBody:注解实现将conreoller方法返回对象转化为json对象响应给客户。 SpingMvc中的控制器的注解一般用那个,有没有别的注解可以替代? 一般用@Controller注解,也可以使用@RestController,@RestController注解相当于@ResponseBody + @Controller,表示是表现层,除此之外,一般不用别的注解代替。 如果在拦截请求中,我想拦截get方式提交的方法,怎么配置? 可以在@RequestMapping注解里面加上method=RequestMethod.GET。 怎样在方法里面得到Request,或者Session? 直接在方法的形参中声明request,SpringMVC就自动把request对象传入。 如果想在拦截的方法里面得到从前台传入的参数,怎么得到? 直接在形参里面声明这个参数就可以,但必须名字和传过来的参数一样。 如果前台有很多个参数传入,并且这些参数都是一个对象的,那么怎么样快速得到这个对象? 直接在方法中声明这个对象,SpringMVC就自动会把属性赋值到这个对象里面。 SpringMVC中函数的返回值是什么? 返回值可以有很多类型,有String, ModelAndView。ModelAndView类把视图和数据都合并的一起的。 SpringMVC用什么对象从后台向前台传递数据的? 通过ModelMap对象,可以在这个对象里面调用put方法,把对象加到里面,前台就可以拿到数据。 怎么样把ModelMap里面的数据放入Session里面? 可以在类上面加上@SessionAttributes注解,里面包含的字符串就是要放入session里面的key。 SpringMvc里面拦截器是怎么写的: 有两种写法,一种是实现HandlerInterceptor接口,另外一种是继承适配器类,接着在接口方法当中,实现处理逻辑;然后在SpringMvc的配置文件中配置拦截器即可: <!-- 配置SpringMvc的拦截器 --> <mvc:interceptors> <!-- 配置一个拦截器的Bean就可以了 默认是对所有请求都拦截 --> <bean id="myInterceptor" class="com.zwp.action.MyHandlerInterceptor"></bean> <!-- 只针对部分请求拦截 --> <mvc:interceptor> <mvc:mapping path="/modelMap.do" /> <bean class="com.zwp.action.MyHandlerInterceptorAdapter" /> </mvc:interceptor> </mvc:interceptors> 注解原理: 注解本质是一个继承了Annotation的特殊接口,其具体实现类是Java运行时生成的动态代理类。我们通过反射获取注解时,返回的是Java运行时生成的动态代理对象。通过代理对象调用自定义注解的方法,会最终调用AnnotationInvocationHandler的invoke方法。该方法会从memberValues这个Map中索引出对应的值。而memberValues的来源是Java常量池 三、Mybatis篇 什么是MyBatis? MyBatis是一个可以自定义SQL、存储过程和高级映射的持久层框架。 讲下MyBatis的缓存 MyBatis的缓存分为一级缓存和二级缓存,一级缓存放在session里面,默认就有, 二级缓存放在它的命名空间里,默认是不打开的,使用二级缓存属性类需要实现Serializable序列化接口, 可在它的映射文件中配置<cache/> Mybatis是如何进行分页的?分页插件的原理是什么? 1)Mybatis使用RowBounds对象进行分页,也可以直接编写sql实现分页,也可以使用Mybatis的分页插件。 2)分页插件的原理:实现Mybatis提供的接口,实现自定义插件,在插件的拦截方法内拦截待执行的sql,然后重写sql。 举例:select * from student,拦截sql后重写为:select t.* from (select * from student)t limit 0,10 简述Mybatis的插件运行原理,以及如何编写一个插件? 1)Mybatis仅可以编写针对ParameterHandler、ResultSetHandler、StatementHandler、 Executor这4种接口的插件,Mybatis通过动态代理, 为需要拦截的接口生成代理对象以实现接口方法拦截功能, 每当执行这4种接口对象的方法时,就会进入拦截方法, 具体就是InvocationHandler的invoke方法,当然, 只会拦截那些你指定需要拦截的方法。 2)实现Mybatis的Interceptor接口并复写intercept方法, 然后在给插件编写注解,指定要拦截哪一个接口的哪些方法即可, 记住,别忘了在配置文件中配置你编写的插件。 Mybatis动态sql是做什么的?都有哪些动态sql?能简述一下动态sql的执行原理不? 1)Mybatis动态sql可以让我们在Xml映射文件内, 以标签的形式编写动态sql,完成逻辑判断和动态拼接sql的功能。 2)Mybatis提供了9种动态sql标签:trim|where|set|foreach|if|choose|when|otherwise|bind。 3)其执行原理为,使用OGNL从sql参数对象中计算表达式的值, 根据表达式的值动态拼接sql,以此来完成动态sql的功能。 #{}和${}的区别是什么? 1)#{}是预编译处理,${}是字符串替换。 2)Mybatis在处理#{}时,会将sql中的#{}替换为?号,调用PreparedStatement的set方法来赋值(有效的防止SQL注入); 3)Mybatis在处理${}时,就是把${}替换成变量的值。 为什么说Mybatis是半自动ORM映射工具?它与全自动的区别在哪里? Hibernate属于全自动ORM映射工具, 使用Hibernate查询关联对象或者关联集合对象时, 可以根据对象关系模型直接获取,所以它是全自动的。 而Mybatis在查询关联对象或关联集合对象时, 需要手动编写sql来完成,所以,称之为半自动ORM映射工具。 Mybatis是否支持延迟加载?如果支持,它的实现原理是什么? 1)Mybatis仅支持association关联对象和collection关联集合对象的延迟加载, association指的就是一对一,collection指的就是一对多查询。 在Mybatis配置文件中, 可以配置是否启用延迟加载lazyLoadingEnabled=true|false。 2)它的原理是,使用CGLIB创建目标对象的代理对象, 当调用目标方法时,进入拦截器方法, 比如调用a.getB.getName, 拦截器invoke方法发现a.getB是null值, 那么就会单独发送事先保存好的查询关联B对象的sql, 把B查询上来,然后调用a.setB(b), 于是a的对象b属性就有值了, 接着完成a.getB.getName方法的调用。 这就是延迟加载的基本原理。 MyBatis与Hibernate有哪些不同? 1)Mybatis和hibernate不同,它不完全是一个ORM框架, 因为MyBatis需要程序员自己编写Sql语句, 不过mybatis可以通过XML或注解方式灵活配置要运行的sql语句, 并将java对象和sql语句映射生成最终执行的sql, 最后将sql执行的结果再映射生成java对象。 2)Mybatis学习门槛低,简单易学,程序员直接编写原生态sql, 可严格控制sql执行性能,灵活度高,非常适合对关系数据模型要求不高的软件开发, 例如互联网软件、企业运营类软件等,因为这类软件需求变化频繁, 一但需求变化要求成果输出迅速。但是灵活的前提是mybatis无法做到数据库无关性, 如果需要实现支持多种数据库的软件则需要自定义多套sql映射文件,工作量大。 3)Hibernate对象/关系映射能力强,数据库无关性好, 对于关系模型要求高的软件(例如需求固定的定制化软件) 如果用hibernate开发可以节省很多代码,提高效率。 但是Hibernate的缺点是学习门槛高,要精通门槛更高, 而且怎么设计O/R映射,在性能和对象模型之间如何权衡, 以及怎样用好Hibernate需要具有很强的经验和能力才行。 总之,按照用户的需求在有限的资源环境下只要能做出维护性、 扩展性良好的软件架构都是好架构,所以框架只有适合才是最好。 MyBatis的好处是什么? 1)MyBatis把sql语句从Java源程序中独立出来,放在单独的XML文件中编写, 给程序的维护带来了很大便利。 2)MyBatis封装了底层JDBC API的调用细节,并能自动将结果集转换成Java Bean对象, 大大简化了Java数据库编程的重复工作。 3)因为MyBatis需要程序员自己去编写sql语句, 程序员可以结合数据库自身的特点灵活控制sql语句, 因此能够实现比Hibernate等全自动orm框架更高的查询效率,能够完成复杂查询。 简述Mybatis的Xml映射文件和Mybatis内部数据结构之间的映射关系? Mybatis将所有Xml配置信息都封装到All-In-One重量级对象Configuration内部。 在Xml映射文件中,<parameterMap>标签会被解析为ParameterMap对象, 其每个子元素会被解析为ParameterMapping对象。 <resultMap>标签会被解析为ResultMap对象, 其每个子元素会被解析为ResultMapping对象。 每一个<select>、<insert>、<update>、<delete> 标签均会被解析为MappedStatement对象, 标签内的sql会被解析为BoundSql对象。 什么是MyBatis的接口绑定,有什么好处? 接口映射就是在MyBatis中任意定义接口,然后把接口里面的方法和SQL语句绑定, 我们直接调用接口方法就可以,这样比起原来了SqlSession提供的方法我们可以有更加灵活的选择和设置. 接口绑定有几种实现方式,分别是怎么实现的? 接口绑定有两种实现方式,一种是通过注解绑定,就是在接口的方法上面加 上@Select@Update等注解里面包含Sql语句来绑定, 另外一种就是通过xml里面写SQL来绑定,在这种情况下, 要指定xml映射文件里面的namespace必须为接口的全路径名. 什么情况下用注解绑定,什么情况下用xml绑定? 当Sql语句比较简单时候,用注解绑定;当SQL语句比较复杂时候,用xml绑定,一般用xml绑定的比较多 MyBatis实现一对一有几种方式?具体怎么操作的? 有联合查询和嵌套查询,联合查询是几个表联合查询,只查询一次, 通过在resultMap里面配置association节点配置一对一的类就可以完成; 嵌套查询是先查一个表,根据这个表里面的结果的外键id, 去再另外一个表里面查询数据,也是通过association配置, 但另外一个表的查询通过select属性配置。 Mybatis能执行一对一、一对多的关联查询吗?都有哪些实现方式,以及它们之间的区别? 能,Mybatis不仅可以执行一对一、一对多的关联查询, 还可以执行多对一,多对多的关联查询,多对一查询, 其实就是一对一查询,只需要把selectOne修改为selectList即可; 多对多查询,其实就是一对多查询,只需要把selectOne修改为selectList即可。 关联对象查询,有两种实现方式,一种是单独发送一个sql去查询关联对象, 赋给主对象,然后返回主对象。另一种是使用嵌套查询,嵌套查询的含义为使用join查询, 一部分列是A对象的属性值,另外一部分列是关联对象B的属性值, 好处是只发一个sql查询,就可以把主对象和其关联对象查出来。 MyBatis里面的动态Sql是怎么设定的?用什么语法? MyBatis里面的动态Sql一般是通过if节点来实现,通过OGNL语法来实现, 但是如果要写的完整,必须配合where,trim节点,where节点是判断包含节点有 内容就插入where,否则不插入,trim节点是用来判断如果动态语句是以and 或or 开始,那么会自动把这个and或者or取掉。 Mybatis是如何将sql执行结果封装为目标对象并返回的?都有哪些映射形式? 第一种是使用<resultMap>标签,逐一定义列名和对象属性名之间的映射关系。 第二种是使用sql列的别名功能,将列别名书写为对象属性名, 比如T_NAME AS NAME,对象属性名一般是name,小写, 但是列名不区分大小写,Mybatis会忽略列名大小写,
-
F#探险之旅(二):函数式编程(上)-函数式编程范式简介 F#主要支持三种编程范式:函数式编程(Functional Programming,FP)、命令式编程(Imperative Programming)和面向对象(Object-Oriented,OO)的编程。回顾它们的历史,FP是最早的一种范式,第一种FP语言是IPL,产生于1955年,大约在Fortran一年之前。第二种FP语言是Lisp,产生于1958,早于Cobol一年。Fortan和Cobol都是命令式编程语言,它们在科学和商业领域的迅速成功使得命令式编程在30多年的时间里独领风骚。而产生于1970年代的面向对象编程则不断成熟,至今已是最流行的编程范式。有道是“*代有语言出,各领风骚数十年”。 尽管强大的FP语言(SML,Ocaml,Haskell及Clean等)和类FP语言(APL和Lisp是现实世界中最成功的两个)在1950年代就不断发展,FP仍停留在学院派的“象牙塔”里;而命令式编程和面向对象编程则分别凭着在商业领域和企业级应用的需要占据领先。今天,FP的潜力终被认识——它是用来解决更复杂的问题的(当然更简单的问题也不在话下)。 纯粹的FP将程序看作是接受参数并返回值的函数的集合,它不允许有副作用(side effect,即改变了状态),使用递归而不是循环进行迭代。FP中的函数很像数学中的函数,它们都不改变程序的状态。举个简单的例子,一旦将一个值赋给一个标识符,它就不会改变了,函数不改变参数的值,返回值是全新的值。 FP的数学基础使得它很是优雅,FP的程序看起来往往简洁、漂亮。但它无状态和递归的天性使得它在处理很多通用的编程任务时没有其它的编程范式来得方便。但对F#来说这不是问题,它的优势之一就是融合了多种编程范式,允许开发人员按照需要采用最好的范式。 关于FP的更多内容建议阅读一下这篇文章:Why Functional Programming Matters(中文版)。F#中的函数式编程 从现在开始,我将对F#中FP相关的主要语言结构逐一进行介绍。标识符(Identifier) 在F#中,我们通过标识符给值(value)取名字,这样就可以在后面的程序中引用它。通过关键字let定义标识符,如: let x = 42 这看起来像命令式编程语言中的赋值语句,两者有着关键的不同。在纯粹的FP中,一旦值赋给了标识符就不能改变了,这也是把它称为标识符而非变量(variable)的原因。另外,在某些条件下,我们可以重定义标识符;在F#的命令式编程范式下,在某些条件下标识符的值是可以修改的。 标识符也可用于引用函数,在F#中函数本质上也是值。也就是说,F#中没有真正的函数名和参数名的概念,它们都是标识符。定义函数的方式与定义值是类似的,只是会有额外的标识符表示参数: let add x y = x + y 这里共有三个标识符,add表示函数名,x和y表示它的参数。关键字和保留字关键字是指语言中一些标记,它们被编译器保留作特殊之用。在F#中,不能用作标识符或类型的名称(后面会讨论“定义类型”)。它们是: abstract and as asr assert begin class default delegate do donedowncast downto elif else end exception extern false finally forfun function if in inherit inline interface internal land lazy letlor lsr lxor match member mod module mutable namespace new nullof open or override private public rec return sig static structthen to true try type upcast use val void when while with yield 保留字是指当前还不是关键字,但被F#保留做将来之用。可以用它们来定义标识符或类型名称,但编译器会报告一个警告。如果你在意程序与未来版本编译器的兼容性,最好不要使用。它们是: atomic break checked component const constraint constructor continue eager event external fixed functor global include method mixinobject parallel process protected pure sealed trait virtual volatile 文字值(Literals) 文字值表示常数值,在构建计算代码块时很有用,F#提供了丰富的文字值集。与C#类似,这些文字值包括了常见的字符串、字符、布尔值、整型数、浮点数等,在此不再赘述,详细信息请查看F#手册。 与C#一样,F#中的字符串常量表示也有两种方式。一是常规字符串(regular string),其中可包含转义字符;二是逐字字符串(verbatim string),其中的(")被看作是常规的字符,而两个双引号作为双引号的转义表示。下面这个简单的例子演示了常见的文字常量表示: let message = "Hello World"r"n!" // 常规字符串let dir = @"C:"FS"FP" // 逐字字符串let bytes = "bytes"B // byte 数组let xA = 0xFFy // sbyte, 16进制表示let xB = 0o777un // unsigned native-sized integer,8进制表示let print x = printfn "%A" xlet main = print message; print dir; print bytes; print xA; print xB; main Printf函数通过F#的反射机制和.NET的ToString方法来解析“%A”模式,适用于任何类型的值,也可以通过F#中的print_any和print_to_string函数来完成类似的功能。值和函数(Values and Functions) 在F#中函数也是值,F#处理它们的语法也是类似的。 let n = 10let add a b = a + blet addFour = add 4let result = addFour n printfn "result = %i" result 可以看到定义值n和函数add的语法很类似,只不过add还有两个参数。对于add来说a + b的值自动作为其返回值,也就是说在F#中我们不需要显式地为函数定义返回值。对于函数addFour来说,它定义在add的基础上,它只向add传递了一个参数,这样对于不同的参数addFour将返回不同的值。考虑数学中的函数概念,F(x, y) = x + y,G(y) = F(4, y),实际上G(y) = 4 + y,G也是一个函数,它接收一个参数,这个地方是不是很类似?这种只向函数传递部分参数的特性称为函数的柯里化(curried function)。 当然对某些函数来说,传递部分参数是无意义的,此时需要强制提供所有参数,可是将参数括起来,将它们转换为元组(tuple)。下面的例子将不能编译通过: let sub(a, b) = a - blet subFour = sub 4 必须为sub提供两个参数,如sub(4, 5),这样就很像C#中的方法调用了。 对于这两种方式来说,前者具有更高的灵活性,一般可优先考虑。 如果函数的计算过程中需要定义一些中间值,我们应当将这些行进行缩进: let halfWay a b = let dif = b - a let mid = dif / 2 mid + a 需要注意的是,缩进时要用空格而不是Tab,如果你不想每次都按几次空格键,可以在VS中设置,将Tab字符自动转换为空格;虽然缩进的字符数没有限制,但一般建议用4个空格。而且此时一定要用在文件开头添加#light指令。作用域(Scope)作用域是编程语言中的一个重要的概念,它表示在何处可以访问(使用)一个标识符或类型。所有标识符,不管是函数还是值,其作用域都从其声明处开始,结束自其所处的代码块。对于一个处于最顶层的标识符而言,一旦为其赋值,它的值就不能修改或重定义了。标识符在定义之后才能使用,这意味着在定义过程中不能使用自身的值。 let defineMessage = let message = "Help me" print_endline message // error 对于在函数内部定义的标识符,一般而言,它们的作用域会到函数的结束处。 但可使用let关键字重定义它们,有时这会很有用,对于某些函数来说,计算过程涉及多个中间值,因为值是不可修改的,所以我们就需要定义多个标识符,这就要求我们去维护这些标识符的名称,其实是没必要的,这时可以使用重定义标识符。但这并不同于可以修改标识符的值。你甚至可以修改标识符的类型,但F#仍能确保类型安全。所谓类型安全,其基本意义是F#会避免对值的错误操作,比如我们不能像对待字符串那样对待整数。这个跟C#也是类似的。 let changeType = let x = 1 let x = "change me" let x = x + 1 print_string x 在本例的函数中,第一行和第二行都没问题,第三行就有问题了,在重定义x的时候,赋给它的值是x + 1,而x是字符串,与1相加在F#中是非法的。 另外,如果在嵌套函数中重定义标识符就更有趣了。 let printMessages = let message = "fun value" printfn "%s" message; let innerFun = let message = "inner fun value" printfn "%s" message innerFun printfn "%s" message printMessages 打印结果: fun value inner fun valuefun value 最后一次不是inner fun value,因为在innerFun仅仅将值重新绑定而不是赋值,其有效范围仅仅在innerFun内部。递归(Recursion)递归是编程中的一个极为重要的概念,它表示函数通过自身进行定义,亦即在定义处调用自身。在FP中常用于表达命令式编程的循环。很多人认为使用递归表示的算法要比循环更易理解。 使用rec关键字进行递归函数的定义。看下面的计算阶乘的函数: let rec factorial x = match x with | x when x < 0 -> failwith "value must be greater than or equal to 0" | 0 -> 1 | x -> x * factorial(x - 1) 这里使用了模式匹配(F#的一个很棒的特性),其C#版本为: public static long Factorial(int n) { if (n < 0) { throw new ArgumentOutOfRangeException("value must be greater than or equal to 0"); } if (n == 0) { return 1; } return n * Factorial (n - 1); } 递归在解决阶乘、Fibonacci数列这样的问题时尤为适合。但使用的时候要当心,可能会写出不能终止的递归。匿名函数(Anonymous Function) 定义函数的时候F#提供了第二种方式:使用关键字fun。有时我们没必要给函数起名,这种函数就是所谓的匿名函数,有时称为lambda函数,这也是C#3.0的一个新特性。比如有的函数仅仅作为一个参数传给另一个函数,通常就不需要起名。在后面的“列表”一节中你会看到这样的例子。除了fun,我们还可以使用function关键字定义匿名函数,它们的区别在于后者可以使用模式匹配(本文后面将做介绍)特性。看下面的例子: let x = (fun x y -> x + y) 1 2let x1 = (function x -> function y -> x + y) 1 2let x2 = (function (x, y) -> x + y) (1, 2) 我们可优先考虑fun,因为它更为紧凑,在F#类库中你能看到很多这样的例子。 注意:本文中的代码均在F# 1.9.4.17版本下编写,在F# CTP 1.9.6.0版本下可能不能通过编译。 F#系列随笔索引页面