第一章函 第二章標量函數 ASCII BLOB ASCII CLOB N N N DBCLOB 16 >>-AVG( +expression TheAVGfunctionreturnstheaverageofasetofnumbers.UsingthePROJECTtable,setthehostvariableAVERAGE(decimal(5,2))totheaveragestaffinglevel(PRSTAFF)ofprojectsindepartment(DEPTNO)'D11'.SELECTAVG(PRSTAFF) INTO:AVERAGE FROMPROJECTWHEREDEPTNO='D11'ResultsiAGEbeingsetto4.25(thatis17/4)whenusingthesampleUsingthePROJECTtable,setthehostvariableANY_CALC(decimal(5,2))totheaverageofeachuniquestaffinglevelvalue(PRSTAFF)ofprojectsindepartment(DEPTNO)'D11'.INTO:ANY_CALCFROMPROJECTWHEREDEPTNO='D11'ResultsinANY_CALCbeingsetto4.66(thatis14/3)whenusingthesample '- TheCORRELATIONfunctionreturnsthecoefficientofcorrelationofasetofnumberpairs.Theargumentvaluesmustbenumbers.Thedatatypeoftheresultisdouble-precisionfloatingpoint.Theresultcanbenull.Whennotnull,theresultisbetween-1and1.Thefunctionisappliedtothesetof(expression1,expression2)pairsderivedfromtheargumentvaluesbytheeliminationofallpairsforwhicheitherexpression1orexpression2isnull.Ifthefunctionisappliedtoanemptyset,orifeitherSTDDEV(expression1)orSTDDEV(expression2)isequaltozero,theresultisanullvalue.Otherwise,theresultisthecorrelationcoefficientforthevaluepairsintheset.TheresultisequivalenttothefollowingTheorderinwhichthevaluesareaggregatedisundefined,buteveryintermediateresultmustbewithintherangeoftheresuatatype.UsingtheEMPLOYEEtable,setthehostvariableCORRLN(double-precisionfloatingpoint)tothecorrelationbetweensalaryandbonusforthoseemployeesindepartment(WORKDEPT)INTO:CORRLNFROMWHEREWORKDEPT=CORRLNissetto y9.99853953399538E-001whenusingthesampleCOUNTaggregate>>-COUNT

+expression-+ '- TheCOUNTfunctionreturnsthenumberofrowsorvaluesinasetofrowsorvalues.UsingtheEMPLOYEEtable,setthehostvariableFEMALE(int)tothenumberofrowswherethevalueoftheSEXcolumnis'F'.SELECT FROM '- TheCOVARIANCEfunctionreturnsthe(population)covarianceofasetofnumberpairs.Theargumentvaluesmustbenumbers.Thedatatypeoftheresultisdouble-precisionfloatingpoint.TheresultcanbeThefunctionisappliedtothesetof(expression1,expression2)pairsderivedfromtheargumentvaluesbytheeliminationofallpairsforwhicheitherexpression1orexpression2isnull.Ifthefunctionisappliedtoanemptyset,theresultisanullvalue.Otherwise,theresultisthecovarianceofthevaluepairsintheset.Theresultisequivalenttothefollowing:Letavgexp1betheresultofAVG(expression1)andletavgexp2betheresultofTheresultofCOVARIANCE(expression1,expression2)isAVG((expression1-avgexp1)*(expression2-avgexp2)Theorderinwhichthevaluesareaggregatedisundefined,buteveryintermediateresultmustbewithintherangeoftheresuatatype.UsingtheEMPLOYEEtable,setthehostvariableCOVARNCE(double-precisionfloatingpoint)tothecovariancebetweensalaryandbonusforthoseemployeesindepartment(WORKDEPT)INTO:COVARNCEFROMWHEREWORKDEPT=COVARNCEissetto y1.68888888888889E+006whenusingthesample aggregatefunction分組函(>>- expression (2006-10-2006-10-y2006-10-2006-10-2006-10-劉SJ

SELECTgrou FROM groupbyMAXaggregatefunction>>-MAX( +expression TheMAXfunctionreturnsthe umvalueinasetofvalues.2006-10-y2006-10-2006-10-2006-10-劉2006-10-selectmax(cj)fromMINaggregatefunction取最小值函>>-MIN( +expression selectmin(cj)fromRegressionfunctions回歸函+- +- |'- '+- +- +- +- '- Theregressionfunctionssupportthefittingofanordinary-least-squaresregressionlineoftheformy=a*x+btoasetofnumberpairs.Theelementofeachpair(expression1)isinterpretedasavalueofthedependentvariable(thatis,a"yvalue").Thesecondelementofeachpair(expression2)isinterpretedasavalueoftheindependentvariable(thatis,an"xvalue").TheREGR_COUNTfunctionreturnsthenumberofnon-nullnumberpairsusedtofittheregressionline(seebelow).TheREGR_INTERCEPT(orREGR_ICPT)functionreturnsthey-interceptoftheregressionline("b"intheaboveequation).TheREGR_R2functionreturnsthecoefficientofdetermination("R-squared"or"goodness-of-fit")fortheregression.TheREGR_SLOPEfunctionreturnstheslopeoftheline("a"intheaboveTheREGR_AVGX,REGR_AVGY,REGR_SXX,REGR_SXY,andREGR_SYYfunctionsreturntiesthatcanbeusedtocomputevariousdiagnosticstatisticsneededfortheevaluationofthequalityandstatisticalvalidityoftheregressionmodel(seebelow).TheargumentvaluesmustbeThedatatypeoftheresultofREGR_COUNTisinteger.Fortheremainingfunctions,thedatatypeoftheresultisdoubleprecisionfloatingpoint.Theresultcanbenull.Whennotnull,theresultofREGR_R2isbetween0and1,andtheresultofbothREGR_SXXandREGR_SYYisEachfunctionisappliedtothesetof(expression1,expression2)pairsderivedfromtheargumentvaluesbytheeliminationofallpairsforwhicheitherexpression1orexpression2isnull.IfthesetisnotemptyandVARIANCE(expression2)ispositive,REGR_COUNTreturnsthenumberofnon-nullpairsintheset,andtheremainingfunctionsreturnresultsthataredefinedasREGR_SLOPE(expression1,expression2)=REGR_INTERCEPT(expression1,expression2)=REGR_R2(expression1,expression2)=POWER(CORRELATION(expression1,expression2),2)ifVARIANCE(expression1)>0REGR_R2(expression1,expression2)=1ifVARIANCE(expression1)=0REGR_AVGX(expression1,expression2)=AVG(expression2)REGR_AVGY(expression1,expression2)=AVG(expression1)REGR_SXX(expression1,expression2)=REGR_COUNT(expression1,expression2)*VARIANCE(expression2)REGR_SYY(expression1,expression2)=REGR_COUNT(expression1,expression2)*VARIANCE(expression1)REGR_SXY(expression1,expression2)=IfthesetisnotemptyandVARIANCE(expression2)isequaltozero,thentheregressionlineeitherhasinfiniteslopeorisundefined.Inthiscase,thefunctionsREGR_SLOPE,REGR_INTERCEPT,andREGR_R2eachreturnanullvalue,andtheremainingfunctionsreturnvaluesasdefinedabove.Ifthesetisempty,REGR_COUNTreturnszeroandtheremainingfunctionsreturnanullvalue.Theorderinwhichthevaluesareaggregatedisundefined,buteveryintermediateresultmustbewithintherangeoftheresuatatype.Theregressionfunctionsareallcomputedsimultaneouslyduringasinglepassthroughthedata.Ingeneral,itismoreefficienttousetheregressionfunctionstocomputethestatisticsneededforaregressionysisthantoperformtheequivalentcomputationsusingordinarycolumnfunctionssuchasAVERAGE,VARIANCE,COVARIANCE,andsoforth.Theusualdiagnosticstatisticsthat panyalinear-regression ysiscanbecomputedintermsoftheabovefunctions.Forexample:Adjusted1-((1-REGR_R2)*((REGR_COUNT-1)/(REGR_COUNT-2))StandardTotalsumofsquaresRegressionsumofsquaresPOWER(REGR_SXY,2)/REGR_SXXResidualsumof(Totalsumofsquares)-(Regressionsumofsquares)tstatisticforslopeREGR_SLOPE*SQRT(REGR_SXX)/(Standarderror)tstatisticfory-intercept UsingtheEMPLOYEEtable,computeanordinary-least-squaresregressionlinethatexpressesthebonusofanemployeeindepartment(WORKDEPT)'A00'asalinearfunctionoftheemployee'ssalary.SetthehostvariablesSLOPE,ICPT,RSQR(double-precisionfloatingpoint)totheslope,intercept,andcoefficientofdeterminationoftheregressionline,respectively.AlsosetthehostvariablesAVGSALandAVGBONUStotheaveragesalaryandaveragebonus,respectively,oftheemployeesindepartment'A00',andsetthehostvariableCNT(integer)tothenumberofemployeesindepartment'A00'forwhombothsalaryandbonusdataareavailable.StoretheremainingregressionstatisticsinhostvariablesSXX,SYY,andSXY.REGR_R2(BONUS,SALARY),REGR_COUNT(BONUS,SALARY),REGR_SXX(BONUS,SALARY),REGR_SYY(BONUS,SALARY), :SLOPE,:RSQR,:SXX,FROMWHEREWORKDEPT=Whenusingthesampletable,thehostvariablesaresettothefollowingapproximate +1.00871888623260E+002 STDDEVaggregatefunction求平均差值函>>-STDDEV( +expression TheSTDDEVfunctionreturnsthestandarddeviationofasetofnumbers.Theargumentvaluesmustbenumbers.Thedatatypeoftheresultisdouble-precisionfloatingpoint.Theresultcanbenull.Example:使用 FROMSUMaggregatefunction求和函>>-SUM( +expression TheSUMfunctionreturnsthesumofasetofnumbers.Example:使用 SELECT FROM TheVARIANCEfunctionreturnsthevarianceofasetofnumbers.Theargumentvaluesmustbenumbers.UsingtheEMPLOYEEtable,setthehostvariableVARNCE(double-precisionfloatingpoint)tothevarianceofthesalariesforthoseemployeesindepartment(WORKDEPT)'A00'.INTO:VARNCEFROMWHEREWORKDEPT=結果為：第二章ABSorABSVALscalarfunction求絕對 +(expression ASCIIscalarfunction>>-ASCII(expression 返回整數參數最左邊的字符的ASCII碼selecii(name)fromtestwhereid='ddd'BLOBscalar TheBLOBfunctionreturnsaBLOBrepresentationofastringofanytype.Astring-expressionwhosevaluecanbeacharacterstring,graphicstring,orabinarystring.AnintegervaluespecifyingthelengthattributeoftheresultingBLOBdatatype.Ifintegerisnotspecified,thelengthattributeoftheresultisthesameasthelengthoftheinput,exceptwheretheinputisgraphic.Inthiscase,thelengthattributeoftheresultistwicethelengthoftheinput.TheresultofthefunctionisaBLOB.Iftheargumentcanbenull,theresultcanbenull;iftheargumentisnull,theresultisthenullvalue.GivenatablewithaBLOBcolumnnamedTOPOGRAPHIC_MAPandaVARCHARcolumnnamedMAP_NAME,locateanymapsthatcontainthestring'PellowIsland'andreturnasinglebinarystringwiththemapnameconcatenatedinfrontoftheactualmap.SELECTBLOB(MAP_NAME ||': ')||TOPOGRAPHIC_MAPFROMONTARIO_SERIES_4CEILINGorCEILscalar>>-+-CEILING-+(

expression selectname,cj,ceil(cj)fromtest3劉CHARscalarCharacterto Datetimeto Integerto Decimalto Floating-pointto > + TheCHARfunctionreturnsafixed-lengthcharacterstringrepresentationTheCASTexpressioncsobeusedtoreturnastringTheresultofthefunctionisafixed-lengthcharacterstring.Iftheargumentcanbenull,theresultcanbenull.Iftheargumentisnull,theresultisthenullvalue.Examples:使用（樣表一selectchar(rq,usa)fromtestwhereselectchar(rq,iso)fromtestwhereAssumethatthePRSTDATEcolumnhasaninternalvalueequivalentto1988-12-25.Thefollowingfunctionreturnsthevalue'12/25/1988'.AssumethattheSTARTINGcolumnhasaninternalvalueequivalentto17:12:30,andthatthehostvariableHOUR_DUR(decimal(6,0))isatimedurationwithavalueof050000(thatis,5hours).Thefollowingfunctionreturnsthevalue'5:12PM'.Thefollowingfunctionreturnsthevalue'10:12AssumethattheRECEIVEDcolumn(TIMESTAMP)hasaninternalvalueequivalenttothecombinationofthePRSTDATEandSTARTINGcolumns.Thefollowingfunctionreturnsthevalue'1988-12-25-00000'.TheLASTNAMEcolumnisdefinedasVARCHAR(15).Thefollowingfunctionreturnsthevaluesinthiscolumnasfixed-lengthcharacterstringsthatare10characterslong.LASTNAMEvaluesthataremorethan10characterslong(excludingtrailingblanks)aretruncatedandawarningisSELECTCHAR(LASTNAME,10)FROMTheEDLEVELcolumnisdefinedasSMALLINT.Thefollowingfunctionreturnsthevaluesinthiscolumnasfixed-lengthcharacterstrings.AnEDLEVELvalueof18isreturnedastheCHAR(6)value'18'('18'followedbyfourblanks).SELECTCHAR(EDLEVEL)FROMTheSALARYcolumnisdefinedasDECIMALwithaprecisionof9andascaleof2.Thecurrentvalue(18357.50)istobedisplayedwithacommaasthedecimalcharacter(18357,50).Thefollowingfunctionreturnsthevalue'00018357,50'.ValuesintheSALARYcolumnaretobesubtractedfrom20000.25anddisplayedwiththedefaultdecimalcharacter.Thefollowingfunctionreturnsthevalue'-0001642.75'.CHAR(20000.25-AssumethatthehostvariableSEASONS_TICKETSisdefinedasINTEGERandhasavalueof10000.Thefollowingfunctionreturnsthevalue'10000.00'.AssumethatthehostvariableDOUBLE_NUMisdefinedasDOUBLEandhasavalue-987.654321E-35.Thefollowingfunctionreturnsthevalue'-9.87654321E-33'.BecausetheresultdatatypeisCHAR(24),thereareninetrailingblanksintheresult.CHRscalar>>-CHR(expression ASCII值返回字符selectchr(65)fromCLOBscalar TheCLOBfunctionreturnsaCLOBrepresentationofacharacterstringtype.InaUnicodedatabase,ifadargumentisagraphicstring,itisconvertedtoacharacterstringbeforethefunctionisexecuted.Anexpressionthatreturnsavaluethatisacharacterstring.AnintegervaluespecifyingthelengthattributeoftheresultingCLOBdatatype.Thevaluemustbebetween0and2147483647.Ifintegerisnotspecified,thelengthoftheresultisthesameasthelengthoftheargument.TheresultofthefunctionisaCLOB.Iftheargumentcanbenull,theresultcanbenull;iftheargumentisnull,theresultisthenullvalue.COALESCEscalar Examples:使用（樣表一）ID=’111’BZ字段為空selectCOALESCE(bz,'顯示的值')fromtestwhereid='111' 注意||maybeusedasasynonymforCONCAT.TheschemaisSYSIBM.22個值類型必須相同.selectid,concat(name,'測試fromtestwhere結果為：測COSscalar>>-COS(expression TheschemaisSYSIBM.(TheSYSFUNversionoftheCOSfunctioncontinuestobeavailable.)Returnsthecosineoftheargument,wheretheargumentisanangleexpressedinradians.Theargumentcanbeofanybuilt-innumerictype.Itisconvertedtoadouble-floating-pointnumberforprocessingbytheTheresultofthefunctionisadouble-precisionfloating-pointnumber.TheresultcanbenulliftheargumentcanbenullorthedatabaseisconfiguredwithDFT_SQLMATHWARNsettoYES;theresultisthenullvalueiftheargumentisnull.COSHscalarfunction弧度函>>-COSH(expression TheschemaisReturnsthehyperboliccosineoftheargument,wheretheargumentisanangleexpressedinTheargumentcanbeofanybuilt-innumericdatatype.Itisconvertedtoadouble-precisionfloating-pointnumberforprocessingbythefunction.Theresultofthefunctionisadouble-precisionfloating-pointnumber.TheresultcanbenulliftheargumentcanbenullorthedatabaseisconfiguredwithDFT_SQLMATHWARNsettoYES;theresultisthenullvalueiftheargumentisnull.COTscalarfunction余切函>>-COT(expression TheschemaisSYSIBM.(TheSYSFUNversionoftheCOTfunctioncontinuestobeavailable.)Returnsthecotangentoftheargument,wheretheargumentisanangleexpressedinradians.Theargumentcanbeofanybuilt-innumerictype.Itisconvertedtoadouble-precisionfloating-pointnumberforprocessingbythefunction.Theresultofthefunctionisadouble-precisionfloating-pointnumber.TheresultcanbenulliftheargumentcanbenullorthedatabaseisconfiguredwithDFT_SQLMATHWARNsettoYES;theresultisthenullvalueiftheargumentisnull.DATEscalarfunction日期函>>-DATE(expression Theargumentmustbeadate,timestamp,apositivenumberlessthanorequalto3652059,avalidstringrepresentationofadateortimestamp,orastringoflength7thatisnotaCLOB,LONGVARCHAR,DBCLOB,orLONGVARGRAPHIC.Theresultofthefunctionisadate.Iftheargumentcanbenull,theresultcanbenull;iftheargumentisnull,theresultisthenullvalue.TheotherrulesdependonthedatatypeoftheIftheargumentisadate,timestamp,orvalidstringrepresentationofadateortimestamp:Theresultisthedatepartofthevalue.IftheargumentisaTheresultisthedatethatisn-1daysafterJanuary1,0001,wherenistheintegralpartoftheIftheargumentisastringwithalengthofTheresultisthedaterepresentedbythestring.AssumethatthecolumnRECEIVED(timestamp)hasaninternalvalueequivalenttoThisexampleresultsinaninternalrepresentationof'1988-12-Thisexampleresultsinaninternalrepresentationof'1988-12-Thisexampleresultsinaninternalrepresentationof'1988-12-Thisexampleresultsinaninternalrepresentationof'0001-02->>-DAY(expression TheDAYfunctionreturnsthedaypartofaTheargumentmustbeadate,timestamp,dateduration,timestampduration,oravalidcharacterstringrepresentationofadateortimestampthatisneitheraCLOBnoraLONGVARCHAR.InaUnicodedatabase,ifadargumentisagraphicstring,itisconvertedtoacharacterstringbeforethefunctionisexecuted.Theresultofthefunctionisalargeinteger.Iftheargumentcanbenull,theresultcanbenull;iftheargumentisnull,theresultisthenullvalue.TEST2006-10-2selectday(‘2006-10-2’)DAYNAMEscalarfunction返回星>>-DAYNAME(expression Selectdayname(‘2006-10-10’)from DAYOFWEEK_ISOscalar >>-DAYOFYEAR(expression >>-DAYS(expression TheDAYSfunctionreturnsanintegerrepresentationofaTheargumentmustbeadate,timestamp,oravalidcharacterstringrepresentationofadateortimestampthatisneitheraCLOBnoraLONGVARCHAR.InaUnicodedatabase,ifadargumentisagraphicstring,itisconvertedtoacharacterstringbeforethefunctionisTheresultofthefunctionisalargeinteger.Iftheargumentcanbenull,theresultcanbenull;iftheargumentisnull,theresultisthenullvalue.Theresultis1morethanthenumberofdaysfromJanuary1,0001toD,whereDisthedatethatwouldoccuriftheDATEfunctionwereappliedtotheargument.DBCLOBscalar 返回一個DBCLOBselectdecimal(currentdate,10)fromThefollowingtableshowsthedecimalresultandresultingprecisionandscaleforvariousdatetimeinputvalues.DECIMAL(arguments) PrecisionandScale DECIMAL(2000-03-21,12, DECIMAL(12:02:21,10, (20, (23, (23, DECRYPT_BINandDECRYPT_CHARscalar>>-+-DECRYPT_BIN >(encrypted-data + TheDECRYPT_BINandDECRYPT_CHARfunctionsbothreturnavaluethatistheresultofdecryptingencrypted-data.Thepasswordusedfordecryptioniseitherthepassword-string-expressionvalueortheENCRYPTIONPASSWORDvalueassignedbytheSETENCRYPTIONPASSWORDstatement.TheDECRYPT_BINandDECRYPT_CHARfunctionscanonlydecryptvaluesthatareencryptedusingtheENCRYPTfunction(SQLSTATE428FE).AnexpressionthatreturnsaCHARFORBITDATAorVARCHARFORBITDATAvalueasacomplete,encrypteddatastring.ThedatastringmusthavebeenencryptedusingtheENCRYPTAnexpressionthatreturnsaCHARorVARCHARvaluewithatleast6bytesandnomorethan127bytes(SQLSTATE428FC).Thisexpressionmustbethesamepasswordusedtoencryptthedataordecryptionwillresultinanerror(SQLSTATE428FD).Ifthevalueofthepasswordargumentisnullornotprovided,thedatawillbeencryptedusingtheENCRYPTIONPASSWORDvalue,whiusthavebeensetforthesession(SQLSTATE51039).TheresultoftheDECRYPT_BINfunctionisVARCHARFORBITDATA.TheresultofDECRYPT_CHARfunctionisVARCHAR.Iftheencrypted-dataincludedahint,thehintisnotreturnedbythefunction.Thelengthattributeoftheresultisthelengthofthedatatypeoftheencrypted-dataminus8bytes.Theactuallengthofthevaluereturnedbythefunctionwillmatchthelengthoftheoriginalstringthatwasencrypted.Iftheencrypted-dataincludesbytestheencryptedstring,thesebytesarenotreturnedbythefunction.Iftheargumentcanbenull,theresultcanbenull.Iftheargumentisnull,theresultisthenullvalue.Ifthedataisdecryptedonadifferentsystemthatusesacodepagedifferentfromthecodepageinwhichthedatawasencrypted,thenexpansionmayoccurwhenconvertingthedecryptedvaluetothedatabasecodepage.Insuchsituations,theencrypted-datavalueshouldbecasttoaVARCHARstringwithalargernumberofbytes.Example1:ThisexampleusestheENCRYPTIONPASSWORDvaluetoholdtheencryptionSELECTDECRYPT_CHAR(SSN)FROMThisreturnsthevalue'289-46-Example2:ThisexampleexplicitlypassestheencryptionSELECTDECRYPT(SSN,'Ben123')FROMThisexamplereturnsthevalue'289-46-DEGREESscalar>>-DEGREES(expression Theargumentcanbeofanybuilt-innumerictype.Itisconvertedtoadouble-precisionfloating-pointnumberforprocessingbythefunction.Theresultofthefunctionisadouble-precisionfloating-pointnumber.Theresultcanbenull;iftheargumentisnull,theresultisthenullvalue.DIGITSscalar>>-DIGITS(expression TheDIGITSfunctionreturnsacharacter-stringrepresentationofaTheargumentmustbeanexpressionthatreturnsavalueoftypeSMALLINT,INTEGER,BIGINTorDECIMAL.Iftheargumentcanbenull,theresultcanbenull;iftheargumentisnull,theresultisthenullTheresultofthefunctionisafixed-lengthcharacterstringrepresentingtheabsolutevalueoftheargumentwithoutregardtoitsscale.Theresuoesnotincludeasignoradecimalcharacter.Instead,itconsistsexclusivelyofdigits,including,ifnecessary,leadingzerostofilloutthestring.Thelengthofthestringis:5iftheargumentisasmallinteger10iftheargumentisalargeinteger19iftheargumentisabigintegerpiftheargumentisadecimalnumberwithaprecisionofp.AssumethatatablecalledTABLEXcontainsanINTEGERcolumncalledINTCOLcontaining10-digitnumbers.ListalldistinctfourdigitcombinationsofthefourdigitscontainedincolumnINTCOL.SELECTDISTINCTSUBSTR(DIGITS(INTCOL),1,4)FROMTABLEXAssumethatCOLUMNXhastheDECIMAL(6,2)datatype,andthatoneofitsvaluesis-6.28.Then,forthisvalue:returnsthevalueTheresultisastringoflengthsix(theprecisionofthecolumn)withleadingzerospaddingthestringouttothislength.Neithersignnordecimalpointappearintheresult.DOUBLEscalarNumericto +- CharacterStringto UsingtheEMPLOYEEtable,findtheratioofsalarytocommissionforemployeeswhosecommissionisnotzero.Thecolumnsinvolved(SALARYandCOMM)haveDECIMALdatatypes.Toeliminatethepossibilityofout-of-rangeresults,DOUBLEisappliedtoSALARYsothatthedivisioniscarriedoutinfloatingpoint:SELECTEMPNO,DOUBLE(SALARY)/COMMFROMEMPLOYEEWHERECOMM>ENCRYPTscalar>>- >(data-string-expression +)- TheENCRYPTfunctionreturnsavaluethatistheresultofencryptingdata-string-expression.Thepasswordusedforencryptioniseitherthepassword-string-expressionvalueortheENCRYPTIONPASSWORDvalue(asassignedusingtheSETENCRYPTIONPASSWORDstatement).InaUnicodedatabase,ifadargumentisagraphicstring,itisconvertedtoacharacterstringbeforethefunctionisexecuted.AnexpressionthatreturnsaCHARorVARCHARvaluetobeencrypted.Thelengthattributeforthedatatypeofdata-string-expressionislimitedto32663withoutahint-string-expressionargumentand32631whenthehint-string-expressionargumentisspecified(SQLSTATE42815).AnexpressionthatreturnsaCHARorVARCHARvaluewithatleast6bytesandnomore127bytes(SQLSTATE428FC).Thevaluerepresentsthepasswordusedtoencryptthedata-string-expression.Ifthevalueofthepasswordargumentisnullornotprovided,thedatawillbeencryptedusingtheENCRYPTIONPASSWORDvalue,whichmusthavebeensetforthesession(SQLSTATE51039).AnexpressionthatreturnsaCHARorVARCHARvalueupto32bytesthatwillhelpdataownersrememberpasswords(forexample,'Ocean'asahinttoremember'Pacific').Ifahintvalueisgiven,thehintisembeddedintotheresultandcanberetrievedusingtheGETHINTfunction.Ifthisargumentisnullornotprovided,nohintwillbeembeddedintheresult.TheresuatatypeofthefunctionisVARCHARFORBITDATA.Thelengthattributeoftheresultis:Whentheoptionalhintparameterisspecified,thelengthattributeofthenon-encrypteddata+8bytes+thenumberofbytestothenext8byteboundary+32bytesforthehintlength.Withnohintparameter,thelengthattributeofthenon-encrypteddata+8bytes+thenumberbytestothenext8byteIftheargumentcanbenull,theresultcanbenull;iftheargumentisnull,theresultisthenullvalue.Noticethattheencryptedresultislongerthanthedata-string-expressionvalue.Therefore,whenassigningencryptedvalues,ensurethattheisdeclaredwithsufficientsizetocontaintheentireencryptedvalue.EncryptionAlgorithm:TheinternalencryptionalgorithmusedisRC2blockcipherwithpadding,the128-bitsecretkeyisderivedfromthepasswordusingaMD2messagedigest.EncryptionPasswordsandData:Itistheuser'sresponsibilitytoperformpasswordmanagement.Oncethedataisencryptedonlythepasswordusedtoencryptitcanbeusedtodecryptit(SQLSTATE428FD).BecarefulwhenusingCHARvariablestosetpasswordvaluesastheymaybepaddedwithblanks.Theencryptedresultmaycontainnullterminatorandothernon-printableTableColumnDefinition:Whendefiningcolumnsandtypestocontainencrypteddata,alwayscalculatethelengthattributeasfollows.Forencrypteddatawithnoumlengthofthenon-encrypteddata+8bytes+thenumberofbytestothenext8byteboundary=encrypteddolumnlength.Forencrypteddatawithanembeddedumlengthofthenon-encrypteddata+8bytes+thenumberofbytestothenext8byteboundary+32bytesforthehintlength=encrypteddolumnlength.Anyassignmentorcasttoalengthshorterthanthesuggesteddatalengthmayresultinfaileddecryptioninthefutureandlostdata.Blanksarevalidencrypteddatavaluesthatmaybetruncatedwhenstoredinacolumnthatistooshort.Somesamplecolumnlengthumlengthofnon-encrypted 68 8Numberofbytestothenext8byte 2Encrypted olumn 16umlengthofnon-encrypted 328 8Numberofbytestothenext8byte 8Encrypteddolumn 48Administrationofencrypteddata:EncrypteddanonlybedecryptedonserversthatsupportthedecryptionfunctionsthatcorrespondtotheENCRYPTfunction.Hence,replicationofcolumnswithencrypteddatashouldonlybedonetoserversthatsupporttheDECRYPT_BINorDECRYPT_CHARfunction.Example1:ThisexampleusestheENCRYPTIONPASSWORDvaluetoholdtheencryptionExample2:ThisexampleexplicitlypassestheencryptionExample3:Thehint'Ocean'isstoredtohelptheuserremembertheencryptionpasswordofEVENT_MON_STATEscalar 返回事件監視器的狀態，0表示不活動，1TheEVENT_MON_STATEfunctionreturnsthecurrentstateofaneventTheargumentisastringexpressionwitharesultingtypeofCHARorVARCHARandavaluethatisthenameofaneventmonitor.IfthenamedeventmonitordoesnotexistintheSYSCAT.EVENTMONITORScatalogtable,SQLSTATE42704willbereturned.InaUnicodedatabase,ifadargumentisagraphicstring,itisconvertedtoacharacterstringbeforethefunctionisexecuted.Theresultisanintegerwithoneofthefollowing0Theeventmonitorisinactive.TheeventmonitorisIftheargumentcanbenull,theresultcanbenull;iftheargumentisnull,theresultisthenullThefollowingexampleselectsallofthedefinedeventmonitors,andindicateswhethereachisactiveorinactive:SELECTEVMONNAME,WHENEVENT_MON_STATE(EVMONNAME)=0THEN'Inactive'WHENEVENT_MON_STATE(EVMONNAME)=1THEN'Active'FROMEXPscalar 指數函>>-EXP(expression ReturnstheexponentialfunctionoftheTheargumentcanbeofanybuilt-innumericdatatype.Itisconvertedtoadouble-precisionfloating-pointnumberforprocessingbythefunction.Theresultofthefunctionisadouble-precisionfloati