0.Oracle 9i 10g 函数(35 页)

Oracle9i/10gRDBMS函数与使用赵元杰2007.06.04E_Mail:[e-mail]2/35Oracle9i/10g新数据类型Oracle9i/10g内置函数Oracle9i/10g新内置函数内容提要3/35Oracle9i/10g新数据类型4/359i新数据类型新的日期与时间类型TIMESTAMP(时间邮戳类型–精度为秒)包括年月日、时分秒，秒精度达1/100秒.TIMESTAMPWITHTIMEZONE以时区显示时间的结果.TIMESTAMPWITHLOCALTIMEZONE表示TIMESTAMPWITHTIMEZONE的所有值,可以是:存储在数据库中的时区.查询时以时区显示.5/359i新数据类型新的日期与时间类型INTERVALYEAR(年)TOMONTH以年和月存储时间区间值，可以是0至9的数。默认值2.INTERVALDAY(天)TOSECOND(秒)参考：Oracle9iSQLReference秒的精度与平台有关：Oracle9i企业版ForTru64UNIX精度最大6位Oracle9i企业版ForWindows2000Professional精度最大3位6/359i新数据类型-例子TIMESTAMP例子创建带TIMESTAMP类型的transaction表插入两行SYSDATE值的数据插入两行SYSTIMESTAMP值的数据CREATETABLEtransactions(trans_idVARCHAR2(5),trans_dateTIMESTAMP(9));INSERTINTOtransactionsVALUES('1',SYSDATE);INSERTINTOtransactionsVALUES('2',SYSDATE);INSERTINTOtransactionsVALUES('3',SYSTIMESTAMP);INSERTINTOtransactionsVALUES('4',SYSTIMESTAMP);7/359i新数据类型-例子TIMESTAMP例子查询结果为：使用SYSTIMESTAMP与SYSDATE表示数据精度不同SELECT*FROMtransactions;TRANS_IDTRANS_DATE------------------------------------------------------111-OCT-0208.05.09.000000000AM211-OCT-0208.05.12.000000000AM311-OCT-0208.05.26.534392000AM411-OCT-0208.05.29.921128000AM8/359i新数据类型-例子TIMESTAMP例子列的值为空才能修改结构：ALTERTABLEtransactionsMODIFYtrans_dateTIMESTAMP(6);ERRORatline1:ORA-30082:datetime/intervalcolumntobemodifiedmustbeemptytodecreasefractionalsecondorleadingfieldprecision9/35Oracle9i/10g一般函数10/359i/10g一般内置函数Oracle函数分为：单值函数(Single-RowFunctions)，再分为：数字函数(NumericFunctions)，如COS,COSH,EXP,LN,LOG,SIN,SINH,SQRT等；字符函数(CharacterFunctions)，如CHR,CONCAT,INITCAP,LOWER等；聚合函数(AggregateFunctions)如DISTINCT，COUNT(*)等分析函数(AnalyticFunctions)-聚合函数类似；对象引用函数(ObjectReferenceFunctions)如DEREF,MAKE_REF,REF,REFTOHEX,VALUE模型函数(ModelFunctions)-10g如CV，ITERATION_NUMBER，PRESENTNNV,PRESENTV，PREVIOUS，11/359i/10g内置函数-单值函数单值函数：ABSACOSASINATANATAN2BITANDCEILCOSCOSHEXPFLOORLNLOGMODNANVLPOWERREMAINDERROUND(number)SIGNSINSINHSQRTTANTANHTRUNC(number)WIDTH_BUCKET12/359i/10g内置函数-单值函数分类CaseConversionLOWER,UPPER,INITCAPCharactermanipulationSUBST,LENGTH,LPAD,RPAD,RTRIM,LTRIM,REPLACENumericROUND,TRUNCDateMONTHS_BETWEEN,ADD_MONTHS,NEXT_DAY,TO_DATEOtherNVL,NVL2,TO_CHAR,DECODE,SOUNDEX13/359i/10g内置函数-单值函数BITAND函数-计算exp1与exp2的“与”运算SELECTorder_id,customer_id,DECODE(BITAND(order_status,1),1,'Warehouse','PostOffice')Location,DECODE(BITAND(order_status,2),2,'Ground','Air')Method,DECODE(BITAND(order_status,4),4,'Insured','Certified')ReceiptFROMordersWHEREorder_status8;ORDER_IDCUSTOMER_IDLOCATIONMETHODRECEIPT----------------------------------------------2458101PostOfficeAirCertified2397102WarehouseAirCertified2454103WarehouseAirCertified2354104PostOfficeAirCertified2358105PostOfficeGroundCertified2381106WarehouseGroundCertified2440107WarehouseGroundCertified14/359i/10g内置函数-单值函数MOD函数-计算exp1与exp2的除的余数SQLselectmod(9,2)fromdual;MOD(9,2)----------1SQLselectmod(10,2)fromdual;MOD(10,2)----------015/359i/10g内置函数-单值函数ROUND函数-四舍五入SQLSELECTROUND(1.5),ROUND(2.5)FROMDUAL;ROUND(1.5)ROUND(2.5)--------------------23--SELECTROUND(15.193,1)RoundFROMDUAL;Round----------15.2SELECTROUND(15.193,-1)RoundFROMDUAL;Round----------2016/359i/10g内置函数-单值函数CHR函数-将十进制转换(返回)字符下例给出1SELECT'SELECTdbms_metadata.get_ddl('||CHR(39)||'TABLE'||chr(39)||','2||chr(39)||table_name||chr(39)||')fromdual;'3*fromuser_tablesSQL/'SELECTDBMS_METADATA.GET_DDL('||CHR(39)||'TABLE'||CHR(39)||','||CHR(39)||TABLE_N--------------------------------------------------------------------------------SELECTdbms_metadata.get_ddl('TABLE','DEPT')fromdual;SELECTdbms_metadata.get_ddl('TABLE','EMP')fromdual;SELECTdbms_metadata.get_ddl('TABLE','BONUS')fromdual;SELECTdbms_metadata.get_ddl('TABLE','SALGRADE')fromdual;17/359i/10g内置函数-LEGTH与LEGTHB函数selectascii('赵')zhao,length('赵'),lengthB('赵')lengfromdual;ZHAOLENGTH('赵')LENG----------------------------547401218/359i/10g内置函数-几个关键函数SUBSTR函数-取字符串的子串SUBSTR(original_string,start_pos,length_of_returned_string)SQLSELECTDISTINCTSUBSTR(zip,1,3)2FROMcustomers;SUB---02107908612230331232333134719/359i/10g内置函数-几个关键函数SUBSTR函数-取字符串的子串start_pos可以是从最末尾向左的第x位，如：SQLSELECTDISTINCTzip,SUBSTR(zip,1,3),SUBSTR(zip,-3,2)2FROMcustomers;ZIPSUBSU----------02110021110796207996086070866012211122213031430331312063122032306323303232832332331113311110rowsselected.20/359i/10g内置函数-几个关键函数LPADRPAD函数-左右加字串LPAD(char_string,len_after_pad,char_to_fill)RPAD(char_string,len_after_pad,char_to_fill)SQLSELECTfirstname,LPAD(firstname,12,'')2FROMcustomers3WHEREfirstnameLIKE'%E%';FIRSTNAMELPAD(FIRSTNA----------------------LEILALEILAMESHIAMESHIAKENNETHKENNETHJORGEJORGEJAKEJAKEREESEREESEJASMINEJASMINESTEVESTEVEMICHELLMICHELLBECCABECCAGREGGREGJENNIFERJENNIFERKENNETHKENNETH13rowsselected.21/359i/10g内置函数-几个关键函数LPADRPAD函数-左右加字串SQLSELECTfirstname,LPAD(firstname,12,'*')2FROMcustomers3WHEREfirstnameLIKE'%E%';FIRSTNAMELPAD(FIRSTNA----------------------LEILA*******LEILAMESHIA******MESHIAKENNETH*****KENNETHJORGE*******JORGEJAKE********JAKEREESE*******REESEJASMINE*****JASMINESTEVE*******STEVEMICHELL*****MICHELLBECCA*******BECCAGREG********GREGJENNIFER****JE