Oracle 中XML处理函数介绍
分类:计算机教程

1. EXTRACT

 

该函数用于返回XML节点路径下的相应内容

F.1字符函数——返回字符值

示例:

 

复制代码 代码如下:SELECT extract(value(a),'/root/main') data FROM xmltable a ;

(chr,concat,initcap,lower,lpad/rpad,nls_initcap,nls_lower,nls_upper,regexp_replace,regexp_substr,replace,trim/ltrim/rtrim,soundex,substr,translate,upper)

2. EXTRACTVALUE

 

该函数用于确定特定的XML节点的路径是否存在,返回0表示节点不存在,返回1表示节点存在。

说明:可以sql和plsql中使用

示例:

 

复制代码 代码如下:SELECT existsnode(value(a),'/root/main/住院号') data FROM xmltable a;

 

4. SYS_DBURIGEN({column|attribute})

CHR

该函数用于根据列或者属性生成类型为DBUrlType的URL

语法:  chr(x)

示例:

功能:给出整数X,返回对应的ASCII码字符。CHR和ASCII是一对反函数。

复制代码 代码如下:SELECT sys_dburigen(a.DISPLAYNAME) aa FROM v_dept a WHERE a.DISPLAYNAME='矽肺病医院'

SQL> select chr(54740) 别名1,chr(65) 别名2 from dual;

5. SYS_XMLAGG

 

该函数用于汇总所有XML文档,并生成一个XML文档。

别名1  别名2

示例:

赵       A

复制代码 代码如下:SELECT SYS_XMLAGG(SYS_XMLGEN(a.order_content)) xml_contentfrom doc_clinic_order a, clinics_item b, med_frequencydict cwhere a.patient_id = 'bd4b425e-a409-4b28-890d-d1d668fcf725'and a.parentid = '0'and a.route = b.item_id( )and a.frequency = c.frequency_id( )


6. SYS_XMLGEN(expr[,fmt])

 

该函数用于根据数据库表的行和列生成XMLType实例。

CONCAT

7. XMLAGG(XMLType_instance[ORDER BY sort_list])

语法: CONCAT(string1,string2)

该函数用于汇总多个XML块,并生成XML文档。

功能:连接两个字符串

示例:

SQL> select concat('010-','88888888')||'23'  连接 from dual;

复制代码 代码如下:select xmlagg(xmlelement("row",xmlforest(a.frequency_id as "频次ID", a.frequency_name as "频次名称")))[]from med_frequencydict a

 

8. XMLFOREST(value_expr1[,value_expr2],...)

连接

该函数用于返回XML块

010-8888888823

9. XMLELEMENT(identifier[,xml_attribute_clause][,value_expr])


该函数用于返回XMLType的实例。其中参数identifier用于指定元素名,参数xml_attribute_clause用于指定元素属性子句,参数value_expr用于指定元素值。

 

示例:

INITCAP

复制代码 代码如下:SELECT xmlelement("row",xmlcolattval(a.frequency_code AS "编码",a.frequency_name as "名称")) xmlfrom med_frequencydict awhere a.frequency_code = 'BID'

语法:INITCAP(string)

10. XMLCOLATTVAL(value_expr[,value_expr2],...)

功能:返回字符单词首字母大写,其余小写,单词用空格和非字母字符分隔。

该函数用于生成XML块,参数value_expr用于指定列名或者别名作为属性名。

SQL> select initcap('smith hEllo') upp from dual;

示例:

 

复制代码 代码如下:SELECT xmlelement("row",xmlcolattval(a.frequency_code AS "编码", a.frequency_name as "名称")) xmlfrom med_frequencydict a where a.frequency_code = 'BID'

UPP

11. XMLCONCAT(XMLType_instance1[,XMLType_instance2],...)

Smith Hello

该函数用于连接多个XMLType实例,并生成新的XMLType实例。

 

示例:


复制代码 代码如下:select xmlconcat(xmltype('1'), xmltype('2'), xmltype('3'))from dual

 

12. XMLSEQUENCE(xmltype_instance)

LOWER

该函数用于返回XMLType实例中顶级节点以下的VARRAY元素。

    语法:LOWER(string)

示例:

     功能:所以字母小写

复制代码 代码如下:select extractvalue(column_value, '/row/医嘱内容') 医嘱内容 from table(xmlsequence(extract((select value(a) from xmltable a where rownum = 1),'/root/detail/row')))

     SQL> select lower('AaBbCcDd') AaBbCcDd from dual;

13. UPDATEXML(XMLType_instance,Xpath_string,value_expr)

 

该函数用于更新特定XMLType实例相应的节点路径的内容

     AaBbCcDd

示例:

     aabbccdd

复制代码 代码如下:select updatexml(xmltype('1名字12名字2'), '/root/row[no=2]/name', '新名字')from dual


14. XMLTRANSFORM(xmltype_instance,xsl_ss)

 

该函数用于将XMLType实例按照XSL样式进行转换,并生成新的XMLType实例

LPAD/RPAD

15. DELETEXML(XMLType_instance,Xpath_string)

语法:LPAD/RPAD(string1,x[,string2])

该函数用于删除特定XMLType实例相应的节点路径的内容

功能:在string1字符左边或右边粘贴数个string2字符,直到字符总字节数达到x字节。string2默认为空格。

16.XML转换表

如果string2的长度要比X字符少,就按照需要进行复制。如果string2多于X字符,则仅string2前面的X各字符被使用。如果string1长度大于x,则返回string1左端x个字符。

复制代码 代码如下:SELECT * FROM XMLTABLE('$SQ/root/detail/row' PASSING (select value(a) from xmltable a) AS SQ COLUMNS 医嘱ID VARCHAR2(50) PATH '/row/医嘱ID', 医嘱内容 VARCHAR2(200) PATH '/row/医嘱内容', 频次 VARCHAR2(200) PATH '/row/频次', 用法 VARCHAR2(200) PATH '/row/用法')order by 医嘱ID

 

基本就是这样了,小编细心帮大家整理了一下代码格式,希望能帮到大家。

RPAD  在列的右边粘贴字符

LPAD  在列的左边粘贴字符

SQL> select lpad(rpad('gao',10,'*'),17,'*')from dual;

 

LPAD(RPAD('GAO',1

*******gao*******

不够字符则用*来填满


 

NLS_INITCAP

语法:NLS_INITCAP(string[,nlsparams])

功能:返回字符串每个单词第一个字母大写而单词中的其他字母小写的string,nlsparams

指定了不同于该会话缺省值的不同排序序列。如果不指定参数,则功能和INITCAP相同。Nlsparams可以使用的形式是:‘NLS_SORT=sort’ 这里sort制订了一个语言排序序列。


 

NLS_LOWER

语法:NLS_LOWER(string[,nlsparams])

功能:返回字符串中的所有字母都是小写形式的string。不是字母的字符不变。

Nlsparams参数的形式与用途和NLS_INITCAP中的nlsparams参数是相同的。如果nlsparams没有被包含,那么NLS_LOWER所作的处理和LOWER相同。


 

男篮世界杯赔率,NLS_UPPER

语法:NLS_UPPER(string[,nlsparams])

功能:返回字符串中的所有字母都是大写的形式的string。不是字母的字符不变。nlsparams参数的形式与用途和NLS_INITCAP中的相同。如果没有设定参数,则NLS_UPPER功能和UPPER相同。

使用位置:过程性语句和SQL语句。


 

REGEXP_REPLACE

语法:REGEXP_REPLACE(str1,pattem[,str2[,pos[,occ[,par]]]])

功能:10g新增函数,扩展了REPLACE函数的功能,并且用于按照特定正则表达式的规则替换字符串。其中参数str1指定源字符表达式,pattem指定正则表达式,str2指定替换字符串,pos指定起始搜索位置,occ指定替换出现的第几个字符串,par指定默认匹配操作的文本串。

 

select REGEXP_REPLACE(a,’(.)’,’1’) a from count;

A r g e n t i n a

 

体会NVL为DECODE,只支持NVL()内不再有其它括号()

select a,

       instr(upper(a), 'NVL(', 1) a3,

       instr(upper(a), ')',instr(upper(a), 'NVL(', 1),1) a4,

       substr(a,instr(upper(a), 'NVL(', 1),instr(upper(a), ')',instr(upper(a), 'NVL(', 1),1)-instr(upper(a), 'NVL(', 1) 1) a41,

       substr(a,instr(upper(a), 'NVL(', 1) 4,instr(upper(a), ')',instr(upper(a), 'NVL(', 1), 1)-instr(upper(a), 'NVL(', 1)-4) a5,

       REGEXP_REPLACE(

       substr(a,instr(upper(a), 'NVL(', 1) 4,instr(upper(a), ')',instr(upper(a), 'NVL(', 1), 1)-instr(upper(a), 'NVL(', 1)-4),

       '(.*),(.*)','2,1'

       ) a6,

       REGEXP_REPLACE(

       substr(a,instr(upper(a), 'NVL(', 1) 4,instr(upper(a), ')',instr(upper(a), 'NVL(', 1), 1)-instr(upper(a), 'NVL(', 1)-4),

       '(.*),(.*)','decode(1,null,2,'''',2,1)'

       ) a7,

       substr(a,1,instr(upper(a), 'NVL(', 1)-1)||REGEXP_REPLACE(

       substr(a,instr(upper(a), 'NVL(', 1) 4,instr(upper(a), ')',instr(upper(a), 'NVL(', 1), 1)-instr(upper(a), 'NVL(', 1)-4),

       '(.*),(.*)','decode(1,null,2,'''',2,1)'

       )||substr(a,instr(upper(a), ')',instr(upper(a), 'NVL(', 1), 1) 1) a8

  from temp_liut a;


 

判断是否是数字

regexp_replace(a, 'd ', '') is null

 

REGEXP_SUBSTR

语法:REGEXP_SUBSTR(str1,pattem [,pos[,occ[,par]]])

功能:10g新增函数,扩展了SUBSTR函数的功能,并且用于按照特定表达式的规则返回字符串的子串。其中参数str1指定源字符表达式,pattem指定规则表达式, pos指定起始搜索位置,occ指定替换出现的第几个字符串,par指定默认匹配操作的文本串。

 

Select REGEXP_SUBSTR(‘http://www.oracle.com/products’,’http://([[:alnum:]] \.?)’) a from dual;

 

a

http://www.oracle.com/


 

REPLACE

语法:REPLACE(string,search_str[,replace_str])

功能:把string中的所有的子字符串search_str用可选的replace_str替换,如果没有指定replace_str,所有的string中的子字符串search_str都将被删除。REPLACE是TRANSLATE所提供的功能的一个子集。

 

REPLACE('string','s1','s2')

string   希望被替换的字符或变量

s1       被替换的字符串

s2       要替换的字符串

SQL> select replace('he lohe you','he','i') from dual;

 

replace('he lohe you','he','i')

i loi you


 

TRIM/LTRIM/RTRIM

语法1:LTRIM/RTRIM(string1,[string2])

语法2:trim([string2] from string1)

语法1功能:中删除从左/右边算起出现在string1中的字符string2,string2如果是多个字符则逐个单字符比对删除,tring2被缺省设置为单个的空格。当遇到不在string2中的第一个字符,结果就被返回了;

语法2功能:删除左右两边出现在string1中的字符string2,tring2必须为单字符,否则报错。

 

select ltrim(rtrim('   gao qian jing   ',' '),' ') from dual;

gao qian jing

 

select ltrim('abaaaabbbcda','ab') from dual;

cda

 

select trim('a' from 'abacda') from dual;

bacd


 

SOUNDEX

语法:  SOUNDEX(string)

功能:  返回string的声音表示形式.这对于比较两个拼写不同但是发音类似的单词而言很有帮助,如果字符发音相同,则返回的结果会一致.

 

SOUNDEX 返回一个与给定的字符串读音相同的字符串

SQL> create table table1(xm varchar(8));

SQL> insert into table1 values('weather');

SQL> insert into table1 values('wether');

SQL> insert into table1 values('gao');

 

SQL> select xm from table1 where soundex(xm)=soundex('weather');

 

XM

weather

wether


 

SUBSTR

语法:  SUBSTR(string,a[,b])

功能:截取字符串,从第a个开始取b个字符,这个务必要注意,是字符。 vachar2最长4000个字节,GBK编码中一个中文字符占2个字节,韩文字符占4个字节,如果string是date或者number的数据类型,会自动转化为varchar2。

 

SQL> select substr('13088888888',3,8) 截取字符串 from dual;

截取字符串

08888888

 

select SUBSTR(t.a,4),a from temp_liut t;

JAN-00  04-jan-00


 

TRANSLATE

语法:  TRANSLATE(string,from_str,to_str)

功能:  将字符string按照from_str与to_str的对应规则进行处理,返回将所出现的from_str中的每个字符替换为to_str中的相应字符以后的string. TRANSLATE是REPLACE所提供的功能的一个超集.如果from_str比to_str长,那么在from_str中而不在to_str中而外的字符将从string中被删除,因为它们没有相应的替换字符. to_str不能为空.Oracle把空字符串认为是NULL,并且如果TRANSLATE中的任何参数为NULL,那么结果也是NULL.

 

Select TRANSLATE('2abc2234','01234abcde','99999XXXXX') tra from dual

9XXX9999

 

select replace(TRANSLATE('as中国fd1234','1234567890','0000000000'),'0') from dual;

 

查找字符串',01234,2342,2,'中逗号出现次数

select length(translate(',01234,2342,2,', 'a0123456789', ' ')) from dual;

 

判断字符串是否是数字

replace(translate(a, '0123456789', '0'),'0') is null

regexp_replace(a, 'd ', '') is null

 

UPPER

语法: UPPER(string)

功能: 所有字母大写.(不是字母的字符不变.如果string是CHAR数据类型的,那么结果也是CHAR类型的.如果string是VARCHAR2类型的,那么结果也是VARCHAR2类型的).

 SQL> select upper('AaBbCcDd') upper from dual;

 

UPPER

AABBCCDD


 

 

F.2    字符函数——返回数字

 

(ascii,instr,instrb,length,lengthb,nls_sort)

 

说明:可以sql和plsql中使用

 

 

ASCII

语法:  ASCII(string)

功能: 返回string字符串首字符的十进制表示ascii码值。 CHR和ASCII是互为相反的函数.CHR得到给定字符编码的响应字符. ASCII得到给定字符的字符编码.

 

SQL> select ascii('A') A,ascii('a') a,ascii('0') zero,ascii(' ') space from dual;

 

A         A      ZERO     SPACE

65        97        48        32


 

INSTR

语法:  INSTR(str1, str2[,a,b])

功能:  得到在str1中包含str2的位置. a>0,str1时从左边开始检查的,开始的位置为a;a<0,那么str1是从右边开始进行扫描的,开始的位置为a。第b次出现的位置将被返回. a和b都缺省设置为1,这将会返回在string1中第一次出现string2的位置.如果string2在a和b的规定下没有找到,那么返回0.位置的计算是相对于string1的开始位置的,不管a和b的取值是多少.

 

INSTR(C1,C2,I,J) 在一个字符串中搜索指定的字符,返回发现指定的字符的位置;

C1    被搜索的字符串

C2    希望搜索的字符串

I     搜索的开始位置,默认为1(如果为负数会从后向前搜索)

J     出现的位置,默认为1

SQL> select instr('oracle traning','ra',1,2) instring from dual;

 

INSTRING

9


 

INSTRB

语法:  INSTRB(string1, string2[a,[b]])

功能:  和INSTR相同,只是操作的对参数字符使用的位置的是字节.


 

LENGTH

语法:  LENGTH(string)

功能:  返回字符串的长度,特别注意的,对于空的字段,返回为空,而不是0。

 

SELECT   LENGTH (' 130 ') 返回字符串长度  FROM DUAL;

 

返回字符串长度

5


 

LENGTHB

语法:  LENGTHB(string)

功能:  返回以字节为单位的string的长度.对于单字节字符集LENGTHB和LENGTH是一样的.


 

NLS_SORT

语法: NLS_SORT(string[,nlsparams])

功能: 得到用于排序string的字符串字节.所有的数值都被转换为字节字符串,这样在不同数据库之间就保持了一致性. Nlsparams的作用和NLS_INITCAP中的相同.如果忽略参数,会话使用缺省排序.


 

 

F.3    数学函数

 

(abs,acos,asin,atan,atan2,ceil,cos,cosh,exp,floor,ln,log,mod,power,round,sign,sin,sinh,sqrt,tan,tanh,trunc)

 

说明:数学函数的输入和输出都是数字型,并且多数函数精确到38位。函数coscoshexplnlogsinsinhsqrttantanh精确到36位,acosasinatanatan2精确到30为。数学函数可以在sql语句和plsql块中引用。

 

 

ABS

语法:   ABS(x)

功能:   得到x的绝对值.

 

SQL> select abs(100),abs(-100) from dual;

 

ABS(100) ABS(-100)

100       100


 

ACOS

语法:  ACOS(x)

功能:  返回x的反余弦值. 输入x应该从-1到1之间的数,结果在0到pi之间,输出以弧度为单位.

 

SQL> select acos(-1) from dual;

 

ACOS(-1)

3.1415927


 

ASIN

语法:  ASIN(x)

功能:  返回x的反正弦值. X的范围应该是-1到1之间,返回的结果在-pi/2到pi/2之间,以弧度为单位.

 

SQL> select asin(0.5) from dual;

 

ASIN(0.5)

.52359878


 

ATAN

语法:  ATAN(x)

功能:  计算x的反正切值.返回值在-pi/2到pi/2之间,单位是弧度.

 

SQL> select atan(1) from dual;

 

ATAN(1)

.78539816


 

ATAN2

语法:  ATAN2(x,y)

功能: 返回x除以y的反正切值.结果在负的pi/2到正的pi/2之间,单位是弧度.


 

CEIL

语法:  CEIL(x)

功能:  计算大于或等于x的最小整数值.

 

SQL> select ceil(3.1415927) from dual;

 

CEIL(3.1415927)

      4


 

COS

语法:  COS(x)

功能:  返回x的余弦值. x的单位是弧度.

 

SQL> select cos(-3.1415927) from dual;

 

COS(-3.1415927)

     -1


 

COSH

语法:  COSH(x)

功能:  计算x的双曲余弦值.

 

SQL> select cosh(20) from dual;

 

COSH(20)

242582598


 

EXP

语法:  EXP(x)

功能:  计算e的x次幂. e为自然对数,约等于2.71828.

 

SQL> select exp(2),exp(1) from dual;

 

EXP(2)    EXP(1)

7.3890561 2.7182818


 

FLOOR

语法:  FLOOR(x)

功能:  返回小于等于x的最大整数值.

 

SQL> SELECT   FLOOR (2345.67), FLOOR (-2345.67) FROM dual;

 

FLOOR(2345.67)   FLOOR (-2345.67)

  2345                        -2346


 

LN

语法:  LN(x)

功能:  返回x的自然对数. x必须是正数,并且大于0

 

SQL> select ln(1),ln(2),ln(2.7182818) from dual;

 

LN(1)     LN(2) LN(2.7182818)

0 .69314718     .99999999


 

LOG

语法:  LOG(x,y)

功能:  计算以x为底的y的对数.底必须大于0而且不等于1, y为任意正数.

 

SQL> select log(2,1),log(2,4) from dual;

 

LOG(2,1)  LOG(2,4)

0         2


 

MOD

语法:  MOD(x,y)

功能:  返回x除以y的余数.如果y是0,则返回x

 

SQL> select mod(10,3),mod(3,3),mod(2,3) from dual;

 

MOD(10,3)  MOD(3,3)  MOD(2,3)

1         0         2


 

POWER

语法:  POWER(x,y)

功能:  计算x的y次幂.

 

POWER 返回n1的n2次方根

SQL> select power(2,10),power(3,3) from dual;

 

POWER(2,10) POWER(3,3)

1024         27


 

ROUND

语法:  ROUND(x[,y])

功能:  四舍五入函数,y缺省值为0,x保留整数;y>0,x保留小数点右边y位;y<0,x保留小数点左边 |y| 位;可以对时间进行round,效果是只保留年月日。

 

SELECT   ROUND (55.655, 2),   --55.66

         ROUND (55.654, 2),   --55.65

         ROUND (45.654, -1),  --50

         ROUND (45.654, -2),  --0

         ROUND (55.654, -2)   --100

  FROM   DUAL;


 

SIGN

语法:  SIGN(x)

功能:  检测x的正负.如果x<0返回-1.如果x=0返回0.如果x>0返回1.

 

SQL> select sign(123),sign(-100),sign(0) from dual;

 

SIGN(123) SIGN(-100)   SIGN(0)

1         -1         0

 

常和decode 结合使用


 

SIN

语法:SIN(x)

功能:计算x的正弦值. X是一个以弧度表示的角度.

 

SQL> select sin(1.57079) from dual;

 

SIN(1.57079)

   1


 

SINH

语法:SINH(x)

功能:返回x的双曲正弦值.

 

SQL> select sin(20),sinh(20) from dual;

 

SIN(20)  SINH(20)

.91294525 242582598


 

SQRT

语法:  SQRT(x)

功能:  返回x的平方根. x必须是正数.

 

SQL> select sqrt(64),sqrt(10) from dual;

 

SQRT(64)  SQRT(10)

8 3.1622777


 

TAN

语法:  TAN(x)

功能:  计算x的正切值, x是一个以弧度位单位的角度.

 

SQL> select tan(20),tan(10) from dual;

 

TAN(20)   TAN(10)

2.2371609 .64836083


 

TANH

语法:  TANH(x)

功能:  计算x的双曲正切值.

 

SQL> select tanh(20),tan(20) from dual;

 

TANH(20)   TAN(20)

1 2.2371609


 

TRUNC

语法:  TRUNC(x[,y])

功能:  截取数字函数,只舍不入函数, y缺省值为0,x保留整数;y>0,x保留小数点右边y位;y<0,x保留小数点左边 |y| 位

 

SELECT   TRUNC (55.655, 2),   --55.65

         TRUNC (55.654, 2),   --55.65

         TRUNC (45.654, -1),  --40

         TRUNC (45.654, -2),  --0

         TRUNC (55.654, -2)   --0

  FROM   DUAL;

 

SELECT   TRUNC (SYSDATE, 'DD'),  --当天

         TRUNC (SYSDATE, 'MM'),  --本月第一天

         TRUNC (SYSDATE, 'yyyy'),  --本年第一天

         TRUNC (SYSDATE, 'day'),  --本周第一天

         TRUNC (SYSDATE, 'q')  --本季度第一天

  FROM   DUAL;


 

 

F.4  日期时间函数

(add_months,current_date,current_timestamp,dbtimesone,extract,from_tz,last_day,months_between,new_time,next_day,numtodsinternal,numtoyminternal,round,sys_extract_utc,sysdate,systimestamp,to_dsinternal,to_timestamp,to_timestamp_tz,to_yminternal,trunc,tz_offset)

 

说明:日期时间函数用于处理date和timestamp类型的数据,除了函数months_between返回数字外,其余均返回date类型,Oracle以7位数字格式来存放日期数据,包括世纪、年、月、日、小时、分钟、秒,并且默认日期显式格式为“DD-MON-YY”。

 

 

ADD_MONTHS

语法:ADD_MONTHS(d,x)

功能:返回日期d加上x个月后的月份。x可以是任意整数。如果结果日期中的月份所包含的天数比d日期中的“日”分量要少。(即相加后的结果日期中的日分量信息已经超过该月的最后一天,例如,8月31日加上一个月之后得到9月31日,而9月只能有30天)返回结果月份的最后一天。

使用位置:过程性语言和SQL语句。

 

SQL> select to_char(add_months(to_date('199912','yyyymm'),2),'yyyymm') from dual;

TO_CHA

200002

 

SQL> select to_char(add_months(to_date('199912','yyyymm'),-2),'yyyymm') from dual;

TO_CHA

199910


 

CURRENT_DATE

语法: CURRENT_DATE

功能:9i新增函数,返回当前会话时区所对应的日期时间。

 

select CURRENT_DATE from dual;


 

CURRENT_TIMESTAMP

语法:CURRENT_TIMESTAMP

功能:9i新增函数,返回当前会话时区所对应的日期时间。

 

select CURRENT_TIMESTAMP from dual;


 

DBTIMESONE

语法:DBTIMESONE

功能:9i新增函数,返回数据库所在时区。

 

select DBTIMESONE from dual;


 

EXTRACT

语法: EXTRACT(s)

功能:9i新增函数,从日期时间值中取得所需要的特定数据

 

Select extract(year from sysdate) year from dual;

 

Yaer

2013


 

FROM_TZ

语法: FROM_TZ(s)

功能:9i新增函数,将特定时区的TIMESTAMP值转换为TIMESTAMP WITH TIME ZONE值。

 

Select from_tz(timestamp ‘2013-03-28 08:00:00’,’3:00’);


 

LAST_DAY

语法:LAST_DAY(d)

功能:计算包含日期的d的月份最后一天的日期.这个函数可以用来计算当月中剩余天数.

使用位置:过程性语言和SQL语句。

LAST_DAY

返回日期的最后一天

SQL> select to_char(sysdate,'yyyy.mm.dd') aa from dual;

 

aa

2004.05.09

 

SQL> select last_day(sysdate) from dual;

 

LAST_DAY(S

31-5月 -04


 

LOCALTIMESTAMP

语法:LOCALTIMESTAMP

功能:9i新增函数,返回当前会话时区的日期时间。

 

Select LOCALTIMESTAMP from dual;


 

MONTHS_BETWEEN

语法:MONTHS_BETWEEN(date1,date2)

功能:计算date1和date2之间相差的月数.如果date1<date2,则返回负数;如果date1,date2这两个日期中日分量信息是相同的,或者这两个日期都分别是所在月的最后一天,那么返回的结果是一个整数,否则包括一个小数,小数为富余天数除以31,Oracle以每月31天为准计算结果。

SQL> select months_between('19-12月-1999','19-3月-1999') mon_between from dual;

MON_BETWEEN

  9

 

SQL>selectmonths_between(to_date('2000.05.20','yyyy.mm.dd'),to_date('2005.05.20','yyyy.dd')) mon_betw from dual;

MON_BETW

-60


 

NEW_TIME

语法:NEW_TIME(d,zone1,zone2)

功能:计算当时区zone1中的日期和时间是d时候,返回时区zone2中的日期和时间. zone1和zone2是字符串. 给出在this时区=other时区的日期和时间。

使用位置:过程性语言和SQL语句。

 

NEW_TIME   (d,   ‘tz1’,   ‘tz2’)

 

d::一个有效的日期型变量

tz1   &   tz2::下表中的任一时区

 

时区1   时区2       说明

AST       ADT           大西洋标准时间

BST       BDT           白令海标准时间

CST       CDT           中部标准时间

EST       EDT           东部标准时间

GMT                     格林尼治标准时间

HST       HDT           阿拉斯加—夏威夷标准时间

MST       MDT           山区标准时间

NST                     纽芬兰标准时间

PST       PDT           太平洋标准时间

YST       YDT           YUKON标准时间

 

SQL> select to_char(sysdate,'yyyy.mm.dd hh24:mi:ss') bj_time,to_char(new_time

2  (sysdate,'PDT','GMT'),'yyyy.mm.dd hh24:mi:ss') los_angles from dual;

 

BJ_TIME             LOS_ANGLES

2004.05.09 11:05:32 2004.05.09 18:05:32


 

NEXT_DAY

语法:NEXT_DAY(d,string)

功能: 给出日期d和星期string之后计算下一个星期的日期. String是星期几;当前会话的语言指定了一周中的某一天.返回值的时间分量与d的时间分量是相同的. String的内容可以忽略大小写.

使用位置:过程性语言和SQL语句。

NEXT_DAY(date,'day')

 

SQL> select next_day('18-5月-2001','星期五') next_day from dual;

 

NEXT_DAY

25-5月 -01


 

NUMTODSINTERNAL

语法:NUMTODSINTERNAL(n,char_expr)

功能:将数字n转换为INTERNAL DAY TO SECOND格式, char_expr可以是DAYHOURMINUTE或SECOND。

 

Select NUMTODSINTERNAL(1000,’minute’) from dual;


 

NUMTOYMINTERNAL

语法:NUMTOYMINTERNAL(n,char_expr)

功能:将数字n转换为INTERVAL YEAR TO MONTH格式,char_expr可以是year或者month。

 

Select NUMTOYMINTERNAL(100,’MONTH’) from dual;


 

ROUND

语法:ROUND(d[,format])

功能:将日期d按照由format指定的格式进行四舍五入处理处理.如果没有给format则使用缺省设置`DD`.

使用位置:过程性语言和SQL语句。

 

Select round(sysdate,’MONTH’) from dual;


 

SYS_EXTRACT_UTC

语法:SYS_EXTRACT_UTC(date)

功能:返回特定时区时间所对应的格林威治时间。

 

Select SYS_EXTRACT_UTC(systimestamp) from dual;


 

SYSDATE

语法: SYSDATE

功能:取得当前的日期和时间,类型是DATE.它没有参数.但在分布式SQL语句中使用时,SYSDATE返回本地数据库的日期和时间.

使用位置:过程性语言和SQL语句。

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;


 

SYSTIMESTAMP

语法:SYSTIMESTAMP

功能:9i新增函数,返回当前系统的日期时间及时区。

 

Select systimestamp from dual;


 

TO_DSINTERNAL

语法:TO_DSINTERNAL(char[,’nls_param’])

功能:9i新增函数,将符合特定日期和时间格式的字符串转变为INTERVAL DAY TO SECOND类型。

 

Select TO_DSINTERNAL(’58:10:10’) from dual;


 

TO_TIMESTAMP

语法:TO_TIMESTAMP(char[fmt[,’nls_param’]])

功能:9i新增函数,将符合特定日期和时间格式的字符串转变为TIMESTAMP类型。

select systimestamp from dual

 

  1. 字符型转成timestamp

Select TO_TIMESTAMP(’01-1月-03’) from dual;

select to_timestamp('01-10月-08 07.46.41.000000000 上午','dd-MON-yy hh:mi:ss.ff AM') 

from dual;

 

  1. timestamp转成date型

select cast(TO_TIMESTAMP('2015-10-01 21:11:11.328', 'yyyy-mm-dd hh24:mi:ss.ff') as date)

from dual;

 

  1. date型转成timestamp

select cast(sysdate as timestamp) date_to_timestamp

from dual;

 


 

TO_TIMESTAMP_TZ

语法:TO_TIMESTAMP_TZ(char[fmt[,’nls_param’]])

功能:9i新增函数,将符合特定日期和时间格式的字符串转变为TIMESTAMP WITH TIME ZONE类型。

 

Select TO_TIMESTAMP_TZ(’20130101’,’yyyymmdd’) from dual;


 

TO_YMINTERNAL

语法:TO_YMINTERNAL(char)

功能:9i新增函数,将符合特定日期和时间格式的字符串转变为INTERVAL YEAR TO MONTH类型。

 

select TO_TIMESTAMP('2015-10-01 21:11:11.328', 'yyyy-mm-dd hh24:mi:ss.ff') -

       TO_TIMESTAMP('2015-10-01 11:11:11.328', 'yyyy-mm-dd hh24:mi:ss.ff')

  from dual;

 


 

TRUNC

语法:TRUNC(d,format)

功能:截断日期时间数据,计算截尾到由format指定单位的日期d.缺省参数同ROUNG.

使用位置:过程性语言和SQL语句。如果fmt='mi'表示保留分,截断秒,如此类推。

 

SQL> select to_char(trunc(sysdate,'hh'),'yyyy.mm.dd hh24:mi:ss') hh,

  2  to_char(trunc(sysdate,'mi'),'yyyy.mm.dd hh24:mi:ss') hhmm from dual;

 

HH                  HHMM

2004.05.09 11:00:00 2004.05.09 11:17:00


 

TZ_OFFSET

语法:TO_OFFSET(time_zone_name||sessiontimezone||dbtimezone)

功能:9i新增函数,返回特定时区与UTC相比的时区偏移。

 

Select TO_OFFSET (’EST’) from dual;


 

 

F.5   转换函数

(asciistr,bin_to_num,cast,chartorowid,compose,convert,decompose,hextoraw, INTERVAL,rawtonhex,rowidtochar,rowidtonchar,scn_to_timestamp,timestamp_to_scn,to_char,to_clob,to_date,to_lob,to_label,to_multi_byte,to_nchar,to_number,to_single_byte,translate...using,unistr)

 

说明:用于将数值从一种数据类型转换为另一种数据类型。

 

 

ASCIISTR

语法:ASCIISTR(s)

功能:9i新增函数,将任意字符集的字符串转变为数据库字符集的ASCII字符串。

 

Select ASCIISTR (’中国’) 中 from dual;

 

4E2D56FD


 

BIN_TO_NUM

语法:BIN_TO_NUM(expr[,expr]…)

功能:9i新增函数,用于将位向量值转变为实际的数字值。

 

Select BIN_TO_NUM(1,0,1,1,1) 中 from dual;

 

23


 

CAST

语法:CAST(expr AS type_name)

功能:用于将一个内置数据类型或集合类型转变为另一个内置数据类型或集合类型。可以作用于长度为0的空字段视图建表格之用。

 

Select cast(SYSDATE AS VARCHAR2) 中 from dual;

Create table tb_dual nologging as Select cast(null as varchar2(1)) from dual;


 

CHARTOROWID

语法:CHARTOROWID(string)

功能: 将字符数据类型转换为ROWID类型,把包含外部格式的ROWID的CHAR或VARCHAR2数值转换为内部的二进制格式.参数string必须是包含外部格式的ROWID的18字符的字符串.oracle7和oracle8中的外部格式是不同的.CHARTOROWID是ROWIDTOCHAR的反函数.

使用位置:过程性语言和SQL语句。

 

SQL> select rowid,rowidtochar(rowid),ename from scott.emp;

 

ROWID              ROWIDTOCHAR(ROWID) ENAME

AAAAfKAACAAAAEqAAA AAAAfKAACAAAAEqAAA SMITH

AAAAfKAACAAAAEqAAB AAAAfKAACAAAAEqAAB ALLEN

AAAAfKAACAAAAEqAAC AAAAfKAACAAAAEqAAC WARD

AAAAfKAACAAAAEqAAD AAAAfKAACAAAAEqAAD JONES


 

COMPOSE

语法:COMPOSE(string)

功能:9i新增函数,用于将输入字符串转变为UNICODE字符串值。

 

Select COMPOSE(‘o’||unistr(‘308’)) 中 from dual;

 

?


 

CONVERT

语法:CONVERT(string,dest_set[,source_set])

功能:将字符串string从source_set所表示的字符集转换为由dest_set所表示的字符集.如果source_set没有被指定,它缺省的被设置为数据库的字符集.

使用位置:过程性语言和SQL语句。

 

SQL> select convert('中国','US7ASCII','WE8ISO8859P1') "conversion" from dual;


 

DECOMPOSE

语法:DECOMPOSE(string)

功能:9i新增函数,用于分解字符串并返回相应的UNICODE字符串。

 

Select COMPOSE(‘chateoux’) 中 from dual;


 

HEXTORAW

语法:HEXTORAW(string)

功能: 将string一个十六进制构成的字符串转换为二进制RAW数值. String中的每两个字符表示了结果RAW中的一个字节..HEXTORAW和RAWTOHEX为相反的两个函数.

使用位置:过程性语言和SQL语句。

 

Select HEXTORAW (‘AB56’) 中 from dual;


 

INTERVAL

语法:INTERVAL 'integer [- integer]' {YEAR | MONTH} [(precision)][TO {YEAR | MONTH}]

功能:该数据类型常用来表示一段时间差, 注意时间差只精确到年和月. precision为年或月的精确域, 有效范围是0到9, 默认值为2。

 

INTERVAL '123-2' YEAR(3) TO MONTH

表示: 123年2个月, "YEAR(3)" 表示年的精度为3, 可见"123"刚好为3为有效数值, 如果该处YEAR(n), n<3就会出错, 注意默认是2.

 

INTERVAL '11:12:10.1234567' HOUR TO SECOND

表示:小时,秒

结果: 00 11:12:10.123457

 

INTERVAL '123' YEAR(3)

表示: 123年0个月

 

INTERVAL '300' MONTH(3)

表示: 300个月, 注意该处MONTH的精度是3啊.

 

INTERVAL '4' YEAR

表示: 4年, 同 INTERVAL '4-0' YEAR TO MONTH 是一样的

 

INTERVAL '50' MONTH

表示: 50个月, 同 INTERVAL '4-2' YEAR TO MONTH 是一样

 

INTERVAL '123' YEAR

表示: 该处表示有错误, 123精度是3了, 但系统默认是2, 所以该处应该写成 INTERVAL '123' YEAR(3) 或"3"改成大于3小于等于9的数值都可以的

 

INTERVAL '5-3' YEAR TO MONTH INTERVAL '20' MONTH =

INTERVAL '6-11' YEAR TO MONTH

表示: 5年3个月 20个月 = 6年11个月

 

RAWTONHEX

语法:RAWTONHEX(rawvalue)

功能:9i新增函数,将RAW类数值rawvalue转换为一个相应的十六进制表示的字符串. rawvalue中的每个字节都被转换为一个双字节的字符串. RAWTOHEX和HEXTORAW是两个相反的函数.

使用位置:过程性语言和SQL语句。

 

Select rawtonhex(‘7D’) from dual;


 

ROWIDTOCHAR

语法:ROWIDTOCHAR(rowid)

功能:9i新增函数,将ROWID类型的数值rowid转换为varchar2的字符串表示,在oracle7和oracle8之间有些不一样的地方. ROWIDTOCHAR和CHARTOROWID是两个相反的函数.

使用位置:过程性语言和SQL语句。

 


 

ROWIDTONCHAR

语法:ROWIDTOCHAR(rowid)

功能:9i新增函数,将ROWID类型的数值rowid转换为Nvarchar2的字符串表示,在oracle7和oracle8之间有些不一样的地方. ROWIDTOCHAR和CHARTOROWID是两个相反的函数.

使用位置:过程性语言和SQL语句。

 


 

SCN_TO_TIMESTAMP

语法:SCN_TO_TIMESTAMP(number)

功能:10g新增函数,根据输入的scn值返回对应的大概日期时间,其中number用于指定scn值.

使用位置:过程性语言和SQL语句。

 

Select SCN_TO_TIMESTAMP(ora_rowscn) from emp;


 

TIMESTAMP_TO_SCN

语法:TIMESTAMP_TO_SCN(timestamp)

功能:10g新增函数,用于根据输入的timestamp返回所对应的scn值,其中timestamp、用于指定日期时间。

使用位置:过程性语言和SQL语句。

 

Select TIMESTAMP_TO_SCN(order_date) from emp;


 

TO_CHAR

语法1:TO_CHAR(character)

功能1:用于将NCHAR,NVARCHAR2,CLOB,NCLOB数据转变为数据库字符集数据,当用于NCHAR,NVARCHAR2,NCLOB时字符用单引号括起来,前面加上n。

 

Select to_char(n’中国’) from dual;

 

语法2:TO_CHAR(d [,format[,nlsparams]])

功能2:将日期d转换为一个VARCHAR2类型的字符串.format指定日期格式,.如果没有给定format,使用的就是该会话的缺省日期格式.nlsparams指定NLS参数. nlsparams的格式是:“NLS_DATE_LANGUAGE”

使用位置:过程性语言和SQL语句。

本文由美洲杯赔率发布于计算机教程,转载请注明出处:Oracle 中XML处理函数介绍

上一篇:Oracle SQL 内置函数大全(2) 下一篇:没有了
猜你喜欢
热门排行
精彩图文
  • MySQL两种临时表的用法详解
    MySQL两种临时表的用法详解
    外部临时表 通过CREATE TEMPORARY TABLE创建的临时表,这种临时表称为外部临时表。这种临时表只对当前用户可见,当前会话结束的时候,该临时表会自动关闭
  • MySQL笔记之数据类型详解
    MySQL笔记之数据类型详解
    数值型数据可以定义为unsigned,对整数而已可以改变其范围,对浮点数而言可以限定其不能为负数 char定长型,字节长度固定,最大长度为255字节,可以使用
  • MySQL 主主同步配置步骤
    MySQL 主主同步配置步骤
    MySQL 主主同步配置 服务器名IP系统MySQLodd.example.com192.168.1.116rhel-5.85.5.16even.example.com192.168.1.115rhel-5.85.5.16假设要同步的库是db_rocky ㈠ 创建同步用户 在 ODD上
  • MYSQL常见出错代码对照
    MYSQL常见出错代码对照
    常见错误!1016错误:文件无法打开,使用后台修复或者使用phpmyadmin进行修复。1044错误:数据库用户权限不足,请联系空间商解决1045错误:数据库服务器
  • 酷派巨亏75亿港元后复牌 5G与海外业务能否助其翻
    酷派巨亏75亿港元后复牌 5G与海外业务能否助其翻
    此外,酷派也一直在没有放弃在5G技术领域的探索。早在2013年,酷派便开始参与5G终端的研发及测试,直至2017年,酷派成立了人工智能科技中心。 半年后,