第7讲 转换函数特殊函数.docx
《第7讲 转换函数特殊函数.docx》由会员分享，可在线阅读，更多相关《第7讲 转换函数特殊函数.docx（15页珍藏版）》请在冰点文库上搜索。
第7讲转换函数特殊函数
第7讲转换函数，特殊函数
目的：
1.掌握Oracle转换函数的语法
2.掌握转换函数的使用
授课内容：
转换函数：
1.数值转为字符串：
TO_CHAR（number）无格式
2.数值转换位字符串，有格式：
TO_CHAR（number,format）：
经常用于报表的数据显示，如财务，条件表要求的格式一般是带千分号，和小数点的格式。
234，222，111。
09
数值格式
说明
9
每个9标识一个有效位。
999,999,999在指定的位置上，放置，号。
一般用于千分号
.
在指定的位置上，放置.号，用于小数点的位置
0
0999返回前导0，而不是空格
9990返回后继0，而不是空格
$
返回带美元符号的数值
L
返回带本地货币的数值，如L999,999.99.RMB
RM
返回数值的大写的罗马格式
rm
返回数值的小写的罗马格式
S
返回带+,-号的格式
S9999:
前面有+,-号
9999S:
后面有+,-号
例子：
selectto_char（'8012.9998','$999,999,999.99'）fromdual
$8,012.98
selectto_char（'8012.97778','$999,999,999.99'）fromdual
$8,012.98（自动进行四舍五入）
3.日期转为字符串：
TO_CHAR（date,format）：
将给定的日期按格式转换为字符串，格式参照to_date（）的日期格式。
在实际应用中，此函数是用的最多的函数，用于数据的统计。
4.字符串转为数值：
TO_NUMBER（String）
无格式的字符串转换位数值的函数，字符串中只能包含数字，小数点，正，负号。
如：
selectto_number（'+9222.989'）fromdual
5.TO_NUMBER（String,number_format）
允许有格式的字符串，转换为数值。
selectto_number（'+9222.00','S9999999.99'）fromdual
selectto_number（'RMB999,999','L999,999.00'）fromdual
如果只写
selectto_number（'RMB999,999'）fromdual
由于没有格式就是非法的。
6.TO_DATE（String,format）：
将字符串按格式转换为日期。
7.TO_CLOB（String）：
将String转换为CLOB的值。
8.TO_LOB（long_column）：
将long类型的列转换为LOB类型，自动判断，将long转换为CLOB,将LONGRAW转换为BLOB.
9.TO_TIMESTAMP（String,format）：
将字符串按日期的格式转换为TIMESTAMP时间。
没有指定格式，按系统当前的默认格式。
selectto_timestamp（'2000','yyyy'）fromdual
2000-6-10:
00:
00.000000
Oracle9i内置的特殊函数
10.UID:
标识登录用户的整数。
selectUIDfromdual
11.USER：
返回当前用户名称的varchar2值
SelectUSERfromdual
12.VSIZE（x）返回x的字节数
selectvsize（comm）fromemp
13.NVL（x1,x2）null处理函数
如果x1是null,则返回是x2,否则是x1自己
14.NVL2（x1,x2,x3）null处理函数
如果x1不是null,返回x2,否则返回x3
selectnvl2（comm,comm,0）fromemp
15.NULLIF（X1,X2）：
如果X1等于X2，返回null,否则返回X1
16.EMPTY_CLOB（）
返回空的字符位置指针，用于在增加新的记录时，对CLOB的字段赋初值。
17.EMPTY_BLOB（）
返回空的二进制位置指针，用于在增加新的记录时，对BLOB的字段赋初值。
18.DECODE（）:
条件判断函数
Decode（X,X1,Y1,X2,Y2,…Xn,Yn,Y）:
如果X的值是X1,则返回Y1,
X2,则返回Y2,
Xn,则返回Yn
否则返回Y.
省略Y,则没有else值。
DECODE函数的主要作用是用于统计报表。
产生多栏的统计报表。
练习：
selectround（sysdate）fromdual
selectmonths_between（min（hiredate）,max（hiredate））
fromemp
selectto_clob（'aaaa'）fromdual
selectnvl（comm,0）fromemp
selectnvl2（comm,comm,0）fromemp
selectUIDfromdual
selectSYS_GUIDfromdual
selectvsize（comm）fromemp
selectempty_clob（）fromdual
selectempty_blob（）fromdual
selectename,decode（job,'SALESMAN','S','O'）
fromemp
selectnullif（'1','2'）fromdual
selectto_char（-999,'999.99S'）fromdual
selectto_number（'RMB999,999'）fromdual
selectto_timestamp（'2000-01-1022:
20:
20.85252','yyyy-mm-ddhh24:
mi:
ss.SSSSS'）fromdual
SELECTproduct_id,product_type_id,
CASEproduct_type_id
WHEN1THEN'Book'
WHEN2THEN'Video'
WHEN3THEN'DVD'
WHEN4THEN'CD'
ELSE'Magazine'
END
FROMproducts;
PRODUCT_IDPRODUCT_TYPE_IDCASEPROD
---------------------------------
1 1Book
2 1Book
3 2Video
4 2Video
5 2Video
6 2Video
7 3DVD
8 3DVD
9 4CD
10 4CD
11 4CD
12 Magazine
SELECTproduct_id,product_type_id,
CASE
WHENproduct_type_id=1THEN'Book'
WHENproduct_type_id=2THEN'Video'
WHENproduct_type_id=3THEN'DVD'
WHENproduct_type_id=4THEN'CD'
ELSE'Magazine'
END
FROMproducts;
PRODUCT_IDPRODUCT_TYPE_IDCASEPROD
---------------------------------
1 1Book
2 1Book
3 2Video
4 2Video
5 2Video
6 2Video
7 3DVD
8 3DVD
9 4CD
10 4CD
11 4CD
12Magazine
13
SELECTproduct_id,price,
CASE
WHENprice>15THEN'Expensive'
ELSE'Cheap'
END
FROMproducts;
PRODUCT_ID PRICECASEWHENP
-----------------------------
1 19.95Expensive
2 30Expensive
3 25.99Expensive
4 13.95Cheap
5 49.99Expensive
6 14.95Cheap
7 13.49Cheap
8 12.99Cheap
9 10.99Cheap
10 15.99Expensive
11 14.99Cheap
12 13.49Cheap
课后作业：
下节前提问：
1.查询员工表，显示员工名称，职位，加入公司的季度（一季度，二季度，三季度，四季度），没有加入公司日期，显示不知道。
2.以每月15日为准，15日之前为‘上半月’，15日以后包括15日为‘下半月‘，显示员工的编号，名称，职位，加入公司的半月情况。
3.员工的工资以2500为界，高于2500为高工资，低于2500为低工资。
等于2500为中等工资。
查询员工的工资情况，显示员工姓名，工资情况。
附录1：
转换函数列表
Table3-3:
ConversionFunctions
Function
Description
ASCIISTR（x）
ConvertsxtoanASCIIstring,wherexmaybeastringinanycharacterset.
BIN_TO_NUM（x）
Convertsxtoabinarynumber.ReturnsaNUMBER.
CAST（xAStype_name）
Convertsavalueinxfromonedatatypetoanotherspecifiedintype_name.
CHARTOROWID（x）
ConvertsxtoaROWID.
COMPOSE（x）
ConvertsxtoaUnicodestringinitsfullynormalizedforminthesamecharactersetasx.Unicodeusesa2-bytecharactersetandcanrepresentover65,000characters;itmayalsobeusedtorepresentnon-Englishcharacters.
CONVERT（x,source_char_set,dest_char_set）
Convertsxfromsource_char_settodest_char_set.
DECODE（x,search,result,default）
Comparesxwiththevalueinsearch;ifequal,DECODE（）returnssearch,otherwisethevalueindefaultisreturned.
DECOMPOSE（x）
ConvertsxtoaUnicodestringafterdecompositioninthesamecharactersetasx.
HEXTORAW（x）
Convertsthecharacterxcontaininghexadecimaldigits（base16）toabinarynumber（RAW）.ThisfunctionreturnsthereturnsRAWnumber.
NUMTODSINTERVAL（x）
ConvertsthenumberxtoanINTERVALDAYTOSECOND.You'lllearnaboutdateandtimeinterval–relatedfunctionsinthenextchapter.
NUMTOYMINTERVAL（x）
ConvertthenumberxtoanINTERVALYEARTOMONTH.
RAWTOHEX（x）
Convertsthebinarynumber（RAW）xtoaVARCHAR2charactercontainingtheequivalenthexadecimalnumber.
RAWTONHEX（x）
Convertsthebinarynumber（RAW）xtoanNVARCHAR2charactercontainingtheequivalenthexadecimalnumber.AnNVARCHAR2isusedtostorestringsinthenationalcharacterset.
ROWIDTOCHAR（x）
ConvertstheROWIDxtoaVARCHAR2character.
ROWIDTONCHAR（x）
ConvertstheROWIDxtoanNVARCHAR2character.
TO_BINARY_DOUBLE（x）
NewforOracleDatabase10g.ConvertsxtoaBINARY_DOUBLE.
TO_BINARY_FLOAT（x）
NewforOracleDatabase10g.ConvertsxtoaBINARY_FLOAT.
TO_CHAR（x[,format]）
ConvertsxtoaVARCHAR2string.Youcansupplyanoptionalformatthatindicatestheformatofx.
TO_CLOB（x）
Convertsxtoacharacterlargeobject（CLOB）.ACLOBisusedtostorelargeamountsofcharacterdata.
TO_DATE（x[,format]）
ConvertsxtoaDATE.
TO_DSINTERVAL（x）
ConvertthestringxtoanINTERVALDAYTOSECOND.
TO_MULTI_BYTE（x）
Convertsthesingle-bytecharactersinxtotheircorrespondingmulti-bytecharacters.Thereturntypeisthesameasthetypeforx.
TO_NCHAR（x）
ConvertsxinthedatabasecharactersettoanNVARCHAR2.
TO_NCLOB（x）
ConvertsxtoanNCLOB.AnNCLOBisusedtostorelargeamountsofnationallanguagecharacterdata.
TO_NUMBER（x[,format]）
ConvertsxtoaNUMBER.
TO_SINGLE_BYTE（x）
Convertsthemulti-bytecharactersinxtotheircorrespondingsingle-bytecharacters.Thereturntypeisthesameasthetypeforx.
TO_TIMESTAMP（x）
ConvertsthestringxtoaTIMESTAMP.
TO_TIMESTAMP_TZ（x）
ConvertsthestringxtoaTIMESTAMPWITHTIMEZONE.
TO_YMINTERVAL（x）
ConvertsthestringxtoanINTERVALYEARTOMONTH.
TRANSLATE（x,from_string,to_string）
Convertsalloccurrencesoffrom_stringinxtoto_string.
UNISTR（x）
Convertsthecharactersinxtothenationallanguagecharacterset（NCHAR）.
附录2：
数值转换为字符的格式to_char（x,‘format’）
Table3-4:
FormatParameters
Parameter
FormatExamples
Description
9
999
Returnsdigitsinspecifiedpositionswithleadingnegativesignifthenumberisnegative.
0
0999
9990
0999:
Returnsanumberwithleadingzeros.
9990:
Returnsanumberwithtrailingzeros.
.
999.99
Returnsadecimalpointinthespecifiedposition.
9,999
Returnsacommainthespecifiedposition.
$
$999
Returnsaleadingdollarsign.
B
B9.99
Iftheintegerpartofafixedpointnumberiszero,returnsspacesforthezeros.
C
C999
ReturnstheISOcurrencysymbolinthespecifiedposition.ThesymbolcomesfromtheNLS_ISO_CURRENCYparameter.
D
9D99
Returnsthedecimalpointsymbolinthespecifiedposition.ThesymbolcomesfromtheNLS_NUMERIC_CHARACTERparameter（defaultisaperiodcharacter）.
EEEE
9.99EEEE
Returnsnumberusingthescientificnotation.
FM
FM90.9
Removesleadingandtrailingspacesfromnumber.
G
9G999
Returnsthegroupseparatorsymbolinthespecifiedposition.ThesymbolcomesfromtheNLS_NUMERIC_CHARACTERparameter.
L
L999
Returnsthelocalcurrencysymbolinthespecifiedposition.ThesymbolcomesfromtheNLS_CURRENCYparameter.
MI
999MI
Returnsanegativenumberwithatrailingminussign.Returnsapositivenumberwithatrailingspace.
PR
999PR
Returnsanegativenumberinanglebrackets（<>）.Returnsapositivenumberwithleadingandtrailingspaces.
RN
rn
RN
rn
ReturnsnumberasRomannumerals.RNreturnsuppercasenumerals;rnreturnslowercasenumerals.Numbermustbeanintegerbetween1and3999.
S
S999
999S
S999:
Returnsanegativenumberwithaleadingnegativesign;returnsapositivenumberwithaleadingpositivesign.
999S:
Returnsanegativenumberwithatrailingnegativesign;returnsapositivenumberwithatrailingpositivesign.
TM
TM
Returnsanumberusingtheminimumnumberofcharacters.DefaultisTM9,whichreturns
升级会员 