O'Reilly Oracle Sql Programming

[ Pobierz całość w formacie PDF ]
.The CONVERT function does not translate words or phrases from one language to another! CONVERTsimply substitutes the letter or symbol in one character set with the corresponding letter or symbol in anothercharacter set.(A character set is not the same thing as a human language.)Two commonly used character sets are US7ASCII (U.S.7-bit ASCII character set) and F7DEC (DEC French7-bit character set).14.2.3 The HEXTORAW functionThe HEXTORAW function converts a hexadecimal string from type CHAR or VARCHAR2 to type RAW.The specification of the HEXTORAW function is:486[Appendix A] What's on the Companion Disk?FUNCTION HEXTORAW (string_in IN CHAR) RETURN RAWFUNCTION HEXTORAW (string_in IN VARCHAR2) RETURN RAW14.2.4 The RAWTOHEX functionThe RAWTOHEX function converts a value from type RAW to a hexadecimal string of type VARCHAR2.The specification of the RAWTOHEX function is:FUNCTION RAWTOHEX (binary_value_in IN RAW) RETURN VARCHAR2RAWTOHEX always returns a variable-length string value, even if its mirror conversion function isoverloaded to support both types of input.14.2.5 The ROWIDTOCHAR functionThe ROWIDTOCHAR function converts a binary value of type ROWID to a string of type VARCHAR2.The specification of the ROWIDTOCHAR function is:FUNCTION ROWIDTOCHAR (row_in IN ROWID ) RETURN VARCHAR2The string returned by this function has the format:BBBBBBBB.RRRR.FFFFwhere BBBBBBBB is the number of the block in the database file, RRRR is the number of the row in theblock, and FFFF is the number of the database file.All three numbers are in hexadecimal format.14.2.6 The TO_CHAR function (date conversion)The TO_CHAR function can be used to convert both dates and numbers to a variable-length string.Thefollowing specification describes TO_CHAR for dates:FUNCTION TO_CHAR(date_in IN DATE[, format_mask IN VARCHAR2[, nls_language IN VARCHAR2]])RETURN VARCHAR2where date_in is the date to be converted to character format, the format_mask is the mask made up of one ormore of the date format elements, and nls_language is a string specifying a date language.Both the formatmask and the NLS language parameters are optional.If the format mask is not specified, then the default date format for the database instance is used.This formatis DD-MON-YY, unless the initialization parameter NLS_DATE_FORMAT is included in the initializationfile.The format of the specification of an alternative date mask is:NLS_DATE_FORMAT = 'MM/DD/YYYY'If the NLS language parameter is not specified, then the default date language for the instance is used.This iseither the language for the instance specified by the NLS_LANGUAGE parameter, or the date languagespecified in the initialization file with the parameter NLS_DATE_LANGUAGE.Note that if you want tospecify a date language, you also must include a format mask.You cannot skip over the interveningparameters.Here are some examples of TO_CHAR for date conversion:"14.2.4 The RAWTOHEX function 487[Appendix A] What's on the Companion Disk?Notice that there are two blanks between month and day and a leading zero for the fifth day:TO_CHAR (SYSDATE, 'Month DD, YYYY') ==> 'February 05, 1994'"Use the FM fill mode element to suppress blanks and zeros:TO_CHAR (SYSDATE, 'FMMonth DD, YYYY') ==> 'February 5, 1994'"Note the case difference on the month abbreviations of the next two samples.You get exactly whatyou ask for with Oracle date formats!TO_CHAR (SYSDATE, 'MON DDth, YYYY') ==> 'FEB 05th, 1994'TO_CHAR (SYSDATE, 'fmMon DDth, YYYY') ==> 'Feb 5th, 1994'"Show the day of year, the month, and the week for the date:TO_CHAR (SYSDATE, 'DDD DD D ') ==> '036 05 7'TO_CHAR (SYSDATE, 'fmDDD DD D ') ==> '36 5 7'"Some fancy formatting for reporting purposes:TO_CHAR (SYSDATE, '"In month "RM" of year "YEAR')==>'In month II of year NINETEEN NINETY FOUR'14.2.7 The TO_CHAR function (number conversion)The TO_CHAR function converts numbers as well as dates.The specification of the TO_CHAR (number)function is:FUNCTION TO_CHAR(number_in IN NUMBER[, format_mask IN VARCHAR2[, nls_language IN VARCHAR2]])RETURN VARCHAR2;where number_in is the number to be converted to character format, the format_mask is the mask made up ofone of more of the number format elements, and nls_language is a string specifying one or more of the NLSparameters which affect the way numbers are displayed.Both the format mask and the NLS languageparameters are optional.If the format mask is not specified, then the default number format for the database instance is used.Here are some examples of TO_CHAR for number conversion:TO_CHAR (564.70, '$999.9') ==> $564.7TO_CHAR (564.70, '$0000999.9') ==> $0000564.714.2.8 The TO_DATE functionThe TO_DATE function converts a character string to a true DATE datatype.The specification of theTO_DATE function is overloaded for string and number input:FUNCTION TO_DATE (string_in IN VARCHAR214.2.7 The TO_CHAR function (number conversion) 488[Appendix A] What's on the Companion Disk?[, format_mask IN VARCHAR2[, nls_language IN VARCHAR2 ]])RETURN DATE;FUNCTION TO_DATE(number_in IN NUMBER[, format_mask IN VARCHAR2 [, nls_language IN VARCHAR2 ]])RETURN DATE;The second version of TO_DATE can be used only with the format mask of J for Julian date.The Julian dateis the number of days which have passed since January 1, 4712 B.C.Only in this use of TO_DATE can anumber be passed as the first parameter of TO_DATE.For all other cases, string_in is the string variable, literal, named constant, or expression to be converted,format_mask is the format mask TO_DATE will use to convert the string, and nls_language is a string whichspecifies the language which is to be used to interpret the names and abbreviations of both months and days inthe string.The format of nls_language is as follows:'NLS_DATE_LANGUAGE='where is a language recognized by your instance of the database.You can usually determine theacceptable languages by checking your installation guide [ Pobierz całość w formacie PDF ]

download - pobieranie - ebook - do ÂściÂągnięcia - pdf

Menu

Drogi użytkowniku!