第7讲 转换函数特殊函数Word格式.docx
《第7讲 转换函数特殊函数Word格式.docx》由会员分享,可在线阅读,更多相关《第7讲 转换函数特殊函数Word格式.docx(15页珍藏版)》请在冰点文库上搜索。
![第7讲 转换函数特殊函数Word格式.docx](https://file1.bingdoc.com/fileroot1/2023-5/5/9be6ac5f-a05e-4caf-ae82-4691be9e62ad/9be6ac5f-a05e-4caf-ae82-4691be9e62ad1.gif)
返回带+,-号的格式
S9999:
前面有+,-号
9999S:
后面有+,-号
例子:
selectto_char('
8012.9998'
'
$999,999,999.99'
)fromdual
$8,012.98
8012.97778'
$8,012.98(自动进行四舍五入)
3.日期转为字符串:
TO_CHAR(date,format):
将给定的日期按格式转换为字符串,格式参照to_date()的日期格式。
在实际应用中,此函数是用的最多的函数,用于数据的统计。
4.字符串转为数值:
TO_NUMBER(String)
无格式的字符串转换位数值的函数,字符串中只能包含数字,小数点,正,负号。
如:
selectto_number('
+9222.989'
5.TO_NUMBER(String,number_format)
允许有格式的字符串,转换为数值。
+9222.00'
S9999999.99'
RMB999,999'
L999,999.00'
如果只写
由于没有格式就是非法的。
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'
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'
selectnvl(comm,0)fromemp
selectnvl2(comm,comm,0)fromemp
selectUIDfromdual
selectSYS_GUIDfromdual
selectempty_clob()fromdual
selectempty_blob()fromdual
selectename,decode(job,'
SALESMAN'
'
S'
O'
)
selectnullif('
1'
2'
selectto_char(-999,'
999.99S'
2000-01-1022:
20:
20.85252'
yyyy-mm-ddhh24:
mi:
ss.SSSSS'
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
3
2Video
4
5
6
7
3DVD
8
9
4CD
10
11
12
Magazine
CASE
WHENproduct_type_id=1THEN'
WHENproduct_type_id=2THEN'
WHENproduct_type_id=3THEN'
WHENproduct_type_id=4THEN'
12Magazine
13
SELECTproduct_id,price,
WHENprice>
15THEN'
Expensive'
Cheap'
PRODUCT_ID
PRICECASEWHENP
-----------------------------
19.95Expensive
30Expensive
25.99Expensive
13.95Cheap
49.99Expensive
14.95Cheap
13.49Cheap
12.99Cheap
10.99Cheap
15.99Expensive
14.99Cheap
课后作业:
下节前提问:
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
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
ReturnsnumberasRomannumerals.RNreturnsuppercasenumerals;
rnreturnslowercasenumerals.Numbermustbeanintegerbetween1and3999.
S
S999
999S
S999:
Returnsanegativenumberwithaleadingnegativesign;
returnsapositivenumberwithaleadingpositivesign.
999S:
Returnsanegativenumberwithatrailingnegativesign;
returnsapositivenumberwithatrailingpositivesign.
TM
Returnsanumberusingtheminimumnumberofcharacters.DefaultisTM9,whichreturns