当前位置:文档之家› oracle存储过程函数汇总().docx

oracle存储过程函数汇总().docx

oracle存储过程函数汇总()Character function return character valueThese functions all received is the character parameter type group (except CHR) and returns the character value?In addition to the special instructions, the function returnsVARCHAR2 most numerical types?The restrictions on the return type of the character function arethe same as those for the basic database type?The maximum value of character variable storage:The VARCHAR2 value is limited to 2000 characters (ORACLE 8 to 4000 characters)The CHAR value is limited to 255 characters (0RACLE8 2000)The long type is 2GBThe Clob type is 4GB1,CHRSyntax: Chr (x)Function: return in the database character set with numerical equivalence with the character of X. CHR and ASCII are a pair of inverse functions? After CHR conversion character after ASCII conversion and obtained the original wordFu.Use location: process and SQL statement?2,CONCATSyntax: CONCAT (stringl, string2)The function returns the stringl, and connected string2 behind.Use location: process and SQL statement?3,INITCAPSyntax: INITCAP (string)Function: returns the first letter of each word in the string, uppercase, and the other letter in the word, lowercase string. Theword is used? Spaces or alphanumeric characters to separate. It'snot letterCharacter unchanged.Use location: process and SQL statement?4,LTRIMSyntax: LTRIM (stringl, string2)Delete function: return counting from the left appears in the string2character in stringl. String2 is set to a single blank by default? The database will scan stringl, starting from the left. WhenWhen the first character in string2, the results will be returned? LTRIM,s behavior is very similar to that of RTRIM.Use location: process and SQL statement?5,NLS_INITCAPSyntax: NLS_INITCAP (stringt, nlsparams])Function: returns the string, the first letter of each wordcapitalized, and the other words in the word, lowercase string,nlsparamsThe different sequenee is different from the default value of thespecified session? If you do not specify a parameter, function andthe same INITCAP? Nlsparams can be used in the form of:'NLS_SORT二sort'Here sort made a sequence of language?Use location: process and SQL statement?Example:Select NLS INITCAP (,ABCD,) from dual;AbedAtSelect NLS_INITCAP ('abed') from dual;Abed6,NLS_LOWERSyntax: NLS_LOWER (string[, nlsparams])The function returns all the letters in the string are lowercasestring? Not alphabetic characters remain unchanged?The nlsparams Nlsparams parameter form and use and NLS_INITCAP are the same? If the nlsparams is not included, then the NLS_LOWER for processing and?Same LOWERUse position; process and SQL statement?7,NLS_UPPERSyntax: nls_upper (string[, nlsparams])Function: the return of all the letters in the string arecapitalized in the form of string? Not alphabetic characters remain unchanged? The parameters of nlsparams and NLS_INITCAP in the form and use of the same? IfNo set parameters, NLS_UPPER function and UPPER.& REPLACESyntax: REPLACE (string, search_str[, replace_str])Function: replace all substring search_str in string with optional replace_str, if no replace_str is specified, all substrings instringSearch_str will be deleted. REPLACE is a subset of TRANSLATE's functions set?Use location: process and SQL statement?9,RPADSyntax: RPAD (stringl, x[, string2])Function: return to insert a string2 character in the X characterin length on the location of string 1. If the length of the string2is less than X characters, according to the need for replication.If string2More than X characters, only the front of the stringl X of each character is used. If string2 is not specified, then use a space to "11. X is used to display the actual length of the string lengthratioBe long? The behavior of RPAD and LPAD is very similar, except that it is on the right and on the left is not filled?Example:Select RPAD ('123456789', 18,' abc') from dual;123456789abcabcabc10,RTRIMSyntax: RTRIM (stringl, [, string2]])Function: returns and deletes the character that appears in the stringl from the right? The string2. string2 is set to a single space by default? The database will scan stringl and start from the rightTo the first character in string2, the results will be returned tothe behavior of RTRIM and LTRIM are very similar?Use location: process and SQL statement.Example:Select RTRIM (T23456789'56789') from dual;One thousand two hundred and thirty-four11,SOUNDEXSyntax: SOUNDEX (string)Function: returns the sound representation of string? This is very helpful for comparing two words that are different in spelling butsimilar in pronunciationUse location: process and SQL statement?12,SUBSTRSyntax: SUBSTR (string, a[, b])Function: returns a substring of B that starts with the letterstring as the value a. If a is 0, then it is considered to startfrom the first character? If it is a positive number, the returncharacter is leftIf the B is negative, then the returned character is calculatedfrom the right end of the string from the right to the left?If B does not exist, then it defaults to the entire characterString. If B is less than 1, then NULL? will be returned? If a or B uses floating point numbers, the value is first converted to aninteger before the processing is performedUse location: process and SQL statement?13,TRANSLATESyntax: TRANSLATE (string, from_str, to_str)Function: return every character will appear in the from_str toreplace the corresponding characters in the to_str after the string? TRANSLATE is a superset of REPLACE,s functions?If from_str is Ionger than to_str, then the characters in from_strbut not in to_str will be deleted from string because they have no corresponding replacement characters? To_str cannot be emptyThe empty string that is.Oracle NULL, and if any of the parametersin TRANSLATE NULL, then the result is NULL?Use location: process and SQL statement?14,UPPERSyntax: UPPER (string)Function: returns uppercase string? is not letter character unchanged? If string is CHAR data type, then lhe result is alsoCHAR type. If string is VARCHAR2 type, thenThe result is also VARCHAR2 typeUsing location: procedural statements and SQL statements?Character function return numbetsThese functions accept character parameters and return numericresults? The parameter can be either CHAR or VARCHAR2? Although many of the actual results are integer values, the return resultsare simple NUMBERThe type, without any precision or scale definition?16,ASCIISyntax: ASCII (string)Function: the database character set returns the decimalrepresentation of the first byte of the string. Note that thefunction is still called ASCII. , although many character sets arenot 7 bits, and ASCII. CHR and ASCII are mutually exclusiveInstead, the function. CHR gets the response character of the given character encoding? ASCII gets the character encoding of the given characterUsing location: procedural statements and SQL statements.17,INSTRSyntax: INSTR (stringl, string2[a, b])Function: get the position of the string2 in string 1. Check fromthe left when stringl starts at a, and if a is a negative number,then stringl starts from the rightThe location of the B appears to be returned? Both a and B are set to 1 by default? This will return to the location of string2 forthe first time in string 1. If string2 is not available under the Aand B rulesFind, then the calculation of the return to the 0. position isrelative to the starting position of the stringl, regardless of thevalue of a and BUsing location: procedural statements and SQL statements?18,INSTRBSyntax: INSTRB (stringl, string2[a, [b]])Function: the same as INSTR, but the operation parameters of the character position is a byte?Using location: procedural statements and SQL statements?19,LENGTHSyntax: LENGTH (string)Function: the length of the byte unit that returns string? The .CHAR value is filled with the space type? If string is a data type CHAR, its ending spaces are computed to the middle of thestring lengthIf string is NULL, the return is NULL, instead of 0.Using location: procedural statements and SQL statements?20,LENGTHBSyntax: LENGTHB (string)Function: returns the length of string in bytes? For single byte character sets, LENGTHB and LENGTH are the sameUsing location: procedural statements and SQL statements?21,NLSSORTSyntax: NLSSORT (stringt, nlsparams])Function: get the string bytes used to sort string. All values areconverted to byte strings, so that consistency is maintainedbetween different databases. The role and function of NlsparamsNLS_INITCAP is the same? If you ignore the session parameters,using the default sort?Using location: procedural statements and SQL statements.The special function of dump Oracle (convert), several (substrb),lengthb (),()2011, 01 Friday, 14, 8:43 p. m.First, DUMP () functionDUMP (w[, x[, y[, z]]])[function] return data type, and the byte length in the internalstorage location.[parameters]W is a string of various types (such as character, numeric, date )X can be expressed as a return position, which can be: 8, 10, 16 or17, respectively: 8/10/16 and character, default is 10.Y and Z determine the position of the internal parameters[return] type <[length] >, index number 1 [bit / symbol, number 2,number 3,???,20]???Such as: Typ=2 Len二7: 60,89,67, 45, 23, 11102SELECT DUMP ('ABC', 1016) FROM dual;Results: Typ二96 Len二3 CharacterSet=ZlIS 16GBK: return 41,42,43 Code data type0 correspondence VARCHAR21 correspondence NUMBER8 correspondenee LONG12 correspondence DATE23correspondence RAW24correspond to LONG RAW69 correspondence ROWID96 correspondence CHAR106 correspondence MSSLABELYour meaning is as follows:1.types: type Number, Type=2 (the type of code can be found from the Oracle document)2.length: refers to the number of bytes stored3.symbol / index bitIn the memory, Oracle of positive and negative numbers were storage conversion:Positive: add 1 storage (to avoid Null)Negative number: reduced by 101, if the total length is less than21 bytes, and finally added a 102 (for sorting needs)Exponential scaling:Positive: index 二symbol / index - 193 (the highest is 1 is positive)Negative numbers: index 二62 - first byteFourStarting from < 1> > is a valid data bitFrom the beginning of "digital 1> is the highest effective bit, the numerical calculation method for storage:Add the results calculated below:Each digit number is multiplied by 10(T (index -N) (N is the sequential number of significant digits, N二0 of the first significant bit)5. ExamplesSQL> select dump (123456.789) from dual;Return: Typ二2 Len=6: 195,13,35,57, 79, 91<index >: 195-193 二 2<number 1> 13 - 1 = 12 *10(T (2-0) 120000<number 2> 35 - 1 二34 *10(T (2-1) 3400<digital 3> 57-1 二56 *10(T (2-2) 56<digital 4> 79-1 二78 *10(T (2-3).78<number 5> 91 - 1 = 90 *10(T (2—4).009One hundred and twenty-three thousand four hundred andfifty-six point seven eight nineSQL> select dump (-123456.789) from dual;Return: Typ二2 Len二7: 60,89,67,45, 23, 11102Algorithm:<index > 62 - 60 = 2 (the highest is 0, representing negative)<digital 1> 101-89 二12 *10(T (2-0) 120000<digital 2> 101-67 二34 *100° (2-1) 3400 < digital 3> 101-45 二56 *10(T (2-2) 56< digital 4> 101-23 二78 *100" (2-3). 78 < digital 5> 101-11 二90 *100° (2-4). 009123456. 789 (-)Now think about why adding 102 at the end is the need for sorting,and -123456? 789 is actually stored in the database60, 89, 67, 45, 23, 11-123456.78901 in the database for the actual storage60, 89, 67, 45, 23, 11,91Visible, if not added at the end of 102, in the sorting will appear-123456.789<-123456.78901 situation.Two, substrb functionSubstr and substrbPreviously known to have substrb, lengthb and other functions, butalso know that they are byte to calculate the length, not used, anddo not quite understand where to use them? Has been with substr, length, with the character to calculate the length, it seems to methat VARCHAR2 and char are inside the character, so naturally it is impossible for byte to calculate the unit length, will not usethese functions, but the fact that I was wrong? There is a procedure error, insert always reported to the table 1401 error,look at the program, feel very strange, the 50 is the error fieldof the target table length, insert corresponding to the fieldmethod is substr (1, 50 * *), what could go wrong? Some doubts arethe reason why Chinese characters are double byte, so try to change substr (* *, 1, 50) to substr (* *, 1, 25), and sure enough, ok.The Internet to find the reasons, find the answers in the asktom.The definition of the database in the VARCHAR2 and char is thelength of the field in two ways, in bytes or characters accordingto the leng th of the byte, by def in it ion is VARCHAR2 (n byte)or char (n byte), which is the default length of definition, thatis to say, we usually use VARCHAR2 (n) or char (n is defined bybyte length), according to the definition of the length of thecharacter is VARCHAR2 (n char) or char (n char), this definitioncan ensure that the field has sufficient space for the storage ofthe required character, whether these characters in length is thenumber of bytes? This is the cause of the error we encounter, the database character set is a multi byte character set, that is tosay Chinese characters accounted for more than one byte, and thesource field are Chinese, so substr (1, 50 * *) byte length mayreach 100, more than 50 natural target text in paragraph?To sum up some experience and lessons, think that before building a table, if a field needs to store Chinese, it is best to make surethat the length of the field is determined by the characters?If it is a character and the database character set is multi byte,then the table should be defined by the length defined by thecharacter to define the length of the field.How many bytes does a Chinese character have?According to coding form:GB231280 encoding is 2 bytes (Byte) contains 20902 Chinese characters, the encoding range is 0x8140-Oxfefe?GB18030-2000 (GBK2K) expands the Chinese characters on the basis of GBK, and increases the shape of Tibetan, Mongolian and other ethnic minorities? Is a variable length encoding, the second byte is compatible with GBK; four part is the extension of the byte font,bits, the encoding range is the first byte 0x81-Oxfe, two bytes of0x30-0x39, three bytes, four bytes of 0x30-0x39 0x81~0xfeUnicode range is generally used for \U0000-\UFFFF, for CJK EXT Barea Chinese characters, the scope is greater than \U20000UTF is divided into UTF-8/16/32 according to its basic length? Among them:UTF-8 is a variable length encoding, each Unicode code pointaccording to different ranges, you can have 1-3 bytes of differentlength?The length of UTFT6 is relatively fixed? As long as the character is not greater than \U200000, each Unicode code point uses 16 bits,i? e. 2 bytes, and exceeds the part using two UTF-16, i. e., 4bytes? According to the order of high and low byte, it is dividedinto UTF-16BE/UTF-16LE.The UTF-32 length is always fixed, and each Unicode code point is represented by 32 bits, 4 bytes? According to the order of high and low byte, it is divided into UTF-32BE/UTF-32LE.In general GB231280, so in most cases is 2 bytes?Technical experience of the differences between substrb and other functions due to different character setsIt has always been thought that English letters, numbers and otherspecial characters make up one byte, and Chinese characters make up two bytes? However, some mistakes have been made in the substrb used recently in Oracle database programmingThe substrb function is a string that intercepts a specific lengthin bytes? After I use it, I don,t get the desired result, becauseit's in the Chinese character stringAnd then I did the test:Substrb ('size abc', 1, 3) results: 'big'Substrb ('size abc', 1, 4) results: 'big' (containing a space)Substrb ('size abc,, 1, 5) results: 'big' (co ntaining two spaces)Substrb ('size abc,, 1,6) results: 'size' The above results suggestthat the database to a Chinese characters as three bytes? In the Forum on test results of other users is Chinese charactersaccounted for two bytes? I just know later, the database selectioncharacter set is UTF-8 form, the character set Chinese charactersfor more than two bytes will thus? The other on byte operationshould also function Chinese characters will have more questions,such as the lengthb function. It seems that after a lot ofattention.In addition, setting for the Chinese characters with the samestring processing function may have different results in differentcharacter sets? In the online search for the number of bytes onthe different character sets the custom Chinese characters whilesummary. Results there is no satisfactory answer, are veryfragmentary. After a time and then look for it.Three, convert () functionHow does Oracle SQL convert US7ASCII string encoding to utf8 orGB2312 encoding?Using CONVERT (char, dest char set,, source_char_set]) functionsWindows can modify theHKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraDblOg_homel nls_lang value directlySIMPLIFIED CHINESE_CHINA. ZHS16GBKOr use the convert functionSQL codeSQL> select CONVERT (datatype,? US7ASCIT ) fromBSTH_SYS_FIELD_ALIAS;CONVERT (DATATYPE,'US7ASCII') gfdfghdf? ?? ?SQL〉选择转换(数据类型的zhsl6gbk v)从bsth_sys_field_a.lias;(数据类型转换,‘zhsl6gbk”)gfdfghdf中文。

相关主题